Versioning Your Database with Liquibase

Shameer C
Shameer C
Share

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

What is the primary function of Liquibase in database versioning?

Liquibase is a powerful open-source tool for database versioning. It allows developers to manage and track database changes, ensuring consistency and integrity across different environments. Liquibase uses a changelog to keep track of all changes, which can be written in various formats like XML, JSON, YAML, or SQL. This changelog is version-controlled, allowing developers to see who made changes, what those changes were, and when they were made. This helps in avoiding conflicts and maintaining a smooth workflow.

How does Liquibase handle database refactoring?

Liquibase provides a structured way to handle database refactoring. It allows developers to describe the changes in a database-agnostic manner, which means the same changelog can be used across different types of databases. Liquibase then converts these changes into SQL statements that are specific to the database you are using. This feature makes Liquibase a versatile tool for managing database changes across various environments.

Can I rollback changes with Liquibase?

Yes, Liquibase provides a rollback feature. This is one of its most powerful features, allowing developers to undo changes made to the database. Each change set in the changelog can include a rollback section, which describes how to undo the changes if necessary. This provides a safety net for developers, ensuring that they can easily revert changes if something goes wrong.

How does Liquibase ensure database consistency?

Liquibase ensures database consistency by using a databasechangelog table. This table keeps track of all the changes that have been applied to the database. When Liquibase is run, it checks this table to see which changes have already been applied and only applies the new ones. This ensures that the database is always in a consistent state, regardless of the environment it’s in.

Can I use Liquibase with PostgreSQL?

Yes, Liquibase supports a wide range of database systems, including PostgreSQL. It allows you to manage and track changes in your PostgreSQL database, ensuring consistency and integrity. You can use the same changelog for different types of databases, making Liquibase a versatile tool for database versioning.

How do I get started with Liquibase?

To get started with Liquibase, you first need to download and install it. You can then create a changelog file, which will keep track of all your database changes. You can write this changelog in various formats like XML, JSON, YAML, or SQL. Once you’ve made some changes, you can use Liquibase to apply them to your database.

What are the benefits of using Liquibase for database versioning?

Liquibase offers several benefits for database versioning. It provides a structured way to manage and track database changes, ensuring consistency across different environments. It supports a wide range of database systems, making it a versatile tool. It also provides a rollback feature, allowing developers to easily undo changes if something goes wrong.

Can I use Liquibase with other version control systems?

Yes, Liquibase can be used with any version control system. It works by tracking database changes in a changelog, which can be version-controlled using your preferred system. This allows you to keep track of who made changes, what those changes were, and when they were made.

How does Liquibase handle conflicts?

Liquibase handles conflicts by using a databasechangeloglock table. This table ensures that only one instance of Liquibase can make changes to the database at a time. If a conflict occurs, Liquibase will wait until the lock is released before applying changes.

Can I automate database changes with Liquibase?

Yes, Liquibase supports automation of database changes. It can be integrated into your build process, allowing you to automatically apply changes as part of your build. This helps in maintaining a consistent database state across different environments, making it easier to manage and track changes.