Using Micronaut Data, JDBC, R2DBC, TestContainers, and Flyway with multiple schemas

One of the core open source projects that powers exdividend.app is Micronaut. Micronaut is a full stack JVM based framework that utilizes AOT rather than reflection. It has a huge selection of diffferent libraries in its Micronaut Data module for accessing databases.

JDBC, R2DBC, and Flyway

exdividend.app utilizes a combination of libraries from Micronaut Data for its data management.

• R2DBC is a reactive relational database driver that brings reactive programming APIs and connectivity to databases
• JDBI is a declarative SQL framework that utilizes the standard JDBC interface for connecting to SQL databases
• Flyway is a database migration tool that helps manage the database schemas
• Postgres is the database of choice for exdividend.app for all data storage

The Micronaut Data repository has thin wrappers around the first three items so things neatly integrate into the design patterns of the framework. They can co-exist together and you can utilize both JDBC and R2DBC drivers at the same time.

Currently I am testing and iterating with both drivers to see how the performance really compares in production.

Testing with TestContainers

TestContainers is another critical project that enables exdividend.app to have an extensive test suite that runs against Postgres instances in containers.

Each test suite spins up an isolated container, runs the Flyway migration steps and then executes the tests.

Micronaut has excellent support for this built-in. By adding the right dependencies and then defining your application-test.yml you can get up and running with integration tests quickly.

# src/test/resources/application-test.yml
datasources:
  app:
    url: jdbc:tc:postgresql:12:///postgres?currentSchema=app
    driverClassName: org.testcontainers.jdbc.ContainerDatabaseDriver
  market:
    url: jdbc:tc:postgresql:12:///postgres?currentSchema=market
    driverClassName: org.testcontainers.jdbc.ContainerDatabaseDriver


flyway:
  datasources:
    default:
      enabled: true
      schemas:
        - app
        - market
      create-schemas: true

Here we have setup two JDBC connections and have defined that Flyway migrations shold be enabled for these datasources. The URLs are a special TestContainers format that conforms to the JDBC URL format but will indicate that the database will be run in a new container managed by TestContainers.

Multiple Schemas, R2DBC and Flyway

In the example above we defined two schemas within our Postgres instance. Lets now add R2DBC connectivity.

# src/test/resources/application-test.yml
r2dbc:
  datasources:
    app:
      url: r2dbc:tc:postgresql:///postgres?TC_IMAGE_TAG=13.5
      dialect: postgres
      options:
        currentSchema: app
    market:
      url: r2dbc:tc:postgresql:///postgres?TC_IMAGE_TAG=13.5
      dialect: postgres
      options:
        currentSchema: market

This new configuration looks quite similar but the configuration has two significant problems.

• The R2DBC instance and JDBC instances are not in sync - TestContainers will create a different database for each one.
• Flyway is only aware of the JDBC instances - and currently has no support for connecting to databases using the R2DBC driver.

One Potential Solution

A potentoal solution in this case is to create and manage the database instance in code rather than via the Micronaut application context.

For my testing needs I created a base class that database tests specifically can inherit from.

// Setup a standard postgres image
class PostgresContainer(image: String = "postgres:13.5-alpine3.15") : PostgreSQLContainer<PostgresContainer>(image)

// Creates a container with the exposed port
fun postgresContainer(): PostgresContainer {
    return PostgresContainer()
        .withExposedPorts(5432)
}

// Helper class to get a jdbi instance for the current database
fun PostgresContainer.jdbi(): Jdbi {
    val jdbi = Jdbi.create(this.jdbcUrl, this.username, this.password)
    jdbi.installPlugins()
    return jdbi
}

open class PostgreSqlTestWithMigrations {

    @Container
    val postgres = postgresContainer()

    private val config = ClassicConfiguration()

    @BeforeEach
    fun migrate() {
        postgres.start()

        config.setDataSource(postgres.jdbcUrl, postgres.username, postgres.password)
        config.schemas = arrayOf("market", "app")

        Flyway(config).migrate()
    }

    @AfterEach
    fun clean() {
        postgres.start()
        Flyway(config).clean()
    }

    //Create a properties map that replaces application-test.yml for R2DBC configuration
    //This allows R2DBC and JDBC to share the same TestContainer database instance above
    fun r2dbcProps(): Map<String, Any> {
        return r2dbcMarketProps().plus(rd2bcAppProps())
    }

    private fun r2dbcMarketProps(): Map<String, Any> {
        return mapOf(
            "r2dbc.datasources.market.url" to "r2dbc:postgresql://${postgres.host}:${postgres.firstMappedPort}/?currentSchema=market",
            "r2dbc.datasources.market.username" to postgres.username,
            "r2dbc.datasources.market.password" to postgres.password,
            "r2dbc.datasources.market.dialect" to Dialect.POSTGRES,
        )
    }

    private fun rd2bcAppProps(): Map<String, Any> {
        return mapOf(
            "r2dbc.datasources.app.url" to "r2dbc:postgresql://${postgres.host}:${postgres.firstMappedPort}/?currentSchema=app",
            "r2dbc.datasources.app.username" to postgres.username,
            "r2dbc.datasources.app.password" to postgres.password,
            "r2dbc.datasources.app.dialect" to Dialect.POSTGRES,
        )
    }
}

A test can then be written using the JDBI interface this way.

class JdbiTest : PostgreSqlTestWithMigrations() {

    @Test
    fun sanityTest() {

        val jdbi = postgres.jdbi()

        jdbi.useHandle<Nothing> { handle ->
            val result = handle.createQuery("SELECT pid from pg_stat_activity").mapTo(Int::class.java)
            assertNotNull(result)
        }
    }
}

A test can also then be written using the Micronaut Data repository interface with the R2DBC this way.

//src/main/StatsRepo.kt

// Note the @TransactionalAdvice("market") selects the proper transaction handler
// Note the @R2dbcRepository(value = "market") selects the proper datasource

@TransactionalAdvice("market")
@R2dbcRepository(dialect = Dialect.POSTGRES, value = "market")
interface StatsRepo : CoroutineCrudRepository<CompanyStatsRow, UUID> { }


// src/test/StatsRepoTests.kt
class StatsRepoTests : PostgreSqlTestWithMigrations() {

    @Inject
    lateinit var context: ApplicationContext

    @BeforeEach
    fun getProperties() {
        context = ApplicationContext.run(r2dbcProps())
    }

    private fun getRepo(): StatsRepo {
        return context.getBean(StatsRepo::class.java)
    }

    @Test
    fun count() = runBlocking {
        val repo = getRepo()

        assertEquals(0, repo.count())
        repo.upsert(fakeData())
        assertEquals(1, repo.count())
    }
}

With this you can now test out multiple database drivers and write integration tests against multiple schemas.