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

When using Spring Boot, I find it easiest to use the JDBC URL support of TestContainers.

You can create a application-integration-test.properties file (typically in src/test/resources with something like this:

spring.datasource.url=jdbc:tc:postgresql://localhost/myappdb
spring.datasource.driverClassName=org.testcontainers.jdbc.ContainerDatabaseDriver
spring.datasource.username=user
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=none
# This line is only needed if you are using flyway for database migrations
# and not using the default location of `db/migration`
spring.flyway.locations=classpath:db/migration/postgresql

Note the :tc part in the JDBC url.

You can now write a unit test like this:

@RunWith(SpringRunner.class)
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) @ActiveProfiles("integration-test")
public class UserRepositoryIntegrationTest {
      @Autowired
      private MyObjectRepository repository;
      @PersistenceContext
      private EntityManager entityManager;
      @Autowired
      private JdbcTemplate template;

@Test
public void test() {
  // use your Spring Data repository, or the EntityManager or the JdbcTemplate to run your SQL and populate your database.
}

Note: This is explained in Practical Guide to Building an API Back End with Spring Boot, chapter 7 in more detail (Disclaimer: I am the author of the book)

Answer 3

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.

Answer 4

There is one more option, if you are defining Postgres container manually without fancy testcontainers JDBC url stuff, not related to Spring directly. Postgres image allows to link directory containing sql scripts to container volume and auto-executes them.

GenericContainer pgDb = new PostgreSQLContainer("postgres:9.4-alpine")
  .withFileSystemBind("migrations/sqls", "/docker-entrypoint-initdb.d",
    BindMode.READ_ONLY)

Also if you need something in runtime, you can always do pgDb.execInContainer("psql ....").

Answer 5

You can use DatabaseRider, which uses DBUnit behind the scenes, for populating test database and TestContainers as the test datasource. Following is a sample test, full source code is available on github here.

@RunWith(SpringRunner.class)
@SpringBootTest
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) @ActiveProfiles("integration-test")
@DBRider //enables database rider in spring tests 
@DBUnit(caseInsensitiveStrategy = Orthography.LOWERCASE) //https://stackoverflow.com/questions/43111996/why-postgresql-does-not-like-uppercase-table-names
public class SpringBootDBUnitIt {

    private static final PostgreSQLContainer postgres = new PostgreSQLContainer(); //creates the database for all tests on this file 

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private UserRepository userRepository;


    @BeforeClass
    public static void setupContainer() {
        postgres.start();
    }

    @AfterClass
    public static void shutdown() {
        postgres.stop();
    }


    @Test
    @DataSet("users.yml")
    public void shouldListUsers() throws Exception {
        assertThat(userRepository).isNotNull();
        assertThat(userRepository.count()).isEqualTo(3);
        assertThat(userRepository.findByEmail("springboot@gmail.com")).isEqualTo(new User(3));
    }

    @Test
    @DataSet("users.yml") //users table will be cleaned before the test because default seeding strategy
    @ExpectedDataSet("expected_users.yml")
    public void shouldDeleteUser() throws Exception {
        assertThat(userRepository).isNotNull();
        assertThat(userRepository.count()).isEqualTo(3);
        userRepository.delete(userRepository.findOne(2L));
        entityManager.flush();//can't SpringBoot autoconfigure flushmode as commit/always
        //assertThat(userRepository.count()).isEqualTo(2); //assertion is made by @ExpectedDataset
    }

    @Test
    @DataSet(cleanBefore = true)//as we didn't declared a dataset DBUnit wont clear the table
    @ExpectedDataSet("user.yml")
    public void shouldInsertUser() throws Exception {
        assertThat(userRepository).isNotNull();
        assertThat(userRepository.count()).isEqualTo(0);
        userRepository.save(new User("newUser@gmail.com", "new user"));
        entityManager.flush();//can't SpringBoot autoconfigure flushmode as commit/always
        //assertThat(userRepository.count()).isEqualTo(1); //assertion is made by @ExpectedDataset
    }

}

src/test/resources/application-integration-test.properties

spring.datasource.url=jdbc:tc:postgresql://localhost/test
spring.datasource.driverClassName=org.testcontainers.jdbc.ContainerDatabaseDriver
spring.datasource.username=test
spring.datasource.password=test
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL9Dialect
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
#spring.jpa.properties.org.hibernate.flushMode=ALWAYS #doesn't take effect 
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

And finally the datasets:

src/test/resources/datasets/users.yml

users:
  - ID: 1
    EMAIL: "dbunit@gmail.com"
    NAME: "dbunit"
  - ID: 2
    EMAIL: "rmpestano@gmail.com"
    NAME: "rmpestano"
  - ID: 3
    EMAIL: "springboot@gmail.com"
    NAME: "springboot"

src/test/resources/datasets/expected_users.yml

users:
  - ID: 1
    EMAIL: "dbunit@gmail.com"
    NAME: "dbunit"
  - ID: 3
    EMAIL: "springboot@gmail.com"
    NAME: "springboot"

src/test/resources/datasets/user.yml

users:
  - ID: "regex:\\d+"
    EMAIL: "newUser@gmail.com"
    NAME: "new user"

Answer 6

After some reviews, I think that it is interesting to review the examples from Spring Data JDBC which use Test Containers:

Note: Use Java 8

git clone https://github.com/spring-projects/spring-data-jdbc.git
mvn clean install -Pall-dbs

I will create a simple project adding some ideas about previous project referenced.

Juan Antonio