Managing Your Database With Liquibase and Gradle

One major system development problem has always been how and when we update the database. There are always at least a few questions that crop up:

• Were scripts for DB changes created?
• Where do we save the DB changes?
• When should we apply these changes?
• Have these changes already been implemented?
• How do we track and manage database changes?
• Who made these changes?

Liquibase is a library that can help address these issues. Liquibase is an open source database used to track, manage, and apply changes to the database.

Liquibase works better because it understands what the changes are. For example, a database comparison program would simply see the “person” table on integration has a “firstname” and a “lastname” column, but on live, the “person” table has a “name” column. It would report that you need to drop the “name” column and add a “firstname” and a “lastname” column. While this would leave you with the correct schema, you would lose everyone’s name in the process. With Liquibase, you would have a changeset that says “rename ‘name’ to ‘lastname’ and add a ‘firstname’ column” or, even better, “split the name column on a space and place the values in new ‘firstname’ and ‘lastname’ columns, then drop the ‘name’ column.” Knowing why they are different allows changes to production databases without the fear of losing valuable data.

In this post, I will show how you can use the powerful tool Liquibase together with Gradle to automate these tasks, from there will be easy to put Liquibase to work with your continuos integration tools.

Some important concepts:

Changelog File

The changelog is the file that contains references of all the scripts that should be applied to the database in any environment.

ChangeSet Files

ChangeSet files:

• Are all the files recorded in a changelog.

• Can be written primarily in XML, YAML, JSON, and SQL, which I chose for this example.

Some advice:

• IDs cannot be repeated. Otherwise, they will not run.
• Scripts should be small.
• Rollbacks should be added whenever possible.
• Must be added new scripts on the changelog.xml
• Everything that was executed is registered on the table DATABASECHANGELOG

Example

Changelog.xml

<?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"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <include file="scripts/001.SAMPLE.sql" relativeToChangelogFile="true"/>
    <include file="scripts/002.SAMPLE.sql" relativeToChangelogFile="true"/>

</databaseChangeLog>

001.SAMPLE.sql

--liquibase formatted sql

--changeset salerno:3
create table test1 (
    id int primary key,
    name varchar(255)
);
--rollback drop table test1;

002.SAMPLE.sql

--liquibase formatted sql

--changeset salerno:4
insert into test1 (id, name) values (1, 'name 1');
insert into test1 (id, name) values (2, 'name 2');
insert into test1 (id, name) values (3, 'name 3');
insert into test1 (id, name) values (4, 'name 4');

Build.gradle

apply plugin: 'liquibase'

buildscript {
    repositories {
        mavenCentral()
    }

    dependencies {
    classpath 'org.liquibase:liquibase-core:3.4.1'
    classpath "org.liquibase:liquibase-gradle-plugin:1.1.1"
classpath 'mysql:mysql-connector-java:5.1.13'
    }
}

def changeLog = "$projectDir/src/main/db/changelog.xml"

task('dev') << {
println "executing dev"

liquibase {
    activities {
        main {
            changeLogFile changeLog
            url 'jdbc:mysql://dev-environment/mydb'
            username 'root'
            password '123456'
        }
    }
}    
}

task('qa') << {
println "executing qa"

liquibase {
    activities {
        main {
            changeLogFile changeLog
           url 'jdbc:mysql://qa-environment/mydb'
            username 'root'
            password '123456'
        }
    }
}    
}

task('prod') << {
println "executing prod"

liquibase {
    activities {
        main {
            changeLogFile changeLog
            u url 'jdbc:mysql://prod-environment/mydb'
            username 'root'
            password '123456'
        }
    }
}    
}

I created tasks for every environment I have and where Liquibase should run the script. With Gradle, I need only to choose a task as it is below:

• To execute:
  • gradle task dev update
    
  • gradle task qa update
    
  • gradle task prod update

After that, you can check within your database that scripts 001 and 002 were applied.

On the table DATABASECHANGELOG, it's possible check some records like these:

This sample above complete in my GitHub.