Introduction

In this article, we are going to see how we can use Testcontainers for database integration testing.

If you are still using HSQLDB or H2 to test your Spring Boot application that runs on Oracle, SQL Server, PostgreSQL or MySQL in production, then you are better off switching to Testcontainers.

In-memory databases vs. Docker

In-memory relational database systems, like HSQLDB and H2 were developed in early 2000 for two reasons:

1. Because, at the time, installing a database system was a very tedious process, taking a significant amount of time.
2. In-memory database access is orders of magnitude faster compared to a database that has to load from or flush to the disk.

However, in 2013, Docker was released, making it very easy to run a Docker container that could host a relational database on any host operating system.

To speed up data access for testing, you can mount the data folder and the transaction log to the tmpfs in-memory Linux storage, as I explained in this article.

So, nowadays, we have better tools that we didn’t have until 2013. We can now use Docker for integration testing instead of an in-memory database.

The advantage of Docker database containers is that you are going to test against the very same database version that you are using in production. If you have SQL-specific queries or stored procedures, then you cannot use H2 or HSQLDB to test them. You need a real DB engine to run the specific queries, stored procedures, or functions.

Testcontainers Database Integration Testing

While you can start and stop the Docker database containers manually or automatically using the docker-maven-plugin, there is a much better way.

Tectcontainers is an open-source project that provides a Java API so that you manage your Docker containers programmatically, and this is extremely useful, as you will soon see.

And the best way to show you how Testcontainers works is by integrating it into the amazing Hypersistence Utils project.

The Hypersistence Utils project provides support for JSON, ARRAY, Ranges, CurrencyUnit, MonetaryAmount, YearMonth, and many other types for Oracle, SQL Server, PostgreSQL, and MySQL.

To add support for a new Type, you’ll have to run the integration tests on all these relational database systems.

Therefore, you have several options:

• you can install Oracle XE, SQL Server Express, PostgreSQL and MySQL locally
• you can use Docker Compose to start all these databases based on the compose configuration file
• you can use Testcontainers to automate the process

In my case, I already have all these Top 4 relational databases installed on my machine since I’m heavily using them for the High-Performance Java Persistence training, and High-Performance SQL training, and to develop Hypersistence Optimizer.

But, other Hypersistence Utils project contributors might not have all these databases installed, so running the project integration tests would be an issue for them.

And this is exactly where Testcontainers comes to the rescue!

You can set up the DataSource provider to connect to a pre-defined database URL, and if it fails, start a Docker container on demand.

Adding Testcontainers to Hypersistence Utils

Prior to using Testcontainer, Hypersistence Utils already used a DataSourceProvider abstraction that allowed tests to define the required database, which looks as follows.

The DataSourceProvider interface defines the database management contract:

public interface DataSourceProvider {

    Database database();

    String hibernateDialect();

    DataSource dataSource();

    String url();

    String username();

    String password();
}

And the AbstractContainerDataSourceProvider defines how the Testcontainers Docker database container is started on demand:

public abstract class AbstractContainerDataSourceProvider 
        implements DataSourceProvider {

    @Override
    public DataSource dataSource() {
        DataSource dataSource = newDataSource();
        try(Connection connection = dataSource.getConnection()) {
            return dataSource;
        } catch (SQLException e) {
            Database database = database();
            if(database.getContainer() == null) {
                database.initContainer(username(), password());
            }
            return newDataSource();
        }
    }

    @Override
    public String url() {
        JdbcDatabaseContainer container = database().getContainer();
        return container != null ?
            container.getJdbcUrl() :
            defaultJdbcUrl();
    }

    protected abstract String defaultJdbcUrl();

    protected abstract DataSource newDataSource();
}

The AbstractContainerDataSourceProvider allows us to use a local database, which is resolved first. In case there’s no local database to connect to, a new database container is instantiated by calling the initContainer method on the associated Database object, which looks as follows:

public enum Database {
    POSTGRESQL {
        @Override
        protected JdbcDatabaseContainer newJdbcDatabaseContainer() {
            return new PostgreSQLContainer(
                "postgres:13.7"
            );
        }
    },
    ORACLE {
        @Override
        protected JdbcDatabaseContainer newJdbcDatabaseContainer() {
            return new OracleContainer(
                "gvenzl/oracle-xe:21.3.0-slim"
            );
        }

        @Override
        protected boolean supportsDatabaseName() {
            return false;
        }
    },
    MYSQL {
        @Override
        protected JdbcDatabaseContainer newJdbcDatabaseContainer() {
            return new MySQLContainer(
                "mysql:8.0"
            );
        }
    },
    SQLSERVER {
        @Override
        protected JdbcDatabaseContainer newJdbcDatabaseContainer() {
            return new MSSQLServerContainer(
                "mcr.microsoft.com/mssql/server:2019-latest"
            );
        }

        @Override
        protected boolean supportsDatabaseName() {
            return false;
        }

        @Override
        protected boolean supportsCredentials() {
            return false;
        }
    };

    private JdbcDatabaseContainer container;

    public JdbcDatabaseContainer getContainer() {
        return container;
    }

    public void initContainer(String username, String password) {
        container = (JdbcDatabaseContainer) newJdbcDatabaseContainer()
            .withEnv(Collections.singletonMap("ACCEPT_EULA", "Y"))
            .withTmpFs(Collections.singletonMap("/testtmpfs", "rw"));
            
        if(supportsDatabaseName()) {
            container.withDatabaseName("high-performance-java-persistence");
        }
        
        if(supportsCredentials()) {
            container.withUsername(username).withPassword(password);
        }
        
        container.start();
    }

    protected JdbcDatabaseContainer newJdbcDatabaseContainer() {
        throw new UnsupportedOperationException(
            String.format(
                "The [%s] database was not configured to use Testcontainers!",
                name()
            )
        );
    }

    protected boolean supportsDatabaseName() {
        return true;
    }

    protected boolean supportsCredentials() {
        return true;
    }
}

The DB-specific DataSource instantiation logic is defined in the DataSourceProvider implementations:

public class MySQLDataSourceProvider 
		extends AbstractContainerDataSourceProvider {

    @Override
    public String hibernateDialect() {
        return "org.hibernate.dialect.MySQL8Dialect";
    }

    @Override
    protected String defaultJdbcUrl() {
        return "jdbc:mysql://localhost/high_performance_java_persistence?useSSL=false";
    }

    protected DataSource newDataSource() {
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setURL(url());
        dataSource.setUser(username());
        dataSource.setPassword(password());

        return dataSource;
    }

    @Override
    public String username() {
        return "mysql";
    }

    @Override
    public String password() {
        return "admin";
    }

    @Override
    public Database database() {
        return Database.MYSQL;
    }
}

That’s it!

If I stop my local MySQL database and run a MySQL test, I can see that a MySQL container is started on demand:

DEBUG [main]: ?.0] - Trying to create JDBC connection using com.mysql.cj.jdbc.Driver to 
    jdbc:mysql://localhost:57127/high-performance-java-persistence?useSSL=false&allowPublicKeyRetrieval=true 
with properties: {
    user=mysql, 
    password=admin
}
INFO  [main]: ?.0] - Container is started (
    JDBC URL: jdbc:mysql://localhost:57127/high-performance-java-persistence
)
INFO  [main]: ?.0] - Container mysql:8.0 started in PT34.213S

Cool, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Adding support for Testcontainers in the Hypersistence Utils project was an extremely easy process. However, the advantages are huge since now anyone can run the existing test cases without no prior database configuration.

And while you can use Docker to manage your containers, doing it programmatically using Testcontainers is much more convenient. In my case, Testcontainers allows me to start a Docker container only when there’s no available local database to use.

Insert details about how the information is going to be processed

DOWNLOAD NOW