Improve support for jOOQ 3.15 by making R2dbcAutoConfiguration back off in the absence of a connection provider

[lukaseder]

Starting with jOOQ 3.15 (hopefully due by the end of Q2 2021), jOOQ will support R2DBC: jOOQ/jOOQ#11700. For this, there is an io.r2dbc:r2dbc-spi dependency from org.jooq:jooq in all distributions. Just like with JDBC, the R2DBC drivers, connection pools, etc. will be provided and configured by users.

This now means that R2dbcAutoConfiguration will be activated and lead to false positive exceptions in 95% of all cases, because most people will still want to use the DataSourceAutoConfiguration.

A special case is when someone already excludes the DataSourceAutoConfiguration because they want to configure their own DataSource, or use DriverManager:

@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class })

In that case, they will have to also exclude R2dbcAutoConfiguration when they upgrade to jOOQ 3.15.0:

@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class, R2dbcAutoConfiguration.class })

Not being a Spring Boot guru myself, I still wonder how we can prepare jOOQ + Spring Boot optimally to prevent any frustration from users when things break after the jOOQ 3.15 upgrade, keeping in mind:

• Most people probably use jOOQ with a DataSourceAutoConfiguration and don't care about R2DBC
• Some people will want to use jOOQ with R2DBC exclusively, and not use a JDBC DataSource
• Some people will want to use jOOQ with both JDBC and R2DBC
• Some people opt out of both of these auto configurations

Ideas?

[wilkinsona]

Thanks for raising this, @lukaseder.

I agree that the majority of Spring Boot and jOOQ users won't care about R2DBC. With that in mind, have you considered making the R2DBC SPI an optional dependency of jOOQ? Users will have to add a dependency on a driver and possibly the connection pool as well anyway. Such a dependency will pull in the SPI transitively so making it optional won't require users of jOOQ and R2DBC to declare any additional dependencies and it'll save a little bit of weight for users of JDBC.

If making the R2DBC API optional isn't possible for technical reasons, we could explore making the R2DBC auto-configuration back off in the absence of a META-INF/services/io.r2dbc.h2.H2ConnectionFactoryProvider resource. This would mean that the auto-configuration for R2DBC then only kicks in when there's an R2DBC driver on the classpath and not just when the SPI is available.

If making the R2DBC API optional isn't possible for a more philosophical reason, we could leave the auto-configuration as-is and explore excluding it in our dependency management or in spring-boot-starter-jooq.

The options described above are what I've managed to think of thus far and I think they're in my order of preference. To summarise, they are:

1. Make the R2DBC SPI an optional dependency of jOOQ
2. Make the R2DBC auto-configuration @ConditionalOnResource("META-INF/services/io.r2dbc.h2.H2ConnectionFactoryProvider")
3. Exclude the R2DBC SPI from jOOQ's dependencies

[lukaseder]

With that in mind, have you considered making the R2DBC SPI an optional dependency of jOOQ?

Yes, but I haven't found a user-friendly and otherwise consistent way to add support for R2DBC SPI types in the jOOQ API. The main entry point is (as always): DSL.using(ConnectionFactory), and the Configuration.connectionFactory() exposes it again, like all the other SPIs. So, if I'm not mistaken, not having that dependency as a mandatory one will lead to NoClassDefFoundError, NoSuchMethodError, and related?

we could explore making the R2DBC auto-configuration back off in the absence of a META-INF/services/io.r2dbc.h2.H2ConnectionFactoryProvider resource

I'm not sure if I understand this part. Is H2 here a placeholder for all the known providers? Or is H2's provider something that is always present?

This would mean that the auto-configuration for R2DBC then only kicks in when there's an R2DBC driver on the classpath and not just when the SPI is available.

That certainly seems to make sense. The SPI alone isn't doing anything. Of course, that would mean that if someone forgets to add the driver, they might not get the relevant error messages...?

If making the R2DBC API optional isn't possible for a more philosophical reason

