6 ms·

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

by draven 3y ago

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.

docapotamus 3y ago
I personally put some logic in the database especially when I’m expressing constraints. If it’s there it means another engineer can’t go directly to the database to bypass these constraints. (By logic, I’m meaning for example a transaction can only transition between states if it’s in a required state). 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.
- quectophoton 3y ago
  In my experience, chances are that the database will outlive whatever application code is layered right on top of it. 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.
Timshel 3y ago
For the versionning part there is usually tools in your language. For ex: Flyway for java, diesel_migration for rust ...
- sverhagen 3y ago
  I don't know... the places I've worked where there was that much of the application shifted into the database layer, there would be no Flyway. You'd be happy to get write permissions, let alone permissions to update the structure. Perhaps correlated, those organizations managed the versioning part through a heavy-handed change management process, ie. humans. Perhaps in some places it's cool now to do this, but for me having the entire application logic modeled in the database will always be associated with painful enterprise culture.
- draven 3y ago
  We're already using Liquibase where I work (I don't remember exactly why it was chosen over Flyway.) I also worked at a company where every DB access had to be done with a stored proc. The stored procs would be reviewed by the DB team. They were versioned by using a version number in the name, like GetUsers_v1, and multiple versions could exist at any given time in the DB.
singingfish 3y ago
I've been designing a somewhat trivial application this week - representing time series data in a reliable manner (basically a holding pen for stuff that will land in opensearch for good visualisation tools). 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.
gregw2 3y ago
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.
sztanko 3y ago
In data engineering, there are frameworks like DBT that do exactly that. In fact, these are industry standards and the recommended way to do transformations and cleanups nowadays. This is essentially a mix of sql and jinja (and yaml files, for variables), you can create your own macros, it comes with it's own testing framework and also strict sql code formatters. Fits git flow quite well. The rationale is that it enables data analysts (data analytics engineers) to do quite sophisticated stuff still using sql. Also, if you are operating on datasets that are larger that a single machine can process, doing it in sql and passing to MPP engines like BigQuery and Snowflake are probably the only way to do it with relative ease. In any case, this is for data engineering only. I wouldn't imagine doing this for live production stuff.
gonzo41 3y ago
liquid base (or something like it) and a bit of forthought is the answer. Change your databse an order of magnitude slower than your higher level code bases. I do think the trend to nosql and document oriented db's was a results of people seeing just the sorts of messes you can get into with things like Oracle and Pg with stuff over the longer term.
phartenfeller 3y ago
I work with Oracle daily and implement a lot of logic directly in the database. 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 https://github.com/OraOpenSource/Logger [1] https://github.com/utPLSQL/utPLSQL https://github.com/utPLSQL/utPLSQL
ggregoire 3y ago
You might find some info in the docs of PostgREST [1] or in the previous discussions on HN about it [2]. 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. [1] https://postgrest.org https://postgrest.org [2] https://hn.algolia.com/?q=postgrest https://hn.algolia.com/?q=postgrest
michael1999 3y ago
Put your functions/procs into files. Check them into git. Use Liquibase (or flyway, etc.) to wrap the files as migrations with rerun-on-change. Deploy by running the Liquibase cli, or directly from your app on startup. 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.

by draven 3y ago

docapotamus 3y ago

I personally put some logic in the database especially when I’m expressing constraints. If it’s there it means another engineer can’t go directly to the database to bypass these constraints. (By logic, I’m meaning for example a transaction can only transition between states if it’s in a required state). 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.

quectophoton 3y ago
In my experience, chances are that the database will outlive whatever application code is layered right on top of it. 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.

Timshel 3y ago

For the versionning part there is usually tools in your language. For ex: Flyway for java, diesel_migration for rust ...

sverhagen 3y ago
I don't know... the places I've worked where there was that much of the application shifted into the database layer, there would be no Flyway. You'd be happy to get write permissions, let alone permissions to update the structure. Perhaps correlated, those organizations managed the versioning part through a heavy-handed change management process, ie. humans. Perhaps in some places it's cool now to do this, but for me having the entire application logic modeled in the database will always be associated with painful enterprise culture.
draven 3y ago
We're already using Liquibase where I work (I don't remember exactly why it was chosen over Flyway.) I also worked at a company where every DB access had to be done with a stored proc. The stored procs would be reviewed by the DB team. They were versioned by using a version number in the name, like GetUsers_v1, and multiple versions could exist at any given time in the DB.

singingfish 3y ago

I've been designing a somewhat trivial application this week - representing time series data in a reliable manner (basically a holding pen for stuff that will land in opensearch for good visualisation tools). 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.

gregw2 3y ago

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.

sztanko 3y ago

In data engineering, there are frameworks like DBT that do exactly that. In fact, these are industry standards and the recommended way to do transformations and cleanups nowadays. This is essentially a mix of sql and jinja (and yaml files, for variables), you can create your own macros, it comes with it's own testing framework and also strict sql code formatters. Fits git flow quite well. The rationale is that it enables data analysts (data analytics engineers) to do quite sophisticated stuff still using sql. Also, if you are operating on datasets that are larger that a single machine can process, doing it in sql and passing to MPP engines like BigQuery and Snowflake are probably the only way to do it with relative ease. In any case, this is for data engineering only. I wouldn't imagine doing this for live production stuff.

gonzo41 3y ago

liquid base (or something like it) and a bit of forthought is the answer. Change your databse an order of magnitude slower than your higher level code bases. I do think the trend to nosql and document oriented db's was a results of people seeing just the sorts of messes you can get into with things like Oracle and Pg with stuff over the longer term.

phartenfeller 3y ago

I work with Oracle daily and implement a lot of logic directly in the database. 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 https://github.com/OraOpenSource/Logger [1] https://github.com/utPLSQL/utPLSQL https://github.com/utPLSQL/utPLSQL

ggregoire 3y ago

You might find some info in the docs of PostgREST [1] or in the previous discussions on HN about it [2]. 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. [1] https://postgrest.org https://postgrest.org [2] https://hn.algolia.com/?q=postgrest https://hn.algolia.com/?q=postgrest

michael1999 3y ago

Put your functions/procs into files. Check them into git. Use Liquibase (or flyway, etc.) to wrap the files as migrations with rerun-on-change. Deploy by running the Liquibase cli, or directly from your app on startup. 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.