Liquibase in Spring Boot project

Initializing and managing data base is an old preoccupation and many solutions (like DbUnit, Spring SQL script handling, Flyway) have been proposed over time to manage database initialization and versions during development and testing phase.

Spring Boot integrates Liquibase to initialize a database in very simple way. That makes it a default choice for many developers for data base initialization.

Il this article, we will deal with Liquibase in simple Spring-boot project and we will propose a way to use it to manage database versions and updates in production.

Start with Liquibase

To start using Liquibase we can generate a project in the spring initializer https://start.spring.io.

We enter Group=”com.mycompany”, Artifact=”todo-web” and Package Name=”com.mycompany.todo.web”
Next, we select dependencies :

  • Spring Web Starter
  • Spring Data JPA
  • H2 Database
  • Liquibase Migration
  • Rest Repositories
  • Lombok

Download the project and open it with your favorite IDE.

In this step, we will add change log files to the project to create a data base schema. Spring Boot default configuration look for Yaml file at this location resources/db/changelog/db.changelog-master.yaml.

Using Yaml can be a bit tricky, as the Liquibase documentation focuses on XML configuration.
For this reason, we made a choice to use XML.

To configure the default change log location, we have to add to the property file src/main/resources/application.properties this entry:
spring.liquibase.change-log: classpath:db/changelog/master.xml

For a better database version management, it’s recommended to split the change log to files and to import them in the main file (in our case it’s master.xml file).
Bellow, we provide the master.xml file code:

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">

  <include file="R0/01_initial_schema.xml" 
           relativeToChangelogFile="true"/>

</databaseChangeLog>

Next, we add the inner change log file at this location src/main/resources/db/changelog/R01_initial_schema.xml.

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">

<property name="now" value="now()" dbms="h2"/>
<property name="now" value="now()" dbms="oracle"/>
<property name="autoIncrement" value="true"/>

<!-- The initial schema. -->
<changeSet id="01" author="SAB">

<createTable tableName="REF_USER">
    <column name="ID" type="bigint" autoIncrement="${autoIncrement}">
        <constraints primaryKey="true" nullable="false"/>
    </column>
    <column name="NAME" type="varchar(100)"/>
</createTable>

<createTable tableName="REF_TODO">
    <column name="ID" type="bigint" autoIncrement="${autoIncrement}">
        <constraints primaryKey="true" nullable="false"/>
    </column>
    <column name="USER_ID" type="bigint"/>
    <column name="TITLE" type="varchar(100)"/>
    <column name="DESCRIPTION" type="varchar(100)"/>
    <column name="TARGET_DATE" type="date"/>
    <column name="IS_DONE" type="boolean"/>
</createTable>

<addForeignKeyConstraint baseColumnNames="USER_ID"
    baseTableName="REF_TODO" constraintName="FK_TODO_USER"
    referencedColumnNames="ID" referencedTableName="REF_USER"/>

<loadData file="users.csv" relativeToChangelogFile="true"
separator=";" tableName="REF_USER">
</loadData>

</changeSet>

<changeSet author="SAB" id="tagDatabase">
    <tagDatabase tag="v0.0.1"/>
</changeSet>

</databaseChangeLog>

We create two tables in the database REF_USER and REF_TODO. The first table will be initialized by a list of users provided in the CSV file bellow:

ID;NAME
1;Samer ABDELKAFI
2;Nicolas CAGE
3;Nicole KIDMAN

We activate H2 console in the file src/main/resources/application.properties to display database content.

spring.h2.console.enabled=true

In the project root directory run:
mvn spring-boot:run

You can connect to the database and display tables at this link:
http://localhost:8080/h2-console

Then, provide those entries in the login form:
Driver Class: org.h2.Driver
JDBC URL: jdbc:h2:mem:testdb
User Name: sa
Password:

When we explore the database, we will find the two tables REF_USER and REF_TOFO. And we will notice the existence of two extra tables DATABASECHANGELOGLOCK and DATABASECHANGELOG.

  • The DATABASECHANGELOGLOCK is used to avoid conflict in case you have two Liquibase instance trying to update the database.
  • The DATABASECHANGELOG provides the list of change-sets and tags executed on the database.

Update production database with SQL script

Usually large companies would keep classic process by updating the database with SQL script. They have a DBA (Data Base Administrator) that control database updates and validate SQL script before execution in production.

Liquibase can help you to generate the SQL script required for production.

Add Liquibase plugin to the pom.xml file:

 <plugin>
     <groupId>org.liquibase</groupId>
     <artifactId>liquibase-maven-plugin</artifactId>
     <version>3.7.0</version>
     <configuration>
         <changeLogFile>src/main/resources/db/changelog/master.xml</changeLogFile>
         <driver>org.h2.Driver</driver>
         <url>jdbc:h2:mem:testdb</url>
         <defaultSchemaName>TODO</defaultSchemaName>
         <username>sa</username>
         <password>""</password>
         <verbose>true</verbose>
         <logging>debug</logging>
     </configuration>
     <dependencies>
         <dependency>
             <groupId>org.liquibase.ext</groupId>
             <artifactId>liquibase-hibernate5</artifactId>
             <version>3.6</version>
         </dependency>
     </dependencies>
 </plugin>

Then, execute mvn liquibase:updateSQL

We will get the SQL script here target/liquibase/migrate.sql

If your target database is oracle you have do make some changes to generate oracle sql script.

set driver tag value = oracle.jdbc.driver.OracleDriver
set url tag value = jdbc:oracle:thin:”host”:”port”/TODO

Then, add the oracle driver dependency to the project.

Update production database with Liquibase

