jOOQ: Type-Safe SQL as a Kotlin DSL

jOOQ flips the usual ORM philosophy on its head. Instead of hiding SQL behind objects and hoping the generated queries are sensible, jOOQ embraces SQL as a first-class language and gives you a fluent, type-safe Kotlin DSL that mirrors it almost word for word. You write `dsl.select(...).from(TABLE).where(FIELD.eq(x)).fetch()`, and what you read is exactly the SQL that runs. There is no lazy-loading magic, no hidden N+1 surprises, and no opaque translation layer between your intent and the database. For teams that genuinely care about their SQL, this is liberating: you keep the full expressive power of your dialect while gaining the safety net of a compiler.

The defining feature is code generation. jOOQ connects to your actual database schema (or reads your migrations) at build time and generates Kotlin/Java classes for every table, column, sequence, and routine. A table like `users` becomes a `USERS` object, and each column becomes a strongly typed `Field`, such as `USERS.EMAIL` of type `Field<String?>` and `USERS.AGE` of type `Field<Int?>`. Because these are real objects with real types, your IDE autocompletes column names, refactoring tools rename them safely, and a column that no longer exists in the schema becomes a compile error rather than a runtime exception. The generated code is the bridge between your evolving database and your statically typed Kotlin.

This is why compile-time-checked SQL beats string queries so decisively. A handwritten string like `"SELECT emial FROM users WHERE age > ?"` compiles fine and blows up only when a real request hits it — possibly weeks later, possibly only for certain inputs. With jOOQ, `USERS.EMIAL` simply does not exist, so the build fails the moment you write the typo. The same protection covers type mismatches: you cannot accidentally compare a `String` field to an `Int`, because `USERS.EMAIL.eq(42)` will not type-check. The class of bugs where SQL drifts out of sync with the schema essentially disappears.

Fetching results is where jOOQ stays pragmatic. A bare `fetch()` returns a `Result<Record>` — an ordered, in-memory list of records you can iterate, where each `Record` exposes typed accessors like `record[USERS.EMAIL]`. Records are flexible and great for ad-hoc, projection-shaped queries that do not map cleanly to a single class. When you do have a target type, `fetchInto(UserDto::class.java)` maps each row's columns onto the DTO's fields by name, giving you a clean `List<UserDto>` with no manual unpacking. There are also convenience terminals: `fetchOne()` for at-most-one row (nullable), `fetchSingle()` when exactly one is required, and `fetchAny()` for the first match.

In a Spring Boot 3 application, jOOQ slots in cleanly. The `org.springframework.boot:spring-boot-starter-jooq` starter wires a `DSLContext` bean on top of your existing `DataSource`, and it participates in Spring's `@Transactional` management like any other data-access component. You simply inject `DSLContext` into a repository class and write queries. Because jOOQ executes plain JDBC under the hood, it coexists peacefully with Spring Data JPA in the same project — many teams use JPA for simple CRUD and reach for jOOQ for the gnarly reporting and analytical queries where precise SQL control matters most.

jOOQ's API is fundamentally blocking JDBC, which deserves an honest note in a coroutines-aware codebase. To call a jOOQ query from a `suspend` function without blocking your event loop or request threads, wrap the call in `withContext(Dispatchers.IO)`, which moves the blocking JDBC work onto a thread pool sized for I/O. jOOQ does ship a reactive, non-blocking flavor built on R2DBC, but for the typical Spring MVC service the `Dispatchers.IO` pattern is the simplest correct bridge between blocking SQL and suspending Kotlin.

The DSL composes the same way SQL does, which is the real payoff at scale. Joins (`.join(ORDERS).on(ORDERS.USER_ID.eq(USERS.ID))`), aggregates (`count()`, `sum()`, `groupBy`, `having`), window functions, common table expressions, and `UNION` all read naturally and stay type-checked end to end. Because queries are ordinary Kotlin expressions, you can build them dynamically — appending conditions with `DSL.and`/`DSL.or`, or assembling a list of `Condition` objects from optional filter parameters — without ever resorting to fragile string concatenation. Dynamic SQL becomes just dynamic data.

In short, jOOQ is the tool to reach for when SQL is a feature of your system rather than an implementation detail to abstract away. You trade the upfront setup of a code-generation step for queries that are readable, refactor-safe, and verified against your real schema by the compiler. When your application leans heavily on complex queries, reporting, or database-specific features, that trade is overwhelmingly worth it: you keep the precision of raw SQL while leaving the whole category of typo-and-drift bugs behind.

import org.jooq.DSLContext
import org.springframework.stereotype.Repository
// generated tables: import com.example.db.tables.Users.USERS

data class UserDto(val id: Long, val email: String, val age: Int)

@Repository
class UserRepository(private val dsl: DSLContext) {

    // SELECT id, email, age FROM users WHERE age >= ? ORDER BY email
    fun findAdults(minAge: Int): List<UserDto> =
        dsl.select(USERS.ID, USERS.EMAIL, USERS.AGE)
            .from(USERS)
            .where(USERS.AGE.ge(minAge))   // USERS.AGE.ge("x") would NOT compile
            .orderBy(USERS.EMAIL.asc())
            .fetchInto(UserDto::class.java)

    // fetchOne() returns a nullable single row
    fun findByEmail(email: String): UserDto? =
        dsl.selectFrom(USERS)
            .where(USERS.EMAIL.eq(email))
            .fetchOne()
            ?.into(UserDto::class.java)
}

import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.withContext
import org.jooq.DSLContext
import org.jooq.impl.DSL.count
// import com.example.db.tables.Users.USERS

class StatsService(private val dsl: DSLContext) {

    // suspend wrapper so blocking JDBC runs off the request thread
    suspend fun usersPerDomain(): Map<String, Int> = withContext(Dispatchers.IO) {
        val domain = USERS.EMAIL.substring(USERS.EMAIL.position("@").plus(1))
        dsl.select(domain, count())
            .from(USERS)
            .groupBy(domain)
            .fetch()                       // Result<Record2<String, Int>>
            .associate { record ->
                record.value1() to record.value2()
            }
    }
}

// Illustration of the 'dynamic SQL = dynamic data' idea using plain Kotlin.
// In real jOOQ these predicates would be org.jooq.Condition objects.
data class Filter(val minAge: Int?, val domain: String?)

fun main() {
    data class User(val email: String, val age: Int)
    val users = listOf(
        User("ana@acme.com", 31),
        User("bob@other.io", 19),
        User("cat@acme.com", 42),
    )

    val filter = Filter(minAge = 21, domain = "acme.com")

    // Collect only the predicates that apply, then AND them together.
    val predicates = buildList<(User) -> Boolean> {
        filter.minAge?.let { min -> add { it.age >= min } }
        filter.domain?.let { d -> add { it.email.endsWith("@" + d) } }
    }

    val result = users.filter { user -> predicates.all { it(user) } }
    result.forEach { println(it.email + " / " + it.age) }
}