Having found Sandra Parsick’s very useful guide on configuring testcontainers with the Spring Boot test framework I realised that with a few additions I could have a nice repeatable way to test and verify my JPA/Hibernate annotations.

I find Spring Boot’s data JDBC test support to be very useful in general but when it comes to ensuring that the MySQL table structure generated by my flyway migrations works correctly with my JPA entities, well I just don’t trust the in-memory databases provided by the test support classes.
This is because whilst all technically SQL compliant, the in-memory databases (HSQL, H2 etc) all have their own syntax variations on top of the variations that MySQL has too, which limits their use for testing as I will end up writing migrations in a format that may or may not be acceptable.

A trivial example of this would be that double quotes " are not allowed in H2 but are in MySQL or more importantly if specifying a database engine type in your create table statement e.g. create table(...) ENGINE=InnoDB; then H2 would not allow this either. Choosing/enforcing the database engine type for your tables can have a big impact on performance and reliability, admittedly some people may leave that to their infrastructure team to take care of but I prefer not to assume or leave anything to chance, especially if I’m the one who will be woken up at 04:00 to fix something.

I prefer to have my tests use a real instance of MySQL, so to this end I turn to the mysql testcontainers module support.

The first step is that the pom file needs to be updated to with the testcontainers specific junit-jupiter dependency, this will provide us with an extension for running tests with testcontainers (this is the Junit 5 version of a runner class):

<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>junit-jupiter</artifactId>
  <version>${testcontainers.version}</version>
  <scope>test</scope>
</dependency>

Next a test class needs to be created with the correct setup:

@Testcontainers
@SpringBootTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
public class VariantsRepositoryTest {

    @Container
    private static final MySQLContainer<?> mySQLContainer = new MySQLContainer<>("mysql:5.6")
            .withConfigurationOverride("mysql")
            .withCreateContainerCmdModifier( cmd -> cmd.withName("testcontainers-mysql") );

    @Autowired
    private MyRepository myRepository;

    private static Connection connection;

    @BeforeAll
    static void setUpBefore() throws SQLException {
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setURL(mySQLContainer.getJdbcUrl());
        dataSource.setUser(mySQLContainer.getUsername());
        dataSource.setPassword(mySQLContainer.getPassword());

        connection = dataSource.getConnection();
    }

    @DynamicPropertySource
    static void databaseProperties(DynamicPropertyRegistry registry) {
      registry.add("spring.datasource.jdbc-url", () -> mySQLContainer.getJdbcUrl());
      registry.add("spring.datasource.username", mySQLContainer::getUsername);
      registry.add("spring.datasource.password", mySQLContainer::getPassword);
      registry.add("spring.datasource.driver-class-name", mySQLContainer::getDriverClassName);
      registry.add("spring.flyway.locations", () -> "classpath:db/migration/data,classpath:db/migration/sql");
    }
}

We shall break down what the different parts of this test class are doing in the next sections.

Extensions

We are using the following JUnit 5 annotations for running the test:

@Testcontainers
@SpringBootTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
  • The @TestContainers annotation handles managing the container lifecycle
  • The @SpringBootTest will bring up the Spring application context and allow for spring based features such as autowired dependencies and property injection
  • The @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) annotation is telling the Spring test framework to not replace the database configuration with the in-memory ones it would usually try to find on the classpath
    • This is very important as without this we won’t be able to get the Spring context to use our container database.

Database Connection

One section I have added that Sandra’s solution did not have was a Connection object.
Having the means to access the database directly is useful for a few reasons:

  1. It allows for data set up to happen before the repository under test is called
  2. And we can perform the above set up by a different mechanism so that our repository.delete(entity) will not rely on repository.save(entity) ensuring that our tests go deeper than a hibernate caching layer.
  3. It allows for us to tear down / delete any data from a test preventing state from a previous test from impacting the next test in the suite.
private static Connection connection;

@BeforeAll
static void setUpBefore() throws SQLException {
    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setURL(mySQLContainer.getJdbcUrl());
    dataSource.setUser(mySQLContainer.getUsername());
    dataSource.setPassword(mySQLContainer.getPassword());

    connection = dataSource.getConnection();
}

Overriding Spring Properties

As we have disabled Spring Boot test’s use of the in-memory database, we need to tell the context about and inject the property values for our container database, we do this via the DynamicPropertySource below:

@DynamicPropertySource
static void databaseProperties(DynamicPropertyRegistry registry) {
  registry.add("spring.datasource.jdbc-url", () -> mySQLContainer.getJdbcUrl());
  registry.add("spring.datasource.username", mySQLContainer::getUsername);
  registry.add("spring.datasource.password", mySQLContainer::getPassword);
  registry.add("spring.datasource.driver-class-name", mySQLContainer::getDriverClassName);
  registry.add("spring.flyway.locations", () -> "classpath:db/migration/data,classpath:db/migration/sql");
}

An important part of this is adding the flyway migration location, because the Spring Boot Data JDBC test framework does not load up a full application context the spring.flyway.locations values will not be populated from your main application.properties file so we add them here.

Conclusion

I’m pretty happy with this solution at this time, there are minimal extra dependencies or configuration and the configuration that is present closely matches what we need/use in our application properties file.
The value of actually being able to test against the exact version of SQL and MySQL that I will be running without having to package up the entire application and manually test is a big win for me.
Now I can have these tests run relatively quickly from my IDE reducing the feedback loop for database schema issues and repository/JPA behaviours that otherwise would require a full deploy to notice.

Note this solution as it stands now has not been tested for and is probably not useful for multiple test instances/classes, there will need to be an improvement for operating across multiple classes. Sandra’s abstract test class in the example may be one way to go.
I will post a follow up on this process when I inevitably need to expand my repository tests to multiple classes and don’t want to have a test container spun up and down for every test case in every test suite.

Footnote

At the time of writing this project; testcontainers-spring-boot looks interesting but was depedency overkill for what I needed and would give maven’s depedency enforcer headaches with trying to get dependency convergence to align.
I value the reduced dependency size, reduced conflicts and subsequent ease of non-breaking dependency updates over this at this point but I may revisit in future.