Java Database Integration Tests with Database Rider and Testcontainers

Introduction

 

You’ve probably come across a pull request that was successfully built, passed every test, and everything was perfect up until you pushed it to production and everything came down crashing and burning. I am, of course, exaggerating, because you wouldn’t and shouldn’t push the code directly to the production, but that’s a subject for another blog :).

The developer will probably say “But it works on my machine” and maybe he would be right, but, what if he had a different version of the database on his machine than the production has? Or, what if he wrote some kind of integration tests but since the pipeline is running those tests, he chose the option to use an embedded database which again differs from the production one? All those reasons are why you should write proper integration tests which replicate the production state correctly.

Of course, the question poses: how to write those integration tests? Worry not, my friend, because, in the next few minutes, you will be able to learn how. To tackle this challenge, my weapons of choice are Database Rider and Testcontainers. Through an example with the MSSQL server database, I will show you how to create fast and reliable integration tests.

 

Basic setup

 

For this particular problem, I’ve decided to use a Spring boot application with the Hibernate framework. Microsoft SQL Server is the database of choice, but at this point, it doesn’t matter which database is used, as mentioned in the introduction. For the basic setup, it’s important to include Maven dependencies:

 

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter</artifactId>

		</dependency>

		<dependency>

			<groupId>com.microsoft.sqlserver</groupId>

			<artifactId>mssql-jdbc</artifactId>

			<version>11.2.1.jre17</version>

		</dependency>

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-data-jpa</artifactId>

			<scope>compile</scope>

		</dependency>

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-web</artifactId>

			<version>3.0.0</version>

			<scope>compile</scope>

		</dependency>

 

I will create a simple 3-layered CRUD application, with controller, service, and repository. I will have a Person model and person entity with parameters as shown in the following pieces of code. I removed getters for better readability, but you should include them in your code:

 

package com.example.dbriderandtestcontainers.model;



import javax.persistence.Column;

import javax.persistence.Id;

import java.util.UUID;



public class PersonModel {



    private UUID id;

    private String name;

    private String surname;

    private String username;

    private Long age;

    private Boolean eligible;



    public PersonModel(UUID id, String name, String surname, String username, Long age, Boolean eligible) {

        this.id = id;

        this.name = name;

        this.surname = surname;

        this.username = username;

        this.age = age;

        this.eligible = eligible;

    }

}

 

package com.example.dbriderandtestcontainers.entity;



import org.hibernate.annotations.Type;



import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.Table;

import java.util.UUID;



@Entity

@Table(name = "Person", schema = "dbo")

public class PersonEntity {

    @Id

    @Column(name="ID")

    @Type(type = "uuid-char")

    private UUID id;



    @Column(name = "Name")

    private String name;



    @Column(name = "Surname")

    private String surname;



    @Column(name = "Username")

    private String username;



    @Column(name = "Age")

    private Long age;



    @Column(name = "Eligible")

    private Boolean eligible;



    public PersonEntity(UUID id, String name, String surname, String username, Long age, Boolean eligible) {

        this.id = id;

        this.name = name;

        this.surname = surname;

        this.username = username;

        this.age = age;

        this.eligible = eligible;

    }



    public PersonEntity() {

    }

}

 

A mapper between those two models is also needed:

 

package com.example.dbriderandtestcontainers.util;



import com.example.dbriderandtestcontainers.entity.PersonEntity;

import com.example.dbriderandtestcontainers.model.PersonModel;



public class PersonConverter {

    public static PersonModel toPersonModel(PersonEntity entity) {

        return new PersonModel(

                entity.getId(),

                entity.getName(),

                entity.getSurname(),

                entity.getUsername(),

                entity.getAge(),

                entity.getEligible()

        );

    }



    public static PersonEntity toPersonEntity(PersonModel personModel) {

        return new PersonEntity(

                personModel.getId(),

                personModel.getName(),

                personModel.getSurname(),

                personModel.getUsername(),

                personModel.getAge(),

                personModel.getEligible()

        );

    }

}

 

