The idea of PostgREST is cool, but I keep coming back to it not being very practical for most purposes.
It's slick when the default query API does everything you need. But it seems inevitable that you'll eventually need to do something it doesn't support well. Then you're building views and stored procedures. Okay, those can work, but version control, unit testing, composeability, etc just aren't at the level of any mainstream web framework. Not to mention logging, tracing, etc. The complexity is going to get unmanageable fast, and then what?
And the auth. It probably works okay with a few dozen users who are all trusted. Would you trust the Postgres auth system with tens of thousands of users, many of who are internet randos who may be malicious? It feels like a recipe for disaster.
But then if you're only supporting trusted users doing basic stuff, why not let them use a regular DB client rather than this API?
Postgres RLS is sophisticated enough to implement any auth model I've seen. The issue there is the same as the rest: that's rare expertise compared to a web framework and there's not much tooling to verify your implementation matches your spec. It's not how much do you trust postgres it's how much do you trust your knowledge of postgres.
That said I've worked on a profitable company's main product that was a full blown webapp running on postgrest and it was awesome. If you do have the expertise in house, and are willing to build some custom tooling around it, it's ideal for certain applications.
IMO its niche is nearly pure backend teams building out prototypes and proofs of concept, or feeling out a market or new product. If it works and you want to throw more resources at it, it's a smooth transition to just start building a real frontend around the DB you already have.
If you have an application that is mostly just relational data, and if all the business logic is easy to write as SQL, then PostgREST is essentially a no-code REST API for your database, and it's way less effort than anything you'd do in Java/Golang/Python/Rust/C++/whatever.
Plus it's easier to get concurrency right if you push as much of the business logic into the RDBMS as SQL or similar.
If you need to built quick, you can get all of this configured out-of-the-box with something like Supabase. If I had to set up all the roles and security myself, I don't know if I'd use it.
The auth is workable - you can authn/z individual users, which is basically what you need with a real backend. Personally though, I think doing auth declaratively is a bit harder than doing it imperatively in code.
If you're just 1 or 2 devs on a fledgling product, I would dare say all your concerns about testing/version control/composability are totally worth it to be able to build fast (and some of it is mitigated with Supabase's setup anyways).
Personally, I think the idea is to eventually migrate to a true backend, without requiring any changes to the schema, and hopefully avoid lock-in.
On concerns about testing etc. I can see letting automated testing, keeping a clean version control history, good architecture, etc slide at an early stage in the name of building fast. To me, that looks like building stuff in a more mainstream language and framework, but letting yourself be a little bit sloppy about those things. The difference is, the tools you are using don't actively preclude those things, so you keep the option open of adding them in later as needed, as gradually as makes sense. If you're building in one of these limited but all encompassing frameworks like PostgREST, sure you can probably go fast at first, as long as you're coloring within their lines, but as soon as you need something more or need to add some of that other stuff in, you hit a wall, and your only option is to do basically a complete rewrite.
Is there a reason you can't do both? I.e. use PostgREST until you can't anymore (and hopefully get an MVP out of that), then create a backend that runs in parallel and implements whatever PostgREST can't?
It's tech debt that will likely need to be cleaned up at some point, but endpoints could be migrated one at a time, as needed. It seems fairly clean as far as dealing with tech debt goes.
You are not wrong here. This is what I'm trying to get to with boringSQL - create more 'packaging' around things to improve developer UX while developing SQL based solutions. Be it APIs, complex apps and everything around.
It doesn't seem like current_user_id() is a provided function, and the docs claim nothing else is done with the JWT except validating it. It looks like your claim already includes user_id, so you'd have to get it from the claim using:
I really didn't understand what the tutorial is doing with JWT. BTW, PostgREST supports JWT for authentication, so there's nothing to do here unless this application is a sort of JWT issuer (but I really didn't get that sense at all).
I'm interested in this because auth(z/n) is front and center. But I'm in love with Pocketbase because it's amazing and though it doesn't have row level security it definitely has an amazing integrated rules system that is almost as good and arguably more flexible.
PostgreSQL connections don't scale up as well as some other databases. Every connection gets a huge chunk of server memory so you'll run out of memory very fast.
It's slick when the default query API does everything you need. But it seems inevitable that you'll eventually need to do something it doesn't support well. Then you're building views and stored procedures. Okay, those can work, but version control, unit testing, composeability, etc just aren't at the level of any mainstream web framework. Not to mention logging, tracing, etc. The complexity is going to get unmanageable fast, and then what?
And the auth. It probably works okay with a few dozen users who are all trusted. Would you trust the Postgres auth system with tens of thousands of users, many of who are internet randos who may be malicious? It feels like a recipe for disaster.
But then if you're only supporting trusted users doing basic stuff, why not let them use a regular DB client rather than this API?