skip to content

Where can jOOQ's code generator read a schema from, a live database, DDLDatabase or LiquibaseDatabase, and what does each trade off?

level: middleimportance: must knowfreq 48%

answer

  1. real catalog versus simulation
  2. in-memory H2 underneath
  3. parser gaps and ignore fences
  4. one source deprecated in 3.21

basics

~20 s

jOOQ's code generator either reverse-engineers a running database over JDBC or simulates one: DDLDatabase parses SQL scripts and LiquibaseDatabase applies a changelog, both on in-memory H2. Simulation needs no server but can miss vendor-specific DDL.

solid answer

~50 s

A **live database** source names a vendor class such as `org.jooq.meta.postgres.PostgresDatabase`, or lets jOOQ infer it from the JDBC URL, and reads the real catalog: the most faithful output, but CI needs a reachable database with the migrations applied. **`DDLDatabase`** (in `jooq-meta-extensions`) loads the files in its `scripts` property, parses them with jOOQ's parser, translates them to H2 and runs them on an in-memory H2 database before reverse-engineering it; no server is needed, but DDL the parser or the H2 simulation cannot handle fails the run unless you fence it with `[jooq ignore start]` and `[jooq ignore stop]` comments. **`LiquibaseDatabase`** applies a Liquibase changelog to in-memory H2 the same way, and is deprecated since jOOQ 3.21.0 (#19353) after Liquibase 5.x changed its license; jOOQ now points you at running the migration against a real database.

code

kotlin · 30 lines
kotlin
plugins {
    id("org.jooq.jooq-codegen-gradle") version "3.21.9"
}

dependencies {
    jooqCodegen("org.jooq:jooq-meta-extensions:3.21.9")
}

jooq {
    configuration {
        generator {
            database {
                name = "org.jooq.meta.extensions.ddl.DDLDatabase"
                properties {
                    property {
                        key = "scripts"
                        value = "src/main/resources/db/migration/*.sql"
                    }
                    property {
                        key = "sort"
                        value = "flyway"
                    }
                }
            }
            target {
                packageName = "com.example.payments.db"
            }
        }
    }
}

go deeper

for a junior

Recall the two families: a live database read over JDBC, or files replayed into a temporary database by DDLDatabase or LiquibaseDatabase.

for a middle

Explain DDLDatabase's pipeline: jOOQ's parser, translation to H2, execution on in-memory H2, then reverse-engineering, and what the ignore comments do.

for a senior

Diagnose output that differs from production: a simulated type, a fenced statement that removed a table, or a live source not built from the migrations.

for a principal

Frame sources as fidelity against infrastructure, and treat LiquibaseDatabase's 3.21 deprecation as a reason not to build new pipelines on it.