To help me mock the random UUID generation (since I am using it as an ID in my model), I’ve created the UUID generator which will be easier to mock in the tests:

 

package com.example.dbriderandtestcontainers.util;



import org.springframework.stereotype.Component;



import java.util.UUID;



@Component

public class UuidGenerator {



    public UUID generateUuid() {

        return UUID.randomUUID();

    }

}

 

After I’ve created the models and their utility, I will create the repository layer:

 

package com.example.dbriderandtestcontainers.repository;



import com.example.dbriderandtestcontainers.entity.PersonEntity;

import org.springframework.data.repository.CrudRepository;



import java.util.UUID;



public interface PersonRepository extends CrudRepository<PersonEntity, UUID> {

}

 

After the repository layer, the Service layer is created:

 

package com.example.dbriderandtestcontainers.service;



import com.example.dbriderandtestcontainers.util.PersonConverter;

import com.example.dbriderandtestcontainers.entity.PersonEntity;

import com.example.dbriderandtestcontainers.model.PersonModel;

import com.example.dbriderandtestcontainers.repository.PersonRepository;

import com.example.dbriderandtestcontainers.util.UuidGenerator;

import org.springframework.stereotype.Service;



import java.util.ArrayList;

import java.util.List;

import java.util.Optional;

import java.util.UUID;



@Service

public class PersonService {

    private PersonRepository personRepository;

    private UuidGenerator uuidGenerator;



    public PersonService(PersonRepository personRepository, UuidGenerator uuidGenerator) {

        this.personRepository = personRepository;

        this.uuidGenerator = uuidGenerator;

    }



    public PersonModel createPerson(PersonModel personModel) {

        personModel.setId(this.uuidGenerator.generateUuid());

        return PersonConverter.toPersonModel(

                this.personRepository.save(PersonConverter.toPersonEntity(personModel)));

    }



    public List<PersonModel> getAllPersons() {

        List<PersonModel> personModels = new ArrayList<>();

        for (PersonEntity personEntity : this.personRepository.findAll()) {

            personModels.add(PersonConverter.toPersonModel(personEntity));

        }

        return personModels;

    }



    public PersonModel updatePerson(PersonModel personModel) {

        Optional<PersonEntity> entity = this.personRepository.findById(personModel.getId());

        if (entity.isPresent()) {

            return PersonConverter.toPersonModel(

                    this.personRepository.save(

                            PersonConverter.toPersonEntity(personModel)));

        }

        throw new IllegalArgumentException();

    }



    public void deletePerson(UUID personId) {

        Optional<PersonEntity> entity = this.personRepository.findById(personId);

        if(entity.isPresent()) {

            this.personRepository.deleteById(personId);

        }

    }

}

 

And, to complete the 3 layer architecture, the Controller:

 

package com.example.dbriderandtestcontainers.controller;



import com.example.dbriderandtestcontainers.model.PersonModel;

import com.example.dbriderandtestcontainers.service.PersonService;

import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.DeleteMapping;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.PutMapping;

import org.springframework.web.bind.annotation.RequestBody;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;



import java.util.List;

import java.util.UUID;



@RestController

@RequestMapping("/person")

public class PersonController {

    private final PersonService personService;



    public PersonController(PersonService personService) {

        this.personService = personService;

    }



    @PostMapping

    public ResponseEntity<PersonModel> createPerson(@RequestBody PersonModel personModel) {

        return ResponseEntity.ok(this.personService.createPerson(personModel));

    }



    @GetMapping

    public ResponseEntity<List<PersonModel>> getAllPersons() {

        return ResponseEntity.ok(this.personService.getAllPersons());

    }



    @PutMapping

    public ResponseEntity<PersonModel> updatePerson(@RequestBody PersonModel personModel) {

        return ResponseEntity.ok(this.personService.updatePerson(personModel));

    }



    @DeleteMapping("/{person-id}")

    public ResponseEntity<Void> deletePerson(@PathVariable("person-id") UUID personId) {

        this.personService.deletePerson(personId);

        return ResponseEntity.ok(null);

    }

}

 

