Posted in Spring Boot

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

In this tutorial I will show you an example on evolving database using Spring Boot and Liquibase with YAML and SQL configuration. We had seen similar example using XML configuration. I will also show you how to build application using both maven and gradle build tools. I will not tell you much details on Liquibase but you can always find more details on it in my previus example.

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

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-yaml) 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-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. 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.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 we are using 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

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:

21.702  INFO 10520 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
23.137  INFO 10520 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
26.208  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
26.257  INFO 10520 --- [           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))
26.313  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
26.332  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : DELETE FROM roytuts.DATABASECHANGELOGLOCK
26.334  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOGLOCK (ID, `LOCKED`) VALUES (1, 0)
26.344  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT `LOCKED` FROM roytuts.DATABASECHANGELOGLOCK WHERE ID=1
26.363  INFO 10520 --- [           main] l.lockservice.StandardLockService        : Successfully acquired change log lock
29.730  INFO 10520 --- [           main] l.c.StandardChangeLogHistoryService      : Creating database history table with name: roytuts.DATABASECHANGELOG
29.733  INFO 10520 --- [           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)
29.789  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOG
29.797  INFO 10520 --- [           main] l.c.StandardChangeLogHistoryService      : Reading from roytuts.DATABASECHANGELOG
29.798  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT * FROM roytuts.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
29.803  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
29.942  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE ADDRESSES
(
	ID INT NOT NULL PRIMARY KEY,
    STREET VARCHAR(100) NOT NULL,
    CITY VARCHAR(100),
    PIN INT
)
30.065  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : 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)
)
30.144  INFO 10520 --- [           main] liquibase.changelog.ChangeSet            : SQL in file changelog/scripts/01-create-users-and-addresses-schema.sql executed
30.145  INFO 10520 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:db/db.changelog-master.yaml::createTable::Soumitra Roy ran successfully in 227ms
30.147  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT MAX(ORDEREXECUTED) FROM roytuts.DATABASECHANGELOG
30.151  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('createTable', 'Soumitra Roy', 'classpath:db/db.changelog-master.yaml', NOW(), 1, '8:d46f6a07497ce29b5907e7c0cec87e39', 'sqlFile', '', 'EXECUTED', NULL, NULL, '3.8.2', '6239149892')
30.164  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : insert into ADDRESSES(ID, STREET, CITY, PIN) values (1, 'street1', 'city1', 111111)
30.169  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : insert into ADDRESSES(ID, STREET, CITY) values (2, 'street2', 'city2')
30.172  INFO 10520 --- [           main] liquibase.changelog.ChangeSet            : SQL in file changelog/scripts/02-insert-data-addresses.sql executed
30.178  INFO 10520 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:db/db.changelog-master.yaml::insertTableAddresses::Soumitra Roy ran successfully in 19ms
30.180  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('insertTableAddresses', 'Soumitra Roy', 'classpath:db/db.changelog-master.yaml', NOW(), 2, '8:b519b6d5c979d8ebb2928a64d72fb432', 'sqlFile', '', 'EXECUTED', NULL, NULL, '3.8.2', '6239149892')
30.201  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : insert into USERS(ID, NAME, EMAIL, ADDRESS) values (1, 'Soumitra', 'soumitra@email.com', 1)
30.206  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : insert into USERS(ID, NAME, EMAIL, PHONE, ADDRESS) values (2, 'Suman', 'suman@email.com', 1254789541, 2)
30.208  INFO 10520 --- [           main] liquibase.changelog.ChangeSet            : SQL in file changelog/scripts/02-insert-data-users.sql executed
30.213  INFO 10520 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:db/db.changelog-master.yaml::insertTableUsers::Soumitra Roy ran successfully in 17ms
30.215  INFO 10520 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('insertTableUsers', 'Soumitra Roy', 'classpath:db/db.changelog-master.yaml', NOW(), 3, '8:94375a5c50836cb61afa5729c5626d63', 'sqlFile', '', 'EXECUTED', NULL, NULL, '3.8.2', '6239149892')
30.230  INFO 10520 --- [           main] l.lockservice.StandardLockService        : Successfully released change log lock
30.490  INFO 10520 --- [           main] c.r.s.b.l.y.SpringBootLiquibaseYamlApp   : Started SpringBootLiquibaseYamlApp in 12.164 seconds (JVM running for 13.27)
30.500  INFO 10520 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
30.538  INFO 10520 --- [extShutdownHook] 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

Thanks for reading.

1 thought on “Evolving Database using Spring Boot and Liquibase with YAML and SQL Configuration”

  1. Nice article. BTW, I tried your project. But I didn’t see table user & address being created and data inserted.

    Why??
    Thanks

    Reply

Leave a Reply

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