Integrating database access and management with Flyway and jOOQ into a Kotlin Ktor app

Feb 28, 2021 Kotlin Ktor jOOQ database Gradle Docker Postgres
Share on:

One of my goals when I started a learning project a couple of years ago was to get more hands-on experience with Kotlin co-routines, so I explored app frameworks that natively supported co-routines. I landed on Ktor, an open-source framework from JetBrains (creators of Kotlin) that supports co-routines throughout the framework. I've really enjoyed working with Ktor, and the lightweight flexibility of the framework allowed me to easily integrate libraries like jOOQ and Flyway.

This post aims to show how I incorporated several libraries and tools to manage schemas and interact with the database from Ktor:

We'll use a simplified bank account as an example throughout this post.

Managing database schema with Flyway

First off, we need a way to manage the database schema - creating tables, etc. I've used Flyway for the past few years as a simple and flexible way to manage database schemas alongside source code, and I wanted to incorporate it into my Ktor projects as well.

I like to split out the database schema and generated jOOQ code into their own Gradle subproject to cleanly separate the mechanics of managing them from the Gradle subproject with the app code.

Database and server Gradle sub projects

First, we'll define the initial database tables in a SQL file under src/main/resources/db/migration in the database subproject. Flyway uses a version file naming convention to know which order to apply migrations in and keep track of which migrations have been applied. In our case we'll name the file starting with V1__ to indicate this is the first migration file.

src/main/resources/db/migration/V1__InitialAccountAndEntryTables.sql

 1CREATE TABLE account_holder(
 2   id         BIGSERIAL PRIMARY KEY,
 3   user_name  TEXT UNIQUE NOT NULL,
 4   first_name TEXT,
 5   last_name  TEXT
 6);
 7CREATE INDEX account_holder_user_name_idx on account_holder(user_name);
 8
 9CREATE TYPE bank_account_type AS ENUM ('CHECKING', 'SAVINGS');
10CREATE TABLE bank_account(
11 id                BIGSERIAL         PRIMARY KEY,
12 account_holder_id BIGINT            REFERENCES account_holder ON DELETE CASCADE,
13 account_type      bank_account_type NOT NULL
14);
15CREATE INDEX bank_account_account_holder_id_idx on bank_account(account_holder_id);
16
17CREATE TYPE transaction_type AS ENUM ('DEPOSIT', 'WITHDRAWAL');
18CREATE TABLE account_transaction(
19    id               BIGSERIAL        PRIMARY KEY,
20    bank_account_id  BIGINT           REFERENCES bank_account ON DELETE CASCADE,
21    amount           NUMERIC(12, 2)   NOT NULL,
22    transaction_type transaction_type NOT NULL
23);
24CREATE INDEX account_transaction_bank_account_id_idx on account_transaction(bank_account_id);

Now we'll add the Flyway dependency to our database subproject:

build.gradle

1dependencies {
2    api "org.flywaydb:flyway-core:7.5.1"
3}

jOOQ code generator

Next we'll configure the jOOQ code generator to generate type-safe database access code to work with our database schema.

The configuration for the jOOQ generator is a bit lengthy - one of the reasons why I like to separate it out into the database subproject to avoid the main app's Gradle file growing too large.

We'll need a running database with the schema applied to point the jOOQ generator at, so I stitch together a couple Gradle plugins to automate this process:

  1. Use the Docker Compose and a Gradle Docker Compose plugin to spin up a fresh Postgres database
  2. Next use the Flyway Gradle plugin to run the migrations against the database to initialize the schema
  3. Run the jOOQ code generator using the jOOQ Gradle plugin
  4. Finally shut down the Postgres instance using the Gradle Docker Compose plugin

