How do I manage rolling back changes made to "rerunnable" (runOnChange=true) changelogs that contain stored logic?

[mpo]

In my liquibase project, I have an sql view that is defined within a file called create_myview.sql.

Each time a change is made to the view, i.e. a column is dropped or renamed, instead of creating a new changeset that contains an ALTER statement, the view definition itself in this file is changed.

This file has the runOnChange attribute set to true so that when changes are made, the view is dropped and recreated the next time liquibase update is run. As such the same file can be run over and over when its contents change, i.e. it is “rerunnable”.

Since previous definitions of the view are overwritten with each change, rolling back to a previous version presents a challenge and I am unable to work out the best way to do this.

Currently the only way that I am storing the previous view definitions is through git branches, i.e. with each view change I create a new branch.
Ideally I would like to checkout an old branch and be able to rollback to the definition that is currently checked out. I would like to be able to hop between versions easily.

Other non-rerunnable changesets defined in my project use a --rollback tag that is specified in the sql file and provides the inverse sql operation to each table change i.e. if a column is added to a table using an alter statement, then the inverse sql statement specified looks like:

-- rollback ALTER TABLE x DROP COLUMN name;

The equivalent of using this tag in my rerunnable view file would be to copy the entire previous view definition into this rollback tag, which doesn’t seem like it would be best practice.

However, liquibase rollback doesn’t seem to work without the --rollback tag being specified.

The file structure:

myDB/
    changelog/
        ver1/
            rerunnable/
                create_myview.sql
                rerunnable.myDB.xml
            myDB.changelog-root.xml

This is how I would make changes and update my view from one version to the next.

1. checkout new branch

   git checkout -b c-01
   

2. add changes to view
   create_myview.sql:

-- changeset john:c-01 runOnChange:true
DROP VIEW my_view IF EXISTS my_view;
CREATE my_view AS
SELECT name, date
FROM my_table;
...

3. update the changeset attributes
   rerunnable.myDB.xml

<changeSet  author="john"  id="c-01">  
    <tagDatabase  tag="1.0.0"/>  
</changeSet>

4. Run liquibase update.
   Next, an update is made to the view when the date column is dropped.
   git checkout -b c-02

-- changeset john:c-02 runOnChange:true
DROP VIEW my_view IF EXISTS my_view;
CREATE my_view AS
SELECT name
FROM my_table;
...

<changeSet  author="john"  id="c-02">  
    <tagDatabase  tag="2.0.0"/>  
</changeSet>

liquibase update
At this point, the view in the database is up to date with the latest update, without the date column, and the databasechangelog looks like:

id	author	orderexecuted	md5sum	description	tag
c-01	john	1	83h8hs…	tagDatabase	1.0.0
c-01	john	2	ln9n2b1…	sql
c-02	john	3	ib309bd…	tagDatabase	2.0.0
c-02	john	4	lmxo21…	sql

From this point I am unable to rollback to how the view was at c-01/1.0.0.

The behaviour that I expect/hope is possible would be something like:

1. check out branch c-01
   • the old view definition is now in the working directory
2. run liquibase rollback or liquibase update
3. the view in the database is dropped and recreated with the c-01 schema (with the date column).
4. The changelog only has the first 2 lines.

Unfortunately, liquibase update does nothing, and liquibase rollback specifies that I need a --rollback statement.

would the runAlways attribute work here as a good solution?

[daryldoak]

I think your best option would be to not use runOnChange:true, but to instead put each new version of the view code into a new changeset, and use the new “rollback to changeset reference” that was added to formatted sql in v4.16:

Add rollback changeset reference for SQL changelogs

example:

--liquibase formatted sql

--changeset jim:v1 
CREATE OR REPLACE my_view AS
SELECT name
FROM my_table;

--rollback empty

--changeset bob:v2
CREATE OR REPLACE my_view AS
SELECT name, date
FROM my_table;

--rollback changeSetId:v1 changeSetAuthor:jim

This does mean you have to add a new changeset for every new version, but this is unavoidable if you need to rollback to previous code versions. I think this is much easier that a manual file manipulation process outside of Liquibase, all of the details are in the changelogs.

[mpo]

I really like this solution, however I’m not sure I will be able to implement it since I am using v3.8.7 and may not be able to upgrade.

[daryldoak]

Then you can use this option:

--liquibase formatted sql

--changeset jim:v1 
CREATE OR REPLACE my_view AS
SELECT name
FROM my_table;

--rollback empty

--changeset bob:v2
CREATE OR REPLACE my_view AS
SELECT name, date
FROM my_table;

--rollback CREATE OR REPLACE my_view AS
--rollback SELECT name
--rollback FROM my_table;

[mpo]

doesnt this go against liquibase best practices for stored logic?

at the moment it seems that using tagDatabase and runAlways:true is the best option to avoid data duplication, which can introduce human error. the only tradeoff i can see is that the databasechangelog will always have the latest values being these runAlways sql files.

[daryldoak]

I’d say best option is the us runOnChange, but since you need to be able to rollback to prior version of the code then what I posted above is your best option.

runAlways is not going to work because it does not allow you to modify the changeset without causing a checksum error.