> It is recommended that you don’t expose tables on your API schema. Instead expose views and stored procedures which insulate the internal details from the outside world. This allows you to change the internals of your schema and maintain backwards compatibility. It also keeps your code easier to refactor, and provides a natural way to do API versioning.
Which is, you'll notice, the same best practice used for decades by DBAs who need to serve multiple applications connecting to the same database.
(Also, it's not exactly uncommon that a data store API would have a single client which you control - eg. a single webapp, an internal application... in which case you can go nuts with breaking changes.)
Clearly, the Database's job contrary to what the name suggests is to not only stick data into it but also a bunch of business logic that's essentially untestable. No one needs domain models when you've got database tightly coupled with behavior and logic of your business.
What could go wrong?
The whole point of the API is that it is a repository for accessing the data from one or more databases, and packaging it up for the user to consume. It imports domain models which are totally isolated from the rest of the dependencies. The data doesn't even have to be in the database and can come from multiple sources such as S3 or whatever. It can consume data from the user and kick off various processes or insert into the database. It is a generic interface that does more than just CRUD in the DB.
There is a risk of mixing storage/business logic, and you might end up being tied to postgres, but it's manageable using functions/views/schemas. And its also a really convenient place to do some business logic, as all your data is right there and available via a very expressive/extensible query language.
It is, however, a pretty common design to have all your data flowing into a database, and being served from there. Indeed, it's probably the simplest and most common design for a basic web application. PostgREST or Hasura can do excellent work there.
In the end they are projects with semi-same goals but different approaches. I went with Postgraphile and I haven't regretted it one bit, but no reason you can't try both, they are easy to set up and get a lay of the land.
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.
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?
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.
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.
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.
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.
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.)
Regardless, that's no reason to say it's "not a good idea at all." having a schema change ripple through the stack is just a trade-off, which is often even desirable. If engineers are shipping code to production with postgrest, and attributing it in part to their success, maybe reconsider the rigidity of your architectural thinking. https://paul.copplest.one/blog/nimbus-tech-2019-04.html#api-...
I know GitHub stars are not a measure of whether something is a good idea or not - but why do you think so many have positively engaged with it?
When you have a tool like this, it's just easier to use it and call it a day - without thinking too much or learning the concepts properly.
Can you elaborate this part? Does PostgREST does that? If not, any example of it