No philosophical reasons, only technical ones.

[wilkinsona]

So, if I'm not mistaken, not having that dependency as a mandatory one will lead to NoClassDefFoundError, NoSuchMethodError, and related?

I don't think you're mistaken. If R2DBC is going to appear as a first-class citizen in the main jOOQ API it'll need to be on the classpath.

I'm not sure if I understand this part

Sorry, that should have been META-INF/services/io.r2dbc.spi.ConnectionFactoryProvider

Of course, that would mean that if someone forgets to add the driver, they might not get the relevant error messages...?

Yes, that's a downside and why this option wasn't first on my list.

[lukaseder]

Another option would be to focus on the first two groups, i.e. those that want to work with either JDBC or R2DBC? As soon as either connection is configured, ignore the other? That would at least make the upgrade convenient for most of the jOOQ users.

[simasch]

@lukaseder But what if I use H2 in-memory and don't configure a datasource because this is also auto-configured?

[lukaseder]

@simasch I'm sorry, I didn't understand that

[wilkinsona]

Another option would be to focus on the first two groups, i.e. those that want to work with either JDBC or R2DBC?

That's already Boot's focus as we don't support auto-configuring both R2DBC and JDBC. If users want to use both R2DBC and JDBC, they have to configure JDBC themselves. We don't have any plans at the moment to change this.

Boot auto-configuration has to run in a particular order and we decided when we added R2DBC support that it should go first. If R2DBC auto-configuration results in an io.r2dbc.spi.ConnectionFactory bean being defined, auto-configuration of a JDBC DataSource will then back off.

To expand a bit on what we discussed above, at the moment, if you have the R2DBC SPI on the classpath and no R2DBC driver or connection URL, Boot will fail and advise you to either add the H2 driver or configure a connection URL. If we make the auto-configuration back off when there's no META-INF/services/io.r2dbc.spi.ConnectionFactoryProvider the user will lose this error message and they'll be left with no R2DBC-related beans instead. That may trigger a subsequent failure but, even if it does, I think it'll be harder to determine why that's happened.

A refinement of requiring a META-INF/services/io.r2dbc.spi.ConnectionFactoryProvider resource to be present for R2DBC to be auto-configured would be to only do so when jOOQ is on the classpath. If jOOQ's absent the presence of the SPI would be sufficient to attempt to auto-configure R2DBC as we do today. The benefit of this is that only jOOQ users would pay the price of what is currently a jOOQ-specific problem.

[lukaseder]

Thanks a lot for the clarifications. That makes sense.

[philwebb]

To expand a bit on what we discussed above, at the moment, if you have the R2DBC SPI on the classpath and no R2DBC driver or connection URL, Boot will fail and advise you to either add the H2 driver or configure a connection URL. If we make the auto-configuration back off when there's no META-INF/services/io.r2dbc.spi.ConnectionFactoryProvider the user will lose this error message and they'll be left with no R2DBC-related beans instead. That may trigger a subsequent failure but, even if it does, I think it'll be harder to determine why that's happened.

We discussed the "harder to determine why that's happened" part of this comment today and thought that a FailureAnalyzer might help here. We could detect a NoSuchBeanDefinitionException for a missing ConnectionFactory.

[lukaseder]

That sounds great! I'm about to release jOOQ 3.15, this or next week. I'll ping you when it's ready

[lukaseder]

So, here we go. jOOQ 3.15 including R2DBC support has been published: https://blog.jooq.org/2021/07/06/3-15-0-release-with-support-for-r2dbc-nested-row-array-and-multiset-types-5-new-sql-dialects-create-procedure-function-and-trigger-support-and-much-more/

[lukaseder]

For the record and for others finding this, I've documented the status quo also on Stack Overflow, as people will find their SpringBoot / jOOQ auto configuration to stop working after upgrading to 3.15.0:
https://stackoverflow.com/q/68297295/521799

[wilkinsona]

