jOOQ setup

AdminUserRepository.kt

package app.sample.api.admin.users

import app.sample.api.Page
import app.sample.api.Pageable
import app.sample.common.extensions.page
import app.sample.common.nowUTC
import app.sample.db.tables.references.PARTNERS
import app.sample.db.tables.references.USERS
import app.sample.domain.User
import app.sample.domain.UserInfo
import app.sample.domain.toUser
import app.sample.domain.toUserInfo
import org.jooq.DSLContext
import org.jooq.Record
import org.springframework.security.crypto.password.PasswordEncoder
import org.springframework.stereotype.Repository
import java.util.*
import java.util.UUID.randomUUID

@Repository
class AdminUserRepository(
    private val dsl: DSLContext,
    private val passwordEncoder: PasswordEncoder,
) {

    fun getUsers(pageable: Pageable): Page<User> {
        val total = dsl.fetchCount(USERS)
        val data = dsl.select(USERS.asterisk())
            .from(USERS)
            .orderBy(USERS.CREATED_AT.desc())
            .page(pageable)
            .fetch(Record::toUser)

        return Page(
            data = data,
            total = total
        )
    }

    fun createUser(request: UserCreateRequest): User {
        val now = nowUTC()
        return dsl.insertInto(USERS)
            .set(USERS.ID, randomUUID())
            .set(USERS.USERNAME, request.username)
            .set(USERS.ENCODED_PASSWORD, passwordEncoder.encode(request.password))
            .set(USERS.ROLES, request.roles.toTypedArray())
            .set(USERS.CREATED_AT, now)
            .set(USERS.UPDATED_AT, now)
            .returningResult(USERS.asterisk())
            .fetchOne()!!
            .let(Record::toUser)
    }

    fun getUser(userId: UUID): User? =
        dsl.select(USERS.asterisk())
            .from(USERS)
            .where(USERS.ID.eq(userId))
            .fetchOne(Record::toUser)

    fun getUserPartners(userId: UUID): List<UserInfo> =
        dsl.select(USERS.ID, USERS.USERNAME)
            .from(PARTNERS)
            .innerJoin(USERS).on(USERS.ID.eq(PARTNERS.TARGET_USER_ID))
            .where(PARTNERS.SOURCE_USER_ID.eq(userId))
            .fetch(Record::toUserInfo)
}

application-dev.yml

spring:
  flyway:
    clean-on-validation-error: true
    locations:
      - classpath:db/migrations
      - classpath:db/fixtures
jooq:
  execute-logging: true
  render-formatted: true
logging:
  level:
    org.jooq.tools.LoggerListener: DEBUG

application-test.yml

spring:
  flyway:
    clean-on-validation-error: true
    locations:
      - classpath:db/migrations
      - classpath:db/fixtures
jooq:
  execute-logging: true
  render-formatted: true
logging:
  level:
    org.jooq.tools.LoggerListener: DEBUG

application.yml

spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    url: ${DATABASE_URL:jdbc:postgresql://127.0.0.1:5432/app}
    username: ${DATABASE_USERNAME:postgres}
    password: ${DATABASE_PASSWORD:postgres}
    hikari:
      connection-init-sql: SET TIME ZONE 'UTC'
  jooq:
    sql-dialect: postgres
  flyway:
    locations:
      - classpath:db/migrations
jooq:
  execute-logging: false
  render-formatted: false

build.gradle.kts

plugins {
    id("nu.studer.jooq") version "5.2"
    id("org.flywaydb.flyway") version "6.5.6"
}

dependencies {
    // Database
    implementation("org.flywaydb:flyway-core")
    implementation("org.springframework.boot:spring-boot-starter-jooq")
    implementation("org.jooq:jooq:3.14.3")
    implementation("org.jooq:jooq-kotlin:3.14.3")
    runtimeOnly("org.postgresql:postgresql")
    jooqGenerator("org.postgresql:postgresql")
}

flyway {
    url = "jdbc:postgresql://127.0.0.1:5432/app"
    user = "postgres"
    password = "postgres"
    cleanOnValidationError = true
    locations = arrayOf(
        "filesystem:src/main/resources/db/migrations",
        "filesystem:src/main/resources/db/fixtures"
    )
}

jooq {
    version.set("3.14.3")

    configurations {
        create("main") {
            generateSchemaSourceOnCompilation.set(true)
            jooqConfiguration.apply {
                logging = org.jooq.meta.jaxb.Logging.WARN
                jdbc.apply {
                    driver = "org.postgresql.Driver"
                    url = "jdbc:postgresql://127.0.0.1:5432/app"
                    user = "postgres"
                    password = "postgres"
                }
                generator.apply {
                    name = "org.jooq.codegen.KotlinGenerator"
                    database.apply {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "public"
                    }
                    generate.apply {
                    }
                    target.apply {
                        packageName = "app.sample"
                    }
                }
            }
        }
    }
}

tasks.named<nu.studer.gradle.jooq.JooqGenerate>("generateJooq") {
    dependsOn(":flywayMigrate")

    inputs.files(fileTree("src/main/resource/db/migrations"), fileTree("src/main/resource/db/fixtures"))
            .withPropertyName("migrations")
            .withPathSensitivity(PathSensitivity.RELATIVE)

    allInputsDeclared.set(true)
    outputs.cacheIf { true }
}

JooqCustomConfiguration.kt

package app.sample

import org.jooq.conf.ExecuteWithoutWhere
import org.jooq.conf.Settings
import org.springframework.boot.context.properties.ConfigurationProperties
import org.springframework.boot.context.properties.ConstructorBinding
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration

@Configuration
class JooqCustomConfiguration(
    private val config: JooqConfiguration,
) {

    @Bean
    fun jooqSettings(): Settings = Settings()
        .withExecuteDeleteWithoutWhere(ExecuteWithoutWhere.THROW)
        .withExecuteUpdateWithoutWhere(ExecuteWithoutWhere.THROW)
        .withExecuteLogging(config.executeLogging)
        .withRenderFormatted(config.renderFormatted)
}

@ConstructorBinding
@ConfigurationProperties("jooq")
data class JooqConfiguration(
    val executeLogging: Boolean,
    val renderFormatted: Boolean,
)