Ok, on balance I do not advocate putting logic, particularly iterative or nontrivial parsing logic, in the database, but the more analytical and SQL-oriented/fr

Ok, on balance I do not advocate putting logic, particularly iterative or nontrivial parsing logic, in the database, but the more analytical and SQL-oriented/friendly the logic is and your infra/data is, the more tempting it can be. I have gone down this road and while I debate its merits, I also think it’s under-rated/under-tried. 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.