Database, MySQL, Web Development

Liquibase – Source control for your database

  • October 18, 2015

Many PHP, Python and Ruby frameworks already have their database source control feature either built-in or supported as a plugin. In our case, we didn’t use (PHP) CodeIgniter’s built-in database migration tool nor use any migration tool at all until we received a recommendation to use Liquibase.

Liquibase

Liquibase is an open source tool to manage your database changes including but not limited to database schema changes, add or modify columns, update or insert data and many more. It has many features but currently, we are only interested on managing database changes that can be deployed (or rolled back) in development servers, co-worker’s laptop, staging or production servers.

System Requirements

In order to run Liquibase, the following are the basic requirements.

For a CentOS 7 based system, you can use Yum to install Liquibase.

sudo yum install java-1.8.0-openjdk liquibase

Workflow

You can use Liquibase from the start of development but the good thing is that you can use it as well to manage an already existing system so that future database changes will be managed by Liquibase. Once you started using Liquibase, all schema changes should be done in Liquibase moving forward.

What we did is include the Liquibase changesets in our source code repository as if it is part of the development workflow (of course it is). In our CodeIgniter setup, it is placed at application/liquibase.

lysender@darkstar:/var/www/html/application$ tree liquibase
liquibase/
|-- README.txt
|-- changelog
|   |-- db.changelog-1.0.0.xml
|   |-- db.changelog-1.1.0.xml
|   |-- db.changelog-1.2.0.xml
|   `-- db.changelog.master.xml
`-- run-migration.sh

Changelogs

We organized our database changes using XML-based changelog files (Liquibase supports different formats). To make things more organized, we used a master changelog file where we simply include all the new changesets as they are added. See an example below.

<?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="changelog/db.changelog-1.0.0.xml"/> 
  <include file="changelog/db.changelog-1.1.0.xml"/> 
  <include file="changelog/db.changelog-1.2.0.xml"/> 
</databaseChangeLog>

Changes are read as they appear on the maxter XML file. See an example of changesets/changelog below:

File: changelog/db.changelog-1.0.0.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"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <changeSet id="1.0.0" author="Lysender">
        <createTable tableName="image_version_source">
            <column name="id" type="INT" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="path" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="filename" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
            <column name="filetype" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="date_created" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <createIndex indexName="ivs_path" tableName="image_version_source" unique="true">
            <column name="path" type="VARCHAR(200)"/>
        </createIndex>

        <createTable tableName="image_versions">
            <column name="id" type="INT" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="version_name" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="path" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="filename" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
            <column name="filetype" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="date_created" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <createIndex indexName="iv_source_vname" tableName="image_versions" unique="true">
            <column name="source_id" type="INT"/>
            <column name="version_name" type="VARCHAR(20)"/>
        </createIndex>

        <rollback>
            <dropIndex tableName="image_version_source" indexName="ivs_path"/>
            <dropIndex tableName="image_versions" indexName="iv_source_vname"/>

            <dropTable tableName="image_version_source"/>
            <dropTable tableName="image_versions"/>
        </rollback>
    </changeSet>
</databaseChangeLog>

The changeset states that:

  • It should create two tables: image_version_source and image_versions
  • It should create additional two indexes: ivs_path and iv_source_vname
  • It should drop the indexes and tables in case rollback is called

Note: Those tables are used to manage versioned images based on the original image. Versions include thumbnails, tiny thumbnails, medium sized images and the like.

Note: I didn’t include the rest of the changesets (1.1.0 and 1.2.0) but you get the idea. Per changeset, you just declare the changes. Filename and ID convention can vary, I just invented my own.

Running Liquibase

In order to apply the changesets, we need to run the Liquibase command. There are many ways to integrate Liquibase to your current workflow, but in our case, we just used the command line workflow. (I read somewhere that they used Maven or something).

To run our changesets and due to my silly changelog paths, I need to go to liquibase directory first before running the Liquibase binary.

cd /var/www/html/application/liquibase && liquibase --driver=com.mysql.jdbc.Driver \
    --classpath="/path/to/usr/share/java/mysql-connector-java.jar" \
    --changeLogFile="/var/www/html/application/liquibase/changelog/db.changelog.master.xml" \
    --url="jdbc:mysql://127.0.0.1:3306/catdb" \
    --username=catuser \
    --password=CatPassw0rdMe0w \
    --logLevel=info \
    migrate

I know the paths are silly. I’m already on the liquibase directory but yet, I have to specify full paths. I don’t bother solving that issue right now.

To make things a bit clearer, you simply run something like below (except we removed the other options).

liquibase migrate

Migrate simply executes your changesets 1.0.0, 1.1.0 and 1.2.0. The good thing is that Liquibase knows if the changesets are already applied so that running liquibase migrate over and over again won’t re-apply the changes. It actually has a dedicated table where it tracks the changes you’ve already applied.

Rollback

I haven’t fully explored the rollback feature but we used it in development environment for testing rollbacks. The only option we used is the rollbackCount n command.

liquibase rollbackCount 1

It should rollback 1 changeset starting from the latest changeset, in this case, the latest changeset. I’m pretty sure it will rollback the next changeset if you execute it one more time, and so on. Therefore, be very carefull when executing rollback.

I’ve read in the documentation that you can rollback specific changesets, but we haven’t experimented on that part yet.

Long version:

cd /var/www/html/application/liquibase && liquibase --driver=com.mysql.jdbc.Driver \
    --classpath="/path/to/usr/share/java/mysql-connector-java.jar" \
    --changeLogFile="/var/www/html/application/liquibase/changelog/db.changelog.master.xml" \
    --url="jdbc:mysql://127.0.0.1:3306/catdb" \
    --username=catuser \
    --password=CatPassw0rdMe0w \
    --logLevel=info \
    rollbackCount 1

Wrapping Up

We put the liquibase command into a script to make it a bit organized and put the database credentials into a source file.

File: application/liquibase/run-migration.sh

#!/bin/sh

source /var/www/scripts/vars.sh

cd $APP_LIQUIBASE_ROOT && liquibase --driver=com.mysql.jdbc.Driver \
    --classpath="/usr/share/java/mysql-connector-java.jar" \
    --changeLogFile="$APP_LIQUIBASE_ROOT/changelog/db.changelog.master.xml" \
    --url="jdbc:mysql://$APP_DB_HOST:3306/$APP_DB_NAME" \
    --username=$APP_DB_USER \
    --password=$APP_DB_PASSWORD \
    --logLevel=info \
    migrate

See? It gets more ugly, except that we don’t see cat passwords.

To make things worst, we created a GIT post-merge hook so that when we update our common development server, it will automatically run the liquibase migration script, then send me the output of the command via email to see if things go wrong.

File: /var/www/html/.git/hoks/post-merge

#!/bin/bash -e

/var/www/html/application/liquibase/run-migration.sh 2>&1 | mail -s "DB Migration Script" me@darkstar.net

That’s it!. Enjoy and share.

Tags: , , , ,
No Comments

Leave a reply

Your email address will not be published. Required fields are marked *