> IMO these types of calcs are better done in your app, where you can at least write a test and assert it's doing the right thing.
I can write a test for the db logic, too.
> It also makes it a lot easier to reason about your code if the logic is in the app rather than bits of it stuck in column definitions.
Its a lot easier to be confident that all consumers of the DB, regardless of whether they are coming through a particular app, have the correct view of the data if the non-app-specific domain logic is in the database.
If you’re the one implementing the functions (or even if you’re not), there are tools such as pgtap that can help you with testing. I’ve used pgtap successful on a number of projects. There’s also no reason you can’t test the behavior of functions through a driver in some other language, though you’re now one step removed.
I’m not aware of any coverage tools, though it’s been a while since I’ve looked.
Or you could test behaviour, and not code. For example, have a test as part of your app's general test suite - insert a row, read it back and check the generated column value is what you expect.
I can write a test for the db logic, too.
> It also makes it a lot easier to reason about your code if the logic is in the app rather than bits of it stuck in column definitions.
Its a lot easier to be confident that all consumers of the DB, regardless of whether they are coming through a particular app, have the correct view of the data if the non-app-specific domain logic is in the database.