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?

Lukas Eder · Accepted Answer · 2021-06-24 14:49:17Z

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

edited Jun 24, 2021 at 14:49

answered May 11, 2021 at 16:26

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert SQL enum with jOOQ in PostgreSQL database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related