Testing is integral part of application building. It’s very helpful to check for regressions and makes the application more robust. To check how the values are being saved into databases and test out those values, integration testing is required. Here we saw Liquibase for Postgresql with Spring Boot how to integrate liquibase with spring boot. In this blog we will learn how to perform integration testing on tables and entities built in the linked article.

H2 database

To perform integration testing we will be using in memory database H2. First add dependency of h2 in pom.xml

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
    <version>2.2.220</version>
</dependency>

H2 configurations for integration testing. Add this to application.yml to application.properties under src/test/resources folder. Here we notify Spring to use h2 database with postgresql mode for running integration tests

spring:
  jpa:
    generate-ddl: false
    hibernate.ddl-auto: none    
  sql.init.platform: h2
  datasource:
    driverClassName: org.h2.Driver
    url: jdbc:h2:mem:;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    username: SA
    password:

Liquibase

Add Liquibase to the configuration. Here we provide the liquibase xml file path which will be read by Spring on running integration tests. All the liquibase migrations will go under the src/test/resources/change-log folder. Create folder change-log under src/test/resources if it doesn’t exist.

spring:
  liquibase:
    enabled: true
    change-log: change-log/changelog-main.xml
    drop-first: true

If the primary key of entity is of type java.util.UUID, uuid-ossp extension has to be added to the postgresql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" with schema public;

but this extension doesn’t exist in h2 database. So that we don’t encounter this exception on running integration tests we have to create an alias which the h2 database can refer. Create new file uuid-v4-function.xml with alias details for uuid under src/test/resources/change-log folder

<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-latest.xsd">
    <changeSet id="uuid-v4-function" author="test">
        <sql>
            <![CDATA[
            CREATE ALIAS IF NOT EXISTS UUID_GENERATE_V4 FOR "org.h2.value.ValueUuid.getNewRandom";
            ]]>
        </sql>
    </changeSet>
</databaseChangeLog>

Create sequences for the state and country table. Create new file sequences.xml under src/test/resources/change-log/

<?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-latest.xsd">
    <changeSet id="createSequences" author="test">        
        <createSequence sequenceName="country_country_id_seq" startValue="10" incrementBy="50"/>        
        <createSequence sequenceName="state_state_id_seq" startValue="10" incrementBy="50"/>        
    </changeSet>
</databaseChangeLog>

Let’s add test data to state and country tables. Create new file test-data.xml under src/test/resources/change-log/

<?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-latest.xsd">
    <changeSet id="test-data" author="test">
        <sql>            
            insert into country(country_id, created, updated, name)  values (1, current_date, current_date, 'USA');
            insert into country(country_id, created, updated, name) values (2, current_date, current_date, 'India');

            insert into state(state_id, abbreviation, code, name, country_id) values (1, 'TX', 'TX', 'Texas', 1);
            insert into state(state_id, abbreviation, code, name, country_id) values (2, 'AK', 'AK', 'Arkansas', 1);
            insert into state(state_id, abbreviation, code, name, country_id) values (3, 'CA', 'CA', 'California', 1);
            insert into state(state_id, abbreviation, code, name, country_id) values (4, 'PA', 'PA', 'Punjab', 2);
            insert into state(state_id, abbreviation, code, name, country_id) values (5, 'HR', 'HR', 'Haryana', 2);
        </sql>
    </changeSet>
</databaseChangeLog>

Add existing migrations to the changelog-main.xml (create file if it doesn’t exist) file. Here we will refer the main file under the src/main/resources/changelog/main.xml which was created in the previous article [Liquibase for Postgresql with Spring Boot] (/technology/liquibase-for-postgresql-with-springboot.html) which contains all the migrations, so we don’t have to rewrite migrations for tests.


<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-latest.xsd">
    <include file="change-log/uuid-v4-function.xml"/>
    <include file="change-log/sequences.xml"/>
    <include file="changelog/main.xml"/>
    <include file="change-log/test-data.xml"/>    
</databaseChangeLog>

With all these configuration, on running integration test Spring will run changelog-main.xml. Here is the flow of migrations

create alias -> create sequences -> apply all existing migrations -> add test data

Integration test

We will be testing the StateService, if it doesn’t exist create the following two files.

src/main/java/com/demo/service/StateService.java

public interface StateService {    
    List<StateDTO> getStatesByCountry(String name);
}

src/main/java/com/demo/service/impl/StateServiceImp.java

@Service
@Transactional
public class StateServiceImpl implements StateService {

    private final StateRepository stateRepository;

    public StateServiceImpl(StateRepository stateRepository) {
        this.stateRepository = stateRepository;
    }
    
    @Override
    public List<StateDTO> getStatesByCountry(String name) {
        List<State> states = this.stateRepository.findStatesByCountryIsoCode2(name);
        return states.stream().map(state -> new StateDTO(state.getStateId(), state.getName())).toList();
    }
}

Now we have all the configuration wired, we are ready to write the integration test

Create file StateServiceIT.java under src/test/java/com/demo

@RunWith(SpringRunner.class)
@SpringBootTest
public class StateServiceIT {

    @Autowired
    private StateService stateService;

    @Test
    public void getStatesByName() {
        List<StateDTO> stateDTO = stateService.getStatesByCountry("US");
        assertEquals(stateDTO.size(), 3);
    }
}

Size is 3 as we have added 3 states in the test-data.xml. So, we don’t need to write code to push data for testing, as it’s much easier using queries mentioned in the test-data.xml

Conclusion

Here we saw how to write integration tests with liquibase migrations. How it’s easier to set up the test data using the sql queries.

For complete working solution please refer this GitHub Repo