An important aspect of Continuous Delivery is a configuration management. Configuration is often stored in the database, especially for more complex business applications. The ability to automatically update changes and rollback them in case of new application version rollback seems to be very important for devops teams. Recently, I had an opportunity to use a powerful tool for tracking, managing and applying database schema changes – Liquibase. This tool has many interesting features like advanced support for rollback, tagging and filtering changes to run. It can be used from Maven, Spring, Jenkins and has support for Hibernate. Today, I’m going to show you how to use Liquibase on Jenkins for changes, update and rollback in the database. We will use Maven plugin and also Jenkins plugin for Liquibase.

Sample code is available at Github. We use liquibase-maven-plugin for calling liquibase during maven build execution. Here’s plugin configuration in pom.xml.

<build>
   <plugins>
      <plugin>
         <groupId>org.liquibase</groupId>
         <artifactId>liquibase-maven-plugin</artifactId>
         <version>3.5.3</version>
         <configuration>
            <propertyFile>src/main/resources/liquibase.properties</propertyFile>
         </configuration>
         <executions>
            <execution>
               <phase>process-resources</phase>
               <goals>
                  <goal>update</goal>
               </goals>
            </execution>
         </executions>
      </plugin>
   </plugins>
</build>
 

Here is a properties configuration file with database settings and liquibase changeset location.

changeLogFile: src/main/script/changelog-master.xml
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.99.100:33306/default?useSSL=false
username: default
password: default
verbose: true
dropFirst: false
 

In the changelog-master.xml file database changes are listed. It is XML based, but there is also support for YAML, JSON, SQL and even groovy language. Following liquibase.org site the best practise is to organize your changelogs by major release.

<?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="src/main/script/changelog-1.0.xml" />
   <include file="src/main/script/changelog-1.1.xml" />
   <include file="src/main/script/changelog-1.2.xml" />

</databaseChangeLog>
 

Here’s changelog-1.0.xml. We’re going to create one table person with some columns and insert there some example rows. Inside the rollback tag we place configuration for rolling back those changes – drop of newly created table.

<?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">

   <changeSet author="minkowski" id="1.0">
      <createTable tableName="person">
         <column name="id" type="int">
            <constraints primaryKey="true" />
         </column>
         <column name="first_name" type="varchar(20)" />
         <column name="last_name" type="varchar(50)" />
         <column name="age" type="int" />
      </createTable>
      <addAutoIncrement tableName="person" columnName="id" columnDataType="int" />
      <rollback>
         <dropTable tableName="person" />
      </rollback>
   </changeSet>

</databaseChangeLog>
 

We apply our changes to the database by running Maven command on the project. It’s also important to place mysql-connector dependency in pom.xml to enable MySQL driver in the project classpath.

mvn package liquibase:update

Now if your build was succesfully finished you can check out changes which were committed in the database. There also should be a table DATABASECHANGELOG with history of changes performed using liquibase. The changes can be rolled back using mvn command. You can set rollback date, number of versions to rollback or tag name to roll the database back to.

mvn package liquibase:rollback

There is also support for liquibase in Jenkins provided by liquibase-runner plugin. It has pipeline support from 1.2.0 version. First you need to download this plugin Manage Jenkins -> Manage plugins section. Then you call it from your pipeline. Here are example pipelines for update and rollback changes.

node {
   stage('Checkout') {
      git url: 'https://github.com/piomin/sample-liquibase-maven.git', credentialsId: 'piomin_gitlab', branch: 'master'
   }

   stage('Update') {
      liquibaseUpdate changeLogFile: 'src/main/script/changelog-master.xml', url: 'jdbc:mysql://192.168.99.100:33306/default?useSSL=false', credentialsId: 'mysql_default', databaseEngine: 'MySQL'
   }
}
 

node {
   stage('Checkout') {
      git url: 'https://github.com/piomin/sample-liquibase-maven.git', credentialsId: 'piomin_gitlab', branch: 'master'
   }

   stage('Rollback') {
      liquibaseRollback changeLogFile: 'src/main/script/changelog-master.xml', url: 'jdbc:mysql://192.168.99.100:33306/default?useSSL=false', credentialsId: 'mysql_default', databaseEngine: 'MySQL', rollbackCount: 2
   }
}
 

In case if someone is not very familiar with jenkins – credentialsId needs to be configured in jenkins ‘Credentials’ section like in the picture below and called by ID inside the pipeline. In the first step of each pipeline named ‘Checkout’ we are cloning Git repository from github.com. In the second we are calling methods of liquibase jenkins plugin and passing arguments same as we set in properties file for maven plugin. We are calling the liquibaseRollback method with rollbackCount=2, which means that two versions of changeset will be rollback 1.2 and 1.1 from my sample configuration available on github.

Unfortunately liquibase-runner using has not support for Oracle database engine in pipeline. But I hope it will be fixed in the future: issue reported by me 🙂