JOOQ code generation with Liquibase, reserved keyword? (error with "value" column name)

Question

I have a table defined in a liquibase script with a column named "value":

- column:
    name: value
    type: VARCHAR
    constraints:
      - nullable: false

dbms is postgresql

When running the JOOQ codegen with the maven plugin, it runs the liquibase script and I get the following error:

Syntax error in SQL statement "CREATE TABLE PUBLIC.TABLE_NAME (ID BIGINT AUTO_INCREMENT NOT NULL, ... , VALUE[*] VARCHAR NOT NULL)"; expected "identifier";

If I change the column name from "value" to anything else, it works. With JOOQ up to version 3.15, it works.

Any clue on how to handle this? I'd rather not change the name, I have multiple tables with a column named "value" so it's a quite big refactor, and naming wise I feel it's the most appropriate name for what it represents.

Solution

This is already fixed in the newer versions of liquibase, so you can manually specify which LB version to use in the jOOQ codegen:

<plugin>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-codegen-maven</artifactId>
        <version>${jooq.version}</version>
        <dependencies>
          <dependency>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-core</artifactId>
            <version>${liquibase.version}</version>
          </dependency>
        ...
</plugin>

The `[*]` marks the error position as part of the error message. I doubt JOOQ or Liquibase adds that. It's most probably an indication that something _before_ that was already invalid e.g. the `auto_increment` - but that would generate a different error. So maybe you are not actually using Postgres to begin with. — , May 04 '22 at 10:05
Thanks, I'm using postgres but JOOQ codegen doesn't as @lukas-eder explained. — MarcoFarinetti, May 04 '22 at 11:13

Lukas Eder · Accepted Answer · 2022-05-04T12:41:03.520

Why this happens

The LiquibaseDatabase in jOOQ 3.16's code generation configuration works by simulating your migration against an in-memory H2 database, which, starting from H2 2.0 has incompatibly declared VALUE a keyword, which can no longer be used as an identifier without quoting.

Workaround in Liquibase

So, your workaround could be to quote all objects (or reserved words, if Liquibase is up to date with H2's latest changes): https://docs.liquibase.com/parameters/object-quoting-strategy.html

E.g.

databaseChangeLog:
    -  object-quoting-strategy: QUOTE_ALL_OBJECTS

However, this means that you should make sure to use only lower case identifiers in your Liquibase configuration, as to not accidentally create case sensitive identifiers in your PostgreSQL database

Upgrading Liquibase

I can't reproduce this with the latest versions of Liquibase. It seems they have fixed this and now support H2 2.x correctly

A future fix in jOOQ

jOOQ should fix this on the jOOQ side. Eventually, H2 will be removed from the equation (at least it will be possible to opt out of using it), and jOOQ will interpret the DDL generated by Liquibase directly in order to generate your code. The relevant feature request is: https://github.com/jOOQ/jOOQ/issues/7034

I tried QUOTE_ALL_OBJECTS but still getting the same error. I'll stick to jOOQ 3.15 until it's fixed. Thanks! — MarcoFarinetti, May 04 '22 at 11:29
@MarcoFarinetti: I can't reproduce this with the latest liquibase versions. Will try, it seems jOOQ 3.16 still depends on LB 4.4.3. You could try upgrading Liquibase for the jOOQ code generation plugin? — Lukas Eder, May 04 '22 at 12:41