Setting up plain SQL versioning with liquibase

In this article I will be explaining how to setup Liquibase to work with plain SQL files, using an existing database. You’ll find at the end some useful commands as well.

A sample project with this configuration can be downloaded here: https://github.com/remborg/liquibase-sql-config

Solution using plain SQL

This solution is based on this post https://www.liquibase.org/blog/plain-sql. As the article say and as I’ve experienced:

[…] most of our users prefer using SQL scripts over the other Liquibase changelog options, like XML, JSON, and YAML.

Steve Donie

Using SQL over XML can be an issue if changing DB but is way quicker and simpler to understand and has a low learning curve… And let’s be honest, how often do you need to change database in a project?

Setup

Install and configure Liquibase (steps 1 and 2 https://www.liquibase.org/get-started/first-steps).
Note: You might have to download the database connector .jar file (for MariaDB: https://mariadb.com/downloads/#connectors).

File structure

The project is following this file structure:

.
├── changelog.xml
├── liquibase-test.properties
├── liquibase-prod.properties
├── README.md
├── mariadb-java-client-2.6.2.jar
├── baseline
|   └── 0010-init-db.sql
└── changesets
    ├── 1.0.0
    |   ├── 0010-adding-customer-phone-column.sql
    |   └── 0020-adding-customer-phone-index.sql
    └── 1.1.0
        └── 0010-changing-customer-phone-length.sql

changelog.xml will contain the liquibase configuration to make our plain SQL approach work.

liquibase-test.properties and liquibase-prod.properties contain the variables liquibase needs to connect to the DB. You’ll need one properties file per environment and will need another one for your local database if you have one.

I like to have a README.md file at the root level of my projects, even though nobody reads them. I usually put all the commands and useful notes for the project.

I like to work with open source technologies, for this example I will use MariaDB and have the mariadb-java-client-2.6.2.jar connector.

Then there is 2 folders:

• baseline contains the SQL commands to run to initialise the database (tables, indexes, static data, etc.). Really useful when I need to create a new DB.
• changesets contains a subfolder per feature release, named after its version number. Each of these folders will then contain all the SQL changes for the new version.

changelog.xml

All the magic happens here. There is 2 <includeAll> elements, one for the baseline folder and one for the changesets folder. It’s then possible to apply one or the other by passing the context as an argument in the command line.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.10.xsd
    http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.10.xsd ">
    <includeAll path="baseline" context="init"/>
    <includeAll path="changesets" context="default"/>
</databaseChangeLog>

.properties files

After installing Liquibase you will get a default properties file that you can use as a template. From it you will need the following:

changeLogFile: changelog.xml
contexts: default
url: jdbc:mariadb://localhost:3306/bifrost-local
username: root
classpath: mariadb-java-client-2.6.2.jar

I never put the password in this file because it will be under version control in Git. I pass the password directly as an argument in the command line (usually using a CI tool and a secret variable so it doesn’t show up in the logs).

Generate the baseline

Assuming you already have a database, let’s create a ‘schema dump’ of this current test DB and put it in the changesets folder:
liquibase --password=<db password> --defaultsFile=<.properties file> --changeLogFile=baseline/0010-baseline.h2.sql generateChangeLog

This generated script will allow us to create new database instances if we need to (adding environments, or duplicating a DB for testing, etc.).

Note: Documentation of the commands can be found here https://docs.liquibase.com/commands/community/home.html

Work with the database

Add a change to the database

For each new feature in your project that requires a change in the database:

1. Create a new folder for all new the changes in changesets/ (eg. changesets/1.1.0), folders order is important.
2. In this folder, create a new SQL file per command using this template:

--liquibase formatted sql
--changeset <your id here>:
<id of the change>

<SQL command to run here>
--rollback <SQL rollback command here>

So for example:

--liquibase formatted sql
--changeset remborg:0010-create-test-table
create table test_table ( 
  id INT PRIMARY KEY, 
  label VARCHAR(255) 
);
--rollback DROP TABLE test_table;

3. Files are run one after the other in alphabetical order. To make things easy name them following a convention. I like to work with 10 increments and name the file with the ‘id of the change’ from the file itself: eg. for the example above, the file name would be 0010-create-test-table.sql

Note: It is good practice to have one change (one SQL command) and its associated rollback command per file.

Create a tag

To simplify rollbacks, create a tag after each update of a changeset:
liquibase --password=<db password> --defaultsFile=<.properties file> tag <tag name>

I always use the version number as a tag (eg. “1.1.0”).

Update the DB with latest changes

You can update the database and apply the changes using the command update:

liquibase --password=<db password> --defaultsFile=liquibase-test.properties update

Note: To do a dry-run use updateSQL instead of update

Some useful commands

Setup a new database

If you have to setup a new database, for example to develop locally:

1. Create a new .properties file with your new database info
2. Run liquibase --password=<db password> --defaultsFile=<new .properties file> --contexts=init update to create schemas, indexes and statics from your baseline.
3. Create the ‘init’ tag so you can rollback to a blank slate if needed liquibase --password=<db password> --defaultsFile=<.properties file> tag init
4. Apply all the changesets liquibase --password=<db password> --defaultsFile=<your .properties file> update

Rollback to a tag

If you need to revert some changes you can rollback the DB to a tag by running:
liquibase --password=<db password> --defaultsFile=<.properties file> rollback <tag name>

To rollback everything and go back to the baseline

If you’ve created your DB as described above you can run this command to go back to the baseline:
liquibase --password=<db password> --defaultsFile=<.properties file> rollback init

Conclusion

As you can see, thanks to Liquibase you only need a few command to get your SQL versioning working and using this SQL configuration is much simpler than learning the XML elements.

When coupled to a CI tool, before merging a feature branch it’s good to have a ‘check job’ running an update with dry-run to test that the scripts are valid, and you can automatise tags creation when deploying too.