Web (micro, non-micro, monolith) services/applications that we write in Java often interact with some database. In my personal experience, most of the time this has been some well known relational database.

With relational databases, our applications use SQL to achieve the desired behavior from the database. The SQL may be hand-written and executed via plain JDBC. Or, it may be generated by some (possibly ORM) framework. The SQL is still there after any intermediate magic does its job.

How do I know that my SQL does what I expect it to do? I test it against the database. I usually have some abstraction layer, some repository/DAO class that exposes a Java interface. I then have some setup code to spawn some database that my tests then exercise my repository class against.

In the past, I relied a lot on in-memory databases like H2 or HSQLDB that I would embed in my unit tests. Those are fast and they have compatibility modes to somewhat emulate PostgreSQL or MySQL - the databases that I used in the projects that I worked on the most.

Sometimes, however, using the in-memory databases was not an option. For example, a few times I had to come to work on an existing project with no tests for the persistence layer only to figure that the project already depended on some vendor-specific database features that the in-memory databases could not emulate.

Other times, the existing project setup would require each developer to run a database on their development machine in order to run the tests. This obviously is unacceptable and always needs to be fixed. Don’t get me started.

In those cases, I used to spawn a containerized database for the persistence tests. It would start before the tests and shut down after them. This has become rather trivial with libraries like testcontainers. We can easily spawn and tear down database containers using simple Java APIs nowadays. The database containers themselves are often optimized for performance as well - for example, the default testcontainers PostgreSQL container by default turns off fsync and does not write anything to disk since we want to lose any state written to those containers on shutdown anyway.

Containerized databases, however, are still slower than the in-memory options. At the same time, the in-memory ones are not the real thing - we run our tests against one database (assuming we can do it in the first place, there are no dependencies on vendor-specific features that the in-memory database does not emulate well) and then run the production system against another one. Which is also not ideal. We also have to think harder about the lifecycle of the database that we spawn for our tests.

Database lifecycle in tests

One database for all the tests

This is the simplest and fastest option. One database container is spawned and reused in all the tests. Here is a simple example with testcontainers.

Obviously, before the container is ready to use in the tests, the database needs to be created and all the migrations must have been executed.

This one is great for its simplicity to set up and the speed. However, it requires discipline to not make any assumptions about the existing data that may be written by previous tests. As well as any data that may be being written by any concurrently running tests.

Those considerations make it less than ideal for unit level tests that should be testing isolated pieces of functionality. For example, one test may be adding some rows of data and asserting on the count of rows in the given table, while another test may be writing more data to that table. Or maybe yet another test is testing something that might delete all the data from that table.

An empty database per test

One fresh, empty database per test method. This option is naturally better suited for unit level tests.

With fast in-memory databases, this is usually the option that makes the most sense and is equally fast and easy to implement.

With a containerized database, this can be a bit more complex, unless we want to greatly sacrifice the test execution speed. We can:

  • Use the previous approach - have a single database container for all the tests, but drop and recreate the database (and run any migration scripts) before each test method and then run all the tests serially.
  • Create a separate database container with a fresh database per test method.

Both of the above options will be rather slow, especially as the number of tests grow, and as the number of migrations to apply grows.

One container, many databases

One optimization that I have been using successfully was to use a single database container for all the tests, but create separate databases (or schemas) within that container for each test method.

This way, we save on the container startup costs - we start the container and the database server in it only once. We then connect to that database server, create as many databases/schemas per tests as we need.

To do this, we need a singleton container, and setup code for each test to connect to that container using the default credentials, create a new database, apply migrations, and acquire a new data source that connects to the new database.

Here is a simple JUnit5 extension implementation with the above concepts for a PostgreSQL database:

public class PostgreSQLTestExtension implements BeforeEachCallback, AfterEachCallback {
    private static final PostgreSQLContainer<?> DB_CONTAINER =
            new PostgreSQLContainer<>("postgres:14.4-alpine").withReuse(true);

    private static final String JDBC_URI;

    static {
        DB_CONTAINER.start();
        JDBC_URI = "jdbc:postgresql://" + DB_CONTAINER.getHost() + ":" + DB_CONTAINER.getMappedPort(5432);
    }

    private final String databaseName = "testdb_" + UUID.randomUUID().toString().replace('-', '_');

    public DataSource getDataSource() {
        return dataSourceForDatabase(databaseName);
    }

    @Override
    public void beforeEach(ExtensionContext context) {
        executeInDefaultDatabase("CREATE DATABASE " + databaseName);
        Flyway.configure()
                .dataSource(dataSourceForDatabase(databaseName))
                .load()
                .migrate();
    }

