At the time of writing, we currently use upgrade scripts to migrate to a new version of the schema.

These upgrade scripts are basically files with SQL commands. The process of developing these SQL scripts is manual and laborious.

The proposal here is to use Liquibase which is a tried and tested database migration tool.

Liquibase provides three primary benefits in comparison to our existing migration model:

1. Changelog
2. Rollback
3. Database diffing

Unlike the upgrade scripts, the input to Liquibase is a changelog itself.

Liquibase is able to keep track of the changes that were applied and the changes that haven't been applied yet.

A changelog can be in any one of the formats:

• XML Format
• YAML Format
• JSON Format
• SQL Format

an example of a changelog is given below:

databaseChangeLog:
  - changeSet:
      id: 1
      author: avi
      changes:
        - createTable:
            tableName: person
            columns:
              - column:
                  name: id
                  type: int
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: firstname
                  type: varchar(50)

  - changeSet:
      id: 2
      author: avi
      changes:
        - addColumn:
            tableName: person
            columns:
              - column:
                  name: username
                  type: varchar(8)

This also allows us to maintain a linear history of changes.

If a particular changeset fails, the changeset can be fixed and re-run from the last point of execution.

The Rollback feature allows us to rollback our database to a given id, date or tag.

It can also automatically generate Rollback commands for a given changeset.

an example of a Rollback is given below:

databaseChangeLog:
  - changeSet:
      id: RollbackTest
      author: avi
      failOnError: true
      changes:
        - createTable:
            tableName: rollback1
            column:
            - column:
                name: id
                type: int
      rollback:
        - sql:
            sql: drop table rollback1;

Note: commands such as “drop table” and “insert data” have no corresponding rollback commands that can be automatically generated.

This is one of the most useful features as this allows us to diff our local development database with a QA or Production database.

With this, we no longer have to manually search for changed schema which can be very error prone. There are high chances that a schema change may be missed.

Liquibase also allows one to generate the changeset by looking at the diff. This helps to cuts down a lot of manual query writing.

An example command is given below:

liquibase.sh --driver=com.mysql.jdbc.Driver\
        --url=jdbc:mysql://qaserver:3306/test \
        --username=bob \
        --password=bob \
    diff \
        --referenceUrl=jdbc:mysql://localhost:3306/test \
        --referenceUsername=bob \
        --referencePassword=bob

Liquibase website: http://www.liquibase.org/