Evolving Database using Spring Boot and Liquibase with YAML and SQL Configuration

Liquibase with Spring Boot

In this tutorial I will show you an example on evolving database using Spring Boot and Liquibase with YAML and SQL configuration.

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.

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

Benefits of Liquibase

This could be found in my previous tutorial Spring Boot Liquibase Gradle Example.

Prerequisites

Java 12/22, Gradle 5.6, Maven 3.6.1/3.9.8, Liquibase, MySQL 8.0.17/8.1.0, Spring Boot 2.2.1/3.5.3

Create Project

You need to create either gradle or maven based project in your favorite IDE or tool.

You can use the following pom.xml reference for Spring Boot 3.5.3 version:

<?xml version="1.0" encoding="UTF-8"?>
<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-yaml</artifactId>
  <version>0.0.1-SNAPSHOT</version>

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

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.release>22</maven.compiler.release>
	</properties>

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

		<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>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
		</dependency>
	</dependencies>

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

If you are creating gradle project (spring-boot-liquibase-yaml), 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 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-yaml</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

Create a YAML file called db.changelog-master.yaml under src/main/resources/db folder. This master file will include all the changelogs written in separate files. The complete master changelog file content given as below:

databaseChangeLog:
  - changeSet:
      id: createTable
      author: Soumitra Roy
      changes:
      - sqlFile:
          dbms: mysql
          encoding: utf8
          endDelimiter: \n/\s*\n|\n/\s*$
          path: changelog/scripts/01-create-users-and-addresses-schema.sql
          relativeToChangelogFile: true
          splitStatements: true
          stripComments: true
  - changeSet:
      id: insertTableAddresses
      author: Soumitra Roy
      changes:
      - sqlFile:
          dbms: mysql
          encoding: utf8
          path: changelog/scripts/02-insert-data-addresses.sql
          relativeToChangelogFile: true
          splitStatements: true
          stripComments: true
  - changeSet:
      id: insertTableUsers
      author: Soumitra Roy
      changes:
      - sqlFile:
          dbms: mysql
          encoding: utf8
          path: changelog/scripts/02-insert-data-users.sql
          relativeToChangelogFile: true
          splitStatements: true
          stripComments: true

Notice in the above YAML file, I have not specified any endDelimiter for changeSet ids insertTableAddresses and insertTableUsers because the default endDelimiter is ;.

Now create below SQL file 01-create-users-and-addresses-schema.sql under src/main/resources/db/changelog/scripts to create tables in the MySQL database:

CREATE TABLE ADDRESSES
(
    ID INT NOT NULL PRIMARY KEY,
    STREET VARCHAR(100) NOT NULL,
    CITY VARCHAR(100),
    PIN INT
)
/
CREATE TABLE USERS
(
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    EMAIL VARCHAR(100),
    PHONE INT,
    ADDRESS INT NOT NULL,
    CONSTRAINT USERS_FK FOREIGN KEY(ADDRESS) REFERENCES ADDRESSES(ID)
)
/

Now create below SQL file 02-insert-data-addresses.sql under src/main/resources/db/changelog/scripts folder to insert data into ADDRESSES table:

insert into ADDRESSES(ID, STREET, CITY, PIN) values (1, 'street1', 'city1', 111111);
insert into ADDRESSES(ID, STREET, CITY) values (2, 'street2', 'city2');

Now create below SQL file 02-insert-data-users.sql under src/main/resources/db/changelog/scripts to insert data into USERS table:

insert into USERS(ID, NAME, EMAIL, ADDRESS) values (1, 'Soumitra', 'soumitra@email.com', 1);
insert into USERS(ID, NAME, EMAIL, PHONE, ADDRESS) values (2, 'Suman', 'suman@email.com', 1254789541, 2);

Application Properties

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

You don’t need to create any bean for database management and Liquibase configuration if you 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.yaml
spring.liquibase.changeLog=classpath:db/db.changelog-master.yaml

The default location of changelog master file is classpath:/db/changelog and Liquibase searches for a file db.changelog-master.yaml. Therefore as I am using different location so I 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

Create bwlow main class in order to start up the application and above tables creation and insertion into tables will be occurring during the application startup.

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

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

@SpringBootApplication
public class SpringBootLiquibaseYamlApp {

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

}

Testing the Application

Running the above main class will give you the following output:

57.890+05:30 [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
58.674+05:30 [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@4beabeec
58.678+05:30 [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
59.517+05:30 [           main] liquibase.changelog                      : Creating database history table with name: roytuts.databasechangelog
59.798+05:30 [           main] liquibase.changelog                      : Reading from roytuts.databasechangelog
00.160+05:30 [           main] liquibase.snapshot                       : Creating snapshot
00.298+05:30 [           main] liquibase.lockservice                    : Successfully acquired change log lock
00.299+05:30 [           main] liquibase.command                        : Using deploymentId: 1368797844
00.303+05:30 [           main] liquibase.changelog                      : Reading from roytuts.databasechangelog
00.334+05:30 [           main] liquibase.ui                             : Running Changeset: db/db.changelog-master.yaml::createTable::Soumitra Roy
00.465+05:30 [           main] liquibase.changelog                      : SQL in file changelog/scripts/01-create-users-and-addresses-schema.sql executed
00.468+05:30 [           main] liquibase.changelog                      : ChangeSet db/db.changelog-master.yaml::createTable::Soumitra Roy ran successfully in 130ms
00.487+05:30 [           main] liquibase.ui                             : Running Changeset: db/db.changelog-master.yaml::insertTableAddresses::Soumitra Roy
00.500+05:30 [           main] liquibase.changelog                      : SQL in file changelog/scripts/02-insert-data-addresses.sql executed
00.505+05:30 [           main] liquibase.changelog                      : ChangeSet db/db.changelog-master.yaml::insertTableAddresses::Soumitra Roy ran successfully in 18ms
00.517+05:30 [           main] liquibase.ui                             : Running Changeset: db/db.changelog-master.yaml::insertTableUsers::Soumitra Roy
00.534+05:30 [           main] liquibase.changelog                      : SQL in file changelog/scripts/02-insert-data-users.sql executed
00.540+05:30 [           main] liquibase.changelog                      : ChangeSet db/db.changelog-master.yaml::insertTableUsers::Soumitra Roy ran successfully in 23ms
00.560+05:30 [           main] liquibase.util                           : UPDATE SUMMARY
00.560+05:30 [           main] liquibase.util                           : Run:                          3
00.560+05:30 [           main] liquibase.util                           : Previously run:               0
00.560+05:30 [           main] liquibase.util                           : Filtered out:                 0
00.560+05:30 [           main] liquibase.util                           : -------------------------------
00.560+05:30 [           main] liquibase.util                           : Total change sets:            3
00.561+05:30 [           main] liquibase.util                           : Update summary generated
00.568+05:30 [           main] liquibase.command                        : Update command completed successfully.
00.569+05:30 [           main] liquibase.ui                             : Liquibase: Update has been successful. Rows affected: 7
00.580+05:30 [           main] liquibase.lockservice                    : Successfully released change log lock
00.584+05:30 [           main] liquibase.command                        : Command execution complete
01.337+05:30 [           main] c.r.s.b.l.y.SpringBootLiquibaseYamlApp   : Started SpringBootLiquibaseYamlApp in 5.498 seconds (process running for 6.038)
01.345+05:30 [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
01.354+05:30 [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

You will find tables created into the database. You will find two rows inserted into addresses and two rows inserted into users tables.

You will also find three rows inserted into the table DATABASECHANGELOG. These 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