如何基于Flyway迁移生成jOOQ代码？解决部署构建失败问题

我有一个使用jOOQ的Kotlin Spring Boot应用，数据库采用PostgreSQL，且已配置Flyway迁移。开发环境下生成jOOQ代码无问题，但通过GitHub Actions和Docker Compose部署到服务器时，项目构建失败，原因是jOOQ尝试连接尚未启动的数据库以生成所需代码。

我的Gradle构建配置如下：

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile
import org.jooq.meta.jaxb.ForcedType
import org.jooq.meta.jaxb.Logging
import org.jooq.meta.jaxb.Property

val environment = System.getenv()

plugins {
    id("org.springframework.boot") version "2.7.5"
    id("io.spring.dependency-management") version "1.0.15.RELEASE"
    kotlin("jvm") version "1.8.0"
    kotlin("plugin.spring") version "1.8.0"

    id("nu.studer.jooq") version "7.0"
}

group = "osu.salat23"
version = "ALPHA"
java.sourceCompatibility = JavaVersion.VERSION_17

repositories {
    mavenCentral()
}

dependencies {
    implementation("org.springframework.boot:spring-boot-starter-data-jpa")
    implementation("org.springframework.boot:spring-boot-starter-security")
    implementation("org.springframework.boot:spring-boot-starter-web")
    implementation("org.springframework.boot:spring-boot-starter-actuator")


    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    implementation("org.flywaydb:flyway-core")
    implementation("org.jetbrains.kotlin:kotlin-reflect")
    implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.6.4")
    implementation("com.squareup.okhttp3:okhttp:4.10.0")
    implementation("com.github.yvasyliev:java-vk-bots-longpoll-api:3.5.3")
    implementation("org.telegram:telegrambots-spring-boot-starter:6.3.0")
    implementation("com.microsoft.playwright:driver-bundle:1.28.1")
    implementation("com.microsoft.playwright:playwright:1.28.1")

    runtimeOnly("org.postgresql:postgresql")

    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("org.springframework.security:spring-security-test")
    testImplementation("com.squareup.okhttp3:mockwebserver:4.10.0")
    testImplementation("junit:junit:4.13.2")

    jooqGenerator("org.postgresql:postgresql:42.5.1")
    jooqGenerator("jakarta.xml.bind:jakarta.xml.bind-api:3.0.1")
}
configurations {
    all {
        exclude("org.apache.logging.log4j", "log4j-slf4j-impl")
    }
}


tasks.withType<KotlinCompile> {
    kotlinOptions {
        freeCompilerArgs = listOf("-Xjsr305=strict")
        jvmTarget = "17"
    }
}

tasks.withType<Test> {
    useJUnitPlatform()
}

jooq {
    version.set("3.16.0")  // default (can be omitted)
    edition.set(nu.studer.gradle.jooq.JooqEdition.OSS)  // default (can be omitted)

    configurations {
        create("main") {  // name of the jOOQ configuration
            generateSchemaSourceOnCompilation.set(false)  // default (can be omitted)

            jooqConfiguration.apply {
                logging = Logging.WARN
                jdbc.apply {
                    driver = "org.postgresql.Driver"
                    url = environment["DATABASE_URL"] ?: "jdbc:postgresql://localhost:5432/postgres"
                    user = environment["DATABASE_USER"] ?: "postgres"
                    password = environment["DATABASE_PASSWORD"] ?: "postgres"
                    properties.add(Property().apply {
                        key = "ssl"
                        value = "false"
                    })
                }
                generator.apply {
                    name = "org.jooq.codegen.DefaultGenerator"
                    database.apply {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "public"
                        forcedTypes.addAll(listOf(
                            ForcedType().apply {
                                name = "varchar"
                                includeExpression = ".*"
                                includeTypes = "JSONB?"
                            },
                            ForcedType().apply {
                                name = "varchar"
                                includeExpression = ".*"
                                includeTypes = "INET"
                            }
                        ))
                    }
                    generate.apply {
                        isDeprecated = false
                        isRecords = true
                        isImmutablePojos = true
                        isFluentSetters = true
                    }
                    target.apply {
                        packageName = "osu.salat23.circler"
                        directory = "src/generated-src/jooq/main"  // default (can be omitted)
                    }
                    strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
                }
            }
        }
    }
}

是否可以不从数据库，而是基于我的Flyway迁移生成jOOQ代码？我已查阅官方文档，但相关信息极少且无Gradle Kotlin DSL代码示例，因此在此求助。

