Hacker News new | ask | show | jobs
by taffer 282 days ago
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.
2 comments
reaanb2 281 days ago
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.
link
MaxMonteil 282 days ago
Oh nice! I had read this Sivers post a long time ago, time for a refresher.

Appreciate the info, thanks!

link
bbkane 282 days ago
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.
link
MaxMonteil 282 days ago
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.

link