jooqr2dbc

Can't get a JOOQ DSL context


I am trying to build a minimal example using Postgres, JOOQ and Flyway.

I expect a context to be generated, when I pass a connection factory configured for r2dbc:

@Configuration
class R2DBC(
    @Value("\${spring.r2dbc.url}") val url: String,
    @Value("\${spring.r2dbc.username}") val username: String,
    @Value("\${spring.r2dbc.password}") val password: String
) {
    @Bean
    fun r2dbcConnectionFactory(): ConnectionFactory = ConnectionFactories.get(
        ConnectionFactoryOptions
            .parse(url)
            .mutate()
            .option(ConnectionFactoryOptions.USER, username)
            .option(ConnectionFactoryOptions.PASSWORD, password)
            .build()
    )
}

@SpringBootApplication
class CoRoutineJooqApplication {

    @Bean
        fun jooqDSLContext(connectionFactory: ConnectionFactory): DSLContext =
        DSL.using(connectionFactory)

}

fun main(args: Array<String>) {
    runApplication<CoRoutineJooqApplication>(*args)
}

But when I inject it

@Component
class ApplicationRunner(private val ctx: DSLContext) : ApplicationRunner {

    override fun run(args: ApplicationArguments?) {
        printTableInformation()
    }

    private fun printTableInformation(): Unit = ctx.meta().tables.forEach { table -> println("Table: ${table.name}") }
}

the context has no connection provider and I get the exception "Caused by: org.jooq.exception.DetachedException: No JDBC Connection provided by ConnectionProvider".

I am also using Flyway and JOOQ code generation via JDBC.

@Configuration
class FlywayConfig(private val env: Environment) {

    @Bean(initMethod = "migrate")
    fun flyway(): Flyway = Flyway(
            Flyway.configure()
                .baselineOnMigrate(false)
                .dataSource(
                    env.getRequiredProperty("spring.flyway.url"),
                    env.getRequiredProperty("spring.flyway.user"),
                    env.getRequiredProperty("spring.flyway.password")
                )
        )
}

These are my application properties:

spring:
  jooq:
    sql-dialect: postgres
  r2dbc:
    url: r2dbc:postgresql://localhost:5432/demo
    username: postgres
    password: postgres
  flyway:
    url: jdbc:postgresql://localhost:5432/demo
    user: ${spring.r2dbc.username}
    password: ${spring.r2dbc.password}
    enabled: true
    schemas: demo

and my build file

import nu.studer.gradle.jooq.JooqGenerate
import org.jooq.meta.jaxb.*
import org.jooq.meta.jaxb.Logging

group = "demo.eisenbarth"
version = "0.0.1-SNAPSHOT"
description = "Demo Reactive Spring Boot With Kotlin Coroutines And JOOQ"


object Versions {
    const val KOTLINX = "1.7.3"
    const val JOOQ = "3.18.6"
    const val SPRING ="3.1.4"
    const val R2DBC_POSTGRES = "1.0.2.RELEASE"
}

// We configure JDBC and R2DBC since Jooq code generation and flyway rely on JDBC and Spring can only autoconfigure one.
data class JdbcDataSource(
    val driver: String = System.getenv("driver") ?: project.property("driver") as String,
    val jdbcUrl: String = System.getenv("jdbc_url") ?: project.property("jdbc_url") as String,
    val username: String = System.getenv("username") ?: project.property("username") as String,
    val password: String = System.getenv("password") ?: project.property("password") as String
)
val dbConfig = JdbcDataSource()

repositories {
    mavenLocal()
    mavenCentral()
}

plugins {
    id("org.springframework.boot") version "3.1.4"
    id("io.spring.dependency-management") version "1.1.3"
    id("nu.studer.jooq") version "8.2.1"
    id("org.flywaydb.flyway") version "9.22.1"
    kotlin("jvm") version "1.9.10"
    kotlin("plugin.spring") version "1.9.10"
}