    @Override
    public void afterEach(ExtensionContext context) {
        executeInDefaultDatabase("DROP DATABASE " + databaseName);
    }

    private static DataSource dataSourceForDatabase(String databaseName) {
        DataSource dataSource = new PGSimpleDataSource();
        dataSource.setUrl(JDBC_URI + "/" + databaseName);
        dataSource.setUser(DB_CONTAINER.getUsername());
        dataSource.setPassword(DB_CONTAINER.getPassword());
        return dataSource;
    }

    private void executeInDefaultDatabase(String sql) {
        DataSource dataSource = dataSourceForDatabase(DB_CONTAINER.getDatabaseName());
        try (Connection conn = dataSource.getConnection()) {
            conn.createStatement().execute(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

The beforeEach method will run before each test and will create a new database for the test. It will also run the migration scripts using Flyway in this case.

The extension can then be used in tests as follows:

class DatabaseTest {
    @RegisterExtension
    private final PostgreSQLTestExtension database = new PostgreSQLTestExtension();

    private final SomeRepository someRepository = new SomeRepository(database.getDataSource());

    @Test
    void myDatabaseTest() {
        // exercise someRepository that talks to the database,
        // or interact with the test database directly - DataSource dataSource = database.getDataSource();
    }
}

I have been using this pattern with great success, but then, as the project started to grow, I noticed that the existing tests started becoming slower and slower.

The culprit turned out to be the growing number of database migrations that get applied to each new database. Which led me to figure out the next optimization.

One container, many databases, one migration

Knowing that a growing amoung of database migrations that run before each test method degrades the performance of my tests, I figured a way to apply the migrations just once. The trick is - apply the migrations to the default database that’s created with the container - just like you would with the one database for all tests approach. And then, clone that clean database for each test instead of running all the migration SQLs over and over on an empty database.

With PostgreSQL, the cloning is easy - an existing database can be used as a template when creating a new one:

public class PostgreSQLTestExtension implements BeforeEachCallback, AfterEachCallback {
    private static final PostgreSQLContainer<?> DB_CONTAINER =
            new PostgreSQLContainer<>("postgres:14.4-alpine").withReuse(true);

    private static final String JDBC_URI;

    static {
        DB_CONTAINER.start();
        JDBC_URI = "jdbc:postgresql://" + DB_CONTAINER.getHost() + ":" + DB_CONTAINER.getMappedPort(5432);
        Flyway.configure()
                .dataSource(dataSourceForDatabase(DB_CONTAINER.getDatabaseName()))
                .load()
                .migrate();
    }

    private final String databaseName = "testdb_" + UUID.randomUUID().toString().replace('-', '_');

    public DataSource getDataSource() {
        return dataSourceForDatabase(databaseName);
    }

    @Override
    public void beforeEach(ExtensionContext context) {
        executeInDefaultSchema("CREATE DATABASE " + databaseName + " TEMPLATE " + DB_CONTAINER.getDatabaseName());
    }

    @Override
    public void afterEach(ExtensionContext context) {
        executeInDefaultSchema("DROP DATABASE " + databaseName);
    }

    private static DataSource dataSourceForDatabase(String databaseName) {
        DataSource dataSource = new PGSimpleDataSource();
        dataSource.setUrl(JDBC_URI + "/" + databaseName);
        dataSource.setUser(DB_CONTAINER.getUsername());
        dataSource.setPassword(DB_CONTAINER.getPassword());
        return dataSource;
    }

    private void executeInDefaultSchema(String sql) {
        DataSource dataSource = dataSourceForDatabase(DB_CONTAINER.getDatabaseName());
        try (Connection conn = dataSource.getConnection()) {
            conn.createStatement().execute(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

The above is almost the same as the previous example. The differences are:

  • Flyway migrations are executed in the static block, right after the DB container is started.
  • The CREATE DATABASE command in beforeEach method uses the default database as a template.

The extension is used the same way as before - no changes in the consumers are needed.

With MySQL this gets a bit more involved - there is no trivial way to clone an existing database. I was able to achieve similar results by dumping the migrated database to a file and running it on a new database. This way, only one SQL file is executed per database instead of numberOfMigrations SQL files.

Eventually, I ended up writing a small library that encapsulates those concepts. The project README has some performance comparisons to show the execution speed difference (up to 87% with only 50 migrations). You can either use that, or have one less dependency in your project and simply copy and reuse what I described above.