SpringBoot, test containers to test queries sent to external DB

My Spring app has its own DB for persistence. The same app needs to send ad-hoc queries to external databases. Queries are provided by users.

• App takes SQL query provided by user
• App takes external database type (postgres / oracle / whatever jdbc)
• App submits adhoc query in runtime to external DB
• App returns result as json to user

Is there any way to utilize spring test containers in order to test this functionaly? My goal is:

• Write tests for every supported DB
• each test starts test container with supported DB (some subset of these I suppose: https://www.testcontainers.org/modules/databases/)
• each test uploads sample data to container DB
• each test runs set of "must work" queries against it.

I see many examples where App itself is tested against test containers, but can I just start container w/o plugging it as App persistence DB?

can I just start container w/o plugging it as App persistence DB?

Yes, this is perfectly possible. Testcontainers in itself has nothing to do with Spring or Spring Boot.

What you would do is:

• pick the container you want to use (different container for different databases
• instantiate the container
• start it up
• construct a DataSource from it
• Use that DataSource for your tests.

Spring Data JDBC does exactly that to run tests against various databases. I add the class doing that for MySQL in the end. It is a Spring application context configuration, but you could put that in a JUnit before method, a JUnit 4 rule or a JUnit 5 extension or just a normal method that you call at the start of your test.

@Configuration
@Profile("mysql")
class MySqlDataSourceConfiguration extends DataSourceConfiguration {

    private static final MySQLContainer MYSQL_CONTAINER = new MySQLContainer().withConfigurationOverride("");

    static {
        MYSQL_CONTAINER.start();
    }

    /*
     * (non-Javadoc)
     * @see org.springframework.data.jdbc.testing.DataSourceConfiguration#createDataSource()
     */
    @Override
    protected DataSource createDataSource() {

        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setUrl(MYSQL_CONTAINER.getJdbcUrl());
        dataSource.setUser(MYSQL_CONTAINER.getUsername());
        dataSource.setPassword(MYSQL_CONTAINER.getPassword());
        dataSource.setDatabaseName(MYSQL_CONTAINER.getDatabaseName());

        return dataSource;
    }

    @PostConstruct
    public void initDatabase() throws SQLException, ScriptException {
        ScriptUtils.executeSqlScript(createDataSource().getConnection(), null, "DROP DATABASE test;CREATE DATABASE test;");
    }
}