And how do you unit test triggers? Yes you can do it in a ton of ways, but you just end up scattering your business layer all over the database, the GraphQL adapter, API gateways etc. The alternative is just to create a dedicated BE service endpoint (in whatever you prefer, REST,HTTP/JSON,SOAP, gRPC etc.), which does the required checks for you.
Triggers, functions or whatever you use are just code; yes, that is my pitch: Have your buisness logic in code, ideally in a dedicated BE API endpoint instead in the DB.
I for one think that the code path that leads to an insert conflict should be integration/unit tested. Something needs to codify how the error is reported to the client.
But unit testing is not nasty at all: just look up pgTAP.
What's nasty is that the incumbent development model intersperses data modelling constraints in the database and in the non-db code, and then we test one layer implicitly from another layer.
Unit testing is nasty? You can use whatever tool you like. I've used phpunit, junit and jasmine for unit testing databases. Choose whichever tool you like most.
It can get hairy due to how most apps are developed.
You'd usually have backend code guarding against constraint-violating entries, and then you'd have a database constraint too.
So what you need to do now is test almost exactly the same thing from your code test and from your database test to get proper coverage.
Enter declarative ORM schemas, and suddenly there's not even a guarantee that the schema you are running really matches what your ORM thinks the schema is.
For that reason, I prefer all those SQL-based database migration/evolution approaches over ORM-based schema generation, coupled with pure SQL tests (eg. with pgTAP, but yeah, any tool can do).
Basically, even for declarative code, there should be a unit test, a la double-entry bookkeeping in accounting.
And even if this is what I prefer and believe is only right, I never worked on a large company project that did all of these things.
So I don't think the entire topic should be easily dismissed: while unit testing is simple, have you ever worked on a project that tested db-schema embedded logic exactly at the right level (and not the level up)?
That’s the path I ended up taking. The GraphQL resolvers had no idea idea there was a database. They talked to a layer that understood all the business objects and that sat on top of a layer that understood authorization and only that layer had any connection to the data store.
Triggers, functions or whatever you use are just code; yes, that is my pitch: Have your buisness logic in code, ideally in a dedicated BE API endpoint instead in the DB.