[friendzis]

Embedding business logic/application code in a database is a very different development model than using database as a relatively transparent record store.

I am not against having business logic in database, there are very legitimate uses for that. However, few things must be noted.

First, code is idempotent: you compile, package, deploy code, then nuke it all out and replace with different version, be it older or newer. Swapping application binaries/images/containers is the gold standard in deployment. Databases are... Perpetual. Rolling back DDLs without rolling back data can be a very complex exercise.

Second, there is no automatic separation of interface and implementation in databases. Database engine will not automatically enforce constraints embedded in stored procedures. Which means extra care must be taken and additional processes must be implemented to control access, otherwise you risk corrupting data.

Embedding application code in database breaks separation boundary, which changes project management. Now you have database development and deployment much more closely tied to application code development and deployment. Without huge care in project management this effectively inhibits any non-linear development flows.

[doliveira]

Yeah, I'm interested in knowing how people manage it. With Redis, I usually put the Lua code within the same repo and embed the Lua code within the deployment artifact, so it gets tested and deployed together with the rest of the code. Then I call SCRIPT LOAD at the application initialization and I get a SHA-1 hash to call the script.

At the very least, this approach makes things feel much safer. I would like to know if there better ways to do it, and if there are similar approaches for Postgres procedures.

[claytongulick]

I wrote this[1] several years ago to help with that.

It lets you write a function in nodejs that actually executes in plv8 inside the database.

To your project code though, it mostly looks like a normal js function (with some limitations).

Amongst other benefits, it allows you to manage your plv8 functions as a normal part of your repo.

[1]https://github.com/claytongulick/pgproxy

[zzzzzzzza]

the ddl that creates the views/tables/triggers that replace the "idempotent code" should also be idempotent so you should be able to roll it back without rolling back any data

actually i wrote a couple tables/stored procedures that (should probably mostly) do this for postgres after looking at some other patch management libraries by depesz and steve purcell a couple weeks ago

https://github.com/NotBrianZach/postgres-sql-migrate