I've implemented a CLI tool[1] that tries to address similar problems with SQL as mentioned in the article: testing, business logic and re-usability. The approach is different though - you write SQL and pgTAP tests using jinja templates and the tool generates
1. SQL queries that can be used by the application code using libs such as yesql, hugsql etc. and,
2. pgTAP tests, ensuring that they test the exact same SQL queries that are actually run by your application
I haven't used it in a /real/ production setting though, so I'd still call it an experiment. But I have been using it in a personal project with a handful of users (friends and family). Most of the business logic is implemented in SQL so the queries are fairly intricate (CTEs, sub queries, multiple joins). Occasionally, I push some features/improvements/tweaks and I am able to do so with high confidence, which I believe is mainly thanks to pgTAP tests. I am quite happy with it so far although that's just my experience. Haven't tried it on a large project with many contributors.
1. SQL queries that can be used by the application code using libs such as yesql, hugsql etc. and,
2. pgTAP tests, ensuring that they test the exact same SQL queries that are actually run by your application
I haven't used it in a /real/ production setting though, so I'd still call it an experiment. But I have been using it in a personal project with a handful of users (friends and family). Most of the business logic is implemented in SQL so the queries are fairly intricate (CTEs, sub queries, multiple joins). Occasionally, I push some features/improvements/tweaks and I am able to do so with high confidence, which I believe is mainly thanks to pgTAP tests. I am quite happy with it so far although that's just my experience. Haven't tried it on a large project with many contributors.
[1]: https://github.com/naiquevin/tapestry