<Tech>Brunch

Using Liquibase to Manage Database States




Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes. It works across various types of databases, and supports various file formats for defining the DB structure. The feature that is probably most attractive in Liquibase is its ability to roll changes back and forward from a specific point - saving you from the need to know what was the last change/script you ran on a specific DB instance.

  • Liquibase uses a changelog to explicitly list database changes in order. The changelog acts as a ledger of changes and contains a list of changeSets (units of change) that Liquibase can execute on a database. The changesets files can be in various formats including XML, JSON, YAML, and SQL.
  • As we continue to change/enhance our DB structure through the development lifecycle we tend to add more changesets. A master file lists all the changeset files (or the directories where they are).
  • Liquibase tracks which changeSets have or have not been deployed in a tracking table called a DATABASECHANGELOG. If our database does not already contain a tracking table, Liquibase will create it for us.
  • Liquibase also prevents conflicts from different callers’ updates on a secondary table called DATABASECHANGELOGLOCK.
  • When we issue a liquibase update command, liquibase looks at the current state of DB, and identifies which changes have already happened. Then it run the rest of the changes - moving us to the latest revision of the structure defined.
  • By integrating Liquibase into the overall code version management system and continuous integration platform we can synch up our database versions with our app version.

    How to integrate Liquibase in your existing app?

  • Add Maven dependency for Liquibase in pom.xml

    1
    2
    3
    4
    5
    <dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>3.4.1</version>
    </dependency>
  • Create a changelog file, where sql related operation to be performed will be added

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <databaseChangeLog ...>

    <changeSet author="John (generated)" id="1439225004329-1">
    <createTable tableName="APP_USER">
    <column autoIncrement="true" name="id" type="BIGINT">
    <constraints primaryKey="true"/>
    </column>
    <column name="accessToken" type="VARCHAR(255)"/>
    <column name="needCaptcha" type="BIT(1)">
    <constraints nullable="false"/>
    </column>
    <column name="password" type="VARCHAR(255)"/>
    <column name="refreshToken" type="VARCHAR(255)"/>
    <column name="tokenExpiration" type="datetime"/>
    <column name="username" type="VARCHAR(255)">
    <constraints nullable="false"/>
    </column>
    <column name="preference_id" type="BIGINT"/>
    <column name="address" type="VARCHAR(255)"/>
    </createTable>
    </changeSet>
    ...
    </databaseChangeLog>

In the above file, we are creating a table named ‘APP_USER’ with necessary columns. The changeset is identified by an id and an author – to make sure it can be uniquely identified and only applied once. We can save this file with name ‘add-appuser-table-liquibase-script.xml’.

  • We can choose one of the option to run the changes on our DB on the application startup. In this example, we are prefering Spring Bean. The Spring bean for the liquibase configuration is below:
    1
    2
    3
    4
    5
    6
    7
    @Bean
    public SpringLiquibase liquibase() {
    SpringLiquibase liquibase = new SpringLiquibase();
    liquibase.setChangeLog("classpath:liquibase/master.xml");
    liquibase.setDataSource(dataSource());
    return liquibase;
    }

The master.xml above encapsulates all the liquibase scripts we will be writing as the application lifecycle proceeds. The sample master.xml file goes below:

1
2
3
4
5
6
7
8
9
<?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.5.xsd">

<!-- include all release changelogs below, as the application grows up and whenever new liquibase scripts are introduced. -->
<include file="iquibase/add-appuser-table-liquibase-script.xml"/>
</databaseChangeLog>

  • At app startup, we can ensure to call the Spring Bean which is configured for liquibase. With these steps, our liquibase scripts will start getting executed in your connected DB.
    We can track what all liquibase executed by quering in the DB: ‘select * from DATABASECHANGELOG;’
  • For more granular control on Liquibase, we can make use of spring profile yml/property files where we can explicitly define parameters and values for different configuration. These values can then be used in the bean classes as per the requirement.