Spring Boot + Liquibase + SQL Example (2024)

In this tutorial, we'll configure Liquibase with a Spring Boot project, and document database schema changes with SQL Format Queries. When the spring boot project is started, the schema changes will be applied immediately to the configured database through Liquibase.

What is Liquibase?

Liquibase is an open-source tool for keeping track of database schema script revisions. It works with a variety of database types and accepts a variety of file formats for defining the database structure.

Liquibase can roll changes back and forward from a given point, removing the need to remember what was the last update or script you executed on a particular DB instance.

When it becomes necessary to update/revert changes in a database or migrate it from one version to another, database schema migration is done. Liquibase and Flyway are the two main migrating tools.

What is Flyway?

Flyway is an open source database migration software. It has Migrate, Clean, Info, Validate, Undo, Baseline, and Repair seven basic commands, SQL (database-specific syntax (such as PL/SQL, T-SQL, etc.) or Java migrations are supported (for advanced data transformations or dealing with LOBs).

You can also refer Java base migration using Spring Boot + Flyway Example.
Refer example for Flyway Command Line base migration.

Differences between Flyway and Liquibase

Refer Liquibase VS Flyway for more details.

	FlywayDB	Liquibase
Diff utility to compare two databases	No	Yes
Generates SQL for you	Yes	No
Rollback	Paid Service	Yes, available
Targeted rollback for any single change and Targeted rollback for a set of changes	No	Paid Service
Ability to manage change files in a variety of ways and can use the monitoring and reporting dashboard.	No	Yes
Specifying changes file	SQL	SQL, XML, JSON, YAML
Repeatable migrations	Yes	Yes
Dry runs	Paid Service	Yes, available
Preconditions	No	Yes

Take a look at our suggested posts:

Apache Storm Tutorial

Spring Boot - Transaction Management

Spring Boot - JWT Authentication Example

Spring Boot - JWT + Angular 8 Authentication Example

Spring Boot - JWT + MYSQL Authentication Example

Spring Boot - Session Management using Redis

OAuth2.0 Tutorial

Spring Boot - Hazelcast

Spring Boot - JPA + REST + MYSQL Example

Java 15

Java 14

Java 8 Interview Questions and Answers

Angular 8 Spring Boot Example

Let's start developing Spring Boot application with Liquibase support.

Project Structure

Maven Dependency

Add dependencies for Liquibase liquibase-core.

<?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.techgeeknext</groupId>
	<artifactId>spring-boot-liquibase-sql-example</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>spring-boot-liquibase-sql-example</name>
	<description>Spring Boot + Liquibase + SQL Example</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.3.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

	</properties>

	<dependencies>
		<dependency>
			<groupId>org.liquibase</groupId>
			<artifactId>liquibase-core</artifactId>
			<version>3.10.2</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>

  </dependencies>

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


</project>

application.properties

Provide database connection details in properties file.

spring.datasource.url=jdbc:mysql://localhost/techgeeknextLiquibasedb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.platform=mysql

spring.jpa.hibernate.ddl-auto=update

Liquibase Changelog File

db.changelog-master.yaml

The change log file is the primary source of configuration for Liquibase. If we don't specify a change log file location in Spring Boot, the default path for YAML is db/changelog/db.changelog-master.yaml.

Create the master change log file changelog-master.yaml at default liquibase and included change log file by using include with correct sequence.

databaseChangeLog:
  - include:
      file: EMPLOYEE/master.yml
      relativeToChangelogFile: true

Include all DDL and DML SQL Files

We can separate and have two folders that DDL DML to have the SQL queries, according to the project requirements.

SQL filenames should be unique, we can include the JIRA Id in the filename, for example, JIRA-NUMBER-UPDATE/INSERT-TABLE-NAME.sql.

Add as many SQL changelogs as you want in the changelog file.

databaseChangeLog:

  - changeSet:
      id: 1
      author: techgeeknext

  - include:
      file: DDL/001_create_tables.sql
      relativeToChangelogFile: true

  - include:
      file: DML/001_insert_data_employee.sql
      relativeToChangelogFile: true

  - include:
      file: DML/002_insert_data_branch.sql
      relativeToChangelogFile: true

Create formatted SQL changelogs

You'll need to add the SQL file for each update to the database schema. While you can use plain SQL, adding a few select comments to your SQL files provides some additional benefits. "liquibase formatted SQL" is the term for this.

--liquibase formatted sql
--changeset <author name>:<a unique identifier for the SQL changeset>
<SQL statements go here>
<SQL statements go here>

--liquibase formatted sql : The comment --liquibase formatted sql must always be the first line. This allows standard SQL tools like Oracle sqldeveloper to recognise those lines as comments while also alerting Liquibase to the knowledge that this file is special.
--changset : Each changeset requires beginning with --changset. Each changeset, must have an author, a unique identification, and be a "atomic unit of change".

Create table using plain SQL query

--liquibase formatted sql
--changeset techgeeknext:create-tables

CREATE TABLE employee(
   id INT PRIMARY KEY,
   name VARCHAR(40)
);

CREATE TABLE branch(
   id INT PRIMARY KEY,
   name VARCHAR(40),
   emp_id INT,
   FOREIGN KEY(emp_id) REFERENCES employee(id) ON DELETE CASCADE
);

Insert Data using plain SQL queries

Insert data into Employee Table

--liquibase formatted sql
--changeset techgeeknext:inset-employee-01
INSERT INTO employee VALUES(01, 'User1');
INSERT INTO employee VALUES(02, 'User2');
INSERT INTO employee VALUES(03, 'User3');
INSERT INTO employee VALUES(04, 'User4');

Insert data into Branch Table

--liquibase formatted sql
--changeset techgeeknext:inset-branch-01
INSERT INTO branch VALUES(1, 'User1',01);
INSERT INTO branch VALUES(2, 'User2',02);
INSERT INTO branch VALUES(3, 'User3',03);
INSERT INTO branch VALUES(4, 'User4',04);

Test

Now, execute mvn spring-boot run command to start spring boot application or run from the IDE. After starting the application, we can see that Liquibase executes all of the SQL Queries in the console to create the tables and data in the database.

  .   ____          _ __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.3.1.RELEASE)
