How to set up Liquibase for Database change management

by GroupKT
Jul 21, 2014 02:37 AM

Liquibase is a opensource tool purely written in Java. It has capability of tracking, managing and applying database changes. Here is how to manage database related changes to Enterprise application with Liquibase.
Now days most of projects are running in Agile model, where everyday a new change comes int database schema, table structure etc. Generally a large number of team works on an enterprise project. It is hard to track and manage the database changes with the system.

Every changes in Database, schema or tables has to go through a DBA approval process, once the changes is approved, this changes should be applied to the application automatically without any manual intervention.Liquibase is the tool that helps in all these type of complexity.

Here are some benefits of Liquibase
1. opensource
2. can track manage and apply database changes
3. keeps history of changes
4. automatic rollback support
5. ability to automation
6. less human resources / errors
6. database changes will be in sync between different environment
Here is normal process flow if you are using a Liquibase to manage database changes

1. Decide database changes (by developer / technical lead)
2. get approval (from DBA)
3. make an entry in Liquibase change log (an xml file)
4. test it dev environment
5. checking this changelog file into version control
6. run a build/deployment on the environment (by the latest code in version control)
(if Liquibase is configured to run with build, it will take case of database related changes here)
7. new database changes applied to the environment.

Now we will try to setup Liquibase with a web application, however Liquibase can be integrated with maven build process as well. But here we integrate Liqiobase with a web application.

Step 1. adding Liquibase jar in classpath

If you are using maven then add following dependency into pom file
<dependency>
	<groupId>org.liquibase</groupId>
	<artifactId>liquibase-core</artifactId>
	<version>3.2.0</version>
</dependency>
otherwise you can download Liquibase jar file and add it to WEB-INF\lib directory of web application.

Step 2. adding Liquibase listener in web.xml file

copy and paste following code into web.xml
<context-param>
	<param-name>liquibase.changelog</param-name>
	<param-value>db/db-incremental.xml</param-value>
</context-param>

<context-param>
	<param-name>liquibase.datasource</param-name>
	<param-value>java:comp/env/jdbc/MySQLDS</param-value>
</context-param>

<listener>
	<listener-class>liquibase.integration.servlet.LiquibaseServletListener</listener-class>
</listener>

Liquibase maintains every database change in a xml file, this file is called changelog. In the web.xml we are creating a Liquibase listener liquibase.integration.servlet.LiquibaseServletListener and telling following things to this listener
1. the location of changelog xmls file and
2. the datasource of the database to be managed by Liquibase.

Step 3. creating a cahngelog xml file

If you are using create a directory db in src/main/resources other wise create db directory inside WEB-INF/classes.
and then create an xml db-incremental.xmlfile inside db
<?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="user.create" author="manoj">
		<sql>
			CREATE TABLE `user` (
			`id` int(11) NOT NULL AUTO_INCREMENT,
			`name` varchar(25) NOT NULL,
			PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=latin1;
		</sql>
	</changeSet>
</databaseChangeLog>

In the above example I created a user table. Every database change is a changeset. . Every changeset has two mandatory attribute 1. id and 2. author.
The combination of these two attribute should be unique in the Liquibase context. There is not such pattern for id attribute you can follow any meaningful pattern e.g. modulename.changeset_number.

Step 4. Deploy the application and restart web-server

Now you are all set to run manager your database related operation with Liquibase. Just deploy the application and restart the server and monitor the server log. You will see something similar to below
INFO 7/23/14 12:04 AM: liquibase: Successfully acquired change log lock
INFO 7/23/14 12:04 AM: liquibase: Reading from schema.DATABASECHANGELOG
INFO 7/23/14 12:04 AM: liquibase: db/db-incremental.xml: db/db-incremental.xml::user.create::manoj: Custom SQL executed
INFO 7/23/14 12:04 AM: liquibase: db/db-incremental.xml: db/db-incremental.xml::user.create::manoj: ChangeSet db/db-incremental.xml::user.create::manoj ran successfully in 355ms
INFO 7/23/14 12:04 AM: liquibase: Successfully released change log lock

Go and verify your database, you will see a user table there.

Points to be noted while using Liquibase


1. the combination of id and author should be unique
2. once any changeset entry is executed, nothing can be changed into that changeset. if you want to revert some thing then create another entry for reversal. e.g. want to add one more column to user table, then create another chnageset to add a column.
3. All executed changeset details can be verified from DATABASECHANGELOG table.

How to fix liquibase: Waiting for changelog lock.... problem with Liquibase

If you are getting
liquibase: Waiting for changelog lock....
liquibase: Waiting for changelog lock....
liquibase: Waiting for changelog lock....
liquibase: Waiting for changelog lock....
liquibase: Waiting for changelog lock....
liquibase: Waiting for changelog lock....
liquibase: Waiting for changelog lock....
then it means last Liquibase startup was stopped abruptly either by killing JVM or due to any other reason. To fix this there are three solutions
1. run UPDATE DATABASECHANGELOGLOCK SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1; or
2. run DELETE FROM DATABASECHANGELOGLOCK; or
3. drop the table DROP TABLE DATABASECHANGELOGLOCK;, this table will be recreated automatically at next start up.

comments powered by Disqus