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.
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="http://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,
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="http://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
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
Any column constraints are specified with
constraints tags. In our example, the
id column has primary key and not null constraints, and the
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:
- Rename column
- Add new column
<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>
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
Let’s run Liquibase once again and then look at the
user table. You should see the two modified columns and a new
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
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:
- Take a backup of database using
- Create a temporary database
- Import the dump to a temporary database
- Drop all changes done in original database
- 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
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
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
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.
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