build.gradle

 1plugins {
 2    id "com.avast.gradle.docker-compose" version "0.14.0"
 3    id 'nu.studer.jooq' version "5.2.1"
 4    id "org.flywaydb.flyway" version "7.5.1"
 5}
 6
 7dependencies {
 8    api "org.flywaydb:flyway-core:7.5.1"
 9    api "com.zaxxer:HikariCP:3.4.5"
10    api "org.jooq:jooq:$jooq_version"
11
12    api "org.postgresql:postgresql:$postgres_driver_version"
13
14    jooqGenerator "org.postgresql:postgresql:$postgres_driver_version"
15}
16
17flyway {
18    url = 'jdbc:postgresql://localhost:5433/bankaccountdb'
19    user = 'testuser'
20    password = 'testpass'
21}
22
23jooq {
24    version = jooq_version
25
26    configurations {
27        main {
28            generateSchemaSourceOnCompilation = false
29            generationTool {
30                jdbc {
31                    driver = 'org.postgresql.Driver'
32                    url = 'jdbc:postgresql://localhost:5433/bankaccountdb'
33                    user = 'testuser'
34                    password = 'testpass'
35                }
36                generator {
37                    name = 'org.jooq.codegen.KotlinGenerator'
38                    strategy {
39                        name = 'org.jooq.codegen.DefaultGeneratorStrategy'
40                    }
41                    database {
42                        name = 'org.jooq.meta.postgres.PostgresDatabase'
43                        excludes = "flyway_schema_history"
44                        inputSchema = "public"
45                    }
46                    generate {
47                        relations = true
48                        deprecated = false
49                        records = true
50                        pojos = true
51                        pojosEqualsAndHashCode = true
52                        daos = true
53                    }
54                    target {
55                        packageName = 'example.bank.database.generated'
56                        directory = 'src/main/kotlin'
57                    }
58                }
59            }
60        }
61    }
62}
63
64generateJooq.inputs.dir("${projectDir}/src/main/resources/db/migration")
65generateJooq.outputs.cacheIf { true }
66
67flywayMigrate.dependsOn composeUp
68generateJooq.dependsOn flywayMigrate
69generateJooq.finalizedBy composeDownForced

A couple of key call-outs from the jOOQ Gradle config:

  1. I prefer to check in the generated jOOQ code as the schema changes much less frequently than I modify app code so that way I don't have to re-generate the jOOQ code every time I build the project. By default, the jOOQ Gradle plugin will run the generator as part of source compilation, but I'm disabling that by setting generateSchemaSourceOnCompilation = false in the jOOQ Gradle plugin config.
  2. jOOQ recently added support for generating Kotlin code, and that is configured with generator { name = 'org.jooq.codegen.KotlinGenerator' } above.
  3. This sequence at the end of the build.gradle file controls the Postgres instance startup and shutdown:
1flywayMigrate.dependsOn composeUp
2generateJooq.dependsOn flywayMigrate
3generateJooq.finalizedBy composeDownForced

With this setup, running the Gradle generateJooq task will automatically run all those operations and we'll end up with a powerful set of generated type-safe database access code.

jOOQ generated code

Using Flyway and jOOQ with Ktor

Next we'll configure and use Flyway and jOOQ in our Ktor application.

First, we need to create Ktor config parameters for the datasource properties (URL, credentials, etc.). One option for Ktor configuration is using an HOCON file application.conf:

src/main/resources/application.conf

 1ktor {
 2    deployment {
 3        port = 8080
 4        port = ${?PORT}
 5    }
 6
 7    application {
 8        modules = [example.bank.ApplicationKt.module]
 9    }
10
11    datasource {
12        username = "testuser"
13        username = ${?DB_USERNAME}
14        password = "testpass"
15        password = ${?DB_PASSWORD}
16        jdbcUrl = "jdbc:postgresql://localhost:5433/bankaccountdb"
17        jdbcUrl = ${?DB_URL}
18        schema = "public"
19        schema = ${?DB_SCHEMA}
20    }
21}

The first definition of each parameter is the default value while the second definition allows overriding the default by setting the specified environment variable - convenient for per-environment settings like these datasource properties.

Next, we'll set up the following on application startup in the main Ktor application file:

  1. Read the datasource config parameters in the Ktor app code
  2. Initialize the datasource - Hakari for this app
  3. Run the Flyway migrations
  4. Create the jOOQ DSLContext - the key jOOQ object
 1fun Application.module(): ModuleContext {
 2    val applicationConfig = environment.config
 3
 4    val dataSourceConfig = DataSourceConfig.createDataSourceConfig(applicationConfig)
 5    val dataSource = DataSourceConfig.createDataSource(dataSourceConfig)
 6    DataSourceConfig.flywayMigrate(dataSource, dataSourceConfig)
 7    val dslContext = DataSourceConfig.createDSLContext(dataSource, dataSourceConfig)
 8    
 9    val bankAccountRepository = BankAccountRepository(dslContext)
10    ...
11}