可以通过Testcontainers+Flyway的组合，在构建阶段自动启动临时PostgreSQL容器，执行Flyway迁移后再生成jOOQ代码，完全脱离对外部运行中数据库的依赖。以下是修改后的Gradle Kotlin DSL配置：

1. 添加必要插件 #

在plugins块中加入Flyway和Testcontainers插件：

plugins {
    id("org.springframework.boot") version "2.7.5"
    id("io.spring.dependency-management") version "1.0.15.RELEASE"
    kotlin("jvm") version "1.8.0"
    kotlin("plugin.spring") version "1.8.0"

    id("nu.studer.jooq") version "7.0"
    // 添加Flyway插件
    id("org.flywaydb.flyway") version "9.22.3"
    // 添加Testcontainers插件
    id("org.testcontainers.gradle") version "1.18.3"
}

2. 配置Flyway使用临时PostgreSQL容器 #

让Flyway连接Testcontainers提供的临时PostgreSQL实例，自动执行迁移脚本：

flyway {
    // 使用Testcontainers JDBC驱动，自动启动PostgreSQL 15-alpine容器
    url = "jdbc:tc:postgresql:15-alpine:///testdb?TC_DAEMON=true"
    user = "test"
    password = "test"
    // 指定Flyway迁移脚本的位置（根据你的实际路径调整）
    locations = arrayOf("filesystem:src/main/resources/db/migration")
}

3. 修改jOOQ配置，依赖Flyway迁移并连接临时容器 #

调整jOOQ的JDBC配置指向临时容器，同时让代码生成任务依赖Flyway迁移任务：

jooq {
    version.set("3.16.0")
    edition.set(nu.studer.gradle.jooq.JooqEdition.OSS)

    configurations {
        create("main") {
            generateSchemaSourceOnCompilation.set(false)

            jooqConfiguration.apply {
                logging = Logging.WARN
                jdbc.apply {
                    // 使用Testcontainers的JDBC驱动
                    driver = "org.testcontainers.jdbc.ContainerDatabaseDriver"
                    // 复用Flyway的临时数据库连接信息
                    url = flyway.url.get()
                    user = flyway.user.get()
                    password = flyway.password.get()
                }
                // 保留你原有的generator配置不变
                generator.apply {
                    name = "org.jooq.codegen.DefaultGenerator"
                    database.apply {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "public"
                        forcedTypes.addAll(listOf(
                            ForcedType().apply {
                                name = "varchar"
                                includeExpression = ".*"
                                includeTypes = "JSONB?"
                            },
                            ForcedType().apply {
                                name = "varchar"
                                includeExpression = ".*"
                                includeTypes = "INET"
                            }
                        ))
                    }
                    generate.apply {
                        isDeprecated = false
                        isRecords = true
                        isImmutablePojos = true
                        isFluentSetters = true
                    }
                    target.apply {
                        packageName = "osu.salat23.circler"
                        directory = "src/generated-src/jooq/main"
                    }
                    strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
                }
            }
        }
    }
}

// 让jOOQ代码生成任务依赖Flyway迁移任务，确保先完成迁移再生成代码
tasks.named("generateJooq") {
    dependsOn(tasks.flywayMigrate)
}

4. 添加Testcontainers依赖 #

在dependencies块中加入Testcontainers相关依赖，供jOOQ生成器使用：

dependencies {
    // 原有依赖保持不变...

    jooqGenerator("org.postgresql:postgresql:42.5.1")
    jooqGenerator("jakarta.xml.bind:jakarta.xml.bind-api:3.0.1")
    // 添加Testcontainers依赖
    jooqGenerator("org.testcontainers:postgresql:1.18.3")
    jooqGenerator("org.testcontainers:jdbc:1.18.3")
}

工作原理 #

1. 执行构建时，Testcontainers会自动拉取并启动指定版本的PostgreSQL容器；
2. Flyway连接该临时容器，执行所有迁移脚本，构建出最新的数据库结构；
3. jOOQ连接这个临时容器，基于迁移后的数据库结构生成代码；
4. 构建完成后，临时容器会自动销毁，无需手动清理。

这种方式既保证了生成的jOOQ代码完全匹配Flyway迁移的结构，又解决了GitHub Actions/Docker Compose部署时数据库未启动的问题，构建过程完全自包含。

内容的提问来源于stack exchange，提问作者salat_