Use jOOQ to generate code from h2 pretending to be PostgreSQL

[edgar...@gmail.com]

Here's my workflow:

- I have a folder of db migration scripts

- Flyway runs them all on an h2 database in Postgres mode, then dumps the final schema

- There's a gradle task that makes sure that the db migrations -> schema mapping is always up-to-date

- I'd like to slurp this schema into an h2 in Postgres mode, and use that to generate code with jOOQ.

The problem is, jOOQ barfs when generating code against the h2 db in Postgres mode.  Here's the jOOQ config:

jooq {

version = '3.9.5'

edition = 'OSS'

models(sourceSets.main) {

generator {

name = 'org.jooq.util.DefaultGenerator'

database {

driver = 'org.h2.Driver'

url = 'jdbc:h2:mem:;MODE=PostgreSQL;INIT=runscript from '/abs/path/init.sql'"'

schema = 'public'

}

strategy {

name = 'org.jooq.util.DefaultGeneratorStrategy'

}

database {

name = 'org.jooq.util.postgres.PostgresDatabase'

inputSchema = 'public'

}

generate {

immutablePojos = true

daos = true

}

target {

packageName = 'models.db.gen'

directory = 'src/main/jooq-generated'

}

}

}

}

And here's the error output:

04:55:47.446 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : select 1 as "one"

04:55:47.473 [main] DEBUG org.jooq.tools.StopWatch - Query executed           : Total: 34.105ms

04:55:47.605 [main] DEBUG org.jooq.tools.LoggerListener - Fetched result           : +----+

04:55:47.605 [main] DEBUG org.jooq.tools.LoggerListener -                          : | one|

04:55:47.605 [main] DEBUG org.jooq.tools.LoggerListener -                          : +----+

04:55:47.605 [main] DEBUG org.jooq.tools.LoggerListener -                          : |   1|

04:55:47.605 [main] DEBUG org.jooq.tools.LoggerListener -                          : +----+

04:55:47.606 [main] DEBUG org.jooq.tools.StopWatch - Finishing                : Total: 169.54ms, +135.435ms

04:55:47.606 [main] DEBUG org.jooq.tools.LoggerListener - Executing query          : select "pg_catalog"."pg_namespace"."nspname" from "pg_catalog"."pg_namespace"

04:55:47.608 [main] WARN org.jooq.util.AbstractDatabase - SQL exception            : Exception while executing meta query: Schema "pg_catalog" not found; SQL statement:

select "pg_catalog"."pg_namespace"."nspname" from "pg_catalog"."pg_namespace" [90079-196]

Any guesses for how I can trick jOOQ into generating PostgreSQL-style code from an h2 db in Postgres mode?

Thanks!

[Lukas Eder]

Hi Edgar,

Thanks for your message.

I don't think that the "H2 in PostgreSQL mode" really goes "all in" on the PostgreSQL mode, including switching all the dictionary views to what would be expected in PostgreSQL. Instead, it simply accepts PostgreSQL style SQL query syntax.

So, the correct approach here would be to turn off PostgreSQL mode when reverse engineering the schema with jOOQ (because the PostgreSQL mode is no longer useful, once you've loaded the schema), and to use the H2Database instead of the PostgresDatabase, like with any other ordinary H2 schema.

Note that since jOOQ 3.10, we now have a DDLDatabase for the code generator as well, which uses jOOQ's new parser internally to reverse engineer any dialect:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-ddl

It internally applies a set of migration scripts (translated from any dialect to H2) to a fresh, in-memory H2 database, and then reverse engineers it again. Perhaps that works for you. We'd be very happy to get any feedback on this new feature.

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Daniel Einspanjer]

Lucas,

I was very interested in the new DDLDatabase, but when I read the docs, it sounded like it doesn't yet support reading in a directory of migration scripts like what you'd find in a typical Flyway-enabled project.

Is this actually supported in 3.10.0?  Currently, we have been reading the PostgresDB and outputting to an XMLDatabase, but of course, this requires creating the DB with the migrations first.  Would be great to eliminate that step.

-Daniel

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hi Daniel,

Thanks for your message. Unfortunately, this is currently not yet supported. The relevant feature request is here:

https://github.com/jOOQ/jOOQ/issues/6261

You could try using a pre-processor to concatenate all your scripts into a single one, prior to passing that to the DDLDatabase. If you're using Maven, this would be rather simple with the maven-ant-plugin. Not sure if there's a suitable Maven plugin for that specific task.

I hope this helps,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.