## What a schema source is jOOQ's code generator never reads your migration files or entity classes directly. It works from an `org.jooq.meta.Database`: an object that can list schemas, tables, columns, keys and routines. The `<database><name>` element of the generator configuration picks the implementation, and that choice is the **schema source**. Everything the generator writes, from column types to record base classes, is only as accurate as what that source reports. There are two families: - **Reverse-engineering a live database**: a vendor-specific class reads the catalog of a running server over JDBC. - **Interpreting files**: an extension class builds a temporary database from files and then reverse-engineers that. ## Live database over JDBC The configuration supplies a `<jdbc>` block (driver, URL, user, password) and a vendor class such as `org.jooq.meta.postgres.PostgresDatabase`, `org.jooq.meta.mysql.MySQLDatabase` or `org.jooq.meta.h2.H2Database`. If `<name>` is omitted, `GenerationTool` infers the class from the JDBC URL. `<inputSchema>` limits the run to one schema; left empty, every available schema is generated. Strengths and costs: - **Highest fidelity**: native enum types, vendor column types, check constraints and routines come out exactly as the production engine reports them. - **Needs infrastructure**: a database of the right engine and version must be reachable during the build, with the migrations already applied. - **Easy to point at the wrong place**: a developer's hand-altered local database or a shared staging server produces code that differs from what the migrations define. ## DDLDatabase: parse, translate, simulate `org.jooq.meta.extensions.ddl.DDLDatabase` lives in the `jooq-meta-extensions` artifact, which must be on the generator's classpath. Its pipeline, read from its source: 1. Load the files matched by the `scripts` property (or inline SQL in the `sql` property), ordered by the `sort` property: `semantic` by default, which is version-aware, or `alphanumeric`, `flyway` or `none`. 2. Parse each file with jOOQ's own SQL parser, ignoring unknown functions and storage clauses. 3. Execute the parsed statements on a fresh **in-memory H2** database, translated to H2's dialect. 4. Reverse-engineer that H2 database as if it were a live source. No server is needed, and the input is the same migration scripts production runs. The cost is fidelity: anything the parser does not understand, or that the H2 simulation cannot express, fails the run. The escape hatch is a pair of comments the parser skips between, on by default (`parseIgnoreComments`): ```sql -- [jooq ignore start] -- a vendor-only statement the simulation may not handle goes here -- [jooq ignore stop] CREATE TABLE payment ( id BIGINT NOT NULL PRIMARY KEY, status VARCHAR(20) NOT NULL ); ``` Every fenced statement is invisible to the generator, so a fence around a `CREATE TABLE` removes that table from the generated code. The `defaultNameCase` property (default `as_is`) can lower- or upper-case unquoted names so the generated names match how the production engine folds them. ## LiquibaseDatabase and its deprecation `org.jooq.meta.extensions.liquibase.LiquibaseDatabase` (artifact `jooq-meta-extensions-liquibase`) hands the changelog named in `scripts` to Liquibase, which applies it to the same kind of in-memory H2 database, and then reverse-engineers the result. It drops Liquibase's own changelog and changelog-lock tables from the output unless its `includeLiquibaseTables` property is `true`. In jOOQ 3.21 the class carries `@Deprecated`, citing issue #19353 and release 3.21.0 and pointing to a containerised real database instead, and every run logs a `DEPRECATION` warning explaining that Liquibase 5.x's license change is the reason. It still works, but it is no longer a source to adopt. ## Comparing the three | Source | Needs a server | Fidelity to production | Status in 3.21 | |---|---|---|---| | Vendor class over JDBC | yes | exact, if migrated by the real scripts | supported | | `DDLDatabase` | no | limited by jOOQ's parser and H2 | supported | | `LiquibaseDatabase` | no | limited by H2 | deprecated since 3.21.0 | ## Failure signs worth recognising - A DDLDatabase run that stops with a parse error names the statement; fence it or simplify it, but remember the fenced object disappears. - A column generated as a generic type where production has a vendor type usually means the simulation could not express that type. - Generated code that differs between two developers points to a live source that is not built from the migrations.

  • How does jOOQ's DDLDatabase order several migration files, and what goes wrong if the order is off?
    Its `sort` property decides, defaulting to `semantic`, a version-aware order in which `V2` comes before `V10`. `alphanumeric` sorts as plain text, `flyway` uses Flyway-compatible ordering, and `none` leaves order to the file system, which may not be deterministic. A wrong order replays an `ALTER` before its `CREATE` and fails the run.
  • What must be on the code generator's classpath to use DDLDatabase?
    The `jooq-meta-extensions` artifact, at the same version as the generator. With the Gradle plugin it goes in the `jooqCodegen` configuration, which the plugin creates for drivers and extensions; with `jooq-codegen-maven` it is a plugin-level `<dependency>`. A live source needs the JDBC driver on that same classpath instead.

Rehearsing a play on a stand-in stage: you learn the blocking without booking the theatre, but any scene that depends on the real stage's trapdoors cannot be rehearsed there. DDLDatabase and LiquibaseDatabase rehearse your schema on in-memory H2, not on the production engine.

  • DDLDatabase connects to the configured production database to read the schema.
  • DDLDatabase accepts any vendor statement because it runs your real engine.
  • LiquibaseDatabase is the recommended source for Liquibase projects on jOOQ 3.21.
  • A live-database source works in CI with no database reachable.
  • Fencing a statement with ignore comments keeps its table in the generated code.

Assumes jOOQ 3.21: LiquibaseDatabase is deprecated since 3.21.0 (#19353), so the advice for it differs from 3.20 and earlier.