Spring Boot Liquibase Gradle Example

Introduction

It is often important to consider when developing web application, how to evolve the database schema of a Java web application.

Liquibase is an open source library for tracking, managing and applying database changes that can be used for any database. It helps you create the schema, run them during deployment and also help you write automated tests so that your changes will work in production.

Liquibase is a tool that reads a list of database changes from a changelog file. The changelog file is often XML-based, but it could be in other formats also, such as YAML, JSON and SQL formats. Once a new change is added to the changelog file, Liquibase will read the file, detect the new change, and apply it to the database. When code changes are committed to the version control system, the changelog file containing the database “version” also gets committed along with it.

In this example I will specify the changelog files in XML format, if you want to specify it using YAML and SQL formats then please read here Evolving Database using Spring Boot and Liquibase

For more information http://www.liquibase.org/

Benefits

Schema could be written database-agnostic. This means code changes are specified once and tests are carried on different databases. For example, write tests on H2 and run the migration on MySQL without changing the code.

Code changes could be specified in XML, YAML, JSON and SQL formats as per your flavor.

The changes could be picked up or choosen to be applied in different environments(dev, staging, QA) as per the requirements. An example could be, seed some static data in development environment to test your application but not in production environment.

Prerequisites

Java at least version 8 needs to be installed and configured
Gradle plugin needs to be installed into Eclipse
Gradle 4.x needs to installed and configured
Dependencies : Spring boot, slf4j, h2 in-memory database

Creating and setting up Gradle project

Create gradle project called SpringBootLiquibaseGradle using the following gradle dependencies

buildscript {
	ext {
 		springBootVersion = '1.5.8.RELEASE'
    }

    repositories {
         mavenCentral()
    }

    dependencies {
         classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'

jar {
     baseName = 'SpringBootGradleLiquibase'
     version = '0.0.1-SNAPSHOT'
     manifest {
        attributes("Main-Class": "com.jeejava.main.Application")
     }
}

sourceCompatibility = 1.8
targetCompatibility = 1.8

configurations {
    all*.exclude module : 'spring-boot-starter-logging'
}

repositories {
    mavenCentral()
}

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web")
    compile("org.springframework.boot:spring-boot-starter-jdbc")
    compile("org.liquibase:liquibase-core")
    runtime("mysql:mysql-connector-java")
    compile("org.slf4j:slf4j-api")
    compile("org.slf4j:slf4j-log4j12")
    compile("commons-logging:commons-logging")
}

When referring to build directories, the tutorial assumes the standard Gradle build paths for your gradle project:

SpringBootLiquibaseGradle/src/main/java – Java source directory
SpringBootLiquibaseGradle/src/main/resources – Resource directory
SpringBootLiquibaseGradle/src/test/java – Java test directory
SpringBootLiquibaseGradle/src/test/resources – Resource test directory

Create main class

package com.jeejava.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

	public static void main(String[] args) {
		SpringApplication.run(Application.class, args);
	}

}

You should be able to build the blank project. Please ensure that the overall state is “BUILD SUCCESS” before continuing.

Note: You won’t be able to import Spring Boot dependencies until your project downloads all dependencies. So first create main class with empty main method and later when your project is successfully built then you can import required dependencies.

Execute command – gradle clean build on the project root directory from cmd prompt.

You will see the required jar files get downloaded and finally you would get “BUILD SUCCESSFUL” message.

Create loggers

Put below log4j.properties file under classpath resource SpringBootLiquibaseGradle/src/main/resources

log4j.rootLogger=INFO, ACT

log4j.appender.ACT=org.apache.log4j.ConsoleAppender
log4j.appender.ACT.layout=org.apache.log4j.PatternLayout
log4j.appender.ACT.layout.ConversionPattern= %d{hh:mm:ss,SSS} [%t] %-5p %c %x - %m%n

Create databaseChangeLog file

Create an XMl file called db.changelog-master.xml under SpringBootLiquibaseGradle/src/main/resources/db. This is where all the changes are specified. An empty databaseChangeLog looks like

<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-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">


</databaseChangeLog>

ChangeSet: This is atomic change that would be applied to the database. Each changeset is uniquely identified by id, author and package of the file where changeset is defined. Single or multiple ChangeSets could be created as per your choice. Each changeset is run as a single transaction by Liquibase. An example of changeset may look like

