Create Database Tests using Jenkins, Liquibase and Gradle

In organizations with data assets that are constantly being modified you need to keep track of the data quality. One way of doing this is to buy expensive data quality tools and run them nightly. Other way is to use Jenkins as a build system and run your tests nightly/weekly whatever schedule you prefer. Organizations solve this problem in myriads of ways.

Recently, I was asked to integrate Jenkins - a continuous integration tool, liquibase -a java based database testing tool, and gradle - a groovy based build tool. It was quite challenging to get the nitty gritty details of liquibase to work with gradle and I found a way to make it all happen.

Basically, you will need a gradle plugin on Jenkins, the gradle script will capture output of the liquibase jar and look for keywords that indicate failure and will fail the build accordingly.


Here's the gradle script that capture standard out/error and fails the build:


task run (dependsOn: copyLibs) << {
    String jarPath = relativePath(new File(libsDir, archivesBaseName + ".jar"))
    ByteArrayOutputStream baos = new ByteArrayOutputStream()
    PrintStream ps = new PrintStream(baos)
    System.setErr(ps)
    def releaseLockResult = javaexec { main = '-jar'; args = [jarPath, '--url=jdbc:mysql://yourdatabasehost:3306/' + "$schema", 'releaseLocks'] }
    def javaExecResult = javaexec { main = '-jar'; args = [jarPath, '--url=jdbc:mysql://yourdatabasehost3306/' + "$schema", '--changeLogFile=' + "$changeLogFile", 'update'] }
    println baos.toString("UTF-8")
    if (baos.toString("UTF-8").contains("LIQUIBASE_ERROR")){
      assert false: "Liquibase Test Failed"
    }
}

The key here is to capture the standard error to a print stream and then do logic on the print stream.
This gradle snippet looks for the string LIQUIBASE_ERROR in the output of liquibase. You can configure liquibase to contain the string LIQUIBASE_ERROR whenever it encounters an error. A snippet of my liquibase file looks like


<?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="325" author="yash" runAlways="true">
        <preConditions onFail="WARN" onFailMessage="LIQUIBASE_ERROR - Test Verify that company_size field maintains integrity.">
            <sqlCheck expectedResult="0">SELECT column from table where condition</sqlCheck>
        </preConditions>
        <comment>This test was added as per pivotal tracker story 12345679</comment>
    </changeSet>
</databaseChangeLog>

Hope this helps people who are trying to do similar things. There's a whole lot of things happening here on second thoughts...this would've been a better post if I'd broken it down.Oh well. :)