Using Embedded Mysql in JUnit Tests

This small tutorial will explain our strategy for running JUnit tests against an “embedded” mysql server.


7 min read
Using Embedded Mysql in JUnit Tests

This small tutorial will explain our strategy for running JUnit tests against an “embedded” mysql server.

For the impatient, here’s the example project.

Motivation

Previously, we would execute our JUnit tests using the H2 in-memory database running in mysql compatibility mode. This setup worked well because tests executed quickly due to the in-memory nature of H2. However, we ran into issues with the compatibility between mysql and H2, namely the need to use features of Mysql’s SQL syntax in our flyway migrations that are not supported by H2. So, rather than run our tests against H2 pretending to be mysql, we decided to try executing them against an actual mysql instance. This is made easier by the wix-embedded-mysql library.

Tooling

The example project is a standard maven-based spring-boot project created with Spring Initialzr. It requires JDK 11 to build. The maven wrapper is included in the project so you don't need to have maven installed on your system to build.

Explanation

Let’s walk through the example project to see how this is all set up. To start, we need to include the wix-embedded-mysql dependency in the pom.xml file, along with a couple other dependencies for our example app to interact with the database.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://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>2.1.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.singlemusic.example</groupId>
    <artifactId>embedded-mysql-example</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>embedded-mysql-example</name>

    <description>Example project for embedded mysql junit tests</description>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>11</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- Test Dependencies -->
        <dependency>
            <groupId>com.wix</groupId>
            <artifactId>wix-embedded-mysql</artifactId>
            <version>4.2.0</version>
            <scope>test</scope>
        </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>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.22.1</version>
                <configuration>
                    <includes>**/TestSuite.class</includes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Notice that since we’re only going to be using the embedded mysql in tests we include it as test scope. We will revisit the build section of the pom file later.

Next, let’s look at the Application class that is just a main spring-boot application class:

Application.java


package com.singlemusic.example;

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);
    }
}

There’s nothing really exciting to see there.

Then, let’s examine the application properties file that contains our configurations:

application.properties

spring.application.name=@project.artifactId@

spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

The spring.application.name property is just there to give our application a name. We tell hibernate not to try to create tables on startup by setting spring.jpa.hibernate.ddl-auto to none. And, lastly, we tell hibernate which dialect to use with the spring.jpa.properties.hibernate.dialect property. Since we're connecting to mysql 5.X, we use the org.hibernate.dialect.MySQL5Dialect dialect.

We need a test to execute for our example. So, we have DatabaseTest here as a really basic test case that performs a SELECT 1 statement against the database. EntityManager is a bean that's automatically created and registered by spring, so we can inject it into our test case with @Resource

DatabaseTest.java

package com.singlemusic.example;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import java.math.BigInteger;

import static org.junit.Assert.assertEquals;

@RunWith(SpringRunner.class)
@SpringBootTest
@ActiveProfiles("test")
public class DatabaseTest {

    @Resource
    private EntityManager entityManager;

    @Test
    public void testDatabase() {
        Query query = entityManager.createNativeQuery("SELECT 1");
        assertEquals(BigInteger.valueOf(1L), query.getSingleResult());
    }
}

The meat of this whole example occurs in our TestSuite class. This is the JUnit suite where all our tests will be configured and executed:

TestSuite.java

package com.singlemusic.example;

import com.wix.mysql.EmbeddedMysql;
import com.wix.mysql.config.MysqldConfig;
import com.wix.mysql.config.SchemaConfig;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.runner.RunWith;
import org.junit.runners.Suite;

import java.time.ZoneId;
import java.util.TimeZone;

import static com.wix.mysql.distribution.Version.v5_7_19;

@RunWith(Suite.class)
@Suite.SuiteClasses({
        DatabaseTest.class
})
public class TestSuite {
    private static EmbeddedMysql embeddedMysql;

    @BeforeClass
    public static void _setupBeforeClass() {
        MysqldConfig config = MysqldConfig.aMysqldConfig(v5_7_19)
                .withPort(3307)
                .withTimeZone(TimeZone.getTimeZone(ZoneId.of("UTC")))
                .withUser("test", "test")
                .build();

        SchemaConfig schemaConfig = SchemaConfig.aSchemaConfig("test_database")
                .build();

        embeddedMysql = EmbeddedMysql.anEmbeddedMysql(config)
                .addSchema(schemaConfig)
                .start();
    }

    @AfterClass
    public static void _tearDownAfterClass() {
        if (null != embeddedMysql) {
            embeddedMysql.stop();
        }
    }
}

The @Suite.SuiteClasses annotation is how we tell JUnit what tests to run. This is where you would add all your tests that require the database and the spring context to run.

In our @BeforeClass method, we are creating the embedded mysql instance and starting it. There are a lot of configuration options that you have for the MysqlConfig object. You can also configure DDL scripts to run in the SchemaConfig configuration if you have any. In this example we're just creating an empty schema called test_database.

In our @AfterClass method we're simply stopping the embedded mysql instance.

By running our tests in this suite we are wrapping the execution of all our tests in these static before and after methods that start up the database and destroy it. This is done mostly for performance reasons — we only start up and use one mysql instance for all the tests. Unit test purists will argue that the test shouldn’t share the same database. Of course, it would be possible to include these methods in individual tests if you need a fresh database for each test case, but you’ll take a big hit in the speed of your tests.

Now, let’s revisit our pom file, highlighting the maven surefire plugin configuration:

...
<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-surefire-plugin</artifactId>
    <version>2.22.1</version>
    <configuration>
        <includes>**/TestSuite.class</includes>
    </configuration>
</plugin>
...

What this does is tell maven to only execute the TestSuite class when it executes the test goal. This ensures that maven won't try to execute any of our tests on their own, without the mysql database running for them.

Lastly, let’s look at our test configuration file

application-test.properties

spring.datasource.url=jdbc:mysql://localhost:3307/test_database
spring.datasource.username=test
spring.datasource.password=test

Here we tell spring about our database connection url and credentials, which you can see match the configuration we set up in TestSuite.java.

...
MysqldConfig config = MysqldConfig.aMysqldConfig(v5_7_19)
                .withPort(3307)
                .withTimeZone(TimeZone.getTimeZone(ZoneId.of("UTC")))
                .withUser("test", "test")
                .build();
...

Spring knows to load the properties in application-test.properties because we’ve declared the test profile as active in DatabaseTest with the @ActiveProfiles annotation.

Execution

So, with all these files in place, if we execute ./mvnw clean package from the root directory of our project, maven pulls our dependencies, compiles our code, and kicks off our tests. The suite in TestSuite.java executes, and our @BeforeClass method executes first. The embedded mysql library will now download the specific version of mysql we specified in our configuration, and save it to cache directory in our home folder. Once the download is finished, it will start up the mysql instance. Next, JUnit executes all of the tests defined in the suite—in this case just DatabaseTest. Once the tests finish executing, our @AfterClass method is executed, which stops the running mysql instance. In subsequent builds, the library will just use the cached mysql executable instead of downloading again, unless it is deleted or moved.

Conclusions

While H2 is a great tool and allows us to execute code written for mysql against a fast in-memory database, we hit some edge cases where it would simply not work for us. I would argue that the closer your test environment can get to your production environment the better. Catching bugs in the early build stages of your pipeline is always better than discovering them later! Executing our JUnit tests against a real mysql instance is just one way in which we try to get our tests closer to a production-like setup.

Related Articles

Logging Strategies
5 min read
Maven-Based Docker Projects
6 min read

Back To Top

🎉 You've successfully subscribed to Single Music Technology!
OK