Populate a database with TestContainers in a SpringBoot integration test

Question

I am testing TestContainers and I would like to know how to populate a database executing a .sql file to create the structure and add some rows.

How to do it?

@Rule
public PostgreSQLContainer postgres = new PostgreSQLContainer();

Answer 1

The easiest way is to use JdbcDatabaseContainer::withInitScript

Advantage of this solution is that script is run before Spring Application Context loads (at least when it is in a static block) and the code is quite simple.

Example:

static {
    postgreSQLContainer = new PostgreSQLContainer("postgres:9.6.8")
            .withDatabaseName("integration-tests-db")
            .withUsername("sa")
            .withPassword("sa");
    postgreSQLContainer
            .withInitScript("some/location/on/classpath/someScript.sql");
    postgreSQLContainer.start();
}

JdbcDatabaseContainer is superclass of PostgreSQLContainer so this solution should work not only for postgres, but also for other containers.

If you want to run multiple scripts you can do it in a similar manner

Example:

static {
    postgreSQLContainer = new PostgreSQLContainer("postgres:9.6.8")
            .withDatabaseName("integration-tests-db")
            .withUsername("sa")
            .withPassword("sa");
    postgreSQLContainer.start();

    var containerDelegate = new JdbcDatabaseDelegate(postgreSQLContainer, "");

     ScriptUtils.runInitScript(containerDelegate, "some/location/on/classpath/someScriptFirst.sql");
     ScriptUtils.runInitScript(containerDelegate, "some/location/on/classpath/someScriptSecond.sql");
     ScriptUtils.runInitScript(containerDelegate, "ssome/location/on/classpath/someScriptThird.sql");
}

There are also other options

Spring Test @Sql annotation

@SpringBootTest
@Sql(scripts = ["some/location/on/classpath/someScriptFirst.sql", "some/location/on/classpath/someScriptSecond.sql"])
public class SomeTest {
    //...
}

ResourceDatabasePopulator from jdbc.datasource.init or r2dbc.connection.init when using JDBC or R2DBC consecutively

class DbInitializer {
    private static boolean initialized = false;

    @Autowired
    void initializeDb(ConnectionFactory connectionFactory) {
        if (!initialized) {
            ResourceLoader resourceLoader = new DefaultResourceLoader();
            Resource[] scripts = new Resource[] {
                    resourceLoader.getResource("classpath:some/location/on/classpath/someScriptFirst.sql"),
                    resourceLoader.getResource("classpath:some/location/on/classpath/someScriptSecond.sql"),
                    resourceLoader.getResource("classpath:some/location/on/classpath/someScriptThird.sql")
            };
            new ResourceDatabasePopulator(scripts).populate(connectionFactory).block();
            initialized = true;
        }
    }
}

@SpringBootTest
@Import(DbInitializer.class)
public class SomeTest {
    //...
}

Init script in database URI when using JDBC

It is mentioned in offical Testcontainers documentation:
https://www.testcontainers.org/modules/databases/jdbc/

Classpath file:
jdbc:tc:postgresql:9.6.8:///databasename?TC_INITSCRIPT=somepath/init_mysql.sql

File that is not on classpath, but its path is relative to the working directory, which will usually be the project root:
jdbc:tc:postgresql:9.6.8:///databasename?TC_INITSCRIPT=file:src/main/resources/init_mysql.sql

Using an init function:
jdbc:tc:postgresql:9.6.8:///databasename?TC_INITFUNCTION=org.testcontainers.jdbc.JDBCDriverTest::sampleInitFunction

package org.testcontainers.jdbc;

public class JDBCDriverTest {
    public static void sampleInitFunction(Connection connection) throws SQLException {
        // e.g. run schema setup or Flyway/liquibase/etc DB migrations here...
    }
    ...
}

Answer 2

Spring framework provides the ability to execute SQL scripts for test suites or for a test unit. For example:

@Test
@Sql({"/test-schema.sql", "/test-user-data.sql"}) 
public void userTest {
   // execute code that relies on the test schema and test data
}

Here's the documentation.

You can also take a look at Spring Test DBUnit which provides annotations to populate your database for a test unit. It uses XML dataset files.

@Test
@DatabaseSetup(value = "insert.xml")
@DatabaseTearDown(value = "insert.xml")
public void testInsert() throws Exception {
     // Inserts "insert.xml" before test execution
     // Remove "insert.xml" after test execution
}

Also, you can take a look at DbSetup, which provides a java fluent DSL to populate your database.