I'm always amazed (and a bit frightened) by the amount of logic that can be implemented directly in the database. How is the code debugged / managed / versioned / deployed ? I would be thankful for any pointer to books / blog posts about that.
Debugging: Extensive logging to tables [0]. Also we have dev, test and prod databases.
Versioning: Git. It's just source code that gets compiled in the db.
Deployment: Upgrade SQL scripts. You already have to do this on any relational DB if you need to alter existing tables. We just also deploy new/updated packages. We trigger them via pipelines.
Also keep in mind that the logic you deploy in the database is generally not as complex as other software as you mostly just query, modify and write highly structured data.
But we still run plenty of tests. There is a great unit testing tool for Oracle: utPLSQL [1]. We also spin up databases and run the installation and upgrade scripts on pull-requests.
[0] https://github.com/OraOpenSource/Logger [1] https://github.com/utPLSQL/utPLSQL
In any case, this is for data engineering only. I wouldn't imagine doing this for live production stuff.
When it comes to debugging, versioning, deployment all these live alongside the code and are managed via migrations. Testing it is done as an integration test with the rest of the system.
It helps that we don’t use an ORM and deal with SQL everywhere.
So ensuring the database itself protects the data integrity and prevents the application code (current or a future refactor or rewrite) from messing it up, sounds to me like the sane thing to do. Be it with triggers, with functions, or whatever.
Though I can understand that people usually don't like how PL/pgSQL looks like (I don't). But if you ignore the ugly language syntax, testing it is no more difficult than testing, say, an AWS Lambda function that is triggered by SQS and writes stuff to DynamoDB.
At one point I was thinking "well I can put that column in the main table so long as I don't fire the 'when_changed' trigger if there's an insert/update on any other column. After about three minutes, I decided the design needed normalisation after all ...
Last year I moved a mostly small but very non trivial database from oracle to postgres. And I cursed the name of every developer who decided on non-trivial logic inside the database along the way.
A few years ago I made some expiry logic inside of some postgres triggers, and it worked really well and was rock solid. However we moved it out of the triggers into the application PDQ because it would never have been resilient changes in requirements. Nonetheless, prototyping the logic in postgres was good, but it absolutely did not belong there for the long run.
For the versioning, I just have a git repo where I keep the definitions of every role, schema, table, view, function, trigger, grant, policy, etc. Every time I change something in the database I first change it in the git repo too to not lose the history. It also helps as a reference for future development, like if I need a trigger function I can just search one in the repo and copy/paste it.
For testing, write some tests and run them. I used ut_plsql when I was working with Oracle.
For debugging, a log table is easy. Have a log() function that inserts into a log table inside a tx-new. You can also use an interactive debugger. Postgres, Oracle, and MS all provide gui debuggers. They aren't as advanced as IntelliJ, but they let you set breakpoints, inspect variables, etc.
The key missing tool for a conventional programmer interested in the topic to consider is liquibase/flyway. More on that in a bit.
How is db code debugged? Print statements and/or log/warn/error tables populated by a simple logging stored procedure you sprinkle in your code. Plus intermediate tables that contain intermediate state of a computation/data-wrangling. The former is crude vs IDE step-through debuggers but workable; the latter is (arguably) better than most programming languages which don’t let you retrieve intermediate RAM state or let you inspect/query them in as flexible a way. Would I rather pore through gdb dumps (or pickled serialized custom checkpoints from some language’s data structures)? Or query tables? Hmm…
You can also debug by creating TDD test frameworks for your logic-encapsulating stored procedures (“sprocs”). For each sproc, you create three small test sprocs. 1) a mock data setup sproc which idempotently inserts mock test data needed for testing different scenarios your code will encounter 2) a mock data tear down sproc which removes the test data and 3) one or more test execution sprocket which first calls sproc#2 then #1 then calls your main logic/state- changing sproc with whatever input parameters you want to check, and then inspects the resulting output values or database state changes and emits/returns testname, PASS/FAIL, and failure reason message as its return values or as its dataset it returns.
Write your test sprocs first, then run an empty test stub of your main sproc code which should fail the test, then write+edit+debug your code until it passes the tests. Presto, debugging database code TDD-style!
How is db code managed/versioned/deployed? In git, with liquibase/flyway called by your CICD process (Jenkins with maven+liquibase for Java apps, Jenkins+liquibase CLI for other types of apps.)
Liquibase lets you define+execute a series of SQL statements as a series of “change sets”. (The changeset definition and properties are configured via structured sql comment annotations before+after one or more sql statements. These statements are within an otherwise conventional “.sql” script that is then read+parsed+executed by a liquibase executable/.jar called by maven/CICD/etc. Liquibase maintains its own private state of whether a changeset has run or not, and you can annotate with each change set definition whether that change set “runs once”, “runs on change”, only if the sql statement was edited since last run (ie liquibase detects its hash of that sql statement code changed) or “run always”. If your .sql bombs out in the middle, liquibase-executed.sql (unlike a conventional .sql piped to your database) just starts off where you left off code+data deployment-wise when it runs the second time, since it knows which changesets have exited successfully and you’ve effectively annotated which should rerun or be rerunnable.
Given all that, you create a master list of .sql files, run through them all each CICD build/deployment with liquibase. Most DDL table creation sql in your .sql code should be configured to be changesets annotated to run once, inserts of reference data likewise, permissions, grants, user creation, etc. similarly. To edit those after they’ve run, just add ALTER SQL statements as a later changeset. Slightly differently, stored procedure or SQL VIEW (re-)creation would be annotated to “run on change”, so if liquibase detects (via hash) you’ve edited that sproc it redeploys it, otherwise it skips rerunning/redefining it. Thus workflow-wise you edit files with that sort of code much like you would any more conventional programming code. Your test suite sprocs should “run always” presumably.
Convention-wise, to make code manageable, I put chunks of related sql in similar files, also putting stored procedures in different files than ddl since that fit my mental model best, and put execution order number prefixes in my liquibase .sql filenames to make the mental model of required/desired execution order very explicit. 1_schema_setup.sql, 2_user_setup.sql, 3_initial_table_setup.sql, 4_initial_data_load_from_csv.sql, 4b_core_views.sql 5_config_sproc_test_suite.sql, 6_config_sprocs.sql 7_core_sproc_test_suite.sql 8_core_sprocs.sql 9_<major_v2_feature>_setup.sql, 10_<new-non-core-oriented sproc>_test_suite.sql, etc.
In theory, if the cumulative DDL gets too complex, you can just reverse engineer a clean db schema and refactor/blow away all the delta-type code.
Liquibase annotations also let you have preconditions and postconditions for each changeset that you can configure to skip execution, fail the change set/job, or execute rollback or other arbitrary sql. So before you have liquibase do some expensive or nonidempotent operation, you can pre check via your own sql if it was done already if you want to be safe or assert some precondition that must be enforced before safely proceeding. When defining an sproc in a changeset, you can configure a post-condition check if the related test sproc returned “PASS” and onFail then run the rollback sql for that changeset which could be basically a copy of the earlier sproc definition code.
Anyway that’s what I did on a team that had (relatively) high engineering standards. Never did write it up in a proper blog post so the above is not quite a cookbook but should give you a flavor of what is possible.
It does take a bit of an app developer + db developer mindset to appreciate/internalize though, and many people are one or the other.
The context of this effort was some SQL code that was the heart of an analytics signal detection engine using stored procedures running over a data warehouse coupled with a Scala app that ended up scanning over its lifetime tens of billions of dollars of big pharma orders for “unusual” orders needing human review. So it can be done in a real production app running over some years with enhancements.
> In theory, if the cumulative DDL gets too complex, you can just reverse engineer a clean db schema and refactor/blow away all the delta-type code.
We use Liquibase at $WORK and I often end up playing the whole changelog locally to then inspect the results on the DB server. Recreating a mental model of the DB structure by reading the changesets gets really hard really fast.
Yeah, mental models of db/DDL structure via changesets is really only meaningful the original engineer. The difficulty of that is why I never maintained views or stored procedures or user permissions as delta-like changeset. They were runOnChange and grew within their own fixed files that I occasionally refactored so I didn’t have to think about deltas for those (that change set history could be seen in git if needed.)
(I mentioned “run once” in my original post, but liquibase actually doesn’t have that; I used runOnChange with preconditions which would MARK_RAN if a sql statement revealed logic had run before already.)
I wonder in hindsight if I just could have reverse engineered the database once a month (or per major release) and dumped that into a git folder to have a current view of data structures that could easily be seen or consulted…