Testing databases with Testcontainers and Liquibase

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:
Testcontainers test result

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.

Verify logback log statements

The basic idea behind unittests is that they prove that the code does what it’s expected to do. That means you have to prove that the code is executed in the first place. But sometimes the only way to prove that the code is executed, is to verify that something is logged. With the recent Log4Shell exploit in mind, we’re going to use a different framework: logback.

Initial setup

Because we want to use this technique in multiple tests, we’re going to create a separate class. This class should capture all logging events for a specific class under test, and store them for later retrieval. So let’s start

public class TestUtils {

  private final Object objectUnderTest;
  
  private final List<ILoggingEvent> loggingEvents = new ArrayList<>();
  
  public TestUtils(Object objectUnderTest){
    this.objectUnderTest = objectUnderTest;
  }
}

Notice that we set this up with an actual object instead of a class. We could just use the class instead. I have chosen to include the actual object under test, so the TestUtils class can include some reflection helpers. But that is beyond the scope of this post.

Setup logging

To start capturing the logging statements, we need to create an appender. Once the appender is created, we need to start it and configure the logger to use it. The appender has one important method: append. In our case, this method should add the loggingEvent to the list of loggingEvents. The code for setting up the logging becomes as follows:

    public void setupLogging(){
        AppenderBase<ILoggingEvent> appender = new AppenderBase<ILoggingEvent>() {
            @Override
            protected void append(ILoggingEvent iLoggingEvent) {
                loggingEvents.add(iLoggingEvent);
            }
        };
        appender.start();

        ch.qos.logback.classic.Logger logger = (ch.qos.logback.classic.Logger) LoggerFactory.getLogger(objectUnderTest.getClass());
        logger.addAppender(appender);
        logger.setLevel(Level.ALL);
    }

Find logging statements

Since we’re using this for unittests, we only expect one statement with a specific message. To get the loggingEvent, if it’s there, we check all logging events:

    public Optional<ILoggingEvent> getLoggingEventWithMessage(String message){
        return loggingEvents.stream()
                .filter(iLoggingEvent -> iLoggingEvent.getMessage().equals(message))
                .findAny();
    }

How to use

Immagine you need to test the following snippet:

public class MessageLogger {
	public void logMessage(boolean bool, String message){
		if(bool){
			LOG.info("Message is {}", message);
		}
	}
}

The test we can write is the following:

public class MessageLoggerTest {
	
	@Test
	public void logMessage(){
		MessageLogger messageLogger = new MessageLogger();
		TestUtils testUtils = new TestUtils(messageLogger);
		testUtils.setupLogging();
		
		messageLogger.logMessage(true, "Hello World!");
		
		Optional<ILoggingEvent> loggingEvent = testUtils.getLoggingEventWithMessage("Message is {}");
		assertTrue(loggingEvent.isPresent());
	}
}

Note that we query for the unformatted message. The arguments can be retrieved using ILoggingEvent.getArgumentArray().

Conclusion

Sometimes we need to verify whether a logging statements has been logged in order to prove that the code under test does what it’s supposed to do. This utility class makes it easy to do so.
We’ve seen how to add a custom appender to a logback logger that stores the received loggingEvents in a list. We have created a method to query the list for an event with a specific message.
Next we have seen an example of how to use this functionality.