Getting started with Liquibase

Liquibase is a source code control management tool for database schemas. It basically consists of a command line tool that logs into your database and applies schema changes from files. The files store the incremental changes to the schema and are logged in a table named DATABASECHANGELOG in that schema. By looking at that table, Liquibase knows which changes need to be applied.

It is designed to work along with source code control management systems like Git, and automation servers like Jenkins. The goal is to automate database changes through a DevOps process, without the help of a database administrator.

The basic steps for deploying a database change with Liquibase are:

1. Create a changelog file with the code.
2. Run a Liquibase update to apply the code.

Download the Liquibase archive and place it into a convenient directory on your server.

Changelog Files

The changelog files can be written in XML, JSON, YAML, or SQL. In this post, I will focus on SQL and XML because they are more appropriate for Oracle work.

The changelog files contain at least an author, a change ID, and the code. Additionally, you can add a comment, a release tag, and a rollback statement:

changelog_1.sql

--liquibase formatted sql
--changeset christoph:1
--comment create department table
create table department (
    id number generated always as identity,
    name varchar2(30),
    active varchar2(1) default 'N',
    constraint department_pk primary key (id)
);

--rollback drop table department;

The first comment tells Liquibase that this is an SQL file and not the expected XML. The –changeset comment tells the author and change number. The –comment is a descriptive comment, and the –rollback states how to undo the above code.

Before we execute this changefile, let’s create the liquibase.properties file that tells Liquibase how to connect to the database:

driver        : oracle.jdbc.OracleDriver
classpath     : /path/to/ojdbc8.jar
url           : jdbc:oracle:thin:@localhost:1521/pdb1
username      : scott
password      : tiger
changeLogFile : changelog_1.sql

From the directory where you unzipped Liquibase, run:

./liquibase update
Liquibase Update Successful

This is basically it. The department table is now created in the scott schema, and you can check the DATABASECHANGELOG table (automatically created when Liquibase is first run)  to see when the change was applied, and who the author was:

select * from databasechangelog;

To undo this change, use the rollback command:

./liquibase rollbackCount 1
Liquibase Rollback Successful

This will drop the table, as specified in the –rollback directive. It will also remove the corresponding row from DATABASECHANGELOG.

ChangeLog Master

It is a good idea to create a changelog “master” file. This file can then call other files that need to be included. This way you don’t have to specify the changelog file, each time you run the command. This is the way to do it if you are running an automated process.

The changelog master file is an XML file that looks like this

This file calls two changelog files that perform DML, and two files that compile PL/SQL code. It is a good practice to keep your DML and PL/SQL code separate. The final directive creates a release tag, which will show up in the TAG column of the DATABASECHANGELOG table. These tags can be used to rollback:

./liquibase rollback 1.0.1
Liquibase Rollback Successful

Integration

You can now use Liquibase as part of your DevOps process. If you use an automation server like Jenkins, you could automatically deploy database changes to your test database as soon as new code gets committed.

Let me know in the comments whether you are using Liquibase, and how you’re doing it.