And the code that is performing those operations:

 1package example.bank.database
 2
 3import com.zaxxer.hikari.HikariConfig
 4import com.zaxxer.hikari.HikariDataSource
 5import io.ktor.config.ApplicationConfig
 6import io.ktor.util.KtorExperimentalAPI
 7import org.flywaydb.core.Flyway
 8import org.jooq.DSLContext
 9import org.jooq.SQLDialect
10import org.jooq.conf.MappedSchema
11import org.jooq.conf.RenderMapping
12import org.jooq.conf.Settings
13import org.jooq.impl.DSL
14import javax.sql.DataSource
15
16@KtorExperimentalAPI
17data class DataSourceConfig(val jdbcUrl: String, val username: String, val password: String, val schema: String) {
18    companion object {
19        fun createDataSourceConfig(applicationConfig: ApplicationConfig) = DataSourceConfig(
20            applicationConfig.property("ktor.datasource.jdbcUrl").getString(),
21            applicationConfig.property("ktor.datasource.username").getString(),
22            applicationConfig.property("ktor.datasource.password").getString(),
23            applicationConfig.property("ktor.datasource.schema").getString()
24        )
25
26        fun createDataSource(dataSourceConfig: DataSourceConfig): HikariDataSource {
27            val hikariConfig = HikariConfig()
28            hikariConfig.username = dataSourceConfig.username
29            hikariConfig.password = dataSourceConfig.password
30            hikariConfig.jdbcUrl = dataSourceConfig.jdbcUrl
31            hikariConfig.schema = dataSourceConfig.schema
32            hikariConfig.maximumPoolSize = 10
33
34            val dataSource = HikariDataSource(hikariConfig)
35
36            return dataSource
37        }
38
39        fun flywayMigrate(dataSource: DataSource, dataSourceConfig: DataSourceConfig) {
40            val flyway = Flyway.configure()
41                .dataSource(dataSource)
42                .schemas(dataSourceConfig.schema)
43                .load()
44
45            flyway.migrate()
46        }
47
48        fun createDSLContext(dataSource: DataSource, dataSourceConfig: DataSourceConfig): DSLContext {
49            val settings = Settings()
50                .withRenderMapping(
51                    RenderMapping()
52                        .withSchemata(
53                            MappedSchema().withInput("public")
54                                .withOutput(dataSourceConfig.schema)
55                        )
56                )
57
58            return DSL.using(dataSource, SQLDialect.POSTGRES, settings)
59        }
60    }
61}

Now we have a database with the migrations applied and the jOOQ root object DSLContext that we can use in our database repository code.

For example, fetching bank accounts by account holder username:

 1package example.bank.account
 2
 3import example.bank.database.generated.enums.BankAccountType
 4import example.bank.database.generated.tables.BankAccount.Companion.BANK_ACCOUNT
 5import example.bank.database.generated.tables.pojos.AccountHolder
 6import example.bank.database.generated.tables.pojos.BankAccount
 7import example.bank.database.generated.tables.references.ACCOUNT_HOLDER
 8import kotlinx.coroutines.Dispatchers
 9import kotlinx.coroutines.withContext
10import org.jooq.DSLContext
11import java.math.BigDecimal
12
13class BankAccountRepository(private val dslContext: DSLContext) {
14
15    suspend fun fetchBankAccounts(accountHolderUserName: String): List<BankAccount> =
16        withContext(Dispatchers.IO) {
17            dslContext.select(BANK_ACCOUNT.fields().toList())
18                .from(BANK_ACCOUNT)
19                .innerJoin(ACCOUNT_HOLDER).on(BANK_ACCOUNT.ACCOUNT_HOLDER_ID.eq(ACCOUNT_HOLDER.ID))
20                .where(ACCOUNT_HOLDER.USER_NAME.eq(accountHolderUserName))
21                .fetchInto(BankAccount::class.java)
22        }
23}

Testing

We'll walk through setting up a Ktor application test case that sets up data in the database, accesses an endpoint to fetch bank accounts for a user, then verifies the returned response.

