Liquibase Spring Boot Testing with H2 DB

Spring boot is the most popular framework for creating microservice API. It offers great support for API testing, and for persistence during testing, it is common to use an in-memory database that is dropped once testing is complete.

One widely used in-memory database for testing in Spring Boot is the H2 database engine. H2 is an open-source Java-based database that supports SQL and JDBC. It can be run embedded inside an application or as a stand-alone server.

The advantage of using H2 as an in-memory database is that it relies on system memory or RAM for storage, rather than disk space. This allows for faster storing and retrieving of data compared to disk-based databases. However, once the application is terminated, all the data stored in H2 is lost.

H2 is an in-memory database, hence data will not persist on the disk, so it should used in development and testing only

Liquibase can be used in conjunction with H2 for managing database changes and schema evolution during development and testing

In the below example, The Controler will retrieve data from the Mysql Database, but for testing purposes, H2 will be used.

package com.example.SpringLiquibase.controller;

import com.example.SpringLiquibase.entity.Apple;
import com.example.SpringLiquibase.repository.AppleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class LiquibaseController {
    @Autowired
    AppleRepository appleRepository;

    @GetMapping
    public List<Apple> getApple() {
        return appleRepository.findAll();
    }
}

package com.example.SpringLiquibase.entity;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
public class Apple {
    @Id
    @Column
    @GeneratedValue
    private Long appleId;
    @Column
    private String appleName;
}

package com.example.SpringLiquibase.repository;

import com.example.SpringLiquibase.entity.Apple;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface AppleRepository extends JpaRepository<Apple, Long> {
}

package com.example.SpringLiquibase;

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

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

\src\main\resources\db\changelog\changelog.mysql.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
    <changeSet author="wesome" id="1692446190573-1">
        <createTable catalogName="appledb" tableName="apple">
            <column autoIncrement="true" name="apple_id" type="BIGINT">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="apple_name" type="VARCHAR(255)"/>
            <column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime"/>
        </createTable>
        <rollback>
            <dropTable tableName="apple"/>
        </rollback>
    </changeSet>
    <changeSet author="wesome" id="1692701200879-2">
        <insert catalogName="appledb" tableName="apple">
            <column name="apple_name" value="Fuji"/>
            <column name="current_date_time" valueDate="now()"/>
        </insert>
        <rollback>
            <delete tableName="apple">
                <where>
                    apple_name = 'Fuji'
                </where>
            </delete>
        </rollback>
    </changeSet>
    <changeSet author="wesome" id="1692701200879-3">
        <insert catalogName="appledb" tableName="apple">
            <column name="apple_name" value="Gala"/>
            <column name="current_date_time" valueDate="now()"/>
        </insert>
        <rollback>
            <delete tableName="apple">
                <where>
                    apple_name = 'Gala'
                </where>
            </delete>
        </rollback>
    </changeSet>
</databaseChangeLog>

\src\main\resources\application.properties

server.servlet.context-path=/liquibase
spring.datasource.url=jdbc:mysql://localhost:3306/appledb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=rootroot
spring.liquibase.change-log=db/changelog/changelog.mysql.xml
spring.jpa.show-sql=true

package com.example.SpringLiquibase.controller;

import com.example.SpringLiquibase.entity.Apple;
import com.example.SpringLiquibase.repository.AppleRepository;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;

import java.util.List;

import static org.junit.jupiter.api.Assertions.assertAll;
import static org.junit.jupiter.api.Assertions.assertEquals;

@DataJpaTest
class LiquibaseControllerTest {

    @Autowired
    AppleRepository appleRepository;

    @Test
    void getApple() {
        List<Apple> apples = appleRepository.findAll();
        Assertions.assertFalse(apples.isEmpty());
        assertAll(() -> Assertions.assertFalse(apples.isEmpty(), "apple list should not be empty"), () -> Assertions.assertTrue(apples.size() == 2, "apple list should contain 2 elements"), () -> assertEquals(1, apples.get(0).getAppleId(), "appleId should be 1"), () -> assertEquals("Macintosh", apples.get(0).getAppleName(), "appleName should be Macintosh"), () -> assertEquals(2, apples.get(1).getAppleId(), "appleId should be 2"), () -> assertEquals("Jonagold", apples.get(1).getAppleName(), "appleName should be Jonagold"));

    }
}

\src\test\resources\db\changelog\changelog.mysql.test.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
    <changeSet author="wesome" id="1692446190573-1">
        <createTable catalogName="appleDbTest" tableName="apple">
            <column autoIncrement="true" name="apple_id" type="BIGINT">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="apple_name" type="VARCHAR(255)"/>
            <column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime"/>
        </createTable>
        <rollback>
            <dropTable tableName="apple"/>
        </rollback>
    </changeSet>
    <changeSet author="wesome" id="1692701200879-2">
        <insert catalogName="appleDbTest" tableName="apple">
            <column name="apple_name" value="Macintosh"/>
            <column name="current_date_time" valueDate="now()"/>
        </insert>
        <rollback>
            <delete tableName="apple">
                <where>
                    apple_name = 'Macintosh'
                </where>
            </delete>
        </rollback>
    </changeSet>
    <changeSet author="wesome" id="1692701200879-3">
        <insert catalogName="appleDbTest" tableName="apple">
            <column name="apple_name" value="Jonagold"/>
            <column name="current_date_time" valueDate="now()"/>
        </insert>
        <rollback>
            <delete tableName="apple">
                <where>
                    apple_name = 'Jonagold'
                </where>
            </delete>
        </rollback>
    </changeSet>
</databaseChangeLog>

\src\test\resources\application.properties

spring.test.database.replace=none
spring.jpa.hibernate.ddl-auto=none
spring.liquibase.change-log=db/changelog/changelog.mysql.test.xml
spring.liquibase.liquibase-schema=appleDbTest
spring.liquibase.drop-first=true
spring.datasource.url=jdbc:h2:mem:appleDbTest;DB_CLOSE_ON_EXIT=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS appleDbTest
spring.datasource.username=root
spring.datasource.password=rootroot

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.4</version>
        <relativePath/>
    </parent>
    <groupId>com.example</groupId>
    <artifactId>SpringLiquibaseApplication</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>SpringLiquibaseApplication</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Once the Application is Up and running, the inserted record can be verified by

SELECT * FROM appledb.apple;

The API result can be verified by executing

curl --location 'http://localhost:8081/liquibase/'

Liquibase Spring Boot Testing with H2 DB

H2 is an in-memory database, hence data will not persist on the disk, so it should used in development and testing only

follow us on