Jooq: Allow for DDLDatabase to connect to a testcontainers managed database

[axelfontaine]

Expected behavior and actual behavior:

Currently one must keep a DB around for generating the jOOQ classes at build time. It would be great if jOOQ could dynamically fire up a container with the correct database (Oracle, PostgreSQL, MySQL, ...), migrate the database using Flyway (of course!) and use that the generate the classes, before finally discarding the DB once again.

Always a clean DB, works in any environment where Docker is present without any need to preprovision anything.

Note that it may be possible to leverage some of the work done by the testcontainers project for this.

Versions:

• jOOQ: 3.10 ;-)

[lukaseder]

That's an interesting idea, thanks for your suggestion. In fact, our jOOQ 3.10 DDLDatabase already does something similar, although it currently translates (using the new parser) the SQL script(s) to H2 and runs the script on an in-memory H2 database.

Of course, your version would be much more robust. This will be added as a configuration option for the DDLDatabase.

jOOQ: 3.10 ;-)

Probably that won't fit anymore :)

[lukaseder]

Another feature request #8311 requested the same thing, maybe with a bit of a less ambitious feature set. I like your proposition better, because the "throwaway database" in the container that you're suggesting would reflect exactly the DDL that would be installed otherwise, without any additional configuration.

This might be worth tackling in the near future. How would I go about that? In fact, would you be interested in providing a PR for this - obviously using Flyway, behind the scenes :)

[luckylookas]

I've recently thought about this issue coincidetaly.

I would recommed looking at https://www.testcontainers.org for setting up the database container.

It really easy to set them up in spring boot tests, i did not yet have the chance to look into using them without spring though.

[bobtiernay-okta]

It might be interesting to leverage one of the following instead of going the Docker route:

• https://github.com/yandex-qatools/postgresql-embedded
• https://github.com/wix/wix-embedded-mysql

Both use the same underlying mechanism.

[lukaseder]

@bobtiernay-okta I'm very reluctant to depend on such third party tools, just because they happen to be available on Github. Can we trust all of these dependencies (security wise)? Will they be maintained / available in the near future? Etc.

@LuckyLukas Yes, testcontainers is definitely a very interesting option

[bobtiernay-okta]

@lukaseder You don't trust the standard MySQL distribution that is used, or the library itself? It seems you trust H2 so I'm failing to see the difference in this regard.

Lastly, how is your concern solved by containers? That has the same issue, no?

[lukaseder]

You don't trust the standard MySQL distribution that is used, or the library itself? It seems you trust H2 so I'm failing to see the difference in this regard.

Well yes. Some things are inevitable to trust. I trust Oracle. I trust H2. Specifically, H2 has a long history, which I trust. Wix... less so. Yandex? Same. I wouldn't want to have a dependency on such third parties which aren't really strictly related to RDBMS. The only credible reason for those companies to publish things as OSS on GitHub are 1) get free patches from the community for their internal stuff, 2) recruiting.

Testcontainers is different because the "vendor" (I'm still hoping that Richard North will make this a commercial product) is pursuing a more focused, credible strategy. Docker is different as well, because RDBMS vendors themselves offer images on there.

Does that make sense?

[bobtiernay-okta]

Yes and no. I don't agree with:

The only credible reason for those companies to publish things as OSS on GitHub are 1) get free patches from the community for their internal stuff, 2) recruiting.

That seems a bit harsh and an unfair characterization. Sometimes folks just want to give back.

In the end, I personally would rather have a jar dependency vs a container dependency and thus a dependency on Docker as this simplifies operational requirements imo. Not trying to dissuade the use of testcontainers, just want to mention there are downsides to that as well.

In the meantime, I'll continue using wix as this has been super simple to setup and use locally and on CI, with basically zero dependencies on the environment.

[lukaseder]

That seems a bit harsh and an unfair characterization. Sometimes folks just want to give back

Companies usually don't "just want to give back" :)

There's a bottom line to business, even if it may not always be measured exactly. That doesn't keep individuals working at companies from claiming they're doing it for a "giving back" reason, or even work at company XYZ precisely because they are "giving back" and that seems to reflect well on them, but usually, management will want to have a business case. One such business case is recruiting, where the "back givers" can attract talent that likes "giving back". So, what's the chicken and what's the egg here?

