[rejschaap]

It's not very hard, it's just impractical. Mainly because a database is a giant bag of statefulness (to put it scientifically).

You need to prepare test data, you need to update and maintain the test data. That is already a big barrier to entry.

The actual testing involves three simple steps: setting the initial state of the database, run your queries/procs, verify the results. This will be unbearably slow even for a small test set. So you start to make things complicated by trying to be smart, like only revert the state you modified, or using SQLite for tests and Postgres for production, or by running the database server on a RAM filesystem, etc, etc.

I've seen a few people go down the rabbit hole and noone came up with a solution I could be happy with.