Insert SQL enum with jOOQ in PostgreSQL database

Question

I'm trying to use jOOQ to insert a SQL enum in my database.

The database migration is setup with Liquibase like:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd">

    <changeSet id="202105031559" author="pk">
        <sql>CREATE TYPE my_type AS ENUM ('TYPE_A', 'TYPE_B')</sql>

        <createTable tableName="a">
            <column name="type" type="my_type" defaultValue='TYPE_A'>
                <constraints nullable="false"/>
            </column>
        </createTable>
        
    </changeSet>

</databaseChangeLog>

The default enum value in the migration works as expected.

Trying to use jOOQ to insert the enum:

var result = dslContext
        .insertInto(A,
                A.TYPE
        )
        .values(
                MyType.TYPE_B
        )
        .execute();

Triggers the error:

org.postgresql.util.PSQLException: ERROR: column "type" is of type my_type but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

My jOOQ configuration looks like this:

@Configuration
public class JooqConfig {

    private final DataSource dataSource;

    public JooqConfig(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    @Bean
    public DataSourceConnectionProvider connectionProvider() {
        return new DataSourceConnectionProvider(new TransactionAwareDataSourceProxy(dataSource));
    }

    @Bean
    public DefaultDSLContext dsl() {
        Settings settings = new Settings()
                .withRenderNameCase(RenderNameCase.LOWER)
                .withRenderQuotedNames(RenderQuotedNames.NEVER)
                .withExecuteLogging(true);
        return new DefaultDSLContext(connectionProvider(), SQLDialect.POSTGRES, settings);
    }

}

My Gradle setup:

jooq {
    version.set(dependencyManagement.importedProperties["jooq.version"])
    edition.set(nu.studer.gradle.jooq.JooqEdition.OSS)

    configurations {
        create("main") {
            jooqConfiguration.apply {
                generator.apply {
                    database.apply {
                        name = "org.jooq.meta.extensions.liquibase.LiquibaseDatabase"
                        withProperties(
                            org.jooq.meta.jaxb.Property()
                                .withKey("scripts")
                                .withValue("src/main/resources/db/changelog/changelog-ddl.xml"),
                            org.jooq.meta.jaxb.Property()
                                .withKey("includeLiquibaseTables")
                                .withValue("false"),
                            org.jooq.meta.jaxb.Property()
                                .withKey("database.liquibaseSchemaName")
                                .withValue("public"),
                            org.jooq.meta.jaxb.Property()
                                .withKey("changeLogParameters.contexts")
                                .withValue("!test"),
                        )
                    }
                }
            }
        }
    }
}

Anyone got any ideas how to insert the enum with jOOQ?

Answer 1

As of jOOQ 3.15, the org.jooq.meta.extensions.liquibase.LiquibaseDatabase translates the DDL statements to H2 and simulates a migration on an in-memory H2 database. This means you can't use vendor specific PostgreSQL features (H2 supports enums, but they have many flaws).

Instead, the recommended approach would be to use testcontainers and an actual PostgreSQL database for code generation. Some ideas on how to do this are documented in this issue: https://github.com/jOOQ/jOOQ/issues/6551 or in this example: https://github.com/jOOQ/jOOQ/tree/main/jOOQ-examples/jOOQ-testcontainers-example