Posted in Spring Boot

Spring Boot Liquibase Gradle Example with XML based Configuration

Introduction

In this example I will show you how to use Liquibase to maintain your database changes history over the time. I will use here XML based configuration for Liquibase. It is often important to consider when developing web application, how to evolve the database schema of a Java web application. I will tell you how to build project using both maven and gradle build tools.

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.

Recommended reading: How to setup Liquibase in Spring for Multiple DataSources

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

Benefits of Liquibase

  • 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

At least Java 8, Eclipse 4.12, Gradle 5.6, Maven 3.6.1, Liquibase 3.8.2, MySQL 8.0.17, Spring Boot 2.2.1

Create Project

You need to create either gradle or maven based project in Eclipse IDE.

If you are creating gradle project (spring-boot-liquibase-xml) in Eclipse then consider the following build.gradle script:

buildscript {
	ext {
		springBootVersion = '2.2.2.RELEASE'
	}
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

plugins {
    id 'java-library'
    id 'org.springframework.boot' version '2.2.2.RELEASE'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    mavenCentral()
}

dependencies {
    implementation "org.springframework.boot:spring-boot-starter:${springBootVersion}"
    implementation("org.springframework.boot:spring-boot-starter-jdbc:${springBootVersion}")
    implementation("org.liquibase:liquibase-core:3.8.2")
    runtime("mysql:mysql-connector-java:8.0.17")
    //need only for jdk 9 or above
    runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

If you are creating maven based project in Eclipse then consider the following maven build file, pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>spring-boot-liquibase-xml</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.2.RELEASE</version>
	</parent>

	<properties>
		<java.version>12</java.version>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>

		<dependency>
			<groupId>org.liquibase</groupId>
			<artifactId>liquibase-core</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<scope>runtime</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

Create Changelog File

We will create changelog files for database, for example, for creating tables, inserting data into tables.

Create an XML file called db.changelog-master.xml under folder 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.

Examples of Changeset

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. The below file db.changelog-master.xml is created under src/main/resources/db folder.

<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-addresses.xml"
		relativeToChangelogFile="true" />
		
	<include file="changelog/02-insert-data-users.xml"
		relativeToChangelogFile="true" />

</databaseChangeLog>

01-create-users-and-addresses-schema.xml in kept under src/main/resources/db/changelog folder.

<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 file is kept under src/main/resources/db/changelog folder.

<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 file is kept under src/main/resources/db/changelog folder.

<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@roytuts.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@roytuts.com" />
			<column name="phone" valueNumeric="1234567891" />
			<column name="address" valueNumeric="2" />
		</insert>
	</changeSet>

</databaseChangeLog>

Create file src/main/resource/application.properties to load the database changelog file during Spring Boot application startup. We also declare the database settings in this file.

We don’t need to create any bean for database management and Liquibase configuration if we have the exact settings as shown below:

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

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

The default location of changelog master file is classpath:/db/changelog and Liquibase searches for a file db.changelog-master.yaml. Therefore as we are using XML file at different location so we required to declare the location of the changelog master file.

In Spring Boot application the key liquibase.change-log does not work, so you need to use spring.liquibase.changeLog.

Create Main Class

The beauty of Spring Boot application is to create a main class and mark with @SpringBootApplication and executing this main class will serve your purpose.

package com.roytuts.spring.boot.liquibase.xml;

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

@SpringBootApplication
public class SpringBootLiquibaseXmlApp {

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

}

Testing the Application

Running the main class will give you the following output in the console:

40.555  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
40.606  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE roytuts.DATABASECHANGELOGLOCK (ID INT NOT NULL, `LOCKED` BIT(1) NOT NULL, LOCKGRANTED datetime NULL, LOCKEDBY VARCHAR(255) NULL, CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))
40.698  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
40.718  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : DELETE FROM roytuts.DATABASECHANGELOGLOCK
40.720  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOGLOCK (ID, `LOCKED`) VALUES (1, 0)
40.731  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT `LOCKED` FROM roytuts.DATABASECHANGELOGLOCK WHERE ID=1
40.751  INFO 11840 --- [           main] l.lockservice.StandardLockService        : Successfully acquired change log lock
46.952  INFO 11840 --- [           main] l.c.StandardChangeLogHistoryService      : Creating database history table with name: roytuts.DATABASECHANGELOG
46.956  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE roytuts.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35) NULL, `DESCRIPTION` VARCHAR(255) NULL, COMMENTS VARCHAR(255) NULL, TAG VARCHAR(255) NULL, LIQUIBASE VARCHAR(20) NULL, CONTEXTS VARCHAR(255) NULL, LABELS VARCHAR(255) NULL, DEPLOYMENT_ID VARCHAR(10) NULL)
47.072  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOG
47.078  INFO 11840 --- [           main] l.c.StandardChangeLogHistoryService      : Reading from roytuts.DATABASECHANGELOG
47.079  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT * FROM roytuts.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
47.083  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
47.192  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE roytuts.users (id INT NOT NULL, name VARCHAR(50) NOT NULL, email VARCHAR(100) NULL, phone INT NULL, address INT NOT NULL, CONSTRAINT PK_USERS PRIMARY KEY (id)) COMMENT='Used to store user''s information'
47.350  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : ALTER TABLE roytuts.users COMMENT = 'Used to store user''s information'
47.373  INFO 11840 --- [           main] liquibase.changelog.ChangeSet            : Table users created
47.375  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE roytuts.addresses (id INT NOT NULL, street VARCHAR(100) NOT NULL, city VARCHAR(100) NULL, pin INT NULL, CONSTRAINT PK_ADDRESSES PRIMARY KEY (id)) COMMENT='Used to store user''s address'
47.542  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : ALTER TABLE roytuts.addresses COMMENT = 'Used to store user''s address'
47.559  INFO 11840 --- [           main] liquibase.changelog.ChangeSet            : Table addresses created
47.562  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : ALTER TABLE roytuts.users ADD CONSTRAINT address_fk FOREIGN KEY (address) REFERENCES roytuts.addresses (id)
47.860  INFO 11840 --- [           main] liquibase.changelog.ChangeSet            : Foreign key constraint added to users (address)
47.861  INFO 11840 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:db/changelog/01-create-users-and-addresses-schema.xml::01::Soumitra Roy ran successfully in 684ms
47.862  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT MAX(ORDEREXECUTED) FROM roytuts.DATABASECHANGELOG
47.866  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('01', 'Soumitra Roy', 'classpath:db/changelog/01-create-users-and-addresses-schema.xml', NOW(), 1, '8:f064675b5dfb44aa74cc343bfddb6c1f', 'createTable tableName=users; createTable tableName=addresses; addForeignKeyConstraint baseTableName=users, constraintName=address_fk, referencedTableName=addresses', '', 'EXECUTED', NULL, NULL, '3.8.2', '6237607110')
47.877  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.addresses (id, street, city, pin) VALUES (1, 'street1', 'city1', 123456)
47.881  INFO 11840 --- [           main] liquibase.changelog.ChangeSet            : New row inserted into addresses
47.882  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.addresses (id, street, city, pin) VALUES (2, 'street2', 'city2', 123457)
47.887  INFO 11840 --- [           main] liquibase.changelog.ChangeSet            : New row inserted into addresses
47.893  INFO 11840 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:db/changelog/02-insert-data-addresses.xml::02::Soumitra Roy ran successfully in 18ms
47.895  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('02', 'Soumitra Roy', 'classpath:db/changelog/02-insert-data-addresses.xml', NOW(), 2, '8:7469ed8028aa762990cce657c0f19d02', 'insert tableName=addresses; insert tableName=addresses', '', 'EXECUTED', NULL, NULL, '3.8.2', '6237607110')
47.906  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.users (id, name, email, phone, address) VALUES (1, 'Soumitra', 'contact@roytuts.com', 1234567890, 1)
47.910  INFO 11840 --- [           main] liquibase.changelog.ChangeSet            : New row inserted into users
47.911  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.users (id, name, email, phone, address) VALUES (2, 'Rushikesh', 'contact@roytuts.com', 1234567891, 2)
47.920  INFO 11840 --- [           main] liquibase.changelog.ChangeSet            : New row inserted into users
47.926  INFO 11840 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:db/changelog/02-insert-data-users.xml::03::Soumitra Roy ran successfully in 22ms
47.927  INFO 11840 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('03', 'Soumitra Roy', 'classpath:db/changelog/02-insert-data-users.xml', NOW(), 3, '8:8a1a0d205f46adf4c7e335d9afe786a5', 'insert tableName=users; insert tableName=users', '', 'EXECUTED', NULL, NULL, '3.8.2', '6237607110')
47.948  INFO 11840 --- [           main] l.lockservice.StandardLockService        : Successfully released change log lock
48.528  INFO 11840 --- [           main] c.r.s.b.l.xml.SpringBootLiquibaseXmlApp  : Started SpringBootLiquibaseXmlApp in 23.336 seconds (JVM running for 24.434)
48.571  INFO 11840 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
48.626  INFO 11840 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

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.

Source Code

Download

Thanks for reading.

1 thought on “Spring Boot Liquibase Gradle Example with XML based Configuration”

  1. i followed the example and only changed my database connection parameters but the table isn’t in database. Did i miss something?

    Reply

Leave a Reply

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