The only postgREST app I ever worked on was awful. Why? Because like most of these ‘simple’ frameworks it’s only simple until your requirements get complicated. Then the original authors had to resort to writing a ton of stored procedures on the database to get back the results they wanted and that led to scalability problems. The solution, as always, is go back to SQL.
Sounds like the team picked the wrong tool for the job. If most of your endpoints contain complex backend logic, don't use PostgREST. It's made for CRUD apps, which applies to the majority of applications I come across.
If your app has the occasional custom backend logic, you can spin up a separate server (or edge function) to handle those one-offs endpoints.
I think it started as pretty much crud and then just grew features that were more complex backend logic. But I was the one brought in to fix it, not the original architect - he had left the company at this point so I don’t know the whole history.
I use PostGREST a lot, but with strong guardrails. IMO you should always have a real API layer, and use this just for a convenience to do the base load. Like all these tools, once you go to real world requirements, adapting the tool is worse than what it is trying to replace (SQL + some language and framework).
Already PostGREST is getting complicated, additions like this will make it less attractive to me.
> Already PostGREST is getting complicated, additions like this will make it less attractive to me.
This feature[1] actually simplified a lot and removed a lot of magic assumptions in the PostgREST codebase. It goes in line with REST as well — SQL functions are REST resources and HTML is just another representation for them.
Most of the code you see here is pure SQL and plpgSQL. The only PostgREST-specific part is the CREATE DOMAIN.
Right now most users view PostgREST as a HTTP->JSON->SQL->JSON->HTTP service and we're trying to turn that into HTTP->SQL->HTTP. If that's not some true top level simplification, I don't know what is!
What, huh? Aren’t stored procedures SQL in function form?
That’s how everyone used to build apps before Rails came along and made everone think putting biz logic into a slow server side language was a good idea.
IMHO and I might be entirely wrong but placing and coupling all that logic into the database seems like a bad idea and it's not a question of speed, it's a question of separating responsibilities. Also, for the case shown in the article, it seems all right for a "hello world" kind of thing. For something complex or prone to deep changes (like most of software projects I've been involved with), this seems like a true nightmare.
I’m sure you know this but the reason for taking computation off the database is that it’s much easier to horizontally scale a stateless middle tier than it is to scale a sql database.
Some DBAs I’ve worked with even advocated for taking sorting off the database. I wasn’t entirely convinced by that one.
My server side language in this case was Scala, so it wasn’t slow, just memory hungry.
Is it really easier to scale a Rails or Node app than Postgres (Scala might be an exception)? And how many pieces of software actually ever reach the kind of scale where database is the bottleneck? For many use cases, biz logic in the database will absolutely smoke doing it on server side due to query planner optimizations.