<changeSet author="Soumitra Roy" id="01">
	<createTable remarks="Used to store user's information"
		tableName="users">
		<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(100)" />
		<column name="phone" type="int" />
		<column name="address" type="int">
			<constraints nullable="false" />
		</column>
	</createTable>

	<createTable remarks="Used to store user's address"
		tableName="addresses">
		<column name="id" type="int">
			<constraints primaryKey="true" nullable="false" />
		</column>
		<column name="street" type="varchar(100)">
			<constraints nullable="false" />
		</column>
		<column name="city" type="varchar(100)" />
		<column name="pin" type="int" />
	</createTable>

	<addForeignKeyConstraint baseTableName="users"
		baseColumnNames="address" constraintName="address_fk"
		referencedTableName="addresses" referencedColumnNames="id" />
</changeSet>

In the above example, we have created two different tables as part of single changeset.

The changesets could be anything related to database changes, for example, create table, insert into, alter table.

Some of the examples are

creating a table and applying constraints at column level

<createTable remarks="Used to store user's address"
	tableName="addresses">
	<column name="id" type="int">                                
		<constraints primaryKey="true" nullable="false" unique="true" />
	</column>
	<column name="street" type="VARCHAR(100)">
					<constraints nullable="false" />
	</column>
	<column name="city" type="VARCHAR(100)" />
	<column name="pin" type="int" />
</createTable>

adding foreign key constraint

<addForeignKeyConstraint baseTableName="users"
	baseColumnNames="address" constraintName="address_fk"
	referencedTableName="addresses" referencedColumnNames="id" />

inserting data into the table

<insert tableName="addresses">
	<column name="id" valueNumeric="1"/>
	<column name="street" value="street"/>
	<column name="city" value="city"/>
	<column name="pin" value="100001" />
</insert>

In order to organize changesets, I have kept changesets in separate files and use include tag in my databaseChangeLog to include them.

<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-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

	<include file="changelog/01-create-users-and-addresses-schema.xml"
					relativeToChangelogFile="true" />
	<include file="changelog/02-insert-data-users.xml"
					relativeToChangelogFile="true" />
	<include file="changelog/02-insert-data-addresses.xml"
					relativeToChangelogFile="true" />
</databaseChangeLog>

01-create-users-and-addresses-schema.xml

<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-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

	<changeSet author="Soumitra Roy" id="01">
		<createTable remarks="Used to store user's information"
			tableName="users">
			<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(100)" />
			<column name="phone" type="int" />
			<column name="address" type="int">
				<constraints nullable="false" />
			</column>
		</createTable>

		<createTable remarks="Used to store user's address"
			tableName="addresses">
			<column name="id" type="int">
				<constraints primaryKey="true" nullable="false" />
			</column>
			<column name="street" type="varchar(100)">
				<constraints nullable="false" />
			</column>
			<column name="city" type="varchar(100)" />
			<column name="pin" type="int" />
		</createTable>

		<addForeignKeyConstraint baseTableName="users"
			baseColumnNames="address" constraintName="address_fk"
			referencedTableName="addresses" referencedColumnNames="id" />
	</changeSet>
</databaseChangeLog>

02-insert-data-addresses.xml

<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-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

	<changeSet id="02" author="Soumitra Roy">
		<insert tableName="addresses">
			<column name="id" valueNumeric="1" />
			<column name="street" value="street1" />
			<column name="city" value="city1" />
			<column name="pin" valueNumeric="123456" />
		</insert>
		<insert tableName="addresses">
			<column name="id" valueNumeric="2" />
			<column name="street" value="street2" />
			<column name="city" value="city2" />
			<column name="pin" valueNumeric="123457" />
		</insert>
	</changeSet>
</databaseChangeLog>

02-insert-data-users.xml

<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-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

	<changeSet id="03" author="Soumitra Roy">
		<insert tableName="users">
			<column name="id" valueNumeric="1" />
			<column name="name" value="Soumitra" />
			<column name="email" value="contact@jeejava.com" />
			<column name="phone" valueNumeric="1234567890" />
			<column name="address" valueNumeric="1" />
		</insert>
		<insert tableName="users">
			<column name="id" valueNumeric="2" />
			<column name="name" value="Rushikesh" />
			<column name="email" value="contact@jeejava.com" />
			<column name="phone" valueNumeric="1234567891" />
			<column name="address" valueNumeric="2" />
		</insert>
	</changeSet>

