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
Thanks for reading.
Nice article. BTW, I tried your project. But I didn’t see table user & address being created and data inserted.
Why??
Thanks