Creating Migrations with Liquibase

Liquibase is a versioning tool for databases. Currently, it’s on version 3.5 and is installed as a JAR. It has been on the market since 2006, and recently completed its 10th anniversary. In its feature list we have:

Code branching and merging

Multiple database types

Supports XML, YAML, JSON and SQL formats

Supports context-dependent logic

Generate Database change documentation

Generate Database “diffs”

Run through your build process, embedded in your application or on demand

Automatically generate SQL scripts for DBA code review

Does not require a live database connection

Why you need it?

Some frameworks comes with built-in solutions out of the box like Eloquent and Doctrine. There is nothing wrong with using something like that when you have only one DB per project, but when you have multiple systems, it starts to get complicated.

Since Liquibase works as a versioning tool, you can branch and merge as needed (like you would with code in git). You have contexts, which means changes can be applied to specific environments only, and tagging capabilities allow you to perform rollbacks.

A rollback is a tricky thing; you can either do an automatic rollback or define a script. Scripted rollbacks are useful when dealing with MySQL, for instance, where DDL changes are NOT transactional.

Guidelines for changelogs and migrations

MUST be written using the JSON format. Exceptions are changes/legacy/base.xml and changes/legacy/base_procedures_triggers.sql.

MUST NOT be edited. If a new column is to be added, a new migration file must be created and the file MUST be added AFTER the last run transaction.

Branching

There could be 3 main branches:

production (master)

staging

testing

Steps:

Create your changelog branch;

Merge into testing;

When the feature ready to staging, merge into staging;

When the feature is ready, merge into production.

Example:

Rules:

testing, staging and productionDO NOT merge amongst themselves in any capacity;

DO NOT rebase the main branches;

Custom branch MUST be deleted after merged into production.

The downside of this approach is the diverging state between the branches. Current process is to, from time to time, compare the branches and manually check the diffs for unplanned discrepancies.

Procedures for converting a legacy database to Liquibase migrations

Some projects are complete monoliths. More than one application connects to it, and this is not a good practice. If you are working with that sort of project, I recommend you treating the database sourcing as its own repository, and not together with your application.

Writing migrations

This is a way I found for keeping the structure reasonably sensible. Suggestions are welcome.

Create the property file

Should be in the root of the project and be named liquibase.properties: