In reality postgREST sucks... it's fine for simple apps, but for something bigger its pain in the butt.
* there is no column level security – e.g. I want to show payment_total to admin, but not to user (granted a feature for postgres likely).
* with the above, you need to create separate views for each role or maintain complex functions that either render a column or return nothing.
* then when you update views, you need to write sql... and you can't just write SQL, restart server and see it applied. You need to execute that SQL, meaning you probably need a migration file for prod system.
* with each new migration it's very easy to loose context of what's going on and who changed what.
* me and my colleague been making changes to the same view and we would override changes from each other, because our changes would get lost in migration history – again it's not one file which we can edit.
* writing functions in PlSQL is plain ass, testing them is even harder.
I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production.
Like when you can have a flask kind of app build in whatever metalanguage w/ ability to easily write tests, then and only then postgREST would be useful for large-scale systems.
For us, it's just easier to build factories that generate collection/item endpoints w/ a small config change.
>I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production.
A long time ago, I joined a company where the "DB Migration Script" was an ever-growing set of migrations that took close to an hour to run. There were the same issues of lack of context, history, etc.
Since we were a Microsoft shop, I took the following approach to use Visual Studio DB projects and the SQL Server sqlpackage tool. Every table, stored procedure, schema, user, index, etc. was represented by a single file in the project, so it would have full git history and DB changes and the code that relied on them would be in the same commmit. (Data migrations still had to be stored separately in up/down migration files)
The "build" was to create the SQLPackage dacpac file from the DB project, and deploy was to apply the dacpac to the target database and then run data migrations (which were rare). Since the dacpac represented the desired end state of the database, it didn't require a DB to be in a specific state first, and it allowed the same deploy to run as part of CI, manual testing, staging deploy, and production deploys. It also generally took less than 5 seconds.
Why wouldn't the "script" be all of the necedssary commands to create the entire database?
If any migration was necessary to transform one table structure to another, that wouldn't be useful to keep around long term, nor interesting once the new table is established. It might be kept as a historical artifact, but why would you on average care beyond what the current schema is now, along with its documentation?
> * with each new migration it's very easy to loose context of what's going on and who changed what.
> * me and my colleague been making changes to the same view and we would override changes from each other, because our changes would get lost in migration history – again it's not one file which we can edit.
> I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production.
With a declarative system, the tooling is responsible for diff'ing the CREATE statements between your current state and desired state, and then generating the appropriate DDL to transition between those states.
> A user may perform SELECT, INSERT, etc. on a column if they hold that privilege for either the specific column or its whole table. Granting the privilege at the table level and then revoking it for one column will not do what one might wish: the table-level grant is unaffected by a column-level operation.
While the OP was wrong on that, the problem I found is that Postgrest threw only slightly helpful errors when someone would try to access a column that they didn't have permission to, rather than exclude that column from the result set and maybe throw a warning, which meant that you had to maintain multiple different versions of queries. This was a while ago, but coupled with my dislike of the query string syntax and some other stylistic choices I migrated to Hasura.
I was working with supabase and in there you can't set a role for the user, it's always `authenticated`.
I was looking for something more like
```
create policy "No payment_total for user"
on todos for select (payment_total, invoice_number, payment_hash)
using (
-- Dynamic code example
(select auth.uid()) = user_id AND get_user_role(auth.uid()) != 'admin'
);
There's this extension https://postgresql-anonymizer.readthedocs.io/en/stable/ which lets you do this, eg: `MASKED WITH NULL` - though I don't have much hands on experience with it / haven't looked into it's implementation details so not sure what trade-offs it might come with.
My general feeling is that it's an extension you'd apply to your human users, whilst exempting your machine users, but it feels like it could work for both in the context of something like postgrest
Nope, this suggestion sucks. Meaning I have to come up w/ table structure that caters to front-end API.
Better idea is to create schemas, e.g. `public_users` and create views inside.
> You don't use version control and a test suite?
that's what I'm saying... we have like a 1k migration files already, what I want instead is
`my-function.sql` that always has the current code and will automatically generate necessary migrations and shenanigans, so I don't have to worry about it.
> * there is no column level security – e.g. I want to show payment_total to admin, but not to user
I think what you want is dynamic data masking. RDMBSes like IBM DB2[1] and SQL Server[2] offer it out of the box.
For PostgreSQL, there's the postgresql_anonymizer[3] extension. For which you could do something like:
SECURITY LABEL FOR user ON COLUMN invoice.payment_total
IS 'MASKED WITH VALUE $$CONFIDENTIAL$$';
Last time I tried it though, it didn't play well with transaction variables (`current_setting(my.username)`), so you could not combine it with RLS logic and application users[4]. I'll be exploring an alternative on https://github.com/steve-chavez/pg_masking.
To be fair, this discussion is more related to a PostgreSQL feature than PostgREST. PostgREST relies on PostgreSQL for all authorization logic by design.
When I used PostgREST for a readonly b2b API I recall that the recommended way (from the PostgREST docs) to employ it was creating postgresql schemas for different use cases: you create a schema that has views for tables in another schema and PostgREST exposes only the views in that schema. The views defined in the schema can, naturally, control what columns are visible, or provide whatever other abstractions one might want.
I suspect a lot of people ignore this documentation and expose tables directly, and then wonder where/how to control such things.
Yes, my memory is correct: from the PostgREST "Schema Isolation" documentation:
"A PostgREST instance exposes all the tables, views, and stored procedures of a single PostgreSQL schema (a namespace of database objects).
This means private data or implementation details can go inside different private schemas and be invisible to HTTP clients.
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."
This has all been embiggened since I last used it. Now you can configure multiple schemas to be exposed. The active user ROLE (as determined by auth) controls access to exposed schemas, and a header in HTTP requests can specify the desired schema.
Given all of this, it is entirely possible to achieve precise control over column visibility, version control, and surface whatever abstraction you can imagine. However, you are expected to fully inculcate PostgreSQL schemes, roles and other database affordances. This has always been the mentality of PostgREST: it's supposed to be a "function" that exposes some subset of a database without any magic beyond the database itself. Implicit in this is the need for adequate CI/CD tools to control database objects: you're doomed the minute you have >1 developer involved without such tools.
I hear this sentiment echoed a fair amount without really saying why it sucks. And sometimes I wonder if the writer omitted it for brevity, or is actually just echoing something they read on the internet without really believing or understanding it themselves, because saying something sucks where clearly other people don’t know that it sucks (or else why would it be so popular) positions the writer in a dialogue as mysteriously knowledgeable. Hard to argue against points you don’t even know the other person is making and all.
"I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production."
There is. Liquibase (or Flyway, or...) Add it to your build/CICD/deployment process.
I have used it even for TDD stored procedure automated integration testing + CICD deployment alongside .jar/(JVM-using Scala) regular code + SQL DML/DDL deployment for a serious production app.
We solved some of this by having a "src" folder with subfolders: "functions", "triggers" and "views". Then a update-src.sql script that drops all of those and recreates them from source files. This way we can track history with git and ensure a database has the latest version of them by running the script and tests (using pgtap and pg_prove).
Many of the migration tools I've worked with include the concept of scripts that are run every time migrations are applied, in addition to your standard migration files. So things like views, functions, SPs can go in these "every time" files rather than being duplicated in migrations every time they change.
The biggest issue with postgREST for me is that it doesn't support transactions well. You can't two 2 insert and keep a consistent state if one fails. That alone is a deal breaker.
I think this sentiment stems from users of postgrest-js[1], which is a JS library that gives an ORM feel to PostgREST requests. Under that abstraction, users don't realize they're using a REST API, instead of a direct postgres connection.
So in this case users are really asking for "client-side transactions"[2], which are not supported in PostgREST.
Yeah, I fully agree. The tooling for putting that much logic into the database is just not great. I've been decently happy with Sqitch[0] for DB change management, but even with that you don't really get a good basis for testing some of the logic you could otherwise test in isolation in app code.
I've also tried to rely heavily on the database handling security and authorization, but as soon as you start to do somewhat non-trivial attribute-/relationship-based authorization (as you would find in many products nowadays), it really isn't fun anymore, and you spend a lot of the time you saved on manually building backend routes on trying to fit you authz model into those basic primitives (and avoiding performance bottlenecks). Especially compared to other modern authz solutions like OPA[1] or oso[2] it really doesn't stack up.
REST itself is crap, at least when it comes to "APIs" serving things besides webpages. There's quite literally no obvious reason that an API actually should be RESTful besides that a lot of web developers believe that it's easier to understand. If REST is so great, then why do you keep finding yourself relying on documentation in order to use it?
I won't say REST is perfect, but I much prefer it to an unstructured api where anything goes. You didn't suggest that, but you really didn't suggest any alternative.
What's the alternative to relying on documentation? Is relying on documentation even a bad thing?
This doesn't seem like a good idea when it comes to consistently hardening an access layer. You mean I get to double the ops maintenance cost of my existing service by adding a new one? You mean I need to figure out how to set up RBAC, rate limiting, logging, and error handling in two places instead of just one?
By and large, opinions in API design has suggest against directly mirroring table structure for quite some time. The reasons are many, but they include things like migrating data sources, avoiding tight coupling with the database schema, and maintaining ultimate control over what the payload looks like at the API layer. Just in case you want to do something dynamic, or hydrate data from a secondary service. And if you still want to generate you response payloads like they came straight from the database, there are plenty of code generation or metaprogramming solutions that make providing access via an existing API layer quite simple.
This solution seems simpler only because it ignores the problems of most practical service-oriented API architectures. If end users need a direct line to the database, then get your RBAC nailed down and open the DB's port up to the end user.
> If end users need a direct line to the database, then get your RBAC nailed down and open the DB's port up to the end user.
I'd really consider creating a new table just for this "database interface" That'll let you keep evolving your internal architecture while preserving backwards compatibility for the client. That won't work in all cases obviously, but I think it's suitable for most of the "sensible" ones.
I thought the prevailing advice was to expose views (materialized or not) rather than tables directly, but I could be wrong or outdated.
I still probably wouldn’t do it, though. My “lazy” solution is usually OpenAPI code generation so I basically only have to fill in the SQL queries.
Not having any code gives me the nagging feeling that very soon I will hit a problem that’s trivial to fix in code but impossible or very stupid to do via a SQL query.
> You mean I get to double the ops maintenance cost of my existing service by adding a new one? You mean I need to figure out how to set up RBAC, rate limiting, logging, and error handling in two places instead of just one?
PostgREST has a 10 line config file, so it's trivial to setup. Monitoring, rate limiting, logging and error handling could be done by central tools like API-gateways or on the reverse proxy.
I don't understand what's the benefit of adding additional third party code into the hot-path. Adding yet another endpoint is hardly a lot of work, sometimes even auto-generated and has the benefit of being available in your existing monitoring / instrumentation environment already.
Also what about caching if people can "craft" random queries and send them straight to your PG instance?
Agree. I've always thought that PostgREST is an interesting project for some niche use-cases and teams. However, his argument about replacing GET request handling with a new tool that lives outside of/alongside your existing application architecture is not a particularly compelling argument. With properly-factored application code adding a GET (list) or GET-by-id is fairly trivial.
The only complexity I've ever run into there is implementing a pagination scheme that is not the typical "OOTB framework" limit/offset mechanism. I still don't think this makes the argument much stronger.
> a new tool that lives outside of/alongside your existing application architecture
It need not live either outside of or alongside application code. Substituting application code with PostgREST is an option.
> With properly-factored application code adding a GET (list) or GET-by-id is fairly trivial.
If it's trivial then it sounds like needless busywork to me. I'd rather generate it or have ChatGPT write it than pay a developer to write it, if it's being forced on me. I'd rather dispense with it altogether if I'm allowed.
> I don't understand what's the benefit of adding additional third party code into the hot-path. Adding yet another endpoint is hardly a lot of work, sometimes even auto-generated and has the benefit of being available in your existing monitoring / instrumentation environment already.
If your existing endpoints already support vertical and horizontal filtering, proper pagination, joining, limiting etc. then you won't get much benefit.
> Also what about caching if people can "craft" random queries and send them straight to your PG instance?
Queries can be blocked or rewritten on the reverse-proxy.
Coupling your API to your database schema is a bad idea. Once you have clients consuming that API you can no longer make changes to your database schema without also updating all of those clients and coordinating their deployments. Advice like this reads like it's coming from somebody that's never stayed in a role long enough to deal with the consequences of their actions.
> Coupling your API to your database schema is a bad idea. Once you have clients consuming that API you can no longer make changes to your database schema without also updating all of those clients and coordinating their deployments.
This is not correct. Whenever you need to change an API you need to upgrade your clients or you add a new version of the API. With PostgREST the versions of the API are served as views or functions that internally map to internal tables. It is absolutely possible to change the tables without changing the API.
Well, the key thing here is writing SQL views to do the access. Once you’re willing to do that, it’s a fairly minor distinction between using PostgREST or writing a thin, possibly even generated, API layer. But that’s exactly what people are typically not willing to do.
100% agree.
And once you are writing SQL views, why use PostgREST and not just use your own framework which abstracts away your choice of Postgres as backend database with something more database agnostic that can point to a view in any database technology? Isn't a main part of the value of an API layer to decouple you from a particular database implementation? If so, why use PostgREST?
> Isn't a main part of the value of an API layer to decouple you from a particular database implementation?
I think the value of an API layer is to provide an interface to interact with, which is a) at the right level of abstraction for the task at hand, and b) in a format that is easy to use.
> why use PostgREST and not just use your own framework which abstracts away your choice of Postgres as backend database with something more database agnostic
Because I'm sticking with PostgreSQL and don't need database agnosticism, and because I don't want to write my own framework when a better one already exists?
> Data retrieval generally does not require any custom business logic, while data-modifying requests do
This just doesn't seem to be the case in my experience, at least not in most cases. Perhaps for internal services which are completely locked down and you can freely just expose the data via a REST API, but for public facing services I just haven't found this to be the case in practice, except in extremely limited circumstances.
Yeah this is it. It's all fine until a product manager asks for analytics on this via their analytics tool of choice, and you have to say "sorry can't do that", or you have to build a complex data pipeline all because you can't do an HTTP POST to an external service.
Also database migrations are notoriously hard to get right, at scale of traffic, at scale of development pace, at scale of team, and often require a bunch of tooling. This pattern pushes even more into database transactions.
I'd rather take a boring ORM plus web framework, where you need a little boilerplate, but you get a stateless handler in Python/whatever to handle this. So much more flexibility for very little extra cost.
Very interesting, I knew about PostRest, but never checked it out. A lot of the boilerplate can probably be cut away with using PostRest.
Just wondering, what about security and especially if someone decides to DDOS your server with high-load queries? Do you try to filter/block those with NGINX?
Speaking of postgrest, it looks like the article links to `www.postgrest.org` which has been "hijacked"? The correct url should be https://postgrest.org
Depends on who is consuming your API. Are they internal, maybe ok. Anything else, you likely need a transformation layer and also take into account Auth, rate limiting etc. PostgREST sounds like giving read only access to your DB and I doubt it would be as customizable.
Readit News
* there is no column level security – e.g. I want to show payment_total to admin, but not to user (granted a feature for postgres likely). * with the above, you need to create separate views for each role or maintain complex functions that either render a column or return nothing. * then when you update views, you need to write sql... and you can't just write SQL, restart server and see it applied. You need to execute that SQL, meaning you probably need a migration file for prod system. * with each new migration it's very easy to loose context of what's going on and who changed what. * me and my colleague been making changes to the same view and we would override changes from each other, because our changes would get lost in migration history – again it's not one file which we can edit. * writing functions in PlSQL is plain ass, testing them is even harder.
I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production.
Like when you can have a flask kind of app build in whatever metalanguage w/ ability to easily write tests, then and only then postgREST would be useful for large-scale systems.
For us, it's just easier to build factories that generate collection/item endpoints w/ a small config change.
A long time ago, I joined a company where the "DB Migration Script" was an ever-growing set of migrations that took close to an hour to run. There were the same issues of lack of context, history, etc.
Since we were a Microsoft shop, I took the following approach to use Visual Studio DB projects and the SQL Server sqlpackage tool. Every table, stored procedure, schema, user, index, etc. was represented by a single file in the project, so it would have full git history and DB changes and the code that relied on them would be in the same commmit. (Data migrations still had to be stored separately in up/down migration files)
The "build" was to create the SQLPackage dacpac file from the DB project, and deploy was to apply the dacpac to the target database and then run data migrations (which were rare). Since the dacpac represented the desired end state of the database, it didn't require a DB to be in a specific state first, and it allowed the same deploy to run as part of CI, manual testing, staging deploy, and production deploys. It also generally took less than 5 seconds.
If any migration was necessary to transform one table structure to another, that wouldn't be useful to keep around long term, nor interesting once the new table is established. It might be kept as a historical artifact, but why would you on average care beyond what the current schema is now, along with its documentation?
> * me and my colleague been making changes to the same view and we would override changes from each other, because our changes would get lost in migration history – again it's not one file which we can edit.
> I wish there would be some tool w/ DDL that you can use to define schemas, functions and views which would automatically sync changes to staging environment and then properly these changes on production.
Declarative schema management solves this, and allows you to manage your database definitions (SQL CREATE statements) just like any other codebase: https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...
With a declarative system, the tooling is responsible for diff'ing the CREATE statements between your current state and desired state, and then generating the appropriate DDL to transition between those states.
My tool Skeema is specific to MySQL/MariaDB, but I've linked some solutions for Postgres here: https://news.ycombinator.com/item?id=39236111
Its a feature postgres has.
Not sure why you think it doesn't exist or doesn't work with PostgREST.> A user may perform SELECT, INSERT, etc. on a column if they hold that privilege for either the specific column or its whole table. Granting the privilege at the table level and then revoking it for one column will not do what one might wish: the table-level grant is unaffected by a column-level operation.
I was working with supabase and in there you can't set a role for the user, it's always `authenticated`.
I was looking for something more like
``` create policy "No payment_total for user" on todos for select (payment_total, invoice_number, payment_hash) using ( -- Dynamic code example (select auth.uid()) = user_id AND get_user_role(auth.uid()) != 'admin' );
```
My general feeling is that it's an extension you'd apply to your human users, whilst exempting your machine users, but it feels like it could work for both in the context of something like postgrest
So don't put it in the same table.
> me and my colleague been making changes to the same view and we would override changes from each other
You don't use version control and a test suite?
Nope, this suggestion sucks. Meaning I have to come up w/ table structure that caters to front-end API. Better idea is to create schemas, e.g. `public_users` and create views inside.
> You don't use version control and a test suite?
that's what I'm saying... we have like a 1k migration files already, what I want instead is `my-function.sql` that always has the current code and will automatically generate necessary migrations and shenanigans, so I don't have to worry about it.
I think what you want is dynamic data masking. RDMBSes like IBM DB2[1] and SQL Server[2] offer it out of the box.
For PostgreSQL, there's the postgresql_anonymizer[3] extension. For which you could do something like:
Last time I tried it though, it didn't play well with transaction variables (`current_setting(my.username)`), so you could not combine it with RLS logic and application users[4]. I'll be exploring an alternative on https://github.com/steve-chavez/pg_masking.To be fair, this discussion is more related to a PostgreSQL feature than PostgREST. PostgREST relies on PostgreSQL for all authorization logic by design.
[1]: https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-...
[2]: https://learn.microsoft.com/en-us/sql/relational-databases/s...
[3]: https://postgresql-anonymizer.readthedocs.io/en/latest/decla...
[4]: https://www.2ndquadrant.com/en/blog/application-users-vs-row...
I suspect a lot of people ignore this documentation and expose tables directly, and then wonder where/how to control such things.
Yes, my memory is correct: from the PostgREST "Schema Isolation" documentation:
This has all been embiggened since I last used it. Now you can configure multiple schemas to be exposed. The active user ROLE (as determined by auth) controls access to exposed schemas, and a header in HTTP requests can specify the desired schema.Given all of this, it is entirely possible to achieve precise control over column visibility, version control, and surface whatever abstraction you can imagine. However, you are expected to fully inculcate PostgreSQL schemes, roles and other database affordances. This has always been the mentality of PostgREST: it's supposed to be a "function" that exposes some subset of a database without any magic beyond the database itself. Implicit in this is the need for adequate CI/CD tools to control database objects: you're doomed the minute you have >1 developer involved without such tools.
You are right that care needs to be taken in the authentication layer.
And yes, developing SQL in a shared environment is a complex task and needs to be accompanied by proper tooling.
In reality REST sucks. It should and only be used for files like WebDAV or S3.
There is. Liquibase (or Flyway, or...) Add it to your build/CICD/deployment process.
I have used it even for TDD stored procedure automated integration testing + CICD deployment alongside .jar/(JVM-using Scala) regular code + SQL DML/DDL deployment for a serious production app.
I think this sentiment stems from users of postgrest-js[1], which is a JS library that gives an ORM feel to PostgREST requests. Under that abstraction, users don't realize they're using a REST API, instead of a direct postgres connection.
So in this case users are really asking for "client-side transactions"[2], which are not supported in PostgREST.
[1]: https://github.com/supabase/postgrest-js
[2]: https://github.com/PostgREST/postgrest/issues/286
I've also tried to rely heavily on the database handling security and authorization, but as soon as you start to do somewhat non-trivial attribute-/relationship-based authorization (as you would find in many products nowadays), it really isn't fun anymore, and you spend a lot of the time you saved on manually building backend routes on trying to fit you authz model into those basic primitives (and avoiding performance bottlenecks). Especially compared to other modern authz solutions like OPA[1] or oso[2] it really doesn't stack up.
[0]: https://github.com/sqitchers/sqitch
[1]: https://www.openpolicyagent.org
[2]: https://www.osohq.com
What's the alternative to relying on documentation? Is relying on documentation even a bad thing?
By and large, opinions in API design has suggest against directly mirroring table structure for quite some time. The reasons are many, but they include things like migrating data sources, avoiding tight coupling with the database schema, and maintaining ultimate control over what the payload looks like at the API layer. Just in case you want to do something dynamic, or hydrate data from a secondary service. And if you still want to generate you response payloads like they came straight from the database, there are plenty of code generation or metaprogramming solutions that make providing access via an existing API layer quite simple.
This solution seems simpler only because it ignores the problems of most practical service-oriented API architectures. If end users need a direct line to the database, then get your RBAC nailed down and open the DB's port up to the end user.
I do, when users are willing to speak libpq. When they insist on speaking HTTP, then I use PostgREST.
I'd really consider creating a new table just for this "database interface" That'll let you keep evolving your internal architecture while preserving backwards compatibility for the client. That won't work in all cases obviously, but I think it's suitable for most of the "sensible" ones.
I still probably wouldn’t do it, though. My “lazy” solution is usually OpenAPI code generation so I basically only have to fill in the SQL queries.
Not having any code gives me the nagging feeling that very soon I will hit a problem that’s trivial to fix in code but impossible or very stupid to do via a SQL query.
PostgREST has a 10 line config file, so it's trivial to setup. Monitoring, rate limiting, logging and error handling could be done by central tools like API-gateways or on the reverse proxy.
Also what about caching if people can "craft" random queries and send them straight to your PG instance?
The only complexity I've ever run into there is implementing a pagination scheme that is not the typical "OOTB framework" limit/offset mechanism. I still don't think this makes the argument much stronger.
It need not live either outside of or alongside application code. Substituting application code with PostgREST is an option.
> With properly-factored application code adding a GET (list) or GET-by-id is fairly trivial.
If it's trivial then it sounds like needless busywork to me. I'd rather generate it or have ChatGPT write it than pay a developer to write it, if it's being forced on me. I'd rather dispense with it altogether if I'm allowed.
PostgREST doesn't add an additional third-party library. It replaces one or more third-party libraries: Spring, Django, RoR, etc.
> Also what about caching if people can "craft" random queries and send them straight to your PG instance?
Put the SQL that you would've put into your controller code into SQL functions, expose only those through PostgREST, and call it a day.
If your existing endpoints already support vertical and horizontal filtering, proper pagination, joining, limiting etc. then you won't get much benefit.
> Also what about caching if people can "craft" random queries and send them straight to your PG instance?
Queries can be blocked or rewritten on the reverse-proxy.
This is not correct. Whenever you need to change an API you need to upgrade your clients or you add a new version of the API. With PostgREST the versions of the API are served as views or functions that internally map to internal tables. It is absolutely possible to change the tables without changing the API.
If you need to change that schema you can update the views to keep them working.
and that was a solved problem from 90s C/S architecture, it's called views.
https://www.postgresql.org/docs/current/tutorial-views.html
Today's B/S shit was a painful and slow reinvention of old things.
Deleted Comment
I think the value of an API layer is to provide an interface to interact with, which is a) at the right level of abstraction for the task at hand, and b) in a format that is easy to use.
Because I'm sticking with PostgreSQL and don't need database agnosticism, and because I don't want to write my own framework when a better one already exists?
This just doesn't seem to be the case in my experience, at least not in most cases. Perhaps for internal services which are completely locked down and you can freely just expose the data via a REST API, but for public facing services I just haven't found this to be the case in practice, except in extremely limited circumstances.
Also database migrations are notoriously hard to get right, at scale of traffic, at scale of development pace, at scale of team, and often require a bunch of tooling. This pattern pushes even more into database transactions.
I'd rather take a boring ORM plus web framework, where you need a little boilerplate, but you get a stateless handler in Python/whatever to handle this. So much more flexibility for very little extra cost.
As for data database migrations, if anything about them is "notoriously hard" it isn't changing views and procedures.
Just wondering, what about security and especially if someone decides to DDOS your server with high-load queries? Do you try to filter/block those with NGINX?
You can do flexible auth in PostgREST, but also on an api-gateway. Are you right now doing rate limiting in the backend?