I'm not being harsh or unfair here. I've met enough vendors at conferences to know what I'm talking about. There are pros and cons to "giving back".

Anyway, I represent a vendor and as such I'm doing a risk analysis / due diligence, and these two contributions simply don't make it according to my standards. That won't keep you from using them yourself. In fact, I'm sure they do add a lot of value. But trust me. Very few vendors are just going to embed such a dependency and ship that to their own customers. Look at the Node ecosystem what happens when "vendors" don't apply due diligence on their many many transitive dependencies. It's a nightmare.

As a matter of fact, jOOQ itself is being screened by vendors much much more thoroughly than by services providers. Most paying jOOQ customers are companies that create tailor made software. Occasionally, a vendor will use jOOQ. But the legal implications of embedding a third party software and sublicensing and distributing that to end users are much more complicated than those of just consuming that software directly.

Hence, you like wix's tool for your own usage - I don't like it because I would have to distribute it.

In the end, I personally would rather have a jar dependency vs a container dependency and thus a dependency on Docker as this simplifies operational requirements imo. Not trying to dissuade the use of testcontainers, just want to mention there are downsides to that as well.

Sure, there are downsides. Like, how do you package the Oracle Database in a jar file? Or SQL Server? At that point, should we really support and maintain a mechanism that works for a select few RDBMS only?

A third party like testcontainers will maintain these things for us. Just like another third party like Flyway can manage the installation of versioned DDL increments.

[bobtiernay-okta]

Leaving aside the fact that I strongly believe companies can be moral and give back to the community for mutually beneficial reasons, let me try to address the less subjectives topics above.

But trust me. Very few vendors are just going to embed such a dependency and ship that to their own customers

Isn't this for testing only? Why does this code need to be embedded or shipped?

Sure, there are downsides. Like, how do you package the Oracle Database in a jar file? Or SQL Server?

The simple answer is you don't put it in a jar. You download it and cache from upstream. That's how https://github.com/flapdoodle-oss/de.flapdoodle.embed.process, an OSS project that these projects depend on, works.

At that point, should we really support and maintain a mechanism that works for a select few RDBMS only?

No, I would say supporting the most popular gives you the most value. Let's be honest here, testcontainers will never have everything you need. Postgres, MySQL and H2 would go a long way.

[lukaseder]

Isn't this for testing only? Why does this code need to be embedded or shipped?

No, this isn't what we're using behind the scenes for testing. We're using whatever works, including VMWare and actual hard installations on Windows (hello Informix).

This issue here is about an extension to the jOOQ code generator which we would be shipping.

Thanks for your various comments. I'm glad we have these to reconsider in the future, should we go forward with this issue.

[KaRkY]

I would really like to see this feature sooner than later. For now I am using a workaround only problem is that DDL statements must be in a separate artifact.

<plugin>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-codegen-maven</artifactId>

        <executions>
          <execution>
            <goals>
              <goal>generate</goal>
            </goals>
          </execution>
        </executions>

        <dependencies>
          <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>${postgresql.version}</version>
          </dependency>

          <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>testcontainers</artifactId>
            <version>${testcontainers.version}</version>
          </dependency>

          <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>postgresql</artifactId>
            <version>${testcontainers.version}</version>
          </dependency>

          <dependency>
            <groupId>${project.groupId}</groupId>
            <artifactId>schema-artifact</artifactId>
            <version>${project.version}</version>
          </dependency>
        </dependencies>

        <configuration>
          <jdbc>
            <driver>org.testcontainers.jdbc.ContainerDatabaseDriver</driver>
            <url>jdbc:tc:postgresql:11-alpine://testcontainers/database?TC_INITSCRIPT=META-INF/shema.sql</url>
          </jdbc>

          <generator>

            <database>
              <name>org.jooq.meta.postgres.PostgresDatabase</name>
              <includes>.*</includes>
              <excludes></excludes>
              <schemata>
                <schema>
                  <inputSchema>public</inputSchema>
                </schema>
              </schemata>
            </database>

            <generate>
              <javaTimeTypes>true</javaTimeTypes>
              <daos>false</daos>
              <sequences>true</sequences>
            </generate>

            <target>
              <packageName>shema.jooq</packageName>
            </target>

          </generator>
        </configuration>
      </plugin>