I've made a start on this and @ConditionalOnResource approach coupled with a failure analyzer seems to work quite nicely. One thing that we have yet to discuss is what, if anything, to do about the jOOQ starter.

At the moment, spring-boot-starter-jooq depends on spring-boot-starter-jdbc. This doesn't make so much sense now that jOOQ also supports R2DBC – unless you're using Flyway or Liquibase for DB initialization, no JDBC-related dependencies are needed. We could leave things as they are and R2DBC users could exclude spring-boot-starter-jdbc if it's causing problems or they want to avoid the bloat. Alternatively, we could create a new starter (spring-boot-starter-jooq-r2dbc or spring-boot-starter-jooq-reactive) that doesn't depend on spring-boot-starter-jdbc.

[wilkinsona]

@lukaseder When trying to build my Boot changes, I've just noticed that jOOQ 3.15.0 appears to require Java 11:

> Task :spring-boot-project:spring-boot:compileJava FAILED
/Users/awilkinson/dev/spring-projects/spring-boot/main/spring-boot-project/spring-boot/src/main/java/org/springframework/boot/jooq/JooqDependsOnDatabaseInitializationDetector.java:22: error: cannot access org.jooq.DSLContext
import org.jooq.DSLContext;
               ^
  bad class file: /Users/awilkinson/.gradle/caches/modules-2/files-2.1/org.jooq/jooq/3.15.0/89e487b8d68abd20ca898c929a4515f0a86bee7f/jooq-3.15.0.jar(org/jooq/DSLContext.class)
    class file has wrong version 55.0, should be 52.0
    Please remove or make sure it appears in the correct subdirectory of the classpath.

javap confirms this is the case for DSLContext, at least:

$ javap -v org.jooq.DSLContext
Classfile /Users/awilkinson/dev/temp/org/jooq/DSLContext.class
  Last modified 06-Jul-2021; size 223759 bytes
  MD5 checksum 5ba7d4d602dc5dfffc513fc5583dd852
  Compiled from "DSLContext.java"
public interface org.jooq.DSLContext extends org.jooq.Scope
  minor version: 0
  major version: 55
  flags: ACC_PUBLIC, ACC_INTERFACE, ACC_ABSTRACT
…

Is that intentional?

[lukaseder]

At the moment, spring-boot-starter-jooq on spring-boot-starter-jdbc. This doesn't make so much sense now that jOOQ also supports R2DBC – unless you're using Flyway or Liquibase for DB initialization, no JDBC-related dependencies are needed.

Well, jOOQ's code generator works with JDBC, and only with JDBC... Not sure if that's relevant here?

Alternatively, we could create a new starter (spring-boot-starter-jooq-r2dbc or spring-boot-starter-jooq-reactive) that doesn't depend on spring-boot-starter-jdbc.

How would it work for users who want both? Would they just include both starters?

@lukaseder When trying to build my Boot changes, I've just noticed that jOOQ 3.15.0 appears to require Java 11:

Yes, the jOOQ Open Source Edition 3.15 requires Java 11. The commercial editions still support Java 8

[wilkinsona]

Yes, the jOOQ Open Source Edition 3.15 requires Java 11. The commercial editions still support Java 8

That's unfortunate from a Spring Boot perspective. Our baseline is going to be Java 8 for all 2.x releases which means that we can't upgrade to jOOQ 3.15 till 3.0. It doesn't prevent us from doing this compatibility work, but the default version of jOOQ in Boot's dependency management will remain 3.14.x.

[lukaseder]

That's unfortunate from a Spring Boot perspective. Our baseline is going to be Java 8 for all 2.x releases which means that we can't upgrade to jOOQ 3.15 till 3.0.

