How to write DB migrations with Liquibase?

I am not ignoring all these intros where Liquibase is explained and such. We are here because we know what it is. I am talking about evolutionary database using Liquibase and all these concept here are also adaptable to Flyway. The following are patterns useful when writing such database migrations.

1) Enumeration in big steps

TL;DR: Do not enumerate in 1,2,3,4… but enumerate in wide steps 100,200,300,400,… such that you can introduce migrations in between later.

When having a migration file in Liquibase you have enumerate them. E.g.

-- changeset erhard.dinhobl:1

But start with a huge number e.g. 100. The reason behind is easy. In some cases you have to fix a bug or maybe have to introduce a new migration before that one because you forgot something which must be done which is different in the production system or different stage. Hence:

-- changeset erhard.dinhobl:100

Also proceed with the followup migrations with higher numbers. E.g.

-- changeset erhard.dinhobl:200

Just do consistent increasing numbers. Sure if there would be some float numbers we could use 1,2,3, and such and then introduce another migration with 1.5 or 1.53. But thats not possible.

2) Be aware of implicit commits (Especially MySQL)

TL;DR: Split your statements into one migration if it is an implicit commit

Let’s consider the following migration which is executed in a transaction (because we configured it that way):

-- changeset erhard.dinhobl:100
create table entity_a (
    id int primary key
);

update entity_b 
    set sample_column = 12;

-- rollback drop table entity_a;
-- rollback update entity_b
-- rollback     set sample_column = null;

What if the sample_column is a foreign key on another table entity_c on the id column and the record with id 12 exists on all your stages except on production. And suddenly on production your migration is going to fail because the foreign key cannot be fulfilled because entity_c with id 12 does not exist.

If this is recognized the migration will fail and will not be taken into successful database migrations. Further a rollback is also not possible because it is not listed in the migrations executed.

Now a fix is done for production and it is getting redeployed. It will fail during the same migration but not in the update statement but in the create table statement. The reason is because of implicit commit. This means that there are certain statements which force especially MySQL to do a commit even if there is not the end of the transaction.

In our case the first time when this migration was executed the table entity_a was created and immediately commited. Hence after we created the fix and want to execute it again it will fail because „the table entity_a table already exists“. The solution to that is that there MUST be an own migration for ALL statements with implicit commits. Maybe in some cases it is not necessary but I recommend to do it just out of the box. The final migrations would look like this:

-- changeset erhard.dinhobl:100
create table entity_a (
    id int primary key
);

-- rollback drop table entity_a;

-- changeset erhard.dinhobl:200
update entity_b 
    set sample_column = 12;

-- rollback update entity_b 
-- rollback     set sample_column = null;

This will solve the problem.

Another problem with implicit commits is the following. Let’s consider the following migration:

-- changeset erhard.dinhobl:100

update book
    set title = concat("[PROMOTION]", title);

create table entity_a (
    id int primary key
);

drop table old_table;

-- rollback update entity_b
-- rollback     set sample_column = null;
-- rollback drop table entity_a;
-- rollback create table entity_b (id ...);

In this case when the drop table statement fails because maybe the user under which the migration is executed does not have the rights to drop table the update and create table were executed and are persisted. If the migration is going to re-run after the problem was fixed because the user has now the rights to drop table then after the run the book.title field is going to contain „[PROMOTION][PROMOTION]….“. This is again because the create table statement in the first run did an implicit commit and all changes were committed.

The solution is again the split into own migrations.

-- changeset erhard.dinhobl:100

update book
    set title = concat("[PROMOTION]", title);

-- rollback update entity_b
-- rollback     set sample_column = null;

-- changeset erhard.dinhobl:200

create table entity_a (
    id int primary key
);

-- rollback drop table entity_a;

-- changeset erhard.dinhobl:300

drop table old_table;

-- rollback create table entity_b (id ...);

3) Idempotency

TL;DR: Make all your statements idempotent if possible.

We now consider this statement:

update book
    set title = concat("[PROMOTION]", title);

This migration is NOT idempotent. Idempotency means that whenever you execute it a multiple times it won’t change if it already happened. Hence only the initial intention is applied and nothing more/multiple times.

To make this idempotent it would require this:

update book
    set title = concat("[PROMOTION]", title)
    where left(title, 11) != "[PROMOTION]";

Idempotency is also available for some DDL definitions. E.g.:

alter table entity_a
    add column if not exists column_a int;

Sure, this is not possible on every RDBMS. When thinking about MySQL especially below 8 the support is not possible. Therefore Liquibase has a great construct.

-- precondition-sql-check expectedResult:0 select count(*) from information_schema.columns where table_name = "entity_a" and column_name = "column_a";

This condition advices Liquibase to only do the migration if the DQL select query returns count of 0 and hence can add the column with the alter table statement. Otherwise it would fail.

4) Awareness of Locks

Doing a deployment especially when it is required to have zero downtime is in many cases hard to maintain. Depending on your commands the locks can within some RDBMS escalate which means that not only the rows you are about to modify/delete are locked but the whole table. This happens especially on SQL Server when the row-lock count reaches 5.000. In other RDBMS it is handled differently. In the next article I am giving out information about locks, problems with it in regards of zero downtime and how to overcome it.