Search code examples
jooq

How can I resolve a jOOQ codegen error caused by incorrectly quoted identifiers in a deployed Flyway migration file?

I’m using the following environment

  • Java: 21
  • Spring Boot: 3.4
  • JOOQ: 3.19.15 (with jooq-meta-extensions)
  • Flyway: org.flywaydb:flyway-core:10.20.1 and org.flywaydb:flyway-database-postgresql

The scenario is as follows

  1. I added a Flyway migration file (version 0.0.5) that inserts initial data into the pricing_plan table without changing any table schema. The SQL in v0.0.5 was written as
INSERT INTO pricing_plan (name)
SELECT unnest(ARRAY['Free', 'Paid', 'Paid with free trial or plan'])
EXCEPT
SELECT name FROM pricing_plan;

Since this migration did not modify any schema, jOOQ codegen was not run in the development environment and the migration was deployed to production as-is.

  1. Later, I added another migration file (version 0.0.6) that alters a table
ALTER TABLE "recommend_product" ADD COLUMN "content" VARCHAR(500);

When I ran the jOOQ codegen in development (triggered by this schema change), I encountered the following error

DDLDatabase Error: Your SQL string could not be parsed or interpreted. 
...
Table "PRICING_PLAN" not found (candidates are: "pricing_plan"); SQL statement:
insert into PRICING_PLAN (NAME) select null except select NAME from PRICING_PLAN [42103-232]

The root cause appears to be In v0.0.5, I did not quote the table and column names properly. It should have been written as

INSERT INTO "pricing_plan" ("name")
SELECT unnest(ARRAY['Free', 'Paid', 'Paid with free trial or plan'])
EXCEPT
SELECT "name" FROM "pricing_plan";

However, the v0.0.5 migration file has already been deployed in production. As a result, my development environment now has migrations from v0.0.1 through v0.0.6 (with v0.0.5 in its unquoted, faulty form), and jOOQ codegen fails due to the discrepancy in identifier quoting

Solution

  • There's a flag called defaultNameCase in the DDLDatabase configuration, see:

    You can set it to:

    <!-- The default name case for unquoted objects:

     - as_is: unquoted object names are kept unquoted
     - upper: unquoted object names are turned into upper case (most databases)
     - lower: unquoted object names are turned into lower case (e.g. PostgreSQL) -->
<property>
  <key>defaultNameCase</key>
  <value>lower</value>
</property>