Initialize testcontainers postgresql database with flyway

With Testcontainers library, you can use a docker container providing services such as a database for your test. With Flyway library, you can track the schema changes of your database and ensure that those changes are applied on all its instances. How can you initialize your test database provided by Testcontainers with the schema described in Flyway ? In this post, we will see how to initialize a postgresql database in a docker container with Flyway scripts.

Libraries

First, we look at our build.sbt, and more specifically to the list of libraries. We need to load flyway library, postgresql jdbc driver, scalatest testing library and Testcontainers scalatest glu and postgresql container module. So we get the following libraries:

libraryDependencies ++= Seq(
  "org.flywaydb" % "flyway-core" % "7.7.0",
  "org.postgresql" % "postgresql" % "42.2.19",
  "org.scalatest" %% "scalatest" % "3.2.5" % Test,
  "com.dimafeng" %% "testcontainers-scala-scalatest" % "0.39.3" % Test,
  "com.dimafeng" %% "testcontainers-scala-postgresql" % "0.39.3" % Test
)

Initialize postgresql container

In our test class, we have to initialize the postgresql container. As explained in Testcontainers scala README, we make our test class extend the trait ForAllTestContainer, and we override the container field with a PostgreSQLContainer.

import com.dimafeng.testcontainers.{ForAllTestContainer, PostgreSQLContainer}
import org.scalatest.BeforeAndAfterAll
import org.scalatest.funsuite.AnyFunSuite
import org.testcontainers.utility.DockerImageName

class PostgresDatabaseWithFlywayTest extends AnyFunSuite
  with ForAllTestContainer
  with BeforeAndAfterAll {

  override val container: PostgreSQLContainer = new PostgreSQLContainer(
    databaseName = Some("my_database"),
    pgUsername = Some("my_user"),
    pgPassword = Some("my_password"),
    dockerImageNameOverride = Some(DockerImageName.parse("postgres:12"))
  )

As we need to use fields specific to PostgreSQL container, we force the type of overridden field container to PostgreSQLContainer. We set different databaseName, pgUsername and pgPassword. We also set a different container image in order to have a more recent postgreSQL version by setting dockerImageNameOverride.

Run Flyway migration scripts

We will run flyway migration scripts to initialize database in overridden beforeAll method of our test class, as below:

import org.flywaydb.core.Flyway
import org.flywaydb.core.api.Location
import org.flywaydb.core.api.configuration.ClassicConfiguration

override def beforeAll(): Unit = {
  super.beforeAll()
  container.start()

  val configuration = new ClassicConfiguration()
  configuration.setDataSource(container.container.getJdbcUrl, container.container.getUsername, container.container.getPassword)
  configuration.setLocations(new Location("classpath:db/migration"))

  val flyway = new Flyway(configuration)
  flyway.migrate()
}

In this beforeAll method, we first call the super method, in case of others beforeAll implementations and, we start the container to ensure that the container is started when we try to apply flyway migration.

Then, we set up the Flyway configuration. To do so, we need to set up the data source which is the database on which apply the migration, and the location where to find the Flyway migration scripts. To set up the data source, we need the jdbc url, the postgresql user and password. We retrieve them straight from container by calling container.container.getJdbcUrl, container.container.getUsername and container.container.getPassword. For the location, as our Flyway migration scripts are in src/main/resources directory, we use a relative classpath path. You can also use full path using filesystem url as explained here.

Finally, we run the migration by creating a Flyway instance and call its method migrate.

Wrap Up: whole test class

So we get the following whole test class:

import com.dimafeng.testcontainers.{ForAllTestContainer, PostgreSQLContainer}
import org.flywaydb.core.Flyway
import org.flywaydb.core.api.Location
import org.flywaydb.core.api.configuration.ClassicConfiguration
import org.scalatest.BeforeAndAfterAll
import org.scalatest.funsuite.AnyFunSuite
import org.testcontainers.utility.DockerImageName

import java.sql.{Connection, DriverManager, Statement}

class PostgresDatabaseWithFlywayTest extends AnyFunSuite with ForAllTestContainer with BeforeAndAfterAll {

  override val container: PostgreSQLContainer = new PostgreSQLContainer(
    databaseName = Some("my_database"),
    pgUsername = Some("my_user"),
    pgPassword = Some("my_password"),
    dockerImageNameOverride = Some(DockerImageName.parse("postgres:12"))
  )

  override def beforeAll(): Unit = {
    super.beforeAll()
    container.start()
    val configuration = new ClassicConfiguration()
    configuration.setDataSource(container.container.getJdbcUrl, container.container.getUsername, container.container.getPassword)
    configuration.setLocations(new Location("classpath:db/migration"))
    val flyway = new Flyway(configuration)
    flyway.migrate()
  }

  test("should load database") {
    val connection: Connection = DriverManager.getConnection(container.container.getJdbcUrl, container.container.getUsername, container.container.getPassword)
    val statement: Statement = connection.createStatement

    val result = statement.executeQuery("SELECT value FROM my_table")
    while (result.next) {
      val value: String = result.getString("value")
      System.out.println(value)
    }
  }

}

The complete example can be found on github: https://github.com/vincentdoba/blog-examples/tree/master/20210314-test-containers-with-flyway