First, let's set up helper code that will create our application under test and configure the database properties (JDBC URL, credentials, etc.):

 1package example.bank
 2
 3import com.fasterxml.jackson.databind.DeserializationFeature
 4import com.fasterxml.jackson.databind.ObjectMapper
 5import com.fasterxml.jackson.databind.PropertyNamingStrategy
 6import com.fasterxml.jackson.databind.SerializationFeature
 7import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule
 8import com.fasterxml.jackson.module.kotlin.registerKotlinModule
 9import com.zaxxer.hikari.HikariDataSource
10import io.ktor.application.*
11import io.ktor.config.*
12import io.ktor.util.*
13import org.jooq.DSLContext
14import org.junit.jupiter.api.AfterEach
15
16@KtorExperimentalAPI
17open class ApplicationTestCase {
18    val objectMapper: ObjectMapper = ObjectMapper()
19        .registerKotlinModule()
20        .registerModule(JavaTimeModule())
21        .setPropertyNamingStrategy(PropertyNamingStrategy.SNAKE_CASE)
22        .disable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS)
23        .configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false)
24
25    lateinit var dataSource: HikariDataSource
26    lateinit var dslContext: DSLContext
27
28    lateinit var bankAccountTestData: BankAccountTestData
29
30    fun createTestApplication(application: Application) {
31
32        (application.environment.config as MapApplicationConfig).apply {
33            put("ktor.datasource.username", System.getenv("DB_USERNAME") ?: "testuser")
34            put("ktor.datasource.password", System.getenv("DB_PASSWORD") ?: "testpass")
35            put("ktor.datasource.jdbcUrl", System.getenv("DB_URL") ?: "jdbc:postgresql://localhost:5433/bankaccountdb")
36            put("ktor.datasource.schema", "public")
37        }
38
39        val appContext = application.module()
40        dataSource = appContext.dataSource
41        dslContext = appContext.dslContext
42
43        bankAccountTestData = BankAccountTestData(dslContext)
44    }
45
46    @AfterEach
47    fun closeDataSource() {
48        dataSource.close()
49    }
50}

First fetching the database values from environment variables allows overriding them in different environments, such as CI with GitHub Actions - where the JDBC URL may be different.

Now we can write up a test case that fetches a bank account by account holder username and verifies the response data:

 1package example.bank.account
 2
 3import com.fasterxml.jackson.module.kotlin.readValue
 4import example.bank.ApplicationTestCase
 5import example.bank.database.generated.enums.BankAccountType
 6import example.bank.database.generated.tables.pojos.BankAccount
 7import io.ktor.http.*
 8import io.ktor.server.testing.*
 9import io.ktor.util.*
10import org.apache.commons.lang3.RandomStringUtils
11import org.junit.jupiter.api.Test
12import strikt.api.expectThat
13import strikt.assertions.all
14import strikt.assertions.hasSize
15import strikt.assertions.isEqualTo
16import kotlin.test.assertNotNull
17
18@KtorExperimentalAPI
19class BankAccountApplicationTest : ApplicationTestCase() {
20
21    @Test
22    fun `should list user bank accounts`() {
23        val userName = RandomStringUtils.randomAlphanumeric(16)
24
25        withTestApplication(::createTestApplication) {
26            handleRequest(HttpMethod.Get, "/accounts/$userName") {
27                val accountHolder = bankAccountTestData.createAccountHolder(userName)
28                bankAccountTestData.createBankAccount(BankAccountType.CHECKING, accountHolder)
29            }.apply {
30                expectThat(response.status()).isEqualTo(HttpStatusCode.OK)
31
32                val responseBody = response.content
33                assertNotNull(responseBody)
34
35                val bankAccounts: List<BankAccount> = objectMapper.readValue(responseBody)
36                expectThat(bankAccounts).hasSize(1).all {
37                    get { accountType }.isEqualTo(BankAccountType.CHECKING)
38                }
39            }
40        }
41    }
42}

Conclusion

Thanks to the lightweight nature of Ktor and the modularity of database libraries such as Flyway and jOOQ, it is straightforward to integrate these tools together for controlled database migrations with Flyway and powerful, type-safe database access code with jOOQ.

Example code

The full code for this example project is at https://github.com/craigatk/bank-account-example