PostgREST Tutorial: APIs made easy
notso.boringsql.com
notso.boringsql.com
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?
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.
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.
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.
Plus it's easier to get concurrency right if you push as much of the business logic into the RDBMS as SQL or similar.
Taking a look at the docs here:
https://postgrest.org/en/v12/references/auth.html
https://postgrest.org/en/v12/explanations/db_authz.html
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:
current_setting('request.jwt.claims', true)::json->>'user_id';
Anyone used both and can offer a comparison?
I’m also trying to imagine validation. Parsing SQL error strings? Kill me now.
Even if you have row-based security.
For example, https://play.clickhouse.com/ is an open public playground, and https://adsb.exposed/ is a full-featured application with HTML+ClickHouse and no dedicated backend.