[docapotamus]

I personally put some logic in the database especially when I’m expressing constraints. If it’s there it means another engineer can’t go directly to the database to bypass these constraints. (By logic, I’m meaning for example a transaction can only transition between states if it’s in a required state).

When it comes to debugging, versioning, deployment all these live alongside the code and are managed via migrations. Testing it is done as an integration test with the rest of the system.

It helps that we don’t use an ORM and deal with SQL everywhere.

[quectophoton]

In my experience, chances are that the database will outlive whatever application code is layered right on top of it.

So ensuring the database itself protects the data integrity and prevents the application code (current or a future refactor or rewrite) from messing it up, sounds to me like the sane thing to do. Be it with triggers, with functions, or whatever.

Though I can understand that people usually don't like how PL/pgSQL looks like (I don't). But if you ignore the ugly language syntax, testing it is no more difficult than testing, say, an AWS Lambda function that is triggered by SQS and writes stuff to DynamoDB.