Managing Databases With Liquibase

Liquibase is an open source tool used to evolve the database schema as well as for database version control and migration management. It tracks and manages database changes for any database with a JDBC driver. All database changes are stored in a human readable and trackable form. Liquibase is built on the Java platform and requires Java runtime environment and the corresponding JDBC driver jar files.

1. Liquibase basics - changeLog files, databaseChangeLog and changeSet

The core of using Liquibase are changeLog files. A changeLog file is an xml file keeping track of all changes that we have to run to update the database. <databaseChangeLog> tag is parsed when we run the Liquibase migrator. Inside the <databaseChangeLog> tag we can add <changeSet> tags with which we group database changes. Each changeSet is uniquely identified by 'id' and 'author' attributes as well as the changelog file classpath name.

Here’s the simplest example of a Liquibase changeLog file:

<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">
</databaseChangeLog>

After we save the code and update the database, Liquibase automatically generates two tables:

DATABASECHANGELOG tracks which changeSets have been ran. Each changeSet is tracked as a row and indentified by three columns (ID, AUTHOR and FILENAME). Each row also contains DATEEXECUTED and ORDEREXECUTED, which determine rollback order, as well as MD5SUM, which is a checksum of the executed changeSet and used on each run to ensure there haven’t been any unexpected changes of the changeSets in the changeLog files.
DATABASECHANGELOGLOCK ensures there’s only one instance of Liquibase running at once to prevent conflicts. Also, if Liquibase doesn’t exit cleanly, a row could be set as locked. We can clear the current lock by running UPDATE DATABASECHANGELOGLOCK SET LOCKED=0.

Both of the tables are used by Liquibase to track changes.

liquibase database

2. Using Liquibase to update the database

Now that we have a basic changeLog file, we will look into how to manipulate the database to create, update and delete tables, columns and data. It is recommended to use a new changeSet for every separate operation of insert, update, and delete, following the rule “one change per changeSet”. Therefore, when we're updating an existing database we're performing a version-based database migration with Liquibase.

2.1. Manipulating database schema and tables

Typically you need some basic operations from Data Definition Language (DDL) to create data structures in databases.

2.1.1. Create schema

In Liquibase there is no "create schema" tag, because it is designed to manage objects within the application's schema. However, we can use a custom SQL statement inside a 'sql' tag to make a creation of a schema a part of our migration.

<changeSet author="asdf" id="1234">
    <sql dbms="h2" endDelimiter=";">
        CREATE SCHEMA schema
    </sql>
</changeSet>

2.1.2. Create table

When creating a table, we add the following code inside the databaseChangeLog tag:

<changeSet author="asdf" id="1234">
    <createTable tableName="newTable">
        <column type="INT" name="newColumn"/> 
    </createTable>
</changeSet>

Each table, in order to be created, needs to contain at least one column with attributes ‘type’ and ‘name’.

2.1.3. Drop table

When deleting a table we have to specify the name of the table and the schema. With cascadeConstraints set to true we also drop all the constraints referring to primary and unique keys in the dropped table. This means that corresponding records in child table/tables will be deleted. If there is a referential integrity constraint but we don't set the cascadeConstraints to true, the database will return an error and won't drop the table.

<changeSet author="asdf" id="1234">
    <dropTable tableName="newTable" schemaName="public" cascadeConstraints="true"/>
</changeSet>

2.1.4. Change existing data structure with alter table

We can alter the table by changing its name (tag renameTable), adding, renaming and dropping columns (tags addColumn, renameColumn, dropColumn) as well as modifying the data type (tag modifyDataType).

2.1.4.1. Rename table

Inside the tag <renameTable> we need to define new table name, old table name and schema name.

<changeSet author="asdf" id="1234">
    <renameTable newTableName="newName" oldTableName="table" schemaName='schema'/>
</changeSet>

2.1.4.2. Rename column

To rename a column we need to provide a column data type, new column name, old column name, schema name and table name.

<changeSet author="asdf" id="1234">
    <renameColumn columnDataType="varchar(255)" newColumnName="newColumn" oldColumnName="column" schemaName="schema" tableName="table"/>
</changeSet>

2.1.4.3. Add column

Tag <addColumn> requires schema name and table name, and an inner tag <column> the name of the new column and its type.

<changeSet author="asdf" id="1234">
    <addColumn schemaName="schema" tableName="table">
        <column name="newColumn" type="varchar(255)"/>
    </addColumn>
</changeSet>

2.1.4.4. Drop column

To drop the column we have to specify column name, table name and schema name.

<changeSet author="asdf" id="1234">
    <dropColumn columnName="column" tableName="table", schemaName="schema"/>
</changeSet>

2.1.4.5. Modify data type

Modifying data type requires column name, new data type, schema name and table name.

<changeSet author="asdf" id="1234">
    <modifyDataType columnName="column" newDataType="int" schemaName="schema" tableName="table"/>
</changeSet>

2.2. Manipulating the data in the database

Besides modifing data structures we can also change the data.

2.2.1. Update

When we are updating the values in the table we have to specify the name of the column, new value and provide the 'where' condition to find the location of the value we want to change.

<update tableName="newTable" schemaName="public">
    <column name="newColumn" value="1337"/>
    <where>newColumn='11'</where>
</update>

2.2.2. Insert

When we want to insert new values in the table, we have to specify the name of the column and the value.

<insert schemaName=”public" tableName="newTable">
    <column name="newColumn" value="newValue"/> 
</insert>

2.2.3. Delete

To delete the data from the table we have to use a 'where' tag to specify which data we'd like to delete. In the example below we'd delete all the rows that contain the value 'deleteMe' in the column 'columnName'.

<changeSet author="asdf" id="1234">
    <delete tableName="newTable" schemaName="public">
        <where>columnName='deleteMe'</where>
    </delete>
</changeSet>

3. Liquibase contexts

In Liquibase we can use a context to ensure certain scripts run, for example, only in production, and others only in test environment. This way we can ensure that when running the migrator with "test" context our test data is only inserted into our test database.

We can add contexts to changeSets. However, if we don’t specify a context in a changeSet that changeSet will run everytime, regardless of the context that is set to be used.

<changeSet id=”1234” author=”asdf” context=”test”>

4. Liquibase with Maven

To configure and run Liquibase with Maven, we need to add the following configuration to our pom.xml file:

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>x.x.x</version>
</dependency>

Maven also enables us to automatically generate a changelog file from:

already existing database:

mvn liquibase:generateChangeLog

the difference between two databases:

mvn liquibase:diff

Read more about the Liquibase data types and the corresponding database and Java data types