How to set up Liquibase for Database change management

2466
Mar 20, 2016

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-INFlib 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

© Copyright 2016