Source control your database with Liquibase

i recently started to use liquibase on a project to keep track of the database changes in our java enterprise application. i must say that i like the way it works. it makes the deployment of my application (or a new release of it) to another environment easier and (more) fool proof. in the past i had to supply a database script to the dba which had to be executed right after or before i redeployed my ear/ war file with all issiues that comes with that procedure (script fails/ dba not available, etc.). now i won’t say there couldn’t be issues with this solution but from a developer perspective it does make life easier.

here is how it works for a straight maven project with some web services that are backed by a mysql database. since i deploy the web services in a war file on jboss i chose to have the liquibase scripts being triggered by a servletcontextlistener instance. to be able to test the database script without having to deploy the application i also embedded the maven plugin for liquibase in my pom file. this way i can run the liquibase scripts manually with maven against my local development database.
first add the necessary dependencies to the maven pom.xml:

<dependency>                                           
	<groupid>org.liquibase</groupid>                      
	<artifactid>liquibase-core</artifactid>               
	<version>2.0.5</version>                              
</dependency>                                          
<dependency>                                           
    <groupid>mysql</groupid>                           
    <artifactid>mysql-connector-java</artifactid>      
    <version>5.1.15</version>                          
    <scope>provided</scope>                            
</dependency>                                          

the mysql dependency is only necessary to be able to run the liquibase scripts with maven. the application itself uses a datasource that is managed by the container in this case jboss.
to be able to run the liquibase scripts with maven i defined the plugin like this:

<plugin>                                                                       
    <groupid>org.liquibase</groupid>                                           
    <artifactid>liquibase-maven-plugin</artifactid>                            
    <version>2.0.1</version>                                                   
    <configuration>                                                            
        <changelogfile>db.changelogs/my-changelog-master.xml</changelogfile>   
        <driver>com.mysql.jdbc.driver</driver>                                 
        <url>jdbc:mysql://localhost:3306/mydb</url>                            
        <username>admin-user</username>                                              
        <password>*****</password>                                              
    </configuration>                                                           
    <executions>                                                               
        <execution>                                                            
            <goals>                                                            
                <goal>update</goal>                                            
            </goals>                                                           
        </execution>                                                           
    </executions>                                                              
</plugin>                                                                      

like i said i execute the scripts by triggering liquibase with a servlet listener. to set this up add the following elements to the ‘web.xml’ inside the war file:

...
    <context-param>
        <param-name>liquibase.changelog</param-name>
        <param-value>db.changelogs/db.changelog-master.xml</param-value>
    </context-param>
    <context-param>
        <param-name>liquibase.datasource</param-name>
        <param-value>java:jboss/datasources/liquibaseds</param-value>
    </context-param>




    <listener>
        <listener-class>liquibase.integration.servlet.liquibaseservletlistener</listener-class>
    </listener>
...

here we see that i defined two parameters:

• liquibase.changelog

this parameter refers to the classpath location of the master db.changelogs file. it is important to notice that this path matches the one used in the maven plugin if you are running against the same database. see this issue which i ran into when using different paths.

• liquibase.datasource

this one refers to the jndi name of the datasource to be used with the liquibase script. since this database user needs rights to create database obejcts like tables i suggest to create a separate datasource to use with the application. this datasource should then connect with a user that only has rights to access the data and not be able to create or drop objects.

the final part is the creation of the db.changelogs files. as suggested here as a best practice i refer to a ‘master’ changelog file in the configuration. in this master changelog file i refer to the individual scripts. for example i have the following ‘master’ script in my ‘resources’ folder:

<?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-2.0.xsd">
    <include file="db.changelog-1.0.xml" relativetochangelogfile="true" />
    <include file="db.changelog-2.0.xml" relativetochangelogfile="true" />
</databasechangelog>

in the ‘db.changelog-1.0.xml’ script i create some tables with xml based changeset:

<?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-2.0.xsd">
    <changeset id="1" author="pascalalma">
        <createtable tablename="test_table">
            <column name="id" type="bigint(20)" autoincrement="true">
                <constraints primarykey="true" nullable="false"/>
            </column>
            <column name="code" type="varchar(20)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="description" type="varchar(200)"/>
            <column name="value" type="varchar(200)"/>
        </createtable>
        <adduniqueconstraint columnnames="code,description"
                             constraintname="code_uk"
                             tablename="test_table"/>
        <createtable tablename="another_test_table">
            <column name="id" type="bigint(20)" autoincrement="true">
                <constraints primarykey="true" nullable="false"/>
            </column>
            <column name="code" type="varchar(20)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="test_code" type="bigint(20)">
            </column>
        </createtable>
        <addforeignkeyconstraint basecolumnnames="test_code"
                                 basetablename="another_test_table"
                                 constraintname="test_other_fk"
                                 referencedcolumnnames="code"
                                 referencedtablename="test_table"/>
    </changeset>
</databasechangelog>

in this example script i show how to create tables with unique constrained on a combination of columns and how to add foreign keys to them.
in ‘db.changelog-2.0.xml’ i show how to load data into the created tables by using a csv file as input:

<?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-2.0.xsd">
    <changeset id="2" author="pascalalma">
    <loaddata file="db.changelogs/mydata.csv"
              schemaname="mydb"
              quotchar="'"
              tablename="test_table"/>
    </changeset>
</databasechangelog>

in the ‘mydata.csv’ i define the rows to be uploaded into the table:

code,description,value
't01','not specified','foo'
't02','wrongly specified','bar'
't03','correct','foo-bar'

the first line of the csv file states the columns in the order the fields are defined in the other rows. this is a very convenient way to load initial data into your database tables.
when all this is in place you can run liquibase from maven. build the project first with ‘mvn clean install’ and then give a ‘mvn liquibase:update’:

now if we look at the result in the database scheme we see the following tables:

we see our tables with the data are being created. we also see two more tables: ‘databasechangelog’ and ‘databasechangeloglock’. these are used by liquibase to determine the state of the database. if i deploy the war file with a datasource against this database liquibase will see that the scripts already have run and won’t execute them again. if i remove the tables however the scripts will be executed as it was against a new database. as you can see this can simplify the deployment of new releases of your software, at least the database part.