How is Stored Logic managed? I can't find any concrete examples of an acceptable workflow

General Discussion
1

The only information I can find regarding Stored Logic is from the Liquibase Best Practices document. Is there a working example that clearly demonstrates the necessary workflow, including issuing rollbacks, that I can see?

2

There are two primary ways to handle stored logic in the database:

  1. Maintain the stored logic in a single changeset over time, using runOnChange:true. This option does not allow you to easily rollback to a prior version.

  2. Add a new changeset for each modification to the stored logic, with the rollback using a reference to the prior changeset.

These examples are formatted sql, but can be done in any Liquibase supported file format:

Option 1:

--liquibase formatted sql
--changeset BOB:create_functionv1 endDelimiter:/ runOnChange:true
--comment: Create test_func function
    create or replace function test_func return date is
      v_dummy1 date;
      v_dummy2 date;
    begin
      --This is select 1
      select sysdate into v_dummy1 from dual;
      --This is select 2
      select sysdate-1 into v_dummy2 from dual;

      return v_dummy2;

    end test_func;
    /  
--rollback empty

Option 2:

--liquibase formatted sql
--changeset BOB:create_functionv1 endDelimiter:/ 
--comment: Create test_func function
    create or replace function test_func return date is
      v_dummy1 date;
      v_dummy2 date;
    begin
      --This is select 1
      select sysdate into v_dummy1 from dual;
      --This is select 2
      select sysdate-1 into v_dummy2 from dual;

      return v_dummy2;

    end test_func;
    /  

--rollback drop function test_func;

--changeset BOB:create_functionv2 endDelimiter:/
--comment:Create v2 of the test_func function, with rollback to prior code version!!
    create or replace function test_func return date is
      v_dummy1 date;
      v_dummy2 date;
      v_dummy3 date;
    begin
      --This is select 1
      select sysdate into v_dummy1 from dual;
      --This is select 2
      select sysdate-1 into v_dummy2 from dual;
      --This is select 3
      select sysdate-2 into v_dummy3 from dual;

      return v_dummy3;

    end test_func;
    /  

--rollback changeSetId:create_functionv1 changeSetAuthor:BOB
3

Hi Daryl, thanks for your reply.

The second option you describe seems to invalidate the benefits of using Stored Logic, and doesn’t align with the description of how it should be managed, according to the BPD (Best Practices Doc). The primary benefit as described is to be able to use git tools to better see the changes over time by overwriting the existing changeset with each logic change, thereby managing it “similar to source code”. Adding a new changeset each time also introduces the possibility of human error.

Option 1 does align with what is described in the BPD, however if rolling back isn’t possible or even documented, then how can stored logic be realistically be implemented in any project where rolling to and from different database versions is a core part of managing a database?

4

I think there are pros and cons to each option. In my company, most people use option 1.

For option 1:

  • rollback assumes you are using something outside of Liquibase (source control) to revert to the prior version of the code. (Also, where I have “–rollback empty” you could provide the prior version of the code if you’d like.)
  • less code replication in changelogs

For Option 2

  • rollback is entirely controlled and documented in Liquibase.
  • more code replication in the changelogs

Both are 100% valid options. You have to decide which works best for you.

5

For option 1, are you able to share any part of the workflows that people in your company use, assuming they are using git for source control?

6

Changelogs are maintained github, and we have an automation process that downloads the scripts from github and runs Liquibase.

So, as the stored logic changes over time, the code is maintained in the single changeset, in github. This allows them to “rollback” by modiying the code to the prior version.