[FIXED] How can I create a schema with JOOQ?

Issue

I am trying to create a Vertica table with JOOQ 3.5.x:

Connection connection = create();
DSLContext dslContext = DSL.using(connection);
Field<String> myColumn = DSL.field("my_column", SQLDataType.VARCHAR);
Table table = DSL.tableByName("my_schema", "my_table");
dslContext.createTable(table)
        .column(myColumn, myColumn.getDataType())
        .execute();

This fails on Schema "my_schema" does not exist.

I can solve it with:

dslContext.execute("create schema if not exists my_schema");

But is there a more elegant way to create a schema with JOOQ?

Solution

Currently JOOQ covers just a subset of the possible DDL statements that can be executed against a server and schema management is not yet included so you have to drop back to plan old SQL.

If you need to do a lot of DDL work you should start to look at the latest version 3.8 as this has extend the capabilities to include

• DEFAULT column values in CREATE TABLE or ALTER TABLE statements
• IF EXISTS in DROP statements
• IF NOT EXISTS in CREATE statements
• ALTER TABLE .. { RENAME | RENAME COLUMN | RENAME CONSTRAINT } statements

Version 3.6 added

• ALTER TABLE ADD CONSTRAINT (with UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK)
• ALTER TABLE DROP CONSTRAINT
• CREATE TEMPORARY TABLE

Answered By - Roger Thomas
Answer Checked By - David Goodson (JavaFixing Volunteer)