What is Database Migration in Spring Boot and how it is done using Liquibase?

Thanks to the original writer : https://dineshchandgr.medium.com/what-is-database-migration-in-spring-boot-and-how-it-is-done-using-liquibase-bd5cad981862

What is Database Version Control? — It is like a Git for SQL Scripts

Most of the applications use version control for the application code, which helps to track the code from branches when needed an example for source code versioning is git. Every software release might have dependent database scripts like Table creation, Modification, Adding constraints, Adding new columns, Inserting some default values, etc. These scripts were manually applied in the production database by the Database Admin before/after the release went live in production. There were many instances where the manually running of SQL scripts might have been missed out resulting in production issues

Database Version Control comes to the rescue in this kind of scenario where the versioning is done for Database scripts also and it is automatically applied to the databases when the release goes live. This is in line with the Agile methodology CI-CD of releasing source code along with the DB scripts and this will help us track the SQL scripts executed and rollback when necessary

As we know Spring Boot has integrations with a lot of tools and frameworks and for Database Version Control, the two famous libraries are used namely?Liquibase and Flyway.?Every version file will have the scripts needed to execute to migrate the database from the previous version to the new version. Liquibase and Flyway help us to update the database from any version to the current version

Using Liquibase with Spring Boot

Liquibase is an open-source tool that keeps track of DB scripts (SQL) revisions and it supports a lot of databases like

• MySQL
• MariaDB
• PostgreSQL
• Oracle
• SQL Server
• HSQL
• H2

It also supports different file formats for defining the DB scripts as follows

• SQL
• XML
• YAML
• JSON

Spring Boot applies the schema changes to the database on application start-up based on the Profiles like Dev, UAT, Prod, etc as shown in the diagram above. The Liquibase uses the DB connection from the application and runs the changes in DB. We can easily roll back changes and again go forward to the required version using Liquibase

Dependencies

Liquibase library can be added to the Spring Boot application using the following

<dependency> 
<groupId>org.liquibase</groupId>  
<artifactId>liquibase-core</artifactId>       <version>4.12.0</version>
</dependency>        

Then we need the Spring Data JPA and a Database dependency like H2 for liquibase to work with the database

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
 </dependency>
<dependency>
 <groupId>com.h2database</groupId>
 <artifactId>h2</artifactId>
 </dependency>        

ChangeLog

Now, let us look at the changeLog file which stores the SQL scripts. As seen above, the changeLog can be in multiple formats like?SQL, XML, YAML, and JSON. Let us look at the example of changeLog in YAML in this article, as it is the default format.

The master changelog has to be created and placed in the following folder as Liquibase will scan for the file in this path

src/main/resources/db/changelog/master-change-log.yaml

A master change log is nothing but a collection of multiple change log files in a logical sequence. If we want to use a different format like json, then we have to specify the below property in application.yml

spring:
  liquibase:
    changeLog: "classpath:db/migration/master-change-log.json"        

Now in the?master-change-log.yaml file, we can specify the change log file for every revision. Let us assume that we have only 1 script and the employee table will be created on the application start up

databaseChangeLog:
  - include:
      file: db/changelog/create-employee-table.xml        

Every change goes to a change set which then goes into a change log. The change log files are tracked in the?master-change-log.yaml file

The above 2 diagrams show how does a changelog file looks like for SQL and XML respectively. Liquibase uses changesets to represent a single change to the database and each change set has a unique id and also the author to indicate who has created it

Let us look at the structure of?create-employee-table.xml. As seen below, the create table tag creates the table called an?employee?and the?rollback?tag is also specified for us to rollback if we need.

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="https://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="https://www.liquibase.org/xml/ns/dbchangelog
 https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

  <changeSet id="01" author="test">
    <createTable tableName="employee"
      remarks="Employee">
      <column name="id" type="int" autoIncrement="true">
        <constraints nullable="false" unique="true"  primaryKey="true"/>
      </column>
      <column name="name" type="varchar(255)">
        <constraints nullable="false" unique="true"/>
      </column>
    </createTable>

    <!-- for manual rollback
    <rollback>
      <dropTable tableName="employee"/>
    </rollback>
   
  </changeSet>
</databaseChangeLog>        

DATABASECHANGELOG

Liquibase creates a table called?DATABASECHANGELOG?in the specified database, that tracks which changesets have been deployed and which are not deployed. On adding a change set every time, the application runs the scripts in DB and this table will be updated

At this point,?DATABASECHANGELOG?will have a single entry of the?create-employee-table.xml

Now, lets us add the next revision to?master-change-log.yaml file?by adding the change log to create a new address table

databaseChangeLog:
  - include:
      file: db/changelog/create-employee-table.xml
  - include:
      file: db/changelog/create-address-table.xml        

This is the structure of?insert-employee-data.xml. As seen below, create table tag will create a new table called address. After adding this file and restarting this application, the new table will be created on the application start-up.

<?xml version="1.0" encoding="UTF-8"?>

 <databaseChangeLog
  xmlns="https://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="https://www.liquibase.org/xml/ns/dbchangelog
         https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

 <changeSet id="02" author="test">
    <createTable tableName="address"
      remarks="Address">
      <column name="id" type="int" autoIncrement="true">
        <constraints nullable="false" unique="true"  primaryKey="true"/>
      </column>
      <column name="city" type="varchar(255)">
        <constraints nullable="false" unique="true"/>
      </column>
      <column name="postalCode" type="varchar(255)">
        <constraints nullable="false" unique="true"/>
      </column>    
   </createTable>

    <!-- for manual rollback
    <rollback>
      <dropTable tableName="address"/>
    </rollback>
   
  </changeSet>
</databaseChangeLog>        

We can also add subsequent changelogs to insert data into these tables. Eg: some master/config data.

At this point,?DATABASECHANGELOG?will have two entries for?create-employee-table.xml and insert-employee-data.xml

Rollback

Liquibase has an amazing facility to rollback the scripts in the Database very easily

We can run the following mvn command to rollback

mvn liquibase:rollback -Dliquibase.rollbackCount=1        

After running this, the recently created address table is deleted because we have specified the following command in the latest version of the file

<rollback>
   <dropTable tableName="address"/>
</rollback>        

At this point,?DATABASECHANGELOG?will have only a single entry for the file?create-employee-table.xml. If we execute the rollback command again, the employee table would be removed and the?DATABASECHANGELOG?table will be empty.

There are different rollback options namely

• Rollback by count
• Rollback by tag
• Rollback by date

and we have seen rollback by count in this article.

More information about liquibase can be found on the liquibase page

https://docs.liquibase.com/concepts/home.html