But, of course, this is not the end. I still don’t have the application to start all of my code with:

 

package com.example.dbriderandtestcontainers;



import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;



@SpringBootApplication

public class Application {



	public static void main(String[] args) {

		SpringApplication.run(Application.class, args);

	}



}

 

And, finally, I need to setup my application with some properties to make the connection with the database through Hibernate work:

 

spring:

  datasource:

    url: jdbc:sqlserver://localhost:1433;databaseName=YOURDBNAME;encrypt=true;trustServerCertificate=true;

    username: YOURUSER

    password: YOURPASSWORD

    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver



  jpa:

    properties:

      hibernate:

        dialect: org.hibernate.dialect.SQLServer2008Dialect

 

Now that the application is created, let’s go and write some integration tests!

 

Testcontainers

 

Testcontainers for Java is a Java library that supports JUnit tests, providing lightweight throwaway instances of anything that can be run in a Docker container. The advantage of this is, the production database can be dockerized (or an official docker image for the database version needed can be used) and simply start a container containing (pun intended) the same type of database used in production.

Let’s see what all of this stuff looks like in code!

Firstly, I will add testcontainer dependency into the pom.xml:

 

<dependency>

	<groupId>org.testcontainers</groupId>

	<artifactId>mssqlserver</artifactId>

	<version>1.17.6</version>

	<scope>test</scope>

</dependency>

 

If I wanted to use a generic testcontainer, or some kind of a different container that the Testcontainer library has the implicit support of, I would just change the Maven dependency.

After adding the dependency, I will create a connection to the database.

Since I am using the official database image without tables, I have to “tell” Hibernate to create the tables on the testcontainer. I will write this into the test configuration:

 

spring:

  datasource:

    url: "jdbc:sqlserver://localhost:33333"

    username: SA

    password: A_Str0ng_Required_Password

    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver



  jpa:

    hibernate:

      ddl-auto: update

    generate-ddl: true

 

The username and password are set to the default values (username: SA, password: A_Str0ng_Required_Password) since it was not really important for this blog, but this can be changed in testcontainer configuration as well. The datasource URL is not important in any case since it needs to be planted with the port value of the newly created testcontainer. Also, MSSQL requires to accept license so I will create a file that will simulate that:

 

mcr.microsoft.com/mssql/server:2017-latest

 

Now that the environment is set up for the testcontainer, it can be created in the test:

 

@SpringBootTest

@ActiveProfiles("test")

class ApplicationTests {

	static {

		DockerImageName myImage = DockerImageName.parse("mcr.microsoft.com/mssql/server:2017-latest")

				.asCompatibleSubstituteFor("mcr.microsoft.com/mssql/server");

		MSSQLServerContainer database = new MSSQLServerContainer(myImage);

		database.start();



		System.setProperty("spring.datasource.url", database.getJdbcUrl());

	}

}

 

I’ve created a new static block to start the container since it’s easier (and faster) to use it that way. If I have more than one integration test, it’s best to separate the testcontainer into a class, start it as a static block, and then extend this class so that it’s not necessary to get through the trouble of starting the testcontainer for each test separately. In this static block, I’ve first swapped the image name for the testcontainer (so that it’s an MSSQL 2017 image, not the latest version). When I got the image name, I started the MSSQL container. Finally, the last line swaps the URL which I’ve set in the application.yml with the real URL of the testcontainer which was created.

After running this piece of code, the first run will be the slowest since the docker image needs to be downloaded. After the application is started in test mode, it can be paused to check that the docker image was started, as shown in this image:

 

Test in debug mode to show that the container is up and running

 

Don’t be afraid if you see the testcontainers/ryuk container, this is perfectly normal :). Ryuk is used to manage the testcontainers and it will also be shut down after the test is executed. After confirming that the testcontainer is working, it’s time to find out how to insert data into that container and assert it in a few easy steps with Database Rider.

 

Database Rider

 

