How versioning maintenance happens in Liquibase

Introduction

In this tutorial I am going to show you how to work with different changes or versioning maintenance on database scripts without touching the original SQL scripts. So we will only work with the changes we need at a later point of time for our database.

Let’s say you have created a table through SQL script using liquibase framework and you forgot to add a column in the table. Later you thought that you need another column which is required to process your business requirements. So what next you are going to do, will you edit the original SQL script or will you create new SQL script only with your changes to append to the table?

This is the situation where liquibase comes to play to rescue you. So you don’t need to edit the original file. You just need to create another SQL file with your changes and you are done. Liquibase will do rest of the things for you.

Let’s say in this example, we are going to create a category table under roytuts database in MySQL server using SQL script through liquibase framework.

The structure of the table is given below:

CREATE TABLE `category` (
  `category_id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(50) NOT NULL,
  `category_link` varchar(255) NOT NULL,
  `parent_id` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `unique` (`category_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

So we have everything to represent a particular category.

Now let’s say after few days we get a requirement for sorting the categories when we are fetching from the database table. But we don’t want to sort categories by ascending or descending order, rather we want to have a column that will indicate the sort order of the categories.

Now we don’t need to edit the above table structure in the SQL script but we will create another SQL script to append our change to the existing table. The change we want is given below:

ALTER TABLE `category`
ADD `sort_order` int NOT NULL DEFAULT '0';

Now we will see how implement this scenario using Spring and Liquibase framework.

Prerequisites

Eclipse 2020-06, Java at least 1.8, Gradle 6.5.1, Maven 3.6.3, Spring Boot 2.3.1, MySQL 8.0.17, Liquibase 3.10.1

Project Setup

You can create either gradle or maven based project in Eclipse. The name of the project is spring-liquibase-version-maintenance.

If you are using gradle based project then you can create build.gradle script similar to below:

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

plugins {
    id 'java-library'
    id 'org.springframework.boot' version "${springBootVersion}"
}

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.10.1")
    runtime("mysql:mysql-connector-java:8.0.17")
	
	//required for jdk 9 or above
	runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

If you are creating maven based project then you can use below pom.xml file:

<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-liquibase-version-maintenance</artifactId>
	<version>0.0.1-SNAPSHOT</version>

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

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</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>
		
		<!--required only if jdk 9 or higher version is used-->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</version>
		</dependency>
	</dependencies>

    <build>
        <plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>at least 8</source>
					<target>at least 8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

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

spring.liquibase.changeLog=classpath:liquibase/db.changelog-master.yml

The default location of changelog master file is classpath:/liquibase/changelog and Liquibase searches for a file db.changelog-master.yml. 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.

Here is the db.changelog-master.yml file:

databaseChangeLog:
  - changeSet:
      id: create_table
      author: Soumitra Roy
      changes:
      - sqlFile:
          dbms: mysql
          encoding: utf8
          endDelimiter: \n/\s*\n|\n/\s*$
          path: changelog/scripts/create-category-table-schema.sql
          relativeToChangelogFile: true
          splitStatements: true
          stripComments: true

Now create below SQL file create-category-table-schema.sql under src/main/resources/liquibase/changelog/scripts to create tables in the MySQL database:

CREATE TABLE `category` (
  `category_id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(50) NOT NULL,
  `category_link` varchar(255) NOT NULL,
  `parent_id` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `unique` (`category_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/

Create Main Class

Create below main class in order to start up the application and creation of the above table during the application startup.

package spring.liquibase.version.maintenance;

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

@SpringBootApplication
public class SpringLiquibaseVersionApp implements CommandLineRunner {

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

	@Override
	public void run(String... args) throws Exception {
		System.out.println("table successfully created");
	}

}

Testing the Application

Running the above main class you will find output in the console:

37.206  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
37.282  INFO 15344 --- [           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))
37.349  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
37.371  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : DELETE FROM roytuts.DATABASECHANGELOGLOCK
37.375  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOGLOCK (ID, `LOCKED`) VALUES (1, 0)
37.387  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT `LOCKED` FROM roytuts.DATABASECHANGELOGLOCK WHERE ID=1
37.407  INFO 15344 --- [           main] l.lockservice.StandardLockService        : Successfully acquired change log lock
37.953  INFO 15344 --- [           main] l.c.StandardChangeLogHistoryService      : Creating database history table with name: roytuts.DATABASECHANGELOG
37.957  INFO 15344 --- [           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)
38.005  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOG
38.011  INFO 15344 --- [           main] l.c.StandardChangeLogHistoryService      : Reading from roytuts.DATABASECHANGELOG
38.013  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT * FROM roytuts.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
38.017  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
38.081  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE `category` (
  `category_id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(50) NOT NULL,
  `category_link` varchar(255) NOT NULL,
  `parent_id` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `unique` (`category_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
38.158  INFO 15344 --- [           main] liquibase.changelog.ChangeSet            : SQL in file changelog/scripts/create-category-table-schema.sql executed
38.159  INFO 15344 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:liquibase/db.changelog-master.yml::create_table::Soumitra Roy ran successfully in 104ms
38.162  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT MAX(ORDEREXECUTED) FROM roytuts.DATABASECHANGELOG
38.166  INFO 15344 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('create_table', 'Soumitra Roy', 'classpath:liquibase/db.changelog-master.yml', NOW(), 1, '8:59850f7fc0106f6a915f9921f69370e5', 'sqlFile', '', 'EXECUTED', NULL, NULL, '3.10.1', '4122818025')
38.184  INFO 15344 --- [           main] l.lockservice.StandardLockService        : Successfully released change log lock
38.435  INFO 15344 --- [           main] s.l.v.m.SpringLiquibaseVersionApp        : Started SpringLiquibaseVersionApp in 12.3 seconds (JVM running for 13.602)
table successfully created

In the log you can see that apart from your own table category there are two more tables get created under roytuts database as shown below in the image:

You will also find one row inserted into the table DATABASECHANGELOG. This row identifies all details about the executed SQL script.

You will also find one row inserted into the table DATABASECHANGELOGLOCK. This row identifies whether current operation holds lock on changesets or not.

Alter Table

Now we will see how to alter the existing category table without editing the original create table schema SQL script.

So for this to happen we will create another SQL script which will contain the alter table statement.

Create a file called alter-category-table-schema.sql under the same path as we created for creating table:

ALTER TABLE `category`
ADD `sort_order` int NOT NULL DEFAULT '0';
/

Now you need to add another entry for this alter table operation into db.changelog-master.yml file:

...
- changeSet:
     id: alter_table
     author: Soumitra Roy
     changes:
     - sqlFile:
         dbms: mysql
         encoding: utf8
         endDelimiter: \n/\s*\n|\n/\s*$
         path: changelog/scripts/alter-category-table-schema.sql
         relativeToChangelogFile: true
         splitStatements: true
         stripComments: true

We also change the code snippets in main class to have updated message:

@Override
public void run(String... args) throws Exception {
	System.out.println("table successfully altered");
}

Re-testing the Application

Now running the main class again you will get below output:

38.842  INFO 12728 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
38.859  INFO 12728 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOGLOCK
38.866  INFO 12728 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT `LOCKED` FROM roytuts.DATABASECHANGELOGLOCK WHERE ID=1
38.899  INFO 12728 --- [           main] l.lockservice.StandardLockService        : Successfully acquired change log lock
39.813  INFO 12728 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT MD5SUM FROM roytuts.DATABASECHANGELOG WHERE MD5SUM IS NOT NULL LIMIT 1
39.817  INFO 12728 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM roytuts.DATABASECHANGELOG
40.094  INFO 12728 --- [           main] l.c.StandardChangeLogHistoryService      : Reading from roytuts.DATABASECHANGELOG
40.095  INFO 12728 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT * FROM roytuts.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
40.144  INFO 12728 --- [           main] liquibase.executor.jvm.JdbcExecutor      : ALTER TABLE `category`
ADD `sort_order` int NOT NULL DEFAULT '0';
40.183  INFO 12728 --- [           main] liquibase.changelog.ChangeSet            : SQL in file changelog/scripts/alter-category-table-schema.sql executed
40.184  INFO 12728 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:liquibase/db.changelog-master.yml::alter_table::Soumitra Roy ran successfully in 58ms
40.187  INFO 12728 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT MAX(ORDEREXECUTED) FROM roytuts.DATABASECHANGELOG
40.192  INFO 12728 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO roytuts.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('alter_table', 'Soumitra Roy', 'classpath:liquibase/db.changelog-master.yml', NOW(), 2, '8:831f648d261a82f879344307d427f20a', 'sqlFile', '', 'EXECUTED', NULL, NULL, '3.10.1', '4123720100')
40.211  INFO 12728 --- [           main] l.lockservice.StandardLockService        : Successfully released change log lock
40.419  INFO 12728 --- [           main] s.l.v.m.SpringLiquibaseVersionApp        : Started SpringLiquibaseVersionApp in 11.686 seconds (JVM running for 12.797)
table successfully altered

From the above output you will see this time only alter table script has run.

You can check the category table in the database and you will find that sort_order column has been added into it.

You will also notice that databasechangelog table is having one more entry for this alter script.

You can download both resultant SQL scripts from the source code below.

Source Code

Download

Thanks for reading.

Roy Tutorials