Comment by dragonwriter | Hacker News Reader

Comment by dragonwriter | Hacker News Reader

Parent Full thread

> This is not a good idea at all.

It's an excellent idea.

> One of the main point of a REST API is to abstract away your internal data structures for the clients that makes sense.

One of the points of database views, which are much older than REST, is to abstract away your internal data structures behind facades that make sense for the clients.

> Also refactoring and database migrations should NOT change the public API of any software, especially not a REST API.

That's...inaccurate. Refactoring, including of the base storage layer shouldn't. The exposed schema of a database is an API, which is logically distinct from the base storage layer. Some designs may just expose base tables and handle mapping for the client outside of the database, but there's no fundamental reason it has to be that way.

> Now, when using something like this, even the most basic change will have a ripple effect on your whole infrastructure and break every client.

No, it won't. PostgREST exposes a single schema. There's no reason that schema should contain low-level implementation, just the views defining the public API.

> One of the points of database views, which are much older than REST, is to abstract away your internal data structures behind facades that make sense for the clients.

It makes sense to put all the logic into DB views and ditch the domain models. Forget unit tests and functional tests against company's business logic - those are pretty obsolete concepts. We don't need robust software, we need quick and dirty ducktaped APIs. Who needs data validation?

> It makes sense to put all the logic into DB views and ditch the domain models

DB views are a mechanism for representing domain models just as much as classes in an OOP language are.

> Forget unit tests and functional tests against company's business logic

Implementing functionality in the DB changes how you implement and execute tests, but it doesn't prevent testing.

If you are using a DB at all and don't understand how to test functionality implemented there, that's a problem, sure, but the correction to that problem isn't to just minimize the functionality in the database and continuing to fail to test it.

You keep talking about domain models and views as though domain models aren't written as well as read.

You keep talking about db views as though you can only read from them (an not write to them) :)

> You keep talking about domain models and views as though domain models aren't written as well as read.

Views can be writable (views with non-trivial relations to base tables require explicit specification of what behavior to execute on writes through triggers, but that's obviously true of classical domain models, too.)

I totally disagree with this approach. Sure, SQL can be used to express domain models.

Let me raise a few questions for you:

- Let's say we want to change the database from postgres to Oracle in the future. How do I go about doing it?

- How about complex logic that needs to be done in a declarative language such as SQL? SQL was not developed to write logic. You could do a lot of things in it. It is turing complete but doesn't mean you should.

- How do you debug SQL views?

- How about version control and updating views, with traceability?

- Do you think SQL is more readable for logic code than say Python? Surely, basic logic can be represented in SQL. But IMO it suffers readability.

- How about CPU/memory consumption and how do you manage to vertically scale?

You're trying to use a tool (views), not for its intented purpose. It was not meant for sticking your company's entire domain model.

This is a completely wrong approach especially in enterprise environment. Might be ok with a small project.

> - Let's say we want to change the database from postgres to Oracle in the future. How do I go about doing it?

If by “database” you mean “storage backend” (either in whole or in part), then the answer is oracle_fdw.

If you mean the API implementation, then, just as if you wanted to use a different language/platform when it wasn't implemented via Postgres originally, it's a complete reimplementation of that layer.

But, really, changing DBs isn't a root need, it's a solution, and unless we know the actual problem, we can't determine a solution (and “switch DBs to Oracle” likely isn't the best solution.

> How about complex logic that needs to be done in a declarative language such as SQL?

Complex logic is often more clearly expressed in a declarative language.

OTOH, to the extent there is a need for procedural/imperative logic, Postgres supports a variety of procedural languages, including Python.

> How about version control and updating views, with traceability?

There are a number of variations on approaches for this with database schemas in devops pipelines. Whether your schema is just base tables or includes views/triggers/etc. doesn't really make any difference here.

> Do you think SQL is more readable for logic code than say Python?

I think most appropriate of SQL, pl/pgSQL, and Python for each component is more readable than just-Python

> You're trying to use a tool (views), not for its intented purpose. It was not meant for sticking your company's entire domain model.

That’s...exactly what views were designed for. For a long time the implementations in most RDBMSs weren't fairly limited, but that's not really the case now.

> This is a completely wrong approach especially in enterprise environment. Might be ok with a small project.

Honestly, LOB apps in an enterprise environment is probably where this approach is most valuable. It might not be right for your core application in a startup where you are aiming for hockey stick growth. At least, from the complaints I've heard about horizontally scaling Postgres, I'd assume that.

Not saying either way, but something to chew on when it comes to Domain models: https://www.cosmicpython.com/book/chapter_02_repository.html

I love this book and it explicitly addresses a lot of painpoints in designing complex systems. For example, business counterparts might request writing to CSV instead of database. You want to decouple domain models from RDBMS.

How does decoupling the domain models from the RDBMS help in having an application accept CSV files as input?

Reply on news.ycombinator.com