How to manage RDBMS SQL script changes with version tracking in continuous integration process

Posted on October 7, 2015 Updated on October 7, 2015

Problem: In agile development, need to manage sql schema or script (DDL or DML) changes in continuous integration process. In this case we need to track the change log, so that at any point of time we can restore the seed database state on any environment like Dev, QA, Stage or Production.

Solution: The ORM tools like Entity Framework, Hibernate etc …  will provide native support for it. But this is restricted to your development environment only.

We need something which can be used across all the environment with any development stack like Dot.NET, Java EE or ROR etc… using   Jenkins or Chef . The Liquibase does that magic for us. In this case we can track the change log using change set either in XML, JSON , YAML, SQL and it also have Groovy based DSL.

Let’s see how to use a gradle based sample application using   liquibase-gradle-plugin to do this job. In this case git is used to track the history of Script Changes. The following diagram will show the flow of it.

The liquibase change log set as gradle project in source tracking system like github. It contains the database script in liquibase supported  format like sql,xml, json etc… The Jenkins is used to checkout github source code and execute gradle task using liquibase-gradle-plugin to execute db changes against the relational database like Mysql.

The sample project is available at liquibase-gradle-sample . The project structure with change log set is look likes below:

Here changelogs.xml contains the change set which require unique author name and id to track the version of change set. The change set is any sql file. Need to add a change set in incremental order in the changelogs.xml.

In build.gradle you need to provide the target database credentials, If you are using other than Mysql need to update the JDBC connector accordingly.


buildscript {
repositories {
jcenter()
}
dependencies {
classpath "org.liquibase:liquibase-gradle-plugin:1.1.1"
classpath 'mysql:mysql-connector-java:5.1.36'
}
}
....
....
liquibase {
activities {
main {
changeLogFile 'src/main/db/changelogs.xml'
url 'jdbc:mysql://localhost:3306/applianceDB'
username 'admin1'
password 'admin123'
}
}
}

To execute the database changes use the following command:

gradle update

Conclusion
Liquibase can be used across any ORM framework like C# Entity Framework, JPA (Hibernate), Spring Data or Active Records of Ruby On Rails to track the database changes smoothly. This can be integrated with jenkins as continuous integration or with Chef recipe as continuous delivery.