Versioning Your Database with Liquibase

Most of the applications we develop are managed using some sort of version control system. But what about the databases used by those applications? More often we make the changes to our development, test, and production databases manually. Such an approach may work for an application with only one or two developers, but in a larger team with a number of developers, it becomes difficult to share the changes with everyone. In this article we’ll discuss Liquibase, an open-source tool for managing and versioning database schema changes. It helps us to organize incremental database changes into different change sets and apply them to the database. Liquibase is not the only database versioning/migration tool. There are many solutions, like Doctrine 2 migrations, Rails AR migrations, DBDeploy, and so on. The first two options are excellent solutions but they are platform specific. DBDeploy is relatively simple, but it isn’t as feature rich as Liquibase. Liquibase solves many problems that are not addressed by different database migration tools like supporting multiple developers, different DBMS systems, branching, and so on. Also, a serious drawback in most of the tools is that they are not change aware. Instead of focusing the changes made, they compare two snapshots of database schema for generating a migration script. So for example, renaming columns is treated as a drop+add operation which may result in data loss. Liquibase is change aware. Let’s take a look at how to use Liquibase in our projects.

How Liquibase Works

If you are using Mac with brew, installing Liquibase is simple. Just run brew install Liquibase and you’re done. The same is the case for Ubuntu, sudo apt-get install liquibase will do it for you. The Liquibase binary is a cross-platform Java application, which means you can download the JAR and use it on Windows, Mac, or Linux. It’s a good idea to keep it in your project folder so that anyone in the project can use it without any installations. When using Liquibase, you store database changes in XML files, commonly known as changelog files. Changes can be kept in a single file or multiple files to be included in a master changelog file. The second option is recommended as it allows greater flexibility when organizing changes. In a changelog file, you organize changes in different changesets. A changeset can contain one or more changes that you want to apply to your database. Each changeset can be uniquely identified using the id and author attributes along with the class path of changelog file. Liquibase creates a table (databasechangelog) in your database to keep track of successfully applied changes. Liquibase runs through each change set one by one and checks if they have already been applied by comparing the checksum in the databasechangelog table. It will apply a change if it hasn’t already been run or if it has a runAlways tag on it.

Getting Started

For demonstration purposes I’ve created a database named application on my local MySQL server, as well as a changelog file. You can keep it in your project folder or in a separate location, but changelog files should be under version control. Here’s the first version of our changelog file with no changesets.

<?xml version="1.0" encoding="UTF-8"?> 
<!--db.changelog.xml-->
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="https://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">
 
</databaseChangeLog>

On the command-line navigate to the location where you are keeping the changelog file and run the following command:

liquibase --driver=com.mysql.jdbc.Driver 
     --classpath=../lib/mysql-connector-java-5.1.21-bin.jar 
     --changeLogFile=db.changelog.xml 
     --url="jdbc:mysql://localhost/application" 
     --username=dbuser 
     --password=secret 
     update

If Liquibase can connect to database using the given username and password, it should create two tables in the application database, DATABASECHANGELOG and DATABASECHANGELOGLOCK, and show the following output:

INFO 8/2/12 10:19 AM:liquibase: Successfully acquired change log lock
INFO 8/2/12 10:19 AM:liquibase: Creating database history table with name: `DATABASECHANGELOG`
INFO 8/2/12 10:19 AM:liquibase: Reading from `DATABASECHANGELOG`
INFO 8/2/12 10:19 AM:liquibase: Reading from `DATABASECHANGELOG`
INFO 8/2/12 10:19 AM:liquibase: Successfully released change log lock
Liquibase Update Successful

In the above command, all of the parameters except classpath are required. driver specifies the class name of the database driver that we want to use. changeLogFile is the name of our database changelog. url specifies the JDBC database connection string which includes the server type, hostname, and database name. classpath is where you keep the classes, like the database connector, used by Liquibase. Instead of specifying command line parameters each time you run Liquibase, you can keep them in a Java properties file named liquibase.properties in the same directory. Then you can run just liquibase <command> The properties file would look like this:

#liquibase.properties
driver: com.mysql.jdbc.Driver
classpath: ../lib/mysql-connector-java-5.1.21-bin.jar
url: jdbc:mysql://localhost/application
changeLogFile: db.changelog.xml
username: dbuser
password: secret

Next let’s create a user table with ID, name, and email fields by adding a changeset to db.changelog.xml. Here’s the updated XML:

<?xml version="1.0" encoding="UTF-8"?>
<!--db.changelog.xml-->
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="https://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="1" author ="shameer">
  <createTable tableName="user">
   <column name="id" type="int">
    <constraints primaryKey="true" nullable="false" />
   </column>
   <column name="name" type="varchar(50)">
    <constraints nullable="false" />
   </column>
   <column name="email" type="varchar(128)">
    <constraints unique="true" nullable="false" />
   </column>
  </createTable>
 </changeSet>
</databaseChangeLog>

createTable takes the name of table in tableName attribute and columns as sub tags. You specify the columns in this table with the column tag with its name and datatype as required attributes. column supports a number of other useful attributes which may not make sense in all cases. For example, you can set a column to auto increment by using autoIncrement="true". Any column constraints are specified with constraints tags. In our example, the id column has primary key and not null constraints, and the email column has a unique constraint. Run Liquibase and look at the result:

liquibase update

INFO 8/4/12 7:16 AM:liquibase: Successfully acquired change log lock
INFO 8/4/12 7:16 AM:liquibase: Creating database history table with name: `DATABASECHANGELOG`
INFO 8/4/12 7:16 AM:liquibase: Reading from `DATABASECHANGELOG`
INFO 8/4/12 7:16 AM:liquibase: Reading from `DATABASECHANGELOG`
INFO 8/4/12 7:16 AM:liquibase: ChangeSet db.changelog.xml::1::shameer ran successfully in 74ms
INFO 8/4/12 7:16 AM:liquibase: Successfully released change log lock
Liquibase Update Successful

If you look at the database, you will see a user table with following structure:

*************** 1. row ***************
  Field: id
   Type: int(11)
   Null: NO
    Key: PRI
Default: NULL
  Extra: 
*************** 2. row ***************
  Field: name
   Type: varchar(50)
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************** 3. row ***************
  Field: email
   Type: varchar(128)
   Null: NO
    Key: UNI
Default: NULL
  Extra: 

Did you notice that we don’t have an auto increment column in the table? We can add a second changeset to alter the table making the following changes:

• Change id column to auto_increment
• Rename column name to fullname
• Add new column age

<changeSet id="2" author="shameer">
 <addAutoIncrement tableName="user" columnName="id" columnDataType="int" />
 <renameColumn tableName="user" oldColumnName="name" newColumnName="fullname" columnDataType="varchar(100)"/>
 <addColumn tableName="user">
  <column name="age" type="SMALLINT"/>
 </addColumn>
</changeSet>

Here, the addAutoIncrement tag will make the id column auto incrementing. renameColumn will rename a column by accepting the name of column to be changed in the oldColumnName attribute and the new name in newColumnName. Let’s run Liquibase once again and then look at the user table. You should see the two modified columns and a new age column.

Generating Changelog file

What if you’ve already started your project without Liquibase, and then you realize its going to be hard to deal with database changes without some automation mechanism? Liquibase comes with a handy feature to generate changelog file from your existing schemas. Use the following command to generate changelog file with the name specified in liquibase.properties.

liquibase generateChangeLog

The generated changelog will have create statements for all of the tables in your database. When we develop an application on a local machine, it’s easier to make changes directly in MySQL than creating changelog files for each change. But we’ll need to have those changes across multiple developers in the team. Luckily Liquibase also provides a facility to compare two databases and generate changelogs from them. Here are the steps to generate change log file for newly created tables in a database:

1. Take a backup of database using mysqldump
2. Create a temporary database
3. Import the dump to a temporary database
4. Drop all changes done in original database
5. Generate a changelog using Liquibase.

