Yup. Postgres cannot send confirmation mails, push notifications, make calls to Stripe or anything really.

I cannot think of a situation where an API only handles CRUD data and lacks any behaviour.

But, If your API really only is pushing data around, ' such tooling is usefull and probably saves a lot of time.

> Yup. Postgres cannot send confirmation mails, push notifications, make calls to Stripe or anything really.

You put an item in table queue using SELECT FOR UPDATE ... SKIP LOCKED and some out of band consumer sends your email.

PostgREST isn't about doing everything in the database it's about doing all the same patterns you already do but with less boilerplate.

You can also use NOTIFY in your postgrest stored procedure to wake up a hypothetical backend processor with low latency. I really love this project! I recommend it to people all the time.

Interesting, so how does that fit into the model definition/admin? How to do migrations for example? Have to do it all in sql, I’m guessing. May not be a win in the long run.

https://sqitch.org/ Is a popular migration tool or you can use whatever your used to from your framework of choice (Rails or what have you)

As awb points out: you now have a splintered codebase.

I'd add that you also have hard to spec couplings and difficult to manage microservices setup.

Tools like MQTT or paradigms like eventsourcing might help. But those all presume your database is a datastore. And not the heart of your businesslogic.

It sure can, should you do it though is a dif. question. You can use PL/Python that ships with PG.

This isn't really true, in most cases you can write FDWs over foreign resources that let you query them like any other table, use them in joins, etc. Postgres is really more of an application platform than a database at this point. Just don't try to have the same PG instance be your master and your platform.

You might want to check Forest Admin in that case. It creates an extensible admin REST API on top of your Postgres.

Comes with native CRUD routes for all your tables but you can also declare your own to refund a transaction using Stripe's API, send an email using Twilio's API...

Here's an explanatory video - https://bit.ly/ForestAdminIntro5min

> When you need to integrate with 3rd parties though you're right back to writing traditional backend code. So now you have extra dependencies and a splintered code base.

Those third parties can still talk to the same database. We use this pattern all the time, PostgREST to serve the API and a whole bag of tools in various languages that work behind the scenes with their native postgres client tooling.

It sounds like it quickly becomes extremely difficult to change the datascheme then.

Do you tightly orchestrate releases? Or do you simply never change the schema?

Does PostgREST have functionality for authentication and authorization?

I guess you could front it with a reverse proxy if not, but would be nice to have auth built in.

Yes, it has first class support for 3rd party auth using signed JWTs. I use it with Auth0 to provide social logins.

[1]: http://postgrest.org/en/v7.0.0/auth.html#client-auth

[2]: http://postgrest.org/en/v7.0.0/ecosystem.html

I use caddy-auth-portal and caddy-jwt to generate JWTs for AuthN (based on SSO) and then use postgres built in row level security for AuthZ.

I haven’t used postgREST, but the user/group authorization model looks fantastic at a glance.

It uses JWT, you can use third party or serve them yourself.

You could try https://onegraph.com. It won't allow you to get rid of all your backend code, but you can definitely get further!