Testcontainers make it easy to test your database integration by providing docker containers for many databases, which can be started from within your unittest. This is great, but it requires a bit of work to get started since the databases are empty. Liquibase is a tool to manage database schema changes. Combining the two is an easy way to get the right schema to use in your tests. For this post we’re going to use both frameworks to setup and test a MySQL database.

Let’s start with creating a test which uses testcontainers. First we need to add some maven dependencies.

    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>testcontainers</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>mysql</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.28</version>
    </dependency>
    <dependency>
      <groupId>org.testcontainers</groupId>
      <artifactId>junit-jupiter</artifactId>
      <scope>test</scope>
    </dependency>

We’re also going to add a dependencyManagement section:

  <dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>testcontainers-bom</artifactId>
        <version>1.16.3</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>

That should contain all the dependencies we need. Docker also needs to be installed in order for the container to start.

Now, let’s create a test that uses a MySQL container.

@Testcontainers
public class TestcontainersTest {

    @Container
    public MySQLContainer<?> mySQLContainer = new MySQLContainer("mysql:8.0.28");

    @Test
    public void testContainer(){
        mySQLContainer.start();
        try(Connection conn = mySQLContainer.createConnection("")){
            Statement statement = conn.createStatement();
            statement.execute(mySQLContainer.getTestQueryString());
            ResultSet resultSet = statement.getResultSet();
            resultSet.next();
            int result = resultSet.getInt(1);
            System.out.println(result);
        } catch (SQLException e){
            fail(e.getMessage());
        }
    }
}

The first thing to notice is the @Testcontainers annotation. This will tell the Testcontainers framework to look for containers to start. Which brings us to the next thing, the @Container annotation. This will create a container. In this case the container will be a MySQL container, specifically running version 8.0.28.

Once the container has been created, it is not started yet. In the first line of our test, we start it by calling the .start() method. After that, we request a JDBC connection to the container, and we’re set.

But we don’t have a useful database yet. That’s where Liquibase comes in. We need to add the Liquibase dependency, create the scripts that update the database, and tell Liquibase to run those scripts.

    <dependency>
      <groupId>org.liquibase</groupId>
      <artifactId>liquibase-core</artifactId>
      <version>4.7.1</version>
    </dependency>

We’ll save the liquibase scripts in /src/main/resources/database, so we can access them from the unittest.

<?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-3.5.xsd">
    <include file="change_001.xml" relativeToChangelogFile="true" />
</databaseChangeLog>

<?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-3.5.xsd">
    <changeSet id="001" author="ghyze.nl">
        <createTable tableName="PERSON">
            <column name="id" type="INT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_person" />
            </column>
            <column name = "firstname" type="varchar(255)" />
            <column name = "lastname" type="varchar(255)" />
        </createTable>
    </changeSet>
</databaseChangeLog>

And then we add the code to execute the scripts to the unittest:

    @Test
    public void testContainer(){
        mySQLContainer.start();
        try(Connection conn = mySQLContainer.createConnection("")){
            Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(conn));
            Liquibase liquibase = new Liquibase("/database/changelog.xml", new ClassLoaderResourceAccessor(), database);
            liquibase.update("test");

			// some more code
        } catch (SQLException | LiquibaseException e){
            fail(e.getMessage());
        }
    }

The liquibase.update() statement needs a context. This is a way to execute or leave out some scripts. For our test this doesn’t really matter, so we just put in “test”.

And now to proof that it works, let’s insert a record in the database and read it again:

    @Test
    public void testContainer(){
        mySQLContainer.start();
        try(Connection conn = mySQLContainer.createConnection("")){
            Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(conn));
            Liquibase liquibase = new Liquibase("/database/changelog.xml", new ClassLoaderResourceAccessor(), database);
            liquibase.update("test");

            PreparedStatement preparedStatement = conn.prepareStatement("insert into PERSON values(?, ?, ?)");
            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, "john");
            preparedStatement.setString(3, "doe");
            preparedStatement.execute();

            Statement statement = conn.createStatement();
            statement.execute("select * from PERSON");
            ResultSet resultSet = statement.getResultSet();
            resultSet.next();
            int id = resultSet.getInt(1);
            String firstName = resultSet.getString(2);
            String lastName = resultSet.getString(3);
            System.out.println("id: "+id+", first name:"+firstName+", last name:"+lastName);
        } catch (SQLException | LiquibaseException e){
            fail(e.getMessage());
        }
    }

And the result should look something like this:

Conclusion

When your system uses a database, that database should be tested. There are different ways to setup and test the database, and Testcontainers is one of them. The advantage of using testcontainers is that the test is close to how the software will run on production. The disadvantage is that these tests are slow, and should be treated more as integration tests than regular unittests. In other words, you don’t want to run these tests all the time. Also, Docker needs to be installed and running. But just starting a MySQL container isn’t enough. We can’t test an empty database. Liquibase can be used to setup the database to get it in a testable state.