How to automate database deployments using Liquibase?

credit

The Liquibase is a database change management tool. Using it you can track database changes made using SQL (or XML) scripts. These scripts can be added to the version control systems like git.

In this post, we will see how to automate database deployments using Liquibase. The automation can be done using

1. Jenkins pipeline

2. Shell scripts

I will not talk about any of these two as it is not subject of this post — just to keep things straightforward and to the point.

There are 2 ways to write changesets in Liquibase i.e. XML and SQL. I have chosen SQL to avoid writing corresponding XML changes for each SQL update we do. This way - the same SQL file we write for our development is used for Liquibase deployments.

Notice that I have tested these steps for Postgre database only however the same steps should be repeatable for any database. Also, make sure that Liquibase is configured on your machine.

Follow the steps given below:

Create a changelog file

Create a XML file named liquibase-changelog.xml(name can be any btw!) with the following content:

<?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.1.xsd">
<include file="<path to changeset SQL file>/<changeset file name>.sql>" relativeToChangelogFile="true"/>
...more <include> tags goes here...
</databaseChangeLog>

Notice the include tag in the XML above. Each SQL changeset file which needs to be tracked by Liquibase has to be registered in this changelog file.

Create SQL changesets

Add the SQLchangeset files in a location of your choice. The SQL syntax which works with Liquibase is:

--liquibase formatted sql
--changeset <author name>:<a unique identifier for the SQL changeset>
<SQL statements go here>
<SQL statements go here>
--rollback <rollback SQL statements>
--rollback <rollback SQL statements>

Let’s see an example:

--liquibase formatted sql
--changeset xameeramir:create-test-table
CREATE TABLE IF NOT EXISTS testTable(
columnName1 VARCHAR (355)
);
--rollback DROP TABLE
--rollback testTable

Notice that a SQL changeset file is different from the XML changelog file.

Register SQL changeset in the changelog XML file

Include the SQL changeset file in the changelog file we created earlier with the following XMLtags:

<include file=”<path to SQL changeset file>/<changeset file name>.sql” relativeToChangelogFile="true" />

Add as many SQLchangesets and register them in the changelog file as you want.

Trigger Liquibase to update the database

Simply run the below command:

liquibase --changeLogFile=<path to changelog file>/<liquibase changelog file name>.xml --username=<database username> --password=<database password> --classpath=<path to the liquibase installation>/postgresql-42.2.5.jar --url=jdbc:postgresql://<database url>/<database name> update

The classpath is the JDBC driver we configured in the last post. The postgresql-42.2.5.jar is the JDBC driver specific for Postgres and should be replaceable with the database of your choice without any special changes in these steps.

The above command can be used in the shell scripts or CI/CD pipeline to trigger database updates.

The CI/CD automation

Once the above configuration is in place - the automation can be done either on the client using shell scripts or on the server using shell scripts or CI/CD implementation.

Assuming a CI/CD implementation in place which triggers Liquibase deployment meaning runs the Liquibase trigger command given above on each git push to DEVELOP (or any) branch.

The first pre-requisite will be to have a liquibase-changelog.xml file. Let’s say we keep it at the ~/ level with include statements pointing to the folder where SQL changesets reside. The following workflow will automate the database deployments using CI/CD pipeline:

• Push SQL changeset file to the feature repository
• Raise a pull request against DEVELOP branch
• After valid peer review and approvals, merge the feature branch with DEVELOP branch
• The CI/CD implementation configured on the DEVELOP server will trigger the Liquibase for database updates
• Liquibase automatically execute new files only (any files already executed will never be executed again)

The automation using shell scripts

The same Liquibase triggering command will be written in the shell scripts. Once the shell scripts are executed, the containing Liquibase changesets will be executed automatically.

You might be wondering how will the shell scripts know when to execute? The answer is simple:

• The shell scripts can be executed on the cron triggers
• The shell scripts can be executed on some system events

The choice is yours!