Jooq + Flyway Kotlin Gradle Setup

build.gradle.kts

plugins {
    kotlin("jvm")
    alias(libs.plugins.jooq)
    alias(libs.plugins.flyway)
}

buildscript {
    dependencies {
        classpath(libs.jdbc.sqlite)
    }
}

val dbUrl = "jdbc:sqlite:${buildDir.resolve("reference.db")}"
val migrationPath = "$projectDir/src/main/resources/db/migration"

flyway {
    url = dbUrl
    locations = arrayOf("filesystem:$migrationPath")
}

sourceSets {
    main {
        kotlin.srcDir(buildDir.resolve("generated-src/jooq/$name"))
    }
}

dependencies {
    jooqGenerator(libs.jdbc.sqlite)
    implementation(libs.flyway.core)
    implementation(libs.jdbc.sqlite)
}

jooq {
    version.set(libs.versions.jooq.get())
    configurations {
        create("main") {
            jooqConfiguration.apply {
                logging = Logging.DEBUG
                jdbc.apply {
                    driver = "org.sqlite.JDBC"
                    url = dbUrl
                }
                generator.apply {
                    name = "org.jooq.codegen.KotlinGenerator"
                    database.apply {
                        name = "org.jooq.meta.sqlite.SQLiteDatabase"
                        excludes = listOf(
                            // Exclude flyway migration tables
                            "flyway_.*",
                        ).joinToString("|")
                    }
                    generate.apply {
                        isDaos = false
                        isRecords = true
                        isImmutablePojos = true
                        isPojosAsKotlinDataClasses = true
                        isKotlinNotNullPojoAttributes = true
                        isKotlinNotNullRecordAttributes = true
                        isKotlinNotNullInterfaceAttributes = true
                    }
                    target.apply {
                        packageName = "" // TODO: Your java package name
                    }
                    strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
                }
            }
        }
    }
}

tasks.named<JooqGenerate>("generateJooq") {
    inputs.dir(migrationPath)
    allInputsDeclared.set(true)
    dependsOn("flywayMigrate")
    doFirst { Thread.sleep(2000) }
}

libs.versions.toml

[versions]
jooq = "3.18.7"

[plugins]
jooq = { id = "nu.studer.jooq", version = "8.2" }
flyway = { id = "org.flywaydb.flyway", version = "10.1.0" }

[libraries]
flyway-core = { module = "org.flywaydb:flyway-core", version = "9.22.3" }
jooq = { module = "org.jooq:jooq", version.ref = "jooq" }
jooq-kotlin = { module = "org.jooq:jooq-kotlin", version.ref = "jooq" }
jooq-coroutines = { module = "org.jooq:jooq-kotlin-coroutines", version.ref = "jooq" }
jooq-test = { module = "org.jooq:jooq-test", version.ref = "jooq" }
jooq-test-kotlin = { module = "org.jooq:jooq-test-kotlin", version.ref = "jooq" }
jdbc-sqlite = { module = "org.xerial:sqlite-jdbc", version.ref = "jdbc_sqlite" }

runMigrations.kt

import org.flywaydb.core.Flyway
import org.flywaydb.core.api.FlywayException
import org.slf4j.Logger

fun runMigrations(connectionString: String, logger: Logger? = null): Boolean {
    val flyway = Flyway.configure()
        .loggers("slf4j")
        .dataSource(connectionString, null, null).load()
    return try {
        flyway.migrate()
        true
    } catch (e: FlywayException) {
        logger?.error("Database migrations failed", e)
        false
    }
}

usage.kt

import org.jooq.SQLDialect
import org.jooq.impl.DSL
import org.sqlite.javax.SQLiteConnectionPoolDataSource

val source = SQLiteConnectionPoolDataSource().apply {
    url = environment.config.property("databaseUrl").getString()
}

val db = DSL.using(source, SQLDialect.SQLITE)

val name = db.select(MY_TABLE.name)
              .from(MY_TABLE)
              .where(MY_TABLE.id.eq(1))
              .fetchOne()

V1__Create_database.sql

# src/main/resources/db/migration/V1__Create_database.sql

CREATE TABLE IF NOT EXISTS my_table
(
    id     INTEGER PRIMARY KEY NOT NULL,
    name   TEXT NOT NULL
);