Generating a schema from the JOOQ Metamodel in a different SQLDialect

I'm considering using jOOQ as a typesafe, DB dialect independent SQL generator, as I need to create a small database DDL script and a large number of INSERT- and UPDATE-statements on the fly from within a larger application. We then provide the file(s) as a download.

I've created the initial DB schema manually in MySQL and generated jOOQ classes to work with it. Currently, JOOQ is used to generate a bunch of inserts into an empty MySql schema). This works fine.

I would like to offer the user a selection of different DB schemes to export to: sqlite, MySQL/Maria, Postgres, and if the license is commercial, then maybe Access, Oracle etc. e.g.

    List<DSLContext> dbdialects = new ArrayList<DSLContext>();
    dbdialects.add(using(SQLDialect.MYSQL));
    dbdialects.add(using(SQLDialect.FIREBIRD));
    dbdialects.add(using(SQLDialect.SQLITE));
    //other dialects including MS Access available via commercial license.

    for (DSLContext create : dbdialects) {

        String dialect = create.configuration().dialect().toString();
        String sql = create.insertInto(EMPLOYEES)
                .set(EMPLOYEES.FIRST_NAME, fn)
                .set(EMPLOYEES.LAST_NAME, ln)
                .getSQL(ParamType.INLINED);
        System.out.println(dialect + "\t" + sql);
    }

Of course, I can recreate the base schema by hand for each RDBMS, but this is tedious, error prone and it seems superfluous: is there not a clever way to use the generated Metamodel from jOOQ that I created with my MySQL DB schema to generate the schema for the other SQL dialects?

Solution

This functionality has been made available through the DSLContext.ddl() methods, which were added in jOOQ 3.8 with #3160.

In order to generate and execute DDL queries (CREATE TABLE, ALTER TABLE ADD CONSTRAINT, etc.), just pass the table name or schema name to the ddl() method as such:

Queries queries = DSL.using(configuration).ddl(EMPLOYEES);
for (Query query : queries)
    query.execute();