[lukaseder]

Thanks a lot for documenting this, @KaRkY.

[KaRkY]

Since testcontainers 1.11 there is no more need for separate package with DDL files. You can use a URL with the folowing format:
jdbc:tc:postgresql:11-alpine://testcontainers/vets?TC_INITSCRIPT=file:${basedir}/src/main/resources/META-INF/shema.sql

But 1.11 testcontainers version has a dependency bug so you must manualy add few dependencies relevant issue

[lukaseder]

Perhaps I have overestimated the complexity of this integration in the past. A quite simple solution using Testcontainers and Liquibase (could also be Flyway) has been displayed on the user group here:
https://groups.google.com/d/msg/jooq-user/Iph6yznNft4/5k0SaWWEBwAJ

~I'll revisit this for 3.12.~ (Never promise release dates or version!) Perhaps, with sufficient abstractions, this can be created as a plugin system to the DDLDatabase

[mbenson]

@KaRkY 's approach has been working well for me. I would mention, however, that since I am using Flyway I am continuing to keep my schema in a separate module so that I can, as part of its build process, concatenate all my flyway migration scripts into a single DB init script for use with the testcontainers JDBC URL. I can imagine that a JOOQ-centric implementation of this functionality might relieve me of that responsibility, so I will watch this space eagerly.

[yuecelm]

@lukaseder I would like to look into this, some objections?

[yuecelm]

some companies also "giving back" to avoid patch/branch maintenance in the future ;)

[lukaseder]

@yuecelm This topic will probably still require quite a bit of research. Currently, I don't want to allocate time for it (note to myself, I should never promise any release number).

You can certainly give it a go, but we might not currently allocate time for reviews.

[adrianskrobaczrevolut]

We've struggled with exact same problem and we developed a plugin for that. It's open source and published to Gradle Plugin Portal. You can find it here:
https://github.com/revolut-engineering/jooq-plugin

I hope it helps :)

[knutwannheden]

@adrianskrobaczrevolut Thanks for sharing this!

[yuecelm]

I think I misunderstood this ticket (at least according the title): I was planning a DDLDatabase extends PostgresDatabase/JDBCDatabase (PostgresDatabase for my explicit use case, JDBCDatabase as a more generic solution). This DDLDatabase would use internally testcontainers for DB interactions. AFAIK DDLDatabase is not using flyway for migrations. Should I open a new ticket or it belongs here?

[lukaseder]

@yuecelm I'm really not sure what you're offering :) Maybe, indeed, a new ticket to explain the idea would help?

[yuecelm]

I wanted a DDLDatabase which is using a PostgreSQL DB (running in a testcontainer) instead of a H2 database. Because of some SQL incompatibilties between H2 and PostgreSQL I need currently SQL migrations scripts for H2 (for code generation) and for PostgreSQL (for integration testing and as production DB) which is getting more and more frustrating.

I saw afterwards #7034 where in future DDLDatabase should not use any background DB at all. I will stop working at this ticket and observe the status of #7034.

[lukaseder]

I wanted a DDLDatabase which is using a PostgreSQL DB (running in a testcontainer) instead of a H2 database. Because of some SQL incompatibilties between H2 and PostgreSQL I need currently SQL migrations scripts for H2 (for code generation) and for PostgreSQL (for integration testing and as production DB) which is getting more and more frustrating.

I see, yes that's what this issue here is about. This issue is about offering an out of the box bit of glue code for what could otherwise be done with just a bit of manual plumbing. Surely, this has been done before.

I saw afterwards #7034 where in future DDLDatabase should not use any background DB at all. I will stop working at this ticket and observe the status of #7034.

Yes, we're working on that, but it won't be in 3.12 anymore. We're making it a high priority for 3.13, though, because DDL simulation is a very useful feature for many jOOQ users.

[sairamvudatha]

I've come across this use case for our project where we need to generate jOOQ code dynamically by adding flyway schema migration files for schema changes. (Rather that using hosted no production environment database)

I've achieved generating the jOOQ code dynamically with orchestrating mysql docker image and maven docker image using docker-maven-plugin.