18:23:09.949  INFO 29988 --- [main] .t.SpringBootLiquibaseExampleApplication : Starting SpringBootLiquibaseExampleApplication  (D:\spring-boot-liquibase-sql-example\target\classes started in D:\spring-boot-liquibase-sql-example)18:23:09.954  INFO 29988 --- [main] .t.SpringBootLiquibaseExampleApplication : No active profile set, falling back to default profiles: default18:23:11.224  INFO 29988 --- [main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFERRED mode.18:23:11.391  INFO 29988 --- [main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 118ms. Found 2 JPA repository interfaces.18:23:13.174  INFO 29988 --- [main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)18:23:13.200  INFO 29988 --- [main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]18:23:13.200  INFO 29988 --- [main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.36]18:23:13.449  INFO 29988 --- [main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext18:23:13.450  INFO 29988 --- [main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 3390 ms18:23:13.676  INFO 29988 --- [main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...18:23:13.961  INFO 29988 --- [main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.18:23:16.784  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM techgeeknextLiquibasedb.DATABASECHANGELOGLOCK18:23:16.813  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE techgeeknextLiquibasedb.DATABASECHANGELOGLOCK (ID INT NOT NULL, `LOCKED` BIT(1) NOT NULL, LOCKGRANTED datetime NULL, LOCKEDBY VARCHAR(255) NULL, CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))18:23:16.857  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM techgeeknextLiquibasedb.DATABASECHANGELOGLOCK18:23:16.873  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : DELETE FROM techgeeknextLiquibasedb.DATABASECHANGELOGLOCK18:23:16.875  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO techgeeknextLiquibasedb.DATABASECHANGELOGLOCK (ID, `LOCKED`) VALUES (1, 0)18:23:16.884  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : SELECT `LOCKED` FROM techgeeknextLiquibasedb.DATABASECHANGELOGLOCK WHERE ID=118:23:16.899  INFO 29988 --- [main] l.lockservice.StandardLockService        : Successfully acquired change log lock18:23:17.320  INFO 29988 --- [main] l.c.StandardChangeLogHistoryService      : Creating database history table with name: techgeeknextLiquibasedb.DATABASECHANGELOG18:23:17.323  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE techgeeknextLiquibasedb.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)18:23:17.384  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM techgeeknextLiquibasedb.DATABASECHANGELOG18:23:17.388  INFO 29988 --- [main] l.c.StandardChangeLogHistoryService      : Reading from techgeeknextLiquibasedb.DATABASECHANGELOG18:23:17.389  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : SELECT * FROM techgeeknextLiquibasedb.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC18:23:17.392  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM techgeeknextLiquibasedb.DATABASECHANGELOGLOCK18:23:17.406  INFO 29988 --- [main] liquibase.changelog.ChangeSet : ChangeSet classpath:/db/changelog/EMPLOYEE/master.yaml::1::techgeeknext ran successfully in 2ms18:23:17.408  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : SELECT MAX(ORDEREXECUTED) FROM techgeeknextLiquibasedb.DATABASECHANGELOG18:23:17.411  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO techgeeknextLiquibasedb.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'techgeeknext', 'classpath:/db/changelog/EMPLOYEE/master.yaml', NOW(), 1, '8:d41d8cd98f00b204e9800998ecf8427e', 'empty', '', 'EXECUTED', NULL, NULL, '3.10.2', '5425597395')18:23:17.444  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE employee(
   id INT PRIMARY KEY,
   name VARCHAR(40)
)18:23:17.492  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE branch(
   id INT PRIMARY KEY,
   name VARCHAR(40),
   emp_id INT,
   FOREIGN KEY(emp_id) REFERENCES employee(id) ON DELETE CASCADE
)18:23:17.546  INFO 29988 --- [main] liquibase.changelog.ChangeSet : Custom SQL executed18:23:17.547  INFO 29988 --- [main] liquibase.changelog.ChangeSet : ChangeSet classpath:/db/changelog/EMPLOYEE/DDL/001_create_tables.sql::create-tables::techgeeknext ran successfully in 128ms18:23:17.549  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO techgeeknextLiquibasedb.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('create-tables', 'techgeeknext', 'classpath:/db/changelog/EMPLOYEE/DDL/001_create_tables.sql', NOW(), 2, '8:f4d2f10c0da9a408e2aa399b1c763622', 'sql', '', 'EXECUTED', NULL, NULL, '3.10.2', '5425597395')18:23:17.563  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO employee VALUES(01, 'User1')18:23:17.567  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO employee VALUES(02, 'User2')18:23:17.569  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO employee VALUES(03, 'User3')18:23:17.571  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO employee VALUES(04, 'User4')18:23:17.573  INFO 29988 --- [main] liquibase.changelog.ChangeSet : Custom SQL executed18:23:17.579  INFO 29988 --- [main] liquibase.changelog.ChangeSet : ChangeSet classpath:/db/changelog/EMPLOYEE/DML/001_insert_data_employee.sql::inset-employee-01::techgeeknext ran successfully in 21ms18:23:17.580  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO techgeeknextLiquibasedb.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('inset-employee-01', 'techgeeknext', 'classpath:/db/changelog/EMPLOYEE/DML/001_insert_data_employee.sql', NOW(), 3, '8:f2e0acb2ad8d10af4a95134200cbe569', 'sql', '', 'EXECUTED', NULL, NULL, '3.10.2', '5425597395')18:23:17.594  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO branch VALUES(1, 'User1',01)18:23:17.599  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO branch VALUES(2, 'User2',02)18:23:17.601  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO branch VALUES(3, 'User3',03)18:23:17.604  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO branch VALUES(4, 'User4',04)18:23:17.606  INFO 29988 --- [main] liquibase.changelog.ChangeSet : Custom SQL executed18:23:17.609  INFO 29988 --- [main] liquibase.changelog.ChangeSet : ChangeSet classpath:/db/changelog/EMPLOYEE/DML/002_insert_data_branch.sql::inset-branch-01::techgeeknext ran successfully in 19ms18:23:17.611  INFO 29988 --- [main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO techgeeknextLiquibasedb.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('inset-branch-01', 'techgeeknext', 'classpath:/db/changelog/EMPLOYEE/DML/002_insert_data_branch.sql', NOW(), 4, '8:f3c028309b7a190808cbe79ddbcfb458', 'sql', '', 'EXECUTED', NULL, NULL, '3.10.2', '5425597395')18:23:17.626  INFO 29988 --- [main] l.lockservice.StandardLockService        : Successfully released change log lock18:23:17.807  INFO 29988 --- [main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'18:23:17.948  INFO 29988 --- [         task-1] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]18:23:18.012  WARN 29988 --- [main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning18:23:18.128  INFO 29988 --- [         task-1] org.hibernate.Version         : HHH000412: Hibernate ORM core version 5.4.17.Final18:23:18.572  INFO 29988 --- [         task-1] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.0.Final}18:23:18.944  INFO 29988 --- [         task-1] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQL8Dialect18:23:18.951  INFO 29988 --- [main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''18:23:18.956  INFO 29988 --- [main] DeferredRepositoryInitializationListener : Triggering deferred initialization of Spring Data repositoriesï¿½18:23:20.455  INFO 29988 --- [         task-1] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]18:23:20.477  INFO 29988 --- [         task-1] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'18:23:20.979  INFO 29988 --- [main] DeferredRepositoryInitializationListener : Spring Data repositories initialized!18:23:20.997  INFO 29988 --- [main] .t.SpringBootLiquibaseExampleApplication : Started SpringBootLiquibaseExampleApplication in 11.75 seconds (JVM running for 12.576)

We can see the tables generated by the Liquibase application in MYSQL. Below you can see databasechangelog table which maintain the log of each executed script from the liquibase.

Employee Table

Branch Table

Other Liquibase Properties

includeAll

Instead of individually adding each sql file you create to the changelog file, you may organise all of your SQL into folders and use the includeAll tag in the changelog to process them all. Because the order in which they are applied is simply alphabetical, you will want to have a well-defined naming strategy for the files while doing this. One feasible approach would be to use something like this:

00000_create_table1.sql
00010_create_table2.sql
00020_insert_data_table1.sql
00030_update_table1.sql

By starting with a five-digit number, the files will be sorted in a predictable manner.

In master.yaml file we can use includeAll as per the feasibility.

databaseChangeLog:

  - changeSet:
      id: 1
      author: techgeeknext

  - include:
      file: DDL/001_create_tables.sql
      relativeToChangelogFile: true

  - includeAll:
      path: DML

splitStatements

Here's an example of having multiple related SQL statements in a single changeset:

--liquibase formatted sql
--changeset stevedonie:create-multiple-tables splitStatements:true endDelimiter:;
create table COMPANY ( ID int not null, NAME varchar(100) not null);
create table EMPLOYEE ( ID int not null, NAME varchar(100) not null);
create table BRANCH ( ID int not null, NAME varchar(100) not null);
create table ADDRESS ( ID int not null, NAME varchar(100) not null);

splitStatements:true informs Liquibase to split the SQL block into many statements, each of which is executed separately. The endDelimiter option specifies that the statements should be separated by a semicolon.

Download Source Code

The full source code for this article can be found on below.
Download it here - Spring Boot + Liquibase Example