Also it doesn't have to be all or nothing. For example, there might be an API endpoint needed where there are multiple mutations needed inside a transaction but we just route between that custom backend and postgrest using nginx.
Other things we use:
- dbmate for migrations: https://github.com/amacneil/dbmate
- postgrest-client, a library I developed for constructing type-safe queries to Postgrest in Elm: https://github.com/alex-tan/postgrest-client
In many situations, some kind of formal migration management is obligatory and my guess is that with PostgREST you will often want to test your migrations on a current database snapshot before deploying it in production...
That's like one of the oldest RDBMS best practices: no direct to table access, with every application (or class of business user with direct access to the DB) having access through a tailored selection of views so that apps/users are largely isolated from DB changes.
> and a separate schema per API version containing views
You'd probably only need a separate schema for semver-major API versions, minor versions would necessarily be supersets which could be accommodate by backwards-compatible schema extension.
> and dealing with the pain of rewriting the views for all the supported API versions when you need to change your data model.
With a well-designed normalized relational model, most changes to the base model are adding attributes or tables, which will have zero impact on view definitions to maintain an existing API. The next most common is factoring an existing attribute out to a different table because what was conceived of as a 1:1 relationship becomes 1:N, which requires adding a join to view defs where that attribute is involved.
It’s like, no matter how low-level you go, at some point someone had a problem with components being tightly coupled and introduced an indirection layer “for next time”.
The same as read operations, with views, which can be, in Postgres, automatically updatable if they are a simple thin layer over base tables, and otherwise can be made updatable by means of appropriate triggers defining the meaning of INSERT/UPDATE/DELETE on them just as the view definition defines the meaning of SELECT.
My understanding is there is no direct-to-table access with PostgREST; you're only ever interacting with views. Keep your view api versions in separate schemas. If you change your tables, yes, you'll need to update your views, but I don't see how that's much different than having to modify ORM code to accommodate schema changes.
Not sure I understand how any other approach to provisioning an data API is going to be able to avoid requiring a data model.
As for postgrest and graphQL, queries and data transformations are proof that your DB schema shouldn't be forced the the stack and onto the clients. I would argue that graphQL strived for the exact opposite of single schema and tends to 'clients should choose their own API' approach.
Still there are a tone of applications for which just the basic types are good enough and the server is just a thin wrapper.
In the end the main requirement for efficiency using it is you skill with not basic SQL/plpgSQL (stored procedure, trigger functions, views and knowledge about the effects of the used isolation level). All well documented in postgres.
Another way I've heard this phrased is "It's much easier to hire JS devs than PL/pgSQL devs."
Worse is better. Cheap JS devs to solve everything with heaps of imperative code is cheaper than elegant solutions.
Even long term this is true. Tech debt with code that people can understand and modify is cheaper than tech debt with an elegant DSL that require learning and mental ability beyond if then else.
I've recently seen an uptick in "Postgres-native" stuff being built, though. So maybe there's hope for the future.
I'm a fan of the concept and would love to have a middle layer as thin as postgrest, but authentication/authorization has been my roadblock. I still use node as an app server because of passport. I had not heard of the postgrest starter kit before today and will check out https://docs.subzero.cloud/athentication-authorization-flow/
If they correct the typo before I get to reading it, it will be https://docs.subzero.cloud/authentication-authorization-flow...
If however you do only want to authenticate the users yourself, the login function can be very simple [2]
1. http://postgrest.org/en/v6.0/auth.html#jwt-from-auth0
2. https://github.com/subzerocloud/postgrest-starter-kit/blob/m...
Even row-level access control, while not widespread any way, is often not enough. Complex validation in SQL can be done, of course, but SQL isn't exactly meant for these things, IMHO.
Maybe I just don't like the idea of putting all my application logic into SQL statements.
What I like about Django's ORM, for example, that you can declare a field to be an IP address, and this not only does the validation (which SQL can do easily, I am sure), but this declaration also carries over to HTML forms, admin interface, REST API serialization and HTML Templates.
It’just the tools you linked didnt prioritize the development workflow from the point of view od a backend developer (code in files, git, tests, migrations) but from the frontend developer perspective (ui to create tables)
Very very old videos but you can get the core idea https://begriffs.com/posts/2014-12-30-intro-to-postgrest.htm...
I just define my model classes. Graphql resolvers look like regular rest endpoints. Everything else is generated from there and I even get compile errors for my apollo client queries too.
I really love the typesafety of it all!
If you are actually changing variable names then your client would fail regardless of what you're using
Postgrest was better, but we had trouble getting it to work with JWT, and have a reasonable permissions model.
Postgrest was DAMN fast though, it was really nice to work with.
In the end we had to write our own.
We recommend every production app to setup Allow Lists to prevent arbitrary querying.
For the record, ActiveRecord models do not specify, for example, table schemas - they are loaded from the database in the runtime, and migrations are not really necessary - they provide schema versioning with a rollback possibility, something that AFAIK the databases don't usually offer. Rails (and Ruby) is actually so non-verbose and non-repetitive, that a lot of people is criticising it for the very reason, usually complaining about it being too "magical".
But stand-alone migration tools like Sqitch do.[0]
The Django ORM is used pervasively throughout django and third party libraries. You can compose and reuse querysets in Templates, Form validation, authorization and so on. You can create a CRUD admin interface and a REST API with almost no extra configuration.
It also has a very mature migration system, meaning you can have your migrations in code, and apply them in production or in multiple environments.
One problem I expect with PostgREST (haven't used it yet) is that validation and authorization may suffer. At the very least this will have to be configured either as SQL data or as SQL stored procedures, with SQL not being the strong suit of most frontend people.
They probably have safeguards against SQL injections through their API. Logic validation will be harder, and Postgres only supports rudimentary permissions, so all of this has to be implemented in stored procedures, also.
My main concern with PostgREST is that the database is stateful and persistent, and that you have to put more code in there. Which means every deployment is going to be a migration, no matter what. You better don't forget any of the state changes you made in your development database. Version management will certainly be harder.
Please don't get me wrong. I am sure PostgREST has perfectly valid usecases, and I like Postgres a lot. I am just dropping a few thoughts on why I would prefer a traditional application between database and frontend.
Additionally, if you are using a lossy intermediate query representation instead of plain SQL, you will end up losing a lot of he database performance and find yourself doing complex joins on your front end.
But as I said, there are trade offs. I didn’t say it’s always a bad idea.
Thanks a lot for sharing.
This approach had widespread adoption in the 1990's, most notably via PowerBuilder[0].
> Is there some inherent architectural flaw or just not enough incremental benefit?
Yes, there is an inherent architectural flaw with two-tiered systems in that they force a system to choose to place complexity either in the GUI or in the RDBMS. This often leads to "Jenga Programming" maintenance techniques which cause the code base to take a life of its own.
It turns out that this does not scale.
PowerBuilder didn't encourage you to know your DB ins-and-outs and use all of its features. It was DB agnostic and through its DataWindow you got a RAD way of doing CRUD for a desktop app but you still needed to add application logic in PowerScript.
PostgREST on the other hand pushes you to know PostgreSQL.
A more suitable analogy would be to something like Oracle Apex[1].
[1]:https://en.wikipedia.org/wiki/Oracle_Application_Express
My intent was not to equate PostgREST with PowerBuilder, but instead to use the latter as a well-known example of a two-tier system.
> PowerBuilder didn't encourage you to know your DB ins-and-outs and use all of its features.
While PowerBuilder was "DB agnostic", as you identified, non-trivial applications relied heavily on RDBMS logic (stored procs, views, etc.). So while PB may not have "encouraged" RDBMS specific implementations, those which I encountered were never without.
Granted, the client connection protocol vastly differs between the two offerings. Yet the system architecture is shared IMHO.
Even here [1] i count 4 places to put logic into besides frontend
1. https://github.com/subzerocloud/postgrest-starter-kit/blob/m...
The first sentence of site reads thusly:
> PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API.
If there is a database on one end and a client on the other, with no intervening system between them, that is the definition of a two-tiered system.
The project home page declares this to be its raison d'être.
> ... you can very well have logic in the proxy layer and then have logic below the db layer ...
Why have a "proxy layer" to "then have logic below the db layer"? Why not just let PostgreSQL manage the data and have...
wait for it...
A system which defines the application logic? I mean, that is what you described in a roundabout way.
> If i apply the same logic (two tier bad) that i can say every other way of building an api is two tier since you have the frontend and then the rest of the code in php/node... the db would not really count as a later since in those scenatios ppl use it as a dumb store
No, what you describe as "an api" (which assumes an external client that interacts with it) and "then the rest of the code" (which is colloquially known as application logic) using PostgreSQL (what you call "a dumb store"), is known in the industry as a "three-tiered system."
Not a "two-tiered" system.
This is because there are three significant actors involved in system operations:
1 - something that requests the service provided (browser, phone, desktop binary, etc.).
2 - something that provides the service (application logic).
3 - something that persists relevant state as determined by #2.
See what happened there?
Three distinct things; a client, something that does stuff, and something that stores stuff.
Similarly after a week of vuejs I couldn't stop thinking redux/vuex should be an embedded sqlite ...
I think it's because PostgreSQL got major features for web development — like JSON and RLS — not too long ago and most developers are just catching up.
JSON was added back in 2013[1] and RLS back in 2016[2].
Separately, I've got a fairly advanced setup of schema/seed lifecycle flow in a Graphile-powered application that will likely be open-sourced at some point. The simplicity of the architecture makes these things relatively easy to spin up from scratch.
https://github.com/o1lab/xmysql/
Give it a try.
(Im the author)
- There is no ORM that you can control. It doesn't mean that there is no transformation needed though. It just simply means that you lose control over it. All the work which were once under your control now is handled by the database layer. Of course you can go to that layer and change the logic of the framework yourself (by sending strangely formatted HTTP requests according to the doc), but then you gain nothing by using this framework. Of course this problem stems from the same fact that there are no separation of concerns. So basically the framework is simply ignoring the fact that there is an inherent object relational impedance mismatch. Of course the authors are aware that there is a mismatch but they simply sweep it under the rug by saying that:
"The PostgREST URL grammar limits the kinds of queries clients can perform. It prevents arbitrary, potentially poorly constructed and slow client queries. It’s good for quality of service, but means database administrators must create custom views and stored procedures to provide richer endpoints."
https://postgrest.org/en/v6.0/api.html#custom-queries
- No testability: pl/pgSQL is a really poor language to write unit/integration tests for, mostly because it's ancient and it's procedural and there are no native frameworks to do so. Of course you can use pl/Python and others, but then again, why would you when you can write the same thing in Python without embedding the code in Postgres, and the Python ORM would take care of having the same performance on the database level for you.
- Lack of tools for migrations, etc. The list goes on.
> There is no ORM that you can control
You don't need an ORM to control your database. If you treat the database as the source of truth(this would be the architecturally defined place) you can do data transformations with plain and short SQL. The great thing about this is that you can leverage PostgreSQL rich functionality and extension ecosystem and you don't have to worry if your ORM supports a pg type or if you need a plugin for that.
> by sending strangely formatted HTTP requests according to the doc
The "strangely formattted request" wouldn't be so strange if you note that PostgreSQL cast operator is "::"(e.g. `select col::json from table`).
> Python ORM would take care of having the same performance on the database level for you.
That's just false. ORMs have to point to the lowest common denominator of databases. They don't use the full potential of pg(things like LATERAL joins, json_agg, etc). PostgREST queries are specifically tailored for pg and they're being constantly improved.
> No testability
I've shared this before, but by using pgTAP[1] you can get native SQL tests.
HNers have shared migration tools in other comments.
[1]: https://pgtap.org/
One of the most significant architectural defects of this approach is by demanding the persistent store provide functionality beyond solely managing data, it both conflates disparate concerns (business logic + data management) as well as unduly complicates system evolution.
> If you treat the database as the source of truth(this would be the architecturally defined place) you can do data transformations with plain and short SQL.
And if you treat the database as the source of truth for managing data, then the idea of making it an application engine would be anathema.
We argue that all "business logic" is in fact "data logic" and they're the same concern. There are several comments on this post on how to evolve this approach(only use VIEWs/sps in a single schema and expose this to clients).
> And if you treat the database as the source of truth for managing data, then the idea of making it an application engine would be anathema.
Why? Could you elaborate more on that?
> Why? Could you elaborate more on that?
Sure.
Your previous declaration of:
> We argue that all "business logic" is in fact "data logic" and they're the same concern.
Is both the source of my position and your question.
For at least the last 50 years of which I am aware, the idea of "data rules all" has inextricably lead to migrating system logic closer to where the data resides. In the last 35-ish years, this meant defining application logic in stored procedures executed in an RDBMS (such as what you describe).
This architectural philosophy is commonly know as "client/server", or "two-tier systems," and has been studied extensively (as I am sure you are aware). What makes this relevant to the argument found in the "Motivation" section on the PostgREST site is:
Using PostgREST is an alternative to
manual CRUD programming.
The PostgREST philosophy establishes
a single declarative source of truth:
the data itself.
PostgREST is a standalone web server
that turns your PostgreSQL database
directly into a RESTful API.
Now, consider a different system architecture commonly known as "three-tier" or "multi-tier." The philosophy of this type of system is largely defined by separation of concerns. This is to say, have each tier be responsible for a single "layer" involved in system definition.
In multi-tier systems, the persistent store (RDBMS) is solely responsible for ensuring data integrity. It may, in fact, service disparate components as well as disparate components may use more than one persistent store. An example of the latter is PostgreSQL and Redis deployed in concert. Programs, components, and/or systems which interact with a persistent store are responsible for providing the behaviour expected by their clients whilst the persistent store is expected to enforce rules regarding the relationships it manages.
Since the persistent store is only asked to fulfill a data integrity contract, having it also attempt to be an application engine would clearly conflate concerns, limit what persistent stores could be used, as well as result in assumptive implementations. Hence anathema to those who would treat the persistent store (database) as the source of truth strictly for the one concern which it was designed to address:
Managing data.
There is a great talk[1] that @felixyz shared regarding the shortcomings of the Persistence Layer approach and the advantages of Processing Engine approach(which we at PostgREST embrace).
In summary, having the "business logic" away from the db results in worse performance: more network roundtrips, ineffective cache utilization, wasted CPU cycles in traversing your programming language VM(plus all libs) code paths. Turns out this actually puts more strain on the DB and makes your whole system scale worse.
> Hence anathema to those who would treat the persistent store (database) as the source of truth strictly for the one concern which it was designed to address: Managing data.
I think marrying these concerns and having them in a single place is more of a benediction for OLTP systems development and maintenance. After all, PostgreSQL has gotten more powerful features over the years and it has evolved to do more than data management(LISTEN/NOTIFY, JIT, etc).
I really recommend watching that talk. Btw, slides here[2].
[1]: http://www.prohuddle.com/webinars/ToonKoppelaars/ThickDB.php
[2]: https://www.nloug.nl/downloads/ogh20170615_toon_koppelaars.p...
No IDE support, no debugging support, poor language choice. It's just simply screaming NO NO NO NO NO NO NO on every level of the framework.
Not to mention that these days it is more important than ever to make the code also executable in cloud environments like GCP Cloud SQL or AWS RDS.
> You don't need an ORM to control your database.
You are actually wrong about that and you are basically the living proof that I was right. You didn't even try to address what I was writing about object relational impedance mismatch and inheritance.
> you can leverage PostgreSQL rich functionality
I think this is a slightly outdated world-view. As I mentioned above these days the most important driving factors are not what a specific DB vendor supports but mostly what the cloud providers do. And many of the cool things are only supported by plugins in Postgres anyway.
> you can do data transformations with plain and short SQL
You guess what? You can do the same in ORMs too! In any language which has a Postgres DB driver! No one is stopping you from that.
Can you elaborate on that? PostgreSQL is an object-relational dbms btw, it supports inheritance[1]. Though I don't know exactly what impedance are you referring to since PostgREST doesn't use an ORM.
[1]: https://www.postgresql.org/docs/12/tutorial-inheritance.html
- https://www.graphile.org/postgraphile/
- https://hasura.io/
Love the idea of having APIs flow out of a single set of schema definitions. The Rails style of speccing a model, migrations, and controller/serializer or graphql types feels overly verbose and repetitive.
To me the biggest thing these groups could do to speed adoption is flesh out the feature development / test story. For instance, the postgraphile examples have development scripts that constantly clear out the DB and no tests. Compared to Rails, it's hard to imagine how you'd iterate on a product.
Are there other reasons this hasn't seen more widespread adoption? Is there some inherent architectural flaw or just not enough incremental benefit?