• Spin up the mysql docker image
• Spin up the maven docker image
  
  
  
  • Run flyway-maven-plugin to apply schema migrations to mysql docker image
  
  
  • Run jooq-codegen-maven plugin to generate jOOQ code from database in mysql docker image
  
  

Ex: https://github.com/sairamvudatha/jooq-dockerized-codegen/blob/master/pom.xml#L37-L201

[knutwannheden]

Thanks for posting this example.

It seems like it should be possible to do this without a second container to run Maven in. The Docker plugin would then be executed in e.g. the pre-generate-sources phase to start the container and then again in some appropriate post-* phase to stop the container again. The Flyway migration and jOOQ code generation could be executed as usual in the same Maven build itself.

[sairamvudatha]

It seems like it should be possible to do this without a second container to run Maven in. The Docker plugin would then be executed in e.g. the pre-generate-sources phase to start the container and then again in some appropriate post-* phase to stop the container again. The Flyway migration and jOOQ code generation could be executed as usual in the same Maven build itself.

Correct, we could do that. In fact we've started with that approach. It worked for a while. But we were running into connections issues with flyway-maven-plugin // jooq-codegen-maven plugin and the mysql docker image. It started failing at least 1 out of every 3 times saying Communciations Link Failure. (Did not investigate it enough. Not sure if the Docker Desktop for mac updates is causing it) So, we've switched to running the flyway-maven-plugin and jooq-codegen-maven plugin inside maven container which is in the same docker network as mysql docker image.

[lukaseder]

We now have the infrastructure required for interpreting DDL on other backing JDBC connections than in-memory H2 ones. Which means we can relatively simply spin up a test containers connection that runs e.g. PostgreSQL or MySQL, and feed that to our DDL interpretation logic via:

• #7034: The new interpreterDialect
• #9460: An SPI providing a JDBC connection for the interpreter

[mbayealioune]

Since testcontainers 1.11 there is no more need for separate package with DDL files. You can use a URL with the folowing format:
jdbc:tc:postgresql:11-alpine://testcontainers/vets?TC_INITSCRIPT=file:${basedir}/src/main/resources/META-INF/shema.sql

But 1.11 testcontainers version has a dependency bug so you must manualy add few dependencies relevant issue

Than you so much for sharing this :D

[smacharacek]

Dockerized jOOQ code generation (using Liquibase & MySql & Testcontainers & Gradle) for those looking for a similar solution:

https://gist.github.com/smacharacek/9006d0abab92c948c817c359299810e2

[ursjoss]

Thanks for sharing. And another alternative using the jooq modelator gradle plugin as I used it e.g. here

[sabomichal]

Just another workaround using PostgreSQL and Flyway here till this issue gets fixed.

[ysb33r]

I recently also built a PoC for going liquibase -> testcontainers -> jooq. My primary use-case is to use with Gradle and the two exsiting jooq plugins had some deficiencies of their own. However, I wanted to see what the process would be like outisde of a build tool first and based upon that I have some observations.