In this part of the article, we will deal with advanced features and we will use Docker container to run an Oracle database.

Update a production database could be done at start-up of your web application by spring-boot and Liquibase.
I’m not fun of this solution because even if Liquibase manage concurrent update by the lock table in case we have more than one instance of the application, I would like to update database data without the need of deploying the web application.

In this part, we will provide a solution to manage a manage database update in a separate module that could be packaged and deployed independently.

We create a maven project with three modules:

todo (parent module)
– todo-config (module 1)
– todo-web (module 2)
– todo-liqui (module 3)

To create the project, you can use maven and copy pom.xml and src directory to the todo-web module

$ mvn archetype:generate -DgroupId=com.mycompany -DartifactId=todo -DinteractiveMode=false
$ cd todo
$ mvn archetype:generate -DgroupId=com.mycompany -DartifactId=todo-config -DinteractiveMode=false
$ mvn archetype:generate -DgroupId=com.mycompany -DartifactId=todo-web -DinteractiveMode=false
$ mvn archetype:generate -DgroupId=com.mycompany -DartifactId=todo-liqui -DinteractiveMode=false

Then use Spring Initializr to generate todo-web module and todo-liqui module.

For todo-liqui module, we select dependencies
– Liquibase Migration
– H2 Database

Then, for todo-web module, we select :
– Spring Web Starter
– Spring Data JPA
– Rest Repositories
– Lombok

After downloading zip file, we extract them and copy to the module directory the pom.xml file and the src directory.
We will start by configuring the todo-config module. This module will handle database configuration for todo-liqui module and todo-web module.
We made this choice for simplicity. I recommend using a config server (https://spring.io/projects/spring-cloud-config).

In the config module, we will add 2 files:

src/main/resources/application-dev.properties
with this content

spring.h2.console.enabled=true

src/main/resources/application-prod.properties

spring.datasource.type: com.zaxxer.hikari.HikariDataSource
spring.datasource.url: jdbc:oracle:thin:@192.168.99.100:49161/xe
spring.datasource.username: TODO
spring.datasource.password: TODO

Next, we will configure the Liquibase module.
In the pom.xml, we add dependency to the config-module and oracle driver for production database:

<dependency>
    <groupId>com.mycompany</groupId>
    <artifactId>todo-config</artifactId>
    <version>${project.parent.version}</version>
</dependency>

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0</version>
</dependency>

Then, we configure spring-boot-maven-plugin to generate executable jar (todo-liqui-1.0-SNAPSHOT-exec.jar)

<plugin>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-maven-plugin</artifactId>
    <configuration>
        <mainClass>com.mycompany.todo.liqui.TodoLiquibase</mainClass>
        <classifier>exec</classifier>
    </configuration>
    <executions>
        <execution>
            <goals>
                 <goal>repackage</goal>
            </goals>
        </execution>
    </executions>
</plugin>

With this config, the plugin will generate an executable Jar here :

Finally, we configure the todo-web module by adding those dependency:

<dependencies>
    <dependency>
        <groupId>com.mycompany</groupId>
        <artifactId>todo-config</artifactId>
        <version>${project.parent.version}</version>
    </dependency>
    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0</version>
</dependency>

<!-- Other dependnecies here -->

</dependencies>

<profiles>
    <profile>
        <id>dev</id>
        <dependencies>
            <dependency>
                <groupId>com.mycompany</groupId>
                <artifactId>todo-liqui</artifactId>
                <version>${project.parent.version}</version>
            </dependency>
        </dependencies>
     </profile>
</profiles>

You can add JPA entity and spring rest repository for CRUD operations.

To go faster, checkout the project from Gitub here: https://github.com/samer-abdelkafi/liquibase-project.

We will simulate production Oracle database by using this Docker image:
https://github.com/wnameless/docker-oracle-xe-11g

create a schema TODO in your oracle database:

CREATE TABLESPACE tbs_perm_01
DATAFILE 'tbs_perm_01.dat'
SIZE 20M
ONLINE;

CREATE TEMPORARY TABLESPACE tbs_temp_01
TEMPFILE 'tbs_temp_01.dbf'
SIZE 5M
AUTOEXTEND ON;

CREATE USER TODO
IDENTIFIED BY TODO
DEFAULT TABLESPACE tbs_perm_01
TEMPORARY TABLESPACE tbs_temp_01
QUOTA 20M on tbs_perm_01;

GRANT create session TO TODO;
GRANT create table TO TODO;
GRANT create view TO TODO;
GRANT create any trigger TO TODO;
GRANT create any procedure TO TODO;
GRANT create sequence TO TODO;
GRANT create synonym TO TODO;

Now, we will run the application in deferent context.

– Development context: initialize H2 database and run the web application.

$ cd todo-web
$ mvn spring-boot:run -Dspring.profiles.active=liqui,dev -P dev

– Running the application in production database:

$ cd todo-web
$ mvn spring-boot:run -Dspring.profiles.active=prod
or
$ java -jar -Dspring.profiles.active=prod target/todo-web-1.0-SNAPSHOT.jar

– Update production database without start application:

$ cd todo-liqui
$ mvn spring-boot:run -Dspring.profiles.active=prod,liqui
or
$ java -jar -Dspring.profiles.active=liqui,prod target/todo-liqui-1.0-SNAPSHOT-exec.jar

Conclusion

In this article we provide an overview of some use cases of Liquibase. It could be used as a database initializer, SQL script generator, production database update and versioning.
Liquibase has many other features like rollback and conditional update.
I use Liquibase to update production database within a Jenkins Job it works fine and it freed me from writing long SQL script.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s