Database Rider is a Java library which integrates JUnit and DBUnit through JUnit rules. Thanks to this integration, it’s easy to prepare database data for tests using yaml, xml, json, xls, or csv files. I will show you how to create database data and assert it using this library.

The first thing to do is, of course, add a Maven dependency:

 

<dependency>

	<groupId>com.github.database-rider</groupId>

	<artifactId>rider-junit5</artifactId>

	<version>1.35.0</version>

	<scope>test</scope>

</dependency>

 

I’ve used Database Rider for JUnit5 since we are testing with JUnit5. Now that Database Rider is available in the codebase, I will add a @DBRider annotation to the test class to let the test know I will use it for filling the database:

 

@SpringBootTest

@ActiveProfiles("test")

@DBRider

class ApplicationTests {

}

 

I can now begin inserting the data which will be set in a .yml file for the purpose of this blog. The data must look like this:

 

PERSON:

  - ID: "5a8d68c8-2f28-4b53-ac5a-2db586512438"

    NAME: "Random"

    SURNAME: "Lady"

    USERNAME: "DiCaprioGF"

    AGE: 25

    ELIGIBLE: 1

 

The first row is the table name, after that, I need to put the dash(“-”) sign indicating the object which is part of a list (this is a convention for .yml files). After the dash, I will list the columns and their values to show what exactly will be inserted into the table. If I wanted to add another row of data, I would just add another dash with the column data filled.

The point of the test should be asserting the state of the database after the test was run (for instance, if I want to test the update endpoint, I must assert that the data in the database really changed). For that purpose, I will create another file that will represent how the database should look after the update was run:

 

PERSON:

  - ID: "5a8d68c8-2f28-4b53-ac5a-2db586512438"

    NAME: "Random"

    SURNAME: "Lady"

    USERNAME: "DiCaprioGFNoMore"

    AGE: 26

    ELIGIBLE: 0

 

Okay, now I’ve created some files containing data that should be inserted at the beginning of the test and some data which is expected at the end of the test. The question now poses: How do I make this test happen? Well, the answer is pretty simple: using annotations. Keep in mind that the .yml files which are being used in this blog should be saved in the resource folder of the project.

To use the .yml as the database state, I just need to use the @DataSet annotation with the name of the file to use. Similar to the @DataSet annotation, there exists a @ExpectedDataSet annotation that will point to the file of the expected dataset (makes sense, right?). I will annotate the test method and I am good to go!

The test method should look like this:

 

@Test

@DataSet("example-data.yml")

@ExpectedDataSet("example-expected.yml")

void dataShouldBeUpdatedInTheDatabase() {

	this.personController.updatePerson(

		new PersonModel(

				UUID.fromString("5a8d68c8-2f28-4b53-ac5a-2db586512438"),

				"Random",

				"Lady",

				"DiCaprioGFNoMore",

				26L,

				false

		)

	);

}

 

Just to make sure the data is inserted correctly at the beginning of the test, the database should be checked during the test. For that reason, I will debug the test and stop the execution before the first line of the test method body is called. The data from “example-data.yml” should be visible in the database table.

 

After running the test with Database Rider, the inserted data in the database is visible

 

Tips&Tricks

 

Here are some tips&tricks I learned using these two libraries in combination:

• As mentioned above in the “Testcontainers” section, it’s best to create and run the container only once. Multiple containers running and stopping takes a lot of time so it’s best to use only one container and keep it alive until the end of all integration tests.


• Since a single container is used for each test, the database should be cleaned after every test, not to mess with the other tests execution. It’s best to have a clean slate with each new test.


• It’s best to mock the id generators (like, for instance, UUID.generateUUID() method) because you have control over this method in the test and you can test more thoroughly. This is more of a testing tip in general, but it really finds its usage when testing on the database.


• If you find yourself in a pickle with Database Rider, read their documentation. It’s very detailed and I personally always found an answer on every trouble I had with their library.

 

Conclusion

 

In this blog, I’ve shown how to reproduce your production database environment in an integration test. I’ve also shown you how to create a database state, and how to easily assert it in a test. The full code (with some additional tests) is on GitHub. Happy coding!