• This introduces additional dependencies: (1) testcontainers and (2) liquibase (or flyway)
• The correct testcontainers artifact needs to be on the runtime classpath. For example is you want mysql, you need org.testcontainers:mysql
• The correct JDBC driver still needs to be on the classpath even if you use the jdbc:tc: URLs
• Once I figured out what to configure for jooq programmatically it was pretty trivial to get it all working.
• If one uses the jdbc:tc: approach and add ?TC_DAEMON=true to the URL, the actual code for running liquibase first and then jooq is relatively small. (If you want to to manage specific containers yourself i.e. MySqlContainer, then the code will be about 4x more.

I think this is a very useful feature to have, but personally I am not sure whether introducing these dependencies into jooq as a project would be a good thing. Maybe if it is spun off as a separate tool, then it would be a better solution.

[lukaseder]

Thanks a lot for sharing your approach here, @ysb33r.

I think this is a very useful feature to have, but personally I am not sure whether introducing these dependencies into jooq as a project would be a good thing. Maybe if it is spun off as a separate tool, then it would be a better solution.

We're introducing more and more of these modules where dependencies can be pulled in optionally. A jooq-meta-extensions-testcontainers or some sort of plugin mechanism is definitely the way to go.

[zero88]

Hi @lukaseder, I just provide simple solution to resolve it

How do you think if resolving this issue by adding one xml block like docker in jooq-codegen-xsd?
In codegen prepare time, we will use testcontainer as jooq-meta-extensions-testcontainers to rollout new SQL server, then auto inject connection into jdbc properties, then executing DDL script from provide DDL script.
After that, code gen start doing as old way.

Is it simple to implement?

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.xsd">
    <docker>
        <enabled>true</enabled>
        <image>postgresql:10-alpine</image>
    </docker>
    <jdbc>
        <driver/>
        <url/>
        <user/>
        <password/>
    </jdbc>
    <generator>
        <database>
            <name>org.jooq.meta.postgres.PostgresDatabase</name>
            <properties>
                <property>
                    <key>scripts</key>
                    <value>src/test/resources/pg_schema.sql</value>
                </property>
            </properties>
        </database>
</configuration>

[lukaseder]

Thanks for your suggestion, @zero88. Without having started investigating the solution to this issue, I can't comment on your particular suggestion yet, I'm afraid.

[zero88]

so, is any space for opening draft PR? for try-out my solution first with 1 or 2 DB provider to better evaluation
I actually need it for making my test is green, haha
Are you welcome it?

[lukaseder]

so, is any space for opening draft PR?

You can do that, of course, but I can't promise to review it. Chances of merging a PR are very slim, from experience, as user-provided PRs tend to overlook many many things on our roadmap. There is no expectation of thinking about all the side effects and feature interactions, but at the same time, I also think that spending time discussing specific PRs is better spent if I just go ahead and offer a solution here myself.

Some background on the jOOQ contribution guidelines:
https://github.com/jOOQ/jOOQ/blob/main/CONTRIBUTING.md

I actually need it for making my test is green, haha

We don't have any publicly available tests, so I'm not sure if there's any need for a PR on your side...

[ysb33r]

Thanks a lot for sharing your approach here, @ysb33r.

I think this is a very useful feature to have, but personally I am not sure whether introducing these dependencies into jooq as a project would be a good thing. Maybe if it is spun off as a separate tool, then it would be a better solution.

We're introducing more and more of these modules where dependencies can be pulled in optionally. A jooq-meta-extensions-testcontainers or some sort of plugin mechanism is definitely the way to go.

@lukaseder I recently completed an alpha release of https://gitlab.com/ysb33rOrg/jooq-generator which does the gradle-testcontainers-jooq approach. Currently it only works for mysql, but it would be easy to support other databases as long as both JOOQ and testcontainers support them. This could take some pressure off having to implement this feature in JOOQ. It does take a bit of a different approach to what Etienne did with his great plugin.

[marceloverdijk]

Just for reference I'm sharing my Gradle setup here:

plugins {
    id "java"
    id "nu.studer.jooq" version "5.2.1"
}

repositories {
    mavenCentral()
}

dependencies {
    jooqGenerator "org.postgresql:postgresql:${postgresqlVersion}"
    jooqGenerator "org.testcontainers:postgresql:${testcontainersVersion}"
}

compileJava {
    sourceCompatibility = "15"
}

jooq {
    version = "${jooqVersion}"
    configurations {
        main {
            generationTool {
                jdbc {
                    driver = "org.testcontainers.jdbc.ContainerDatabaseDriver"
                    url = "jdbc:tc:postgresql:11-alpine:///databasename?TC_INITSCRIPT=file:${projectDir}/buildSrc/src/main/resources/sql/schema.sql"
                }
                generator {
                    database {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "public"
                        outputSchemaToDefault = true
                        properties {
                            property {
                                key = "defaultNameCase"
                                value = "lower"
                            }
                        }
                    }
                    generate {
                        javaTimeTypes = true
                    }
                    target {
                        directory = "${projectDir}/buildSrc/src/jooq/java"
                        packageName = "com.company.jooq"
                    }
                }
            }
        }
    }
}

Note it uses the driver = "org.testcontainers.jdbc.ContainerDatabaseDriver" and url = "jdbc:tc:postgresql:11-alpine:///databasename" settings.
The url part jdbc:tc:postgresql will cause testcontainers to startup a testcontainer automatically.
So it just a matter of running the generateJooq task.