I’d be interested to see your approach to unit testing your functions.
I’ve worked on large codebases in the past where the majority of business logic was contained in stored procedures (SQL Server) and it was really a nightmare to work with - difficult to test, difficult to understand, and not DRY at all.
I’m sure there are better approaches to the philosophy of business logic in the database than that codebase, though.
I just have a “fixtures.sql” file with some sample data I use for testing, a mini version of real-world data, and before each unit test it dumps and reloads the fixtures file. I’ve been doing it this way for years, and it works great.
To me, it’s all quite DRY. Any outside code becomes more like the “Controller” in MVC, just calling these stored procedures, since all the data model and logic is in the PostgreSQL functions. I love it.
I too have worked where stored procedures proliferated. Unfortunately tests were really ad-hoc. Because risk of breakage was great, any changes were generally implemented as a clone & modify, leading to a profusion of almost identical stored procedures. It was not pretty.
I don’t know if it was the culture or the tools that made tests so rare at that place. You could test stored procedures with a test suite written in any language that lets you call stored procedures conveniently, and has decent test infrastructure.
Is there a way to maintain DB objects separately in a repository and refresh the DB as they get modified? Perhaps running the automated tests right away.
For me it’s quite stressful to work with live database directly - I cannot use my favourite editor and have to use mouse intensively.
Versioning dumps is also problematic. Rows are randomly ordered in the dumps, which causes inaccurate diffs.
I’d be interested to see your approach to unit testing your functions.
I’ve worked on large codebases in the past where the majority of business logic was contained in stored procedures (SQL Server) and it was really a nightmare to work with - difficult to test, difficult to understand, and not DRY at all.
I’m sure there are better approaches to the philosophy of business logic in the database than that codebase, though.
Thanks. My unit tests are in the Github:
Tests of the hidden functions and triggers:
https://github.com/sivers/store/blob/master/store/test-db.rb?ts=2
Tests for the API:
https://github.com/sivers/store/blob/master/store/test-api.rb?ts=2
I just have a “fixtures.sql” file with some sample data I use for testing, a mini version of real-world data, and before each unit test it dumps and reloads the fixtures file. I’ve been doing it this way for years, and it works great.
To me, it’s all quite DRY. Any outside code becomes more like the “Controller” in MVC, just calling these stored procedures, since all the data model and logic is in the PostgreSQL functions. I love it.
I too have worked where stored procedures proliferated. Unfortunately tests were really ad-hoc. Because risk of breakage was great, any changes were generally implemented as a clone & modify, leading to a profusion of almost identical stored procedures. It was not pretty.
I don’t know if it was the culture or the tools that made tests so rare at that place. You could test stored procedures with a test suite written in any language that lets you call stored procedures conveniently, and has decent test infrastructure.
I worked at a place that had 20 million lines of Oracle PL/SQL doing all the business logic. Breakage and debugging was really stressful for the DBAs.
Is there a way to maintain DB objects separately in a repository and refresh the DB as they get modified? Perhaps running the automated tests right away.
For me it’s quite stressful to work with live database directly - I cannot use my favourite editor and have to use mouse intensively.
Versioning dumps is also problematic. Rows are randomly ordered in the dumps, which causes inaccurate diffs.
@sievers, many thanks for these kind of posts.
DB programming is becoming a lost art (especially in the internets) and is extremely useful for server side coding.
I’m pretty happy with this architecture; I just wish there were a better language to write them in than PL/SQL or whatever they call it these days.
There’s an extension system for it, with Perl, Python and Tcl built-in and a good few other languages too.
https://www.postgresql.org/docs/current/xplang.html
https://wiki.postgresql.org/wiki/PL_Matrix