Hi Ulrich, Thank you very much for your message and for sharing so many details about your planned process. I will comment inline
On Tue, Apr 9, 2019 at 12:31 PM Ulrich Grepel <[email protected]> wrote: > I'm looking for the "best" way to integrate jOOQ class generation in > gradle build scripts. I would prefer not having to have an up-and-running > database for the target system, since that defeats "check out and build" > scenarios. I would also prefer not checking in the generated classes, but > rather generating them on the fly. > > I do use liquibase with a SQL/DDL script, but this is not and should not > be limited to SQL/DDL (liquibase supports XML and other variants) or > liquibase itself (flyway offers similar features, especially when using > SQL/DDL scripts). > > It is fairly easy using a H2 database: > > - use liquibase gradle plugin to set up the schema in a file system > based H2 database underneath $builddir > - use Etienne Studer's gradle plugin to generate the classes, with the > jooq plugin depending on the update task of the liquibase plugin. > > This works just fine. > I think this is a very good approach in your case, since you want to profit from the full power of e.g. liquibase. While it would be possible, in principle, to emulate liquibase somewhere inside of the jOOQ code generation process, why not just use liquibase in the first place. Of course, using H2 database here will limit you to the least common denominator of H2 and whatever you're using in production. Other approaches you've mentioned suffer from the same limitation. > Another approach is using jOOQ's own DDLDatabase, which allows using an > on-the-fly in-memory H2 database, being fed an SQL script, which could be > the very same liquibase script (assuming it is SQL and not XML or other). > This eliminates liquibase from the above approach. > Yes, from the code generation approach, but not from your migration. You'll still need liquibase eventually, and thus limit your liquibase integration. The DDLDatabase has been designed to work with migration tools like Flyway. > Works fine, except for https://github.com/jOOQ/jOOQ/issues/8386 which I > actually did hit with my SQL/DDL script. > Indeed, due to how we're currently maintaining the parser, we cannot backport the many changes we're making to it to 3.11.x, so a lot of issues have been fixed already, but will be available only in jOOQ 3.12. > But there is another problem: while H2 is a fine database, it does not > allow everything. In production, I'm going to use PostgreSQL. Including > some stored procedures and triggers. And these are, obviously, part of the > SQL/DDL script. Which H2 doesn't understand. > In some distant future, we'll be able to parse a large part of those scripts. We don't have to be able to parse the procedure body entirely, as long as we can parse the signature, we can create dummy procedures in H2 - to some extent. Also, in the future, the DDLDatabase will simulate the DDL statements rather than executing them on H2, which will further remove some of the limitations. But we're not there yet. > So I would like to use PostgreSQL for the jOOQ generator. But still > without actually installing a local PostgreSQL instance. > > PostgreSQL doesn't have an in-memory-option like H2. > > But there is testcontainers, which I'm using for my DAO unittests as well. > > It requires docker to be installed locally, but that is fine. Less > "special" than PostgreSQL. > > Now my approach would be similar to the above liquibase and jooq gradle > plugin, but using a testcontainers based PostgreSQL database. > > Unfortunately, this doesn't work, because Etienne Studer's plugin does, > for valid reasons, start up a separate JVM for the jOOQ code generator. > Which causes the PostgreSQL container not to be found by the code generator. > > > Two possible approaches: > > - use a separately written Java program that > - starts testcontainers for PostgreSQL > - lets liquibase do its job > - lets jOOQ do its job > - or use a variation of DDLDatabase which basically does the same. > > I chose the second approach. Right now it is working "just fine", however > the code still requires some improvement. Other than the DDLDatabase > version it does not use the jOOQ internal SQL/DDL parser, but delegates > that job to liquibase (I propably could have used flyway as well). > > Improvements include: > > - do not use the DDLDatabase way to scan directories for the SQL > scripts (when using liquibase with more than one file, you'd usually have a > separate XML file using includes of other files, which can be SQL/DDL files > as well, and flyway basically does the same thing as well). > - allow more variations - right now this is fixed to PostgreSQL, but > it could be done with every database supported by testcontainers, > liquibase/flyway and jOOQ. > > Using this approach has the definite advantage that the SQL/DDL script can > use every feature of the target database (not limited to H2's feature set). > > > Using the real target database solves another issue, especially with the > combination of H2 and PostgreSQL: H2 does everything in uppercase, > PostgreSQL in lowercase, jOOQ uses quoted identifiers. This doesn't mix > well. While this could be solved in various ways (quotes in the DDL script > - but what about the "public" vs. "PUBLIC" schema name?, or using > RenderNameStyle.AS_IS), but just using "the real thing" eliminates this > altogether. > This has already been solved in jOOQ 3.12, but again, will not be backported to 3.11.x. > I could provide (after cleanup) the code I derived from DDLDatabase, but > according to the jOOQ contribution rules I'm asking first for opinions, > ideas and other suggestions. > Thanks for doing that :-) Well, you can always share your derived code here, if you think there's something worth discussing, or if you think others could profit from it. We're currently unlikely to merge any PR related to such an approach, as it will be difficult to maintain at this moment. Having said so, in the long run, I would definitely favour a solution that uses a real PostgreSQL database for code generation. No tool in your tool chain should prevent you from getting the most out of your RDBMS. The DDLDatabase is a quick win for relatively simple scenarios, but it stops working at some point. I also approve of the docker compose approach shared by Marshall, which works around the limitations imposed by Testcontainers. 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