liquibase diffChangeLog 
--referenceUrl=jdbc:mysql://localhost/application_temp 
--referenceUsername=dbuser 
--referencePassword=secret

This will append a new changeset in db.changelog.xml for creating the missing tables in the database.

<changeSet author="shameerc (generated)" id="1344051849388-1">
 <createTable tableName="temp">
  <column autoIncrement="true" name="id" type="INT">
   <constraints nullable="false" primaryKey="true"/>
  </column>
  <column name="name" type="VARCHAR(200)"/>
  <column name="value" type="TEXT"/>
 </createTable>
</changeSet>

Once generated, you should check and verify this against your local database before you commit it to version control. Also be cautious when generating changelog files like this since Liquibase will consider renamed columns as a drop+add column which will cause data loss.

Rolling Back Changes

Up until now we’ve only looked at making changes to our database schema. But what if we need to undo changes after they have been applied to a database? We can easily rollback changes applied through Liquibase using rollback commands. Most of the refactoring tags like createTable and renameColumn will automatically generate rollback statements. If that doesn’t satisfy your needs, you can add custom rollback statements to a changeset. You can rollback to certain number of previous changesets, to a given date, or a particular tag that you previously created.

liquibase rollbackCount 1

Liquibase allows us to add checkpoints to our database by tagging the current state of it. This helps us easily rollback to a specific state by simply calling its tag.

liquibase tag checkpoint_1

You can then rollback to this state after making some changes, by issuing following command.

liquibase rollback checkpoint_1

Sometimes you may want to check the SQL applied for rolling back the changes. To achieve this, replace rollback with rollbackSql and rollbackCount with rollbackCountSql. Liquibase will print the result to standard output, which you can redirect to a file and save for later execution. You can specify custom statements to be executed in a rollback tag for each changelog. When you perform a rollback, Liquibase will apply these statements instead of the default rollback SQL.

<changeSet id="6" author="shameer">
 <modifyDataType columnName="value" newDataType="TEXT" tableName="temp"/>
 <rollback>
  <dropTable tableName="temp"/>
 </rollback>
</changeSet>

You can verify it by creating rollback SQL after applying the changeset.

liquibase rollbackCountSql 1

-- Lock Database
-- Rolling Back ChangeSet: db.changelog.xml::6::shameer::(Checksum: 3:29343b94088e34367e51a6633f572b81)
DROP TABLE `temp`;

DELETE FROM `DATABASECHANGELOG`  WHERE ID='6' AND AUTHOR='shameer' AND FILENAME='db.changelog.xml';

-- Release Database Lock
Liquibase Rollback Successful

Conditionally Applying Changes

Sometimes you may want to check for a certain condition before executing a changeset, such as making sure a table contains no data before you drop it. preConditions is a useful tag that lets you check for various conditions in your database.

<changesSet id="8" author="shameer">
 <preConditions onFail="CONTINUE">
  <sqlCheck expectedResult="0">select count(*) from user</sqlCheck>
 </preConditions>
 <dropTable tableName="user"/>
</changesSet>

In this case, Liquibase will first check if the user table contains any records, and if so then it will continue without executing this changeset. If the condtion is critical, you can halt the execution by setting onFail="HALT" instead of CONTINUE. Preconditions have many other handy features such as checking which database system is using, the username of current user, etc. which you can read more about in their manual.

Summary

In this article we have talked about Liquibase, a database refactoring and change management tool. The most important use of Liquibase is to sync database changes with multiple developers in a team; when you make some changes in local database, other developers can pull those changes and apply in their machines. Your changes will be versioned in each changesets and you can easily rollback to previous state if something goes wrong. Apart from that, you can use Liquibase for database migration and deployment. I’ve not covered all the great features of Liquibase in this article, but Liquibase has a very good manual which you should definitely read. Image via Fotolia

Frequently Asked Questions (FAQs) about Liquibase Database Versioning