[MaxMonteil]

This sounds very interesting, I've been using SQL more lately and am constantly impressed by what it can just do.

My thinking is everything I could get done by the DB avoids heavier and maybe slower application code.

Do you have some resources or material I could check to learn more?

[taffer]

Putting business logic in the database: https://sive.rs/pg

Example code: https://github.com/sivers/store

This talk is more Oracle specific but with some minor changes you can apply the same ideas to postgres: https://www.youtube.com/watch?v=GZUgey3hwyI

Personally I am using an architecture similar to https://sive.rs/pg in my personal projects and I am very happy with it. It is important that you put data and procedures/views in different schemas so that you can use migrations for your data but automatically delete and recreate the procedures/views during deployment. Also use pgtap or something similar for testing.

[reaanb2]

That's a cool approach that could work well if you don't need realtime data validation such as in a UI. I would love to find a solution that allows the same validation rules to be used in the DBMS as well as in the backend and frontend code.

[MaxMonteil]

Oh nice! I had read this Sivers post a long time ago, time for a refresher.

Appreciate the info, thanks!

[bbkane]

I like this point of view but putting logic in the database also has downsides - the tooling in particular is bad. No debugger, hard to write tests, can't canary changes, bad or non-existent autocompletion, lack of linters, etc.

[MaxMonteil]

The tooling keeps getting better so I'm bullish on that side.

For canaries there's growing support for branching the database which can help.

But in the end, this like all things requires balance. Putting everything in there can lead to pain down the road, for example I wouldn't follow the http part.

My use case is with app's local sqlite and I have a lot of code transforming the returned rows into JSON. It works but feels slower and to divorced from the data.