Testing Dockerized SQL Databases

Alex SotoMay 3rd, 2017Last Updated: May 3rd, 2017

2 37 3 minutes read

One of the big advantages of using Docker for testing is that you don’t need to install the required dependencies of code under tests in all machines where you are going to run these tests. This is really helpful for external services such as database servers, mail services, JMS queues, … Also one of the big advantages of this approach is that the tests are going to use the same version used in production.

So for persistence tests using Docker is a really good approach to follow. But as usually this approach comes with some drawbacks.

The first one is that obviously you need to have Docker installed in all machines that needs to run the tests, not a big problem but something to take into consideration, as well as Docker inside Docker problem.

The second one is that you need to automate somehow the starting and stopping of the container.

The third one is that Docker containers are ephemeral. This means that when you start the container, in this case a container with a SQL server, then you need to migrate the database schema there.

The fourth one, and this is not only related to Docker, is that you need to maintain test method execution isolated from test to test execution, by providing known data before execution and cleaning data after the execution so other test finds the environment clean.

First and second problems are fixed with Arquillian Cube ( http://arquillian.org/arquillian-cube/). It manages lifecycle of containers by starting and stopping them automatically before and after test class execution. Also it detects when you are running into a DinD situation and configures started containers accordantly.

Arquillian Cube offers three different ways to define container(s).

Defining a docker-compose file.
Defining a Container Object.
Using Container Object DSL.

For this post, Container Object DSL approach is the one used. To define a container to be started before executing tests and stopped after you only need to write next piece of code.

 
 @ClassRule
public static ContainerDslRule redis = new ContainerDslRule("redis:3.2.6")
                                           .withPortBinding(6379);

In this case a JUnit Rule is used to define which image should be used in the test (redis:3.2.6) and add as binding port the Redis port (6379).

The third one can be fixed using Flyway. It is an open-source database migration tool for SQL databases that allows you to automate the creation of database schemas.

Flyway is useful here since you can start the Docker container and then apply all migrations to the empty database using Flyway.

The fourth problem can be fixed by using tools like DBUnit. iI puts your database into a known state between test runs by populating database with known data, and cleaning it after the test execution.

Arquillian integrates with both of these tools (Flyway and DBUnit) among others with its extension called Arquillian Persistence Extension (aka APE),

An example on how to use APE with DBUnit is shown in next snippet:

@Rule
public ArquillianPersistenceRule arquillianPersistenceRule = new ArquillianPersistenceRule();

@DbUnit
@ArquillianResource
RdbmsPopulator rdbmsPopulator;

// To populate data
rdbmsPopulator.forUri(URI.create("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1"))
              .withDriver(Driver.class)
              .withUsername("sa")
              .withPassword("")
              .usingDataSet("heroes.yml")
            .execute();

// To clean data

rdbmsPopulator.forUri(URI.create("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1"))
              .withDriver(Driver.class)
              .withUsername("sa")
              .withPassword("")
              .usingDataSet("heroes.yml")
            .clean();

You can use Arquillian runner as shown in dbunit-ftest-example or as shown in previous snippet using a JUnit Rule. Choosing one or other depends on your test requirements.

So how everything fits together in Arquillian so you can boot up a Docker container with a SQL database, such as PostgreSQL, before test class execution, then migrate SQL schema and populate it with data, execute the test method, then clean the whole database so next test method finds a clean database and finally after test class execution, the Docker container is destroyed?

Let’s see it in the next example:

public class FilmLibraryTest {

    public static final String DB = "filmlibrary";
    public static final String USERNAME = "postgres";
    public static final String PASSWORD = "postgres";

    @ClassRule
    public static ContainerDslRule postgresql = new ContainerDslRule("postgres:9.6.2-alpine")
        .withPortBinding(5432)
        .withEnvironment("POSTGRES_PASSWORD", PASSWORD,
            "POSTGRES_USER", USERNAME,
            "POSTGRES_DB", DB);

    @Rule
    public ArquillianPersistenceRule arquillianPersistenceRule = new ArquillianPersistenceRule();

    @Flyway
    @ArquillianResource
    RdbmsPopulator flywayRdbmsPopulator;

    @DbUnit
    @ArquillianResource
    RdbmsPopulator dbUnitRdbmsPopulator;


    @Test
    public void should_find_all_hollywood_films() {

        final URI jdbcUri = URI.create(
            String.format("jdbc:postgresql://%s:%d/%s", postgresql.getIpAddress(), postgresql.getBindPort(5432), DB));

        flywayRdbmsPopulator
            .forUri(jdbcUri)
            .withDriver(Driver.class)
            .withUsername(USERNAME)
            .withPassword(PASSWORD)
            .usingDataSet("db/migration")
            .execute();

        dbUnitRdbmsPopulator.forUri(jdbcUri)
            .withDriver(Driver.class)
            .withUsername(USERNAME)
            .withPassword(PASSWORD)
            .usingDataSet("hollywoodfilms.yml")
            .withOptions(options()
                            .schema("hollywood")
                            .build()
            )
            .execute();

        // Code under test

        flywayRdbmsPopulator
            .forUri(jdbcUri)
            .withDriver(Driver.class)
            .withUsername(USERNAME)
            .withPassword(PASSWORD)
            .usingDataSet("db/migration")
            .clean();

    }

}

# src/test/resources/

films:

  - title: Trolls
    release: 08/10/2016

-- src/test/resources/db/migration/
CREATE SCHEMA hollywood;
CREATE TABLE hollywood.films (title text, release text);

Test is not so much complicated and it is pretty much self explanatory of what it is doing in each step . You are creating the Docker container using Arquillian Cube DSL, and also you are configuring the populators by just using Arquillian APE DSL.

So thanks of Arquillian Cube and Arquillian APE you can make your test totally isolated from your runtime, it will be executed always agains the same PostgreSQL database version and each test method execution will be isolated.

You can see full code at https://github.com/arquillian/arquillian-extension-persistence/tree/2.0.0/arquillian-ape-sql/standalone/dbunit-flyway-ftest

Reference:

Testing Dockerized SQL Databases from our JCG partner Alex Soto at the One Jar To Rule Them All blog.