Database Change Management using Liquibase and Maven

Hi Guys, this is my recent research

We never develop code without version control, why should we develop database without it?

Liquibase is an open source (Apache 2.0 Licensed), database-independent library for tracking, managing and applying database changes. It is built on a simple premise: All database changes are stored in a human readable yet trackable form.

Simple configuration of liquibase project is projected here.

Step1: Create a Maven Project using your IDE(in my case Eclipse3.7)

Step2: Place the liquibase Maven Plugin in the POM.xml

as shown below:


<project>

..........

<build>
 <plugins>
 <plugin>
 <groupId>org.liquibase</groupId>
 <artifactId>liquibase-maven-plugin</artifactId>
 <version>2.0.1</version>
 <dependencies>
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.21</version>
 </dependency>
 </dependencies>
 <executions>
 <execution>
 <configuration>
 <propertyFile>src/main/resources/liquibase.properties</propertyFile>
 </configuration>
 <phase>process-resources</phase>
 <goals>
 <goal>update</goal>
 </goals>
 </execution>
 </executions>
 </plugin>
 </plugins>
 </build>

</project>

Step3: Place the liquibase.properties file in src/main/resources/ folder  – and a sample is here:


#liquibase.properties

changeLogFile=src/main/resources/changelogs/change_log.xml
 driver=com.mysql.jdbc.Driver
 url=jdbc:mysql://localhost:3306/testdbbkp
 username=user
 password=pwd

Note: the schema should exist before you run the project

Step4: Now create a change_log.xml to create all tables and even modifications to those tables as shown below


<databaseChangeLog
 xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
 xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
 http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<changeSet id="1" author="pavan">
 <createTable tableName="department">
 <column name="id" type="int">
 <constraints primaryKey="true" nullable="false"/>
 </column>
 <column name="name" type="varchar(50)">
 <constraints nullable="false"/>
 </column>
 <column name="active" type="boolean" defaultValueBoolean="true"/>
 </createTable>
 </changeSet>

<changeSet id="2" author="Mantha Pavan Kumar">
 <comment>
 changeSet 2 with modifications to existing table;
 </comment>

<renameColumn tableName="department" oldColumnName="id" newColumnName="dept_id" columnDataType="int"/>
 <renameColumn tableName="department" oldColumnName="name" newColumnName="dept_name" columnDataType="varchar(50)"/>
 </changeSet>

<changeSet id="3" author="Mantha Pavan Kumar">
 <comment>
 changeSet 2 with modifications to existing table;
 </comment>

<loadData tableName="department" file="src/main/resources/csv/Book1.csv">
 <column name="dept_id" type="numeric"/>
 <column name="dept_name" type="string"/>
 </loadData>
 </changeSet>

<changeSet id="4" author="Mantha Pavan Kumar">
 <comment>
 creating table using sql commands with liqibase;
 </comment>

<sql>
 create table employee(id int,first_name varchar(50),last_name varchar(50));
 </sql>
 </changeSet>

<changeSet id="5" author="Mantha Pavan Kumar">
 <comment>
 creating complete database with data using sql backup file;
 </comment>

<sqlFile path="src/main/resources/sql/database.sql"/>
 </changeSet>

<changeSet id="6" author="Mantha Pavan Kumar">
 <comment>
 creating a lookuptable
 </comment>
 <createTable tableName="lookup">
 <column name="lookup_id" type="int">
 <constraints primaryKey="true" nullable="false"/>
 </column>
 <column name="lookup_name" type="varchar(50)">
 <constraints nullable="false"/>
 </column>
 <column name="active" type="boolean" defaultValueBoolean="true"/>
 </createTable>
 </changeSet>

</databaseChangeLog>

The above file shows you the different configurations that can be made. once the project is ran liquibase will create two tables databasechangelog and databasechangeloglock. now the liquibase will store all the modifications we do in databasechangelog table according to the <changeSet> defined in “change_log.xml”

to run the project issue the command : mvn clean install package

all your tables gets installed and track will be maintained

Happy coding 🙂

Advertisements

2 thoughts on “Database Change Management using Liquibase and Maven

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s