How Liquibase Works
If you are using Mac with brew, installing Liquibase is simple. Just runbrew 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 namedapplication
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 updateIf 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 SuccessfulIn 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: secretNext 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 SuccessfulIf 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 toauto_increment
- Rename column
name
tofullname
- 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 inliquibase.properties
.
liquibase generateChangeLogThe 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
mysqldump
- 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=secretThis 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 likecreateTable
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 1Liquibase 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_1You can then rollback to this state after making some changes, by issuing following command.
liquibase rollback checkpoint_1Sometimes 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 FotoliaFrequently 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.
Shameer is a passionate programmer and open-source enthusiast from Kerala, India. He has experience in web development using Scala, PHP, Ruby, MySQL, and JavaScript. While not working, Shameer spends his time coding personal projects, learning, watching screen casts, blogging, etc. His specific areas of interest include cloud computing, and system and database administration.