dependencies {

    // coroutines
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core-jvm")
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-reactor") // Reactor/Coroutine-Bridge
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-slf4j") // MDC
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-reactive") // NEEDED ???

    // marshalling
    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    implementation("com.fasterxml.jackson.core:jackson-databind")
    implementation("org.jetbrains.kotlin:kotlin-reflect")

    // web
    implementation("org.springframework.boot:spring-boot-starter-webflux")
    implementation("org.springframework.boot:spring-boot-starter-validation")

    // database
    implementation("org.springframework.boot:spring-boot-starter-data-r2dbc")
    implementation("org.postgresql:r2dbc-postgresql:${Versions.R2DBC_POSTGRES}") // reactive postgres driver
    // implementation("org.springframework:spring-jdbc")
    implementation("org.postgresql:postgresql") // flyway and jooq code generation uses jdbc

    // flyway
    implementation("org.flywaydb:flyway-core")

    // jooq
    jooqGenerator("org.postgresql:postgresql")
    implementation("org.jooq:jooq:${Versions.JOOQ}}")
    implementation("org.jooq:jooq-kotlin")
    implementation("org.jooq:jooq-kotlin-coroutines")
    // What about: spring-boot-starter-jooq?
    // Don't need it, because we use studer's plugin to configure JOOQ also it depends on spring-boot-starter-jdbc.

    // test
    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("io.projectreactor:reactor-test")
}

flyway {
    println("Configuring Flyway")
    driver = dbConfig.driver
    url = dbConfig.jdbcUrl
    user = dbConfig.username
    password = dbConfig.password
    locations = arrayOf("filesystem:./src/main/resources/db/migration")
    placeholders = mapOf("schema_name" to "demo")
}

// Configure jooq plugin. Make sure this comes before configuring tasks, otherwise "generateJooq" will be unknown.
jooq {
    version.set("${Versions.JOOQ}")
    edition.set(nu.studer.gradle.jooq.JooqEdition.OSS)
    configurations {
        create(name = "main") {
            generateSchemaSourceOnCompilation.set(true)
            jooqConfiguration.apply {
                logging = Logging.WARN
                jdbc.apply {
                    driver = dbConfig.driver
                    url = dbConfig.jdbcUrl
                    user = dbConfig.username
                    password = dbConfig.password
                }
                generator.apply {
                    name = "org.jooq.codegen.KotlinGenerator"
                    database.apply {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "demo"
                        includes = ".*"
                        isIncludeIndexes = false
                        // Excludes match before includes, i.e. excludes have a higher priority.
                        excludes = "flyway.*"
                        schemaVersionProvider = "SELECT MAX(\"version\") FROM \"flyway_schema_history\""
                        // Give enough time to jOOQ to trigger the queries needed for generating sources
                        logSlowQueriesAfterSeconds = 20
                    }

                    generate.apply {
                        withPojosAsKotlinDataClasses(true)
                        //withSpringAnnotations(true)
                        //withSpringDao(true)
                        //withValidationAnnotations(true)
                        //withKotlinNotNullRecordAttributes(true)
                        //withImplicitJoinPathsAsKotlinProperties(true)
                        //withUdts(true)
                    }
                    // Prefix generated Pojos with 'Jooq'
                    strategy.withMatchers(
                        Matchers()
                            .withTables(
                                arrayOf(
                                    MatchersTableType()
                                        .withPojoClass(
                                            MatcherRule()
                                                .withExpression("Jooq_$0")
                                                .withTransform(MatcherTransformType.PASCAL)
                                        )
                                ).toList()
                            )
                    )

                    target.apply {
                        packageName = "jooq.generated"
                        directory = "build/generated-sources"
                    }
                }
            }
        }
    }
}

// There's only one module / one KotlinCompile task, so we don't need to configure all tasks via the type.
tasks {
    compileKotlin {
        kotlinOptions {
            freeCompilerArgs = listOf("-Xjsr305=strict")
            jvmTarget = "17"
        }
    }
    test {
        useJUnitPlatform()
    }
    // Configure jOOQ code generation task such that it only executes when something that potentially affects code generation has changed.
    named<JooqGenerate>("generateJooq") {

        // Ensure database schema has been prepared by Flyway before generating the jOOQ sources.
        dependsOn("flywayMigrate")

        // Declare Flyway migration scripts as inputs on the jOOQ task.
        inputs.files(fileTree("${rootDir}/src/main/resources/db/migration"))
            .withPropertyName("migrations")
            .withPathSensitivity(PathSensitivity.RELATIVE)

        // Make jOOQ task participate in incremental builds and build caching.
        allInputsDeclared.set(true)
        outputs.cacheIf { true }

    }
}

I've tried to follow the example provided by kamilgregorczyk in this thread, but I don't know what I am missing here, please help.


Solution

  • The jOOQ Meta API is based on JDBC, not R2DBC, and you're not injecting your JDBC connection into your DSLContext, so it can't run queries on the JDBC DatabaseMetaData.

    To solve this, create a DSLContext using your JDBC Connection, instead.