Database Versioning: Using Liquibase

Liquibase is one of the leading open source products for DIY database change versioning. In this article I show how Liquibase can be used to version control a database.

Test Cases

For a real world scenario, I came up with some test cases. I wanted to have an existing database from which I wanted to extract schema. The assumption here is that I already have several projects in progress with several legacy databases in place. Said databases also have copies in lots of environments. So I am also going to make changes in one environment and try and push them into another environment. Lastly I am going to try and quantify changes meaning how easy it is to rollback and roll forward.

 

Installation

Liquibase can be downloaded from its website http://www.liquibase.org as a .zip or .tar.gz. Its written in java so you will need JRE installed. You will also need to download the relevant JDBC driver of the database you are connecting to. Unzip or untar the file to any path of your choosing and add it to the path variable of your OS if you wish. I unziped it in c:\liquibase. You will find the liquibase.jar is under c:\liquibase. You will also find a lib directory under c:\liquibase. You can directly place your JDBC jars inside this directory so you don’t have to constantly add them to your classpath.

Database

For this exercise I used Windows 8.1 and SQL Server 2014. Since I had recently installed both I did not have a real database to play with so I turned to AdventureWorks. AdventureWorks is a test database provided by Microsoft that can be downloaded from here. Follow the download and attach instructions at the link if you wish to download it for testing. After my database was attached I was all set to run my tests

.

Configuration File and Baseline

Now I needed to create a configuration file to tell liquibase how to connect to my database. I also needed the JDBC driver to enable liquibase to connect to my SQL server. I could download that from here. I simply unziped it and dropped the jar in the lib directory and it worked. I did not have to update the classpath. Next I created a simple configuration file like below.

driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks
username=nilesh
password=sekrit
changeLogFile=c:\\Liquibase\\BaseLine.xml

Then I ran the command below to extract the schema/baseline the database.

liquibase generateChangeLog

This successfully generated the baseline of the database in XML format. The baseline looks like below

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<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">
    <changeSet author="nileshnimkar (generated)" id="1395377975239-1">
        <createTable tableName="AdventureWorksDWBuildVersion">
            <column name="DBVersion" type="NVARCHAR(50)"/>
            <column name="VersionDate" type="datetime"/>
        </createTable>
    </changeSet>
    <changeSet author="nileshnimkar (generated)" id="1395377975239-2">
        <createTable tableName="DatabaseLog">
            <column autoIncrement="true" name="DatabaseLogID" type="INT">
                <constraints nullable="false"/>
            </column>
            <column name="PostTime" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="DatabaseUser" type="SYSNAME(128)">
                <constraints nullable="false"/>
            </column>
            <column name="Event" type="SYSNAME(128)">
                <constraints nullable="false"/>
            </column>
            <column name="Schema" type="SYSNAME(128)"/>
            <column name="Object" type="SYSNAME(128)"/>
            <column name="TSQL" type="NVARCHAR(MAX)">
                <constraints nullable="false"/>
            </column>
            <column name="XmlEvent" type="XML">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>

As you can see it generated change sets with unique, incremental ids. One change set per database object. Liquibase works by maintaining a table inside the database called DATABASECHANGELOG in which it maintains a list of all the changeset ids it has applied to the database. Normally when you would create a change set, it will compare your file against this table and figure out what to apply to the database. However since baseline was generated for me and so where the changeset id, I had to generate the tables and populate it by running the command below so that the baseline and the database were now in sync.

liquibase changeLogSync

ChangeSets and Versioning

Next I broke up the change sets a bit to make them more manageable. I created a file called c:\liquibase\ChangeLog.xml which looked like below

<?xml version="1.0" encoding="UTF-8"?>
 
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
    <include file="baseline.xml"/>
    <include file="changeSet-1.xml"/>
</databaseChangeLog>

This made sure I would create a separate file for each change set to keep things clean. I could also create a framework where I could have a separate directory for each developer I have as liquibase supports includeall tag with directory name. In my properties file I replaced baseline.xml with changelog.xml

driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks
username=nilesh
password=sekrit
changeLogFile=c:\\Liquibase\\ChangeLog.xml

Finally I created my changeSet-1.xml with a test table. I also included a rollback code in it to test out rollback.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
 
<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">
<changeSet author="nileshnimkar" id="12345678-1">
        <createTable tableName="GoodByeCruelWorld">
            <column name="id" type="int"/>
            <column name="name" type="NVARCHAR(200)"/>
        </createTable>
    <rollback>
        drop table GoodByeCruelWorld
        </rollback>
</changeSet>
</databaseChangeLog>

Then I ran liquibase update. I checked sql server and the table shows up. However when I try to rollback to previous version I find I am unable to rollback. I get an error saying I need a tag to rollback to and I forgot to tag before I rolled forward to the current version. A little digging in the documentation reveals that I can also rollback by number of changesets or even date and time. So I rolled back by one change set and my table vanished.

Lastly I create a empty QA copy of the database called AdventureWorks_QA. I changed the configuration file to point to the QA database and see if liquibase can push out the whole database to my QA server.  My first few tries failed. It seems during the schema extraction the order of views had been a bit messed up. However with a little trail and error I got the order corrected and within about 15 minutes I was able to push out my database to QA. So now I could do my changes in XML, check them in to any VCS of choice (git, SVN etc.) and push them out or roll them back from multiple environments. And it had only taken me a couple of hours to set this up and get a threshold level of understanding of Liquibase.

Looking back at my test cases, I think liquibase did really well. A few pitfall were that it could not extract stored procs, functions or other objects from database but those short comings are listed on liquibase’s site. It also has a ton of other feature and I barely managed to scratch the surface of the feature list so everyone reading this should check out the site.

Has any of you had experience with Liquibase ? Leave me comments below.