PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations.
Is the idea to hit the database directly from the web browser, without a server component? Do people actually do this?
I mean, I can see the appeal, but I feel that having a layer of indirection between your database and your API is useful to keep the database somewhat normalized while making your API endpoints logically useful for the problem domain.
But, I haven’t been in the web programming game in over 5 years and pretty much stuck with plain old rails back then, so I think I’m just out of touch in general.
This is not a postgres extension. It is a standalone server application (built with Haskell), so you have 3 pieces: postgres itself, postgrest and a frontend layer/app/site (it doesn't have to be a web browser btw). The intention is to replace alll of the dumbass CRUD api's that we normally build for each project. With that capability you can build like 95% of what you need on the frontend side, most likely. This assumes you will put all of your extra business logic in the frontend. Wether or not that is the right thing to do is another discussion, but this projects can be super helpful, esp for internal projects that's never on the public web. Or go for it, just tripple check that you aren't opening yourself up for attacks.
So no, it doesn't expose your postgres instance directly to the internet, that would be nuts. It's just a server application that essentially generates an endpoint for each table and it enforces all the rules and constraints of the schema (as close as possible) without you having to code all those things by hand. That's the short version, it has a ton of other great features.
I recommend you read the docs, they do a nice job of explaining what it does and where it fits.
This still sounds nuts. It lets the web client (controlled by the user, which in this context is pronounced "attacker") do any db queries/updates that the backend application in a normal app stack could. Who wants to write their whole app in browser JS anyway? Even if you do want to do that, do you end up having to write the sensitive parts it as psql stored procedures to stop the client from pwning the database?
As a solo dev/data person, an appeal of PostgREST is that I can effectively encode the database schema and the API schema in the same step. And operationally speaking, it's easy to figure out how the API is configured without having to look at application code or application configs -- just look to the database.
A simple translation layer between the database (to address your worries about indirection) is to create a materialized view for each API endpoint. Need to expose a new column? Easy, just redefine the view. Isolation through schema usage can go really far here also.
Not sure if I would put a production system on PostgREST, but I'd love to hear others' experience if they put it in prod ever.
To be more specific, you can add that layer of indirection in your database. Have a schema called something like "api", and only let it contain views/functions/procedures that reference one or more "real" schemas underneath. Point PostgREST at that. Now you can change your data model without breaking your API contract, and can do so transactionally which is a benefit you don't get with other indirection layers.
This is a good approach even when you do have a traditional API middleware layer, but why have more than one?
Biggest downside is many folks are SQL-shy so the middleware devs may not want to or may not be able to transition in this way.
https://postgrest.org/en/v9.0/index.html