Using jOOQ as schema generator (plain sql as output)

Question

I'm trying to use jOOQ as a schema generator to generate DDL statements, and later to generate SQL insert statements.

The underlying data is coming from static CSV files and I basically want to write a static SQL script which includes statements to create the schema and insert the data; merely a dump file.

For example I have this Groovy class containing the table deifnition:


class ContinentTable extends CustomTable<Record> {

    static ContinentTable CONTINENT = new ContinentTable()

    static UniqueKey<Record> CONTINENT_PK = createUniqueKey(CONTINENT, name("continent_pk"), [CONTINENT.ID] as TableField[], true)

    TableField<Record, String> ID = createField(name("id"), VARCHAR(255).nullable(false))
    TableField<Record, String> CODE = createField(name("code"), VARCHAR(2).nullable(false))
    TableField<Record, String> NAME = createField(name("name"), VARCHAR(255).nullable(false))
    TableField<Record, String> DEMONYM = createField(name("demonym"), VARCHAR(255).nullable(false))

    private ContinentTable() {
        super(name("continent"))
    }

    @Override
    Class<? extends Record> getRecordType() {
        return Record
    }

    @Override
    UniqueKey<Record> getPrimaryKey() {
        return CONTINENT_PK
    }
}

It contains the fields and the PK, but when I use jOOQ to create the SQL statement with:

dsl.createTable(ContinentTable.CONTINENT)

it only generates:

create table "continent";

without the columns. Off course I could do something like:

dsl.createTable(ContinentTable.CONTINENT).columns(ContinentTable.CONTINENT.fields())

but it seems a bit of unnecessary as I already pass in the table??

It gets more complex if I want to add primary keys and possible foreign keys, indexes etc.

Is it expected behaviour that these are all not added when calling dsl.createTable(ContinentTable.CONTINENT)?

Note: I'm creating the ContinentTable manually as I'm not using codegen as I have no source database.

personally I don't like this approach. Why don't you use Flyway and generate the jOOQ model from the DDL statements? — Simon Martinelli, Mar 31 '21 at 05:58
The thing is I don't have an actual database. Just want to generate a dump.sql with create table statements and inserts. — Marcel Overdijk, Mar 31 '21 at 07:11
But indeed generating Jooq classes from DDL is also an option for my use case: https://www.jooq.org/doc/latest/manual/code-generation/codegen-ddl/ — Marcel Overdijk, Mar 31 '21 at 07:28
This is my default use case in new projects. I always use Flyway (you could also use Liquibase) for database migration. — Simon Martinelli, Mar 31 '21 at 07:41
In my case I'm not interested in migrations, it is a fixed (historical) schema that won't change. But the schema could be included in a schema.sql as input foor jOOQ codegen. — Marcel Overdijk, Mar 31 '21 at 08:15
@SimonMartinelli: Even if you're using Flyway, you could be using its programmatic migrations with this part of jOOQ's API. The ideas are complementary, not competing with each other. — Lukas Eder, Mar 31 '21 at 18:31

score 2 · Answer 1 · answered Mar 31 '21 at 07:15

I was using jOOQ's dsl.createTable(ContinentTable.CONTINENT).columns(ContinentTable.CONTINENT.fields())

But jOOQ also has a ddl method which does exactly what I want.

def queries = create.ddl(ContinentTable.CONTINENT)
queries.each { query ->
    println query}
}

which does generate the table including columns, constraints etc.

Using jOOQ as schema generator (plain sql as output)

1 Answers1