I thought about this, but then I found the limitation arbitrary, both in jOOQ and in Spring Boot. For a while, the industry seemed to have come to a sort of agreement to keep baselines at a Java 8 level everywhere. Supporting less wasn't required (because yay, streams, lambas, default methods, java.time and much more). Requiring more wasn't reasonable (because 9,10,11,... didn't bring that much, and 9 took long to ship, so no big wins here).

But depending on how far away Spring Boot 3.0 is, this Java 11 (or 17) clock is ticking. With all the goodies that are available in Java 17 by now (records, sealed classes, pattern matching, etc. etc.), I would imagine even more libraries will start upgrading their baselines to something equally random as Java 8. Maybe, jOOQ is a first mover here - being dual licensed, and thus able to move forward faster. But I'm convinced other libraries will follow eventually.

So, maybe there is another solution? An integrator like Spring Boot should be able to support multiple JDK versions out of the box, I think, especially considering how fast Java is moving now. I obviously don't know what it takes for this to work...

It doesn't prevent us from doing this compatibility work, but the default version of jOOQ in Boot's dependency management will remain 3.14.x

But then, you can't auto configure the R2DBC ConnectionFactory in jOOQ, I suspect?
https://www.jooq.org/javadoc/3.15.1-SNAPSHOT/org.jooq/org/jooq/Configuration.html#set(io.r2dbc.spi.ConnectionFactory)

[wilkinsona]

An integrator like Spring Boot should be able to support multiple JDK versions out of the box, I think.

We already do so, supporting Java 8, 11, and 16 right now.

But then, you can't auto configure the R2DBC ConnectionFactory in jOOQ, I suspect?

We may be able to do so via reflection. That's what we do for Jetty 10 which requires Java 11, while Jetty 9.x remains Boot's default version.

[lukaseder]

We already do so, supporting Java 8, 11, and 16 right now.

OK, by "support" you mean you test things with these JDK versions, but you compile everything on a JDK 8 language level, right? Could there be 3 distributions of Spring Boot instead? And the Java 8 distribution would stick with jOOQ 3.14, whereas the other two would default to 3.15?

We may be able to do so via reflection. That's what we do for Jetty 10 which requires Java 11, while Jetty 9.x remains Boot's default version.

Ah yes, that makes sense.

[wilkinsona]

you compile everything on a JDK 8 language level, right?

Right.

Could there be 3 distributions of Spring Boot instead?

While technically possible (probably), we don't have any plans for anything like that. A single distribution served us well in 1.x where we supported Java 6, 7, and 8 and continues to do so in 2.x where we've supported, at various times, Java 8, 9, 10, 11, 12, 13, 14, 15, and 16.

[lukaseder]

While technically possible (probably), we don't have any plans for anything like that. A single distribution served us well in 1.x where we supported Java 6, 7, and 8 and continues to do so in 2.x where we've supported, at various times, Java 8, 9, 10, 11, 12, 13, 14, 15, and 16.

OK, makes sense. Well, as I said. So far, this worked well, because everyone agreed on the Java 8 baseline (or even less) for years. We'll see if that changes or not. Until then, upgrading jOOQ from Spring Boot's default of 3.14 isn't too hard.

I had thought of another alternative which I'm going to keep in mind if this bothers too many people: jOOQ could publish a jOOQ-config artifact, where the Configuration and its SPIs reside, i.e. the stuff that Spring Boot compiles against. That artifact could be kept Java 8 compatible. I'm not thrilled about this idea, but it would be an option.

[lukaseder]

Until Spring Boot 2.6 ships with this fix, is there a chance of improving the error message in Spring Boot 2.5.x? I keep supporting folks with this problem (example here: https://twitter.com/amizrash/status/1448345414109962255) and linking to this Stack Overflow answer, which apparently isn't easy to find on google:

• https://stackoverflow.com/a/68297496/521799

I've added the error message No qualifying bean of type 'org.jooq.DSLContext' available: expected at least 1 bean which qualifies as autowire candidate. to the answer, maybe this helps with SEO for this problem.

I think this problem leads to quite a bit of frustration among jOOQ users, and given how easy the fix is (once it is known, and it is very hard to know from the error message alone), I think a better error message would really help users.

[wilkinsona]

I've opened #28378 to see if we can improve the error message when there's no DSLContext bean. This'll still apply even with the changes proposed in this issue, it'll just be less likely to occur.

[lukaseder]

Thanks a lot, @wilkinsona

[lukaseder]

For the record, I can confirm this issue disappears after upgrading to Spring Boot 2.6, great job everyone 💪

[kamilgregorczyk]

Would be great to get some small tutorial on how to configure reactive jooq with spring, I'm failing to boot spring-boot with 2.7.0-SNAPSHOT and jooq 3.15. Even though i have r2dbc in my classpath, connection string is r2dbc:postgresql://localhost:5432/sa.

The error is the one you guys added:

***************************
APPLICATION FAILED TO START
***************************

Description:

jOOQ has not been auto-configured as R2DBC has been auto-configured in favor of JDBC and jOOQ auto-configuration does not yet support R2DBC. 

Action:

To use jOOQ with JDBC, exclude R2dbcAutoConfiguration. To use jOOQ with R2DBC, define your own jOOQ configuration.

I tried searching jooq examples for reactive spring but I only found the blocking one

(full deps)

plugins {
    id 'org.springframework.boot' version '2.7.0-SNAPSHOT'
    id 'io.spring.dependency-management' version '1.0.11.RELEASE'
    id 'java'
    id 'groovy'
    id 'com.revolut.jooq-docker' version '0.3.7'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
    mavenCentral()
    maven { url 'https://repo.spring.io/milestone' }
    maven { url 'https://repo.spring.io/snapshot' }
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-actuator'
    implementation 'org.springframework.boot:spring-boot-starter-webflux'
    implementation 'org.springframework.boot:spring-boot-starter-validation'
    implementation 'org.springframework.boot:spring-boot-starter-jooq'

    implementation 'org.codehaus.groovy:groovy:3.0.8'
    implementation "org.flywaydb:flyway-core:8.3.0"
    implementation 'org.jooq:jooq:3.15.5'
    implementation 'io.r2dbc:r2dbc-postgresql'

    compileOnly 'org.projectlombok:lombok'
    developmentOnly 'org.springframework.boot:spring-boot-devtools'
    annotationProcessor 'org.springframework.boot:spring-boot-configuration-processor'
    annotationProcessor 'org.projectlombok:lombok'

    testImplementation 'org.testcontainers:postgresql:1.16.2'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testImplementation 'org.spockframework:spock-spring:2.0-groovy-3.0'
    testImplementation 'org.spockframework:spock-core:2.0-groovy-3.0'
    testImplementation 'org.skyscreamer:jsonassert:1.5.0'
}

test {
    useJUnitPlatform()
}

tasks {
    generateJooqClasses {
        schemas = ["public", "other_schema"]
        basePackageName = "org.jooq.generated"
        inputDirectory.setFrom(project.files("src/main/resources/db/migration"))
        outputDirectory.set(project.layout.buildDirectory.dir("generated-jooq"))
        excludeFlywayTable = true
        outputSchemaToDefault = ["public"]
    }
}

[lukaseder]

To my understanding, because of the jOOQ 3.15 Open Source Edition's baseline of Java 11, Spring Boot 2.x cannot auto configure jOOQ 3.15 yet, only up to jOOQ 3.14, see #26439 (comment)

As the error message states, you'll have to configure jOOQ yourself by wiring the R2DBC ConnectionFactory to a jOOQ Configuration

[kamilgregorczyk]

@lukaseder this was enough , flyway had other issues related to r2dbc but they aren't related to jooq (fixed them too) https://github.com/kamilgregorczyk/test-spring-reactive-jooq/blob/master/src/main/java/com/example/jpademo/TestSpringDataJpaApplication.java

[lukaseder]

Yeah, that looks like it should work. If you're interested, that might be a good idea to document also on Stack Overflow, so others will find your solution more easily. You could ask and answer your own question, FAQ style: https://stackoverflow.blog/2011/07/01/its-ok-to-ask-and-answer-your-own-questions