</databaseChangeLog>

Create file src/main/resource/application.properties to load the databaseChangeLog file during Spring Boot application startup

liquibase.change-log=classpath:db/db.changelog-master.xml

add below datasource details

spring.datasource.url=jdbc:mysql://localhost/jeejava
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Additionally, add the following line

spring.h2.console.enabled=true

The above line enables you to login to H2 database through browser during development.

We don’t want tomcat server in spring boot application to be started on random port, so set the server port

server.port=9999

The whole application.properties file looks like below

#start server on specific port
server.port=9999

#database connection parameters
spring.datasource.url=jdbc:mysql://localhost/jeejava
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.h2.console.enabled=true


#liquibase change log file
liquibase.change-log=classpath:db/db.changelog-master.xml

Running the application

Run the main class Application.java file.

Output on console

...
07:39:57,721 [main] INFO  liquibase  - Successfully acquired change log lock
07:39:58,541 [main] INFO  liquibase  - Creating database history table with name: jeejava.DATABASECHANGELOG
07:39:58,736 [main] INFO  liquibase  - Reading from jeejava.DATABASECHANGELOG
07:39:58,968 [main] INFO  liquibase  - classpath:db/db.changelog-master.xml: classpath:db/changelog/01-create-users-and-addresses-schema.xml::01::Soumitra Roy: Table users created
07:39:59,168 [main] INFO  liquibase  - classpath:db/db.changelog-master.xml: classpath:db/changelog/01-create-users-and-addresses-schema.xml::01::Soumitra Roy: Table addresses created
07:40:00,092 [main] INFO  liquibase  - classpath:db/db.changelog-master.xml: classpath:db/changelog/01-create-users-and-addresses-schema.xml::01::Soumitra Roy: Foreign key constraint added to users (address)
07:40:00,094 [main] INFO  liquibase  - classpath:db/db.changelog-master.xml: classpath:db/changelog/01-create-users-and-addresses-schema.xml::01::Soumitra Roy: ChangeSet classpath:db/changelog/01-create-users-and-addresses-schema.xml::01::Soumitra Roy ran successfully in 1293ms
07:40:00,157 [main] INFO  liquibase  - classpath:db/db.changelog-master.xml: classpath:db/changelog/02-insert-data-users.xml::02::Soumitra Roy: New row inserted into addresses
07:40:00,159 [main] INFO  liquibase  - classpath:db/db.changelog-master.xml: classpath:db/changelog/02-insert-data-users.xml::02::Soumitra Roy: New row inserted into addresses
07:40:00,230 [main] INFO  liquibase  - classpath:db/db.changelog-master.xml: classpath:db/changelog/02-insert-data-users.xml::02::Soumitra Roy: ChangeSet classpath:db/changelog/02-insert-data-users.xml::02::Soumitra Roy ran successfully in 78ms
07:40:00,355 [main] INFO  liquibase  - classpath:db/db.changelog-master.xml: classpath:db/changelog/02-insert-data-addresses.xml::03::Soumitra Roy: New row inserted into users
07:40:00,357 [main] INFO  liquibase  - classpath:db/db.changelog-master.xml: classpath:db/changelog/02-insert-data-addresses.xml::03::Soumitra Roy: New row inserted into users
07:40:00,393 [main] INFO  liquibase  - classpath:db/db.changelog-master.xml: classpath:db/changelog/02-insert-data-addresses.xml::03::Soumitra Roy: ChangeSet classpath:db/changelog/02-insert-data-addresses.xml::03::Soumitra Roy ran successfully in 64ms
07:40:00,528 [main] INFO  liquibase  - Successfully released change log lock
...

You will find tables created into the database. You will find total four rows inserted into users and addresses tables. You will also find three rows inserted into the table DATABASECHANGELOG. This row identifies all details about the executed file. You will also find one row inserted into the table DATABASECHANGELOGLOCK. This row identifies whether current operation holds lock on changesets or not.

Thanks for reading.

Soumitra Roy Sarkar

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials Twitter Facebook  Google Plus Linkedin Or Email Me

Leave a Reply

Your email address will not be published. Required fields are marked *