Spring Boot + Testcontainers + DbRider + P6spy = Testing Relational Databases

In this article you will find the approach of writing integration tests for the persistence layer using Spring Boot, Testcontainers, DbRider, Datasource Proxy.

All test examples are persistence layer agnostic — it means that the persistence layer can be implemented only using JDBC specification (without JPA specification)

This is the second article about Persistence Layer implementation and testing. The first article can be found here.

Also, you can find the full source code on the GitHub page.

But now let's jump right into the tests.

First of all, we will create our own annotation @PersistenceLayerTest and hide all configurations there. So our tests will look as follows:

The content of the annotation:

We will start with @DataJpaTest annotation as it is the main annotation that tells the Spring framework to load the context for the test. It is also one of the ways to “slice” your application (see here more about “slice” annotations). In our case, we ask Spring to load only database-related beans.

You can notice that we disable the out-of-the-box feature “showSql”. We did it because default logging does not show the actual query parameters. Here is an example of the default logging:

Hibernate: select author0_.id as id1_0_0_, author0_.email as email2_0_0_, author0_.full_name as full_nam3_0_0_ from author author0_ where author0_.id=?

To solve this problem we can use datasource proxy (p6spy proxy in our case) that will log queries nicely like this:

p6spy: select author0_.id as id1_0_0_, author0_.email as email2_0_0_, author0_.full_name as full_nam3_0_0_ from author author0_ where author0_.id=99

There is a nice spring-boot-starter for configuring the datasource proxy automatically. All we need to do is to bring the dependency:

and configure it with a couple of properties:

Take a note that we disabled proxy by default. Ideally, we would like to choose tests that need to have SQL logging. And it is done by another custom annotation @EnableSqlLogging:

Here we declared proxy auto-configuration explicitly as some Spring slices ignore it by default (for instance: DataJpsTest slice)

I personally keep SQL logging only for the Persistence Layer test + some rare cases on the other layers.

Now, let's think about where we will get the database for testing. The best practice is to keep the test environment as similar to the production environment as possible. So we will use the same database as in production (MySQL in our case).

Likely we have Docker that can bring us almost any external dependency for testing. We will go further and use Testcontainers library that facilitates running docker containers directly from our tests. So we will need the necessary dependencies:

Take a note that testcontainers provide maven bom

Also, Testcontainers provides nice wrappers for many popular products (including MySQL database). Now we can create DatabaseContainerInitializer as a custom Spring initializer:

Let's look at the class more closely. You can see that MySQLContainer wrapper gives us nice DSL for running MySQL docker containers.

In the initialize part we override and configure our datasource for the newly created docker container (a container port will be assigned automatically to any free host port).

But the best part of the Testcontainers is that we can reuse the same docker containers for our tests. This feature is called ‘reusabl’ containers. For that we will configure additional options:

.withReuse(true)
.withCreateContainerCmdModifier(cmd -> cmd.getHostConfig().withRestartPolicy(RestartPolicy.alwaysRestart()))
.withLabel("group", "demo-db");

Each application should use its own labels to isolate containers from each other.

You can notice that the restart policy is set to ‘always’ as it will keep our container running even after the computer restart.

Also, there is an option to disable Testcontainers at all. It may be useful for CI/CD environments that do not support Testcontainers library yet (as it is basically a client for the docker demon and some CI tools may block it due to security reasons. At the time of writing this article Atlassian Bitbucket Pipelines had such problems so the only option was to create a docker container manually before the tests.

Another tricky moment is that ‘reusable’ containers work only after enabling the feature in the .testcontainers.properties file under ‘user home’ directory. This work can be automated with Maven plugin:

The content of the .testcontainers.properties file:

testcontainers.reuse.enable=true
checks.disable=true

“check.disable=true” disables the startup checks in favor of test speed.

Now Testcontainers will take care of creating the database for the tests.

The structure of the database is handled by Flyway migration tool (already built-in in the Spring context — see the source code for more details).

So we need to tell Spring that we do not need to use an embedded database (Spring Boot will try to configure the one if there is a dependency on the classpath) and also we do not need transaction rollback (sometimes it is useful to debug the database state after the test). We can do it by adding more annotations to the @PersistenceLayerTest annotation:

...
@Transactional(propagation = Propagation.NOT_SUPPORTED)
@AutoConfigureTestDatabase(replace = NONE)
public @interface PersistenceLayerTest {
}

Here we have disabled the transaction support for the test level so transaction boundaries are managed fully on the source code level (it means that repositories will commit/rollback transaction and tests will play a role of the client).

The last missing part for comfortable testing is the test data.

There is a great tool Database Rider for managing datasets. It uses another library DbUnit as the main engine and makes the configuration extremely easy. There are a lot of options for configuration depending on your environment, but in the case of JUnit5 and Spring Boot all you need to do is to place @Dbrider annotation for your test and that is it (for more information see the official documentation — luckily the documentation is pretty good).

After that, you can place @DataSet annotation on your class/test method and use the DbUnit dataset in a preferable format (YAML, XML, JSON, CSV or XLS format). In our case, it will be XML:

dataset/init.xml

There are many options for using the DbRider library so you can check its official page. Here I would concentrate on the one option that I find really important.

By default, there is an enabled-by-default option ’Sequence Filtering’ that executes your dataset in the correct order, but it has a performance penalty as it needs to get table metadata for building the dependency graph (see issue #252 for more details).

So I would recommend disabling the ‘Sequence Filtering’ option. It can be easily done on the application level: just create dbunit.yml file in the test resource folder with the property:

disableSequenceFiltering: true

But keep in mind that you will need to maintain the correct order of your dataset in that case.

Hope you enjoyed the article and let me know if you have any comments or suggestions.