jooq support

[ieugen]

I think Jooq is a good fit for Quarkus because it works with generated code and does not rely on reflection. My experience with using Jooq is very good.

[ppalaga]

There is a jooq extension by @leotu https://github.com/leotu/quarkus-ext-jooq

[ieugen]

Thanks @ppalaga , It seems a bit outdated. Maybe @leotu can fill us in on the status.

[geoand]

I would love to know what exactly are people looking for when asking for jooq support in Quarkus.

[ieugen]

For one, I would like to do the following flow:

1. Generate code based on database schema
2. Use the generated code in a quarkus project
3. Ability to map data from jooq result to DTO object using fetch().into() and the JPA annotations ?! (I think it's the few times Jooq use reflection)
4. Profit ?!

I do believe this is achievable already but I haven't tried it yet since I've just started working with quarkus. I've put a pause on it since I can't build a CLI app - which is what I was going to build.

[geoand]

@ieugen there is a work in progress PR for command line support: https://github.com/quarkusio/quarkus/pull/7681

Perhaps you would like to play around with it and jooq?

I am no expert, but it doesn't seem from your description that there is a whole lot that Quarkus can / needs to do about jooq.

[ieugen]

Thanks @geoand . I will play around with quarkus and do a POC around https://tackler.e257.fi/ . I'll see how that goes and then try again with Jooq.

[ieugen]

/area/persistence

[dominik42]

I would love to know what exactly are people looking for when asking for jooq support in Quarkus.

Thanks @geoand for that question. JOOQ allows the usage of typesafe SQL in java which is a huge advantage compared to plain sql as String. Furthermore, the lack of support of nativeQueries in Spring Data Repository methods within a quarkus app #5348 , makes it nearly unusable for use cases where complex SQL (common table expressions, window functions etc.) are needed for queries. JPA/JPQL can't provide such functionality.

[geoand]

@dominik42 thanks for the info.

I would like to know what integration of JOOQ with Quarkus you would like to see.

[dominik42]

Well, an 'official' extension so that anyone can @Inject the org.jooq.DSLContext which is based on the datasource configured for the quarkus app. This DSLContext is the main 'bean' which allows using JOOQ (e.g. write typesafe statements based on the previously generated java source files for your current DB schema). The generation can be integrate into maven / gradle builds easily, also for scenarios where DB migration tools like liquibase/flyway are in use. I good starting point may the already mentioned github repo of @leotu https://github.com/leotu/quarkus-ext-jooq and maybe the JOOQ's Spring Boot autoconfiguration

[geoand]

Understood.

I like the idea, @emmanuelbernard, @maxandersen WDYT?

[emmanuelbernard]

Aside from the issue of jOOQ being open core, I think having an extension for it to integrate the usage makes sense. But if by official it means let’s find someone from red hat to maintain, I’m more reluctant. Could we find an official maintainer ? See https://github.com/quarkusio/quarkus/wiki/Adding-extensions-to-the-Quarkus-ecosystem

[dominik42]

If you can provide an initial version, maybe based on the existing repo I could do my very best. I'm primary a freelance Spring Boot guy using JOOQ, but currently I'm member of a project using Quarkus and I would like to use JOOQ also in this area.

[lukaseder]

Aside from the issue of jOOQ being open core

Just like Flyway and Liquibase! :)

Could we find an official maintainer ? See https://github.com/quarkusio/quarkus/wiki/Adding-extensions-to-the-Quarkus-ecosystem

While I won't take the lead, I'll certainly be available for any questions that Red Hat or the community may have, just as with Spring Boot or Micronaut

[ppalaga]

@emmanuelbernard I think it may not be clear to potential jooq extension maintainers whether you prefer the extension to live inside or outside of Quarkus source tree.

[emmanuelbernard]

@ppalaga it's in there https://github.com/quarkusio/quarkus/wiki/Adding-extensions-to-the-Quarkus-ecosystem we prefer not to add more extension int he monorepo but that does not change how it's exposed.

[emmanuelbernard]

@dominik42 I don't think there is an initial version besides the repo you point to, so you could work from it. Assuming the repo has a working version, I can see the following steps to world domination:

• update from Quarkus 0.15 to Quarkus 1.5.1 (and keep up)
  • this will likely break the existing code. 0.15 was around when dinosaurs were young
  • alternative is to restart from scratch, check out https://quarkus.io/guides/building-my-first-extension and the more complete but more intimidating guide https://quarkus.io/guides/writing-extensions
• add documentation
  • see configuration documentation here https://quarkus.io/guides/writing-extensions#configuration-reference-documentation
• create the extension metadata (to get its doc, category, status etc)
  • @maxandersen do we have a doc explaining how
• have the test suite and the github actions to test the extension on Quarkus master
  • @geoand is there a doc people should look at for this? Including the set suite subset to be run by Quarkus Platform
• publish on Quarkus Platform which is just a PR here https://github.com/quarkusio/quarkus-platform/
  • @maxandersen , we would need more doc on this area too
• profit

[geoand]

• @geoand is there a doc people should look at for this? Including the set suite subset to be run by Quarkus Platform

For ecosystem-ci which is what we propose for running tests against Quarkus master, there is https://github.com/quarkusio/quarkus-ecosystem-ci and https://www.youtube.com/watch?v=VpbRA1n0hHQ

[emmanuelbernard]

@geoand fancy adding a testing your extension into the cosystem section in https://quarkus.io/guides/writing-extensions? If only to point to these?

[geoand]

Good idea! Let me do that

[emmanuelbernard]

Aside from the issue of jOOQ being open core

Just like Flyway and Liquibase! :)

Just to clarify my statement. There is a technical aspect to Open Core when it comes to Quarkus. If your open core model adds new features or new code path (which it will invariably do), this means two things:

1. the extension exposing properties, pre-starting the framework at build time etc, will need to know about the extra properties to deal with them
2. in native image compilation, extra code path will need to be compiled which leads to possible additional substitution or code changes. Neat interface / implementation separations are no more in native image compilation in that regard.

So in practice, while one can build and test an extension for the open part. This would either require a separate extension for the non open part or the extension would have to do magic to deal with the non open part, and do test on the non open part.

My intuition in that in this situation it's better that the corporation behind the closed parts handle the extension to cover all bases. Or accept that the closed product might not work with the open extension. But someone can build that bridge when we get there.

[lukaseder]

Thanks for the clarifications, @emmanuelbernard.

So in practice, while one can build and test an extension for the open part. This would either require a separate extension for the non open part or the extension would have to do magic to deal with the non open part, and do test on the non open part.

In jOOQ's case, we take a lot of care to make the various editions / distributions as binary compatible as possible. The main difference for users is the Maven groupId. As the Spring Boot integration has shown so far, the jOOQ distributions can be switched with only little effort.

My intuition in that in this situation it's better that the corporation behind the closed parts handle the extension to cover all bases. Or accept that the closed product might not work with the open extension. But someone can build that bridge when we get there.

We'll definitely be very happy to help support any third party extension in this area, as we've done with Spring Boot.

Given that due to the nature of our dual licensing model, the open source version is enjoying much wider adoption than the closed editions, I think it's a good idea to get something working for the open source version first, and handle potential issues with other editions later.

[Sanne]

hi @lukaseder , great to see you here!

For sake of clarity, regarding this statement:

In jOOQ's case, we take a lot of care to make the various editions / distributions as binary compatible as possible.

That's a great premise but it's not enough when it comes to native-image compilation.

It's a little more of a burden than "just" maintining both source and binary compatibility, as any internal change you might have has possible impact on it no longer compiling to native (or - more subtly - have some impact on other modules). I'm sure you'll see what I mean when you get into the details. As a consequence, when any internal detail matters - it's hard for us to help unless all such code is OSS.

Of course these are in no means blocking concerns: I'm just clarifying so that you can make a better estimate of the maintenance effort. You'll likely need CI jobs running the native compiler, which implies higher than usual computation load.

I hope that works for you, personally I'd love to see a jOOQ extension.

[emmanuelbernard]

Thanks for the clarifications, @emmanuelbernard.

So in practice, while one can build and test an extension for the open part. This would either require a separate extension for the non open part or the extension would have to do magic to deal with the non open part, and do test on the non open part.

In jOOQ's case, we take a lot of care to make the various editions / distributions as binary compatible as possible. The main difference for users is the Maven groupId. As the Spring Boot integration has shown so far, the jOOQ distributions can be switched with only little effort.

Ah but they don't do GraalVM native image :) That's the case I was talking about as Sanne mentioned in his answer.

[lukaseder]

it's hard for us to help unless all such code is OSS.

Well, the OSS part is already a (big) start.

You'll likely need CI jobs running the native compiler, which implies higher than usual computation load.

Yes, we have that on our roadmap

[angrymango]

I updated the extension by @leotu to 1.5.2.Final.

• Currently proxies are disabled to make native compilation work. I'm not sure if this a big deal or not, I'm new to jOOQ. According to this https://www.jooq.org/doc/latest/manual/sql-execution/fetching/pojos/#N77C0C it looks like it only uses them for "fetching data into abstract classes or interfaces". Longer term I guess the proper solution would be to create the proxies at build time like the hibernate-orm extension does.

• I had to restrict the dialects to the open source ones

• The tests aren't working, haven't had time to investigate yet. The extension does work in a (basic) test project, both JVM and native.

Needs more testing, tests and documentation.

Code is here: https://github.com/angrymango/quarkus-ext-jooq

[lukaseder]

• Currently proxies are disabled to make native compilation work. I'm not sure if this a big deal or not, I'm new to jOOQ. According to this https://www.jooq.org/doc/latest/manual/sql-execution/fetching/pojos/#N77C0C it looks like it only uses them for "fetching data into abstract classes or interfaces".

Yes, jOOQ doesn't use proxies otherwise.

Longer term I guess the proper solution would be to create the proxies at build time like the hibernate-orm extension does.

I'm not sure if this is something we should invest a lot of time in on the jOOQ side. Most people who use record mapping will use ordinary "POJOs", perhaps with Lombok annotations, or even better: JEP 359 / 384 records.

The fact that we support proxies are more of a "because we can" feature.

[angrymango]

Thanks for confirming the proxy usage. It sounds like the best thing to do will be to leave them out of the native compilation for now. I'll focus on getting the tests working next.

[itboy87]

Hi @angrymango thanks for creating jooq extension. I'm new to quarkus so I don't have an idea how to add extensions that are not available by quarkus. Can you please tell me how I can use your extension in my project and also better if have some doc. I'm using Gradle.

[angrymango]

Hi @itboy87, currently as the extension is not released the only way to use it is via a local build. In Maven that would mean:

Warning - this should not be considered production ready!

git clone https://github.com/angrymango/quarkus-ext-jooq.git
cd quarkus-ext-jooq
mvn clean install

This will make the following dependency available:

<dependency>
    <groupId>io.quarkus.ext</groupId>
    <artifactId>quarkus-ext-jooq</artifactId>
    <version>1.0-SNAPSHOT</version>
</dependency>

Or for gradle something like:

compile 'io.quarkus.ext:quarkus-ext-jooq:1.0-SNAPSHOT'

You also need quarkus-agroal:

./gradlew addExtension --extensions="io.quarkus:quarkus-agroal"

The minimum required configuration after adding you chosen database drivers (in this case postgres) would be:

quarkus.jooq.dialect=PostgresSQL

quarkus.datasource.url=jdbc:postgresql:db_name
quarkus.datasource.driver=org.postgresql.Driver
quarkus.datasource.username=postgres
quarkus.datasource.password=********

The DSL context can then be injected with:

@Inject
DSLContext dsl; // default

[angrymango]

The unit tests are now working.

[itboy87]

@angrymango thanks I tried it build successfully but build native image failed. Can you please check it https://github.com/angrymango/quarkus-ext-jooq/issues/1

[laguiar]

Hi folks... any news on this topic? @angrymango how is going the extension development?

I'm starting a new project and seems that it's a big pain to not use JPA/Hibernate on Quarkus 😞 Really missing something like spring-data-jdbc/micronaut-data-jdbc.

[maxandersen]

Have you tried using statelesssession from an entity manager ? Would be curious to know what you are missing when using that ?

About jooq - it works with hibernate in jvm mode “as is” afaik - we can make it nicer/easier but should work or am I missing something ?

. Is it native mode explicitly you are after ?

[laguiar]

. Is it native mode explicitly you are after ?

@maxandersen thanks for replying... yes, going native is the major point for being using Quarkus, so choosing components that will fit this purpose is a must.

Is Jooq "as is" native compatible to use without a quarkus extension?

[maxandersen]

@maxandersen thanks for replying... yes, going native is the major point for being using Quarkus, so choosing components that will fit this purpose is a must.

ack - valid point; just that even without native you get speed and performance improvements - but of course native gives you way faster startup. Thus if minimal startup time is your goal native is what you want.

Is Jooq "as is" native compatible to use without a quarkus extension?

You tell me - I haven't tried and honestly this is where we really need help and participation to figure out. Try it out and see - maybe you can help get started on the jooq extension ?

the StatelessSession does work in native - and that does provide spring jdbc/data like API's (it even existed before those but its not that well advertised that hibernate does not only work with entities but also can do plain pojo's or raw row/map as you prefer)

[angrymango]

The extension really serves two purposes. It provides integration into Quarkus (configuration, dsl context injection) and it deals with making jOOQ compile natively via GraalVM.

Current status:

• The original extension created by @leotu https://github.com/leotu/quarkus-ext-jooq has been updated to Quarkus 1.6.1 and jOOQ 3.13.3. I haven't tested it yet.

• I'm trying to stay up to date with the version I copied from the original by @leotu, currently Quarkus 1.7.2 and jOOQ 3.13.4.

I'm using the extension for an experiment I'm currently working on. I'm not any closer at this point to releasing the extension. It's not great that there is duplicated effort going on. I started because I thought @leotu was not updating the extension, really we need one version that is kept current and is released as an official extension.

I'd just like to add that I started this without having really having used @jOOQ and the more I use it, the more I realise just how powerful and well designed it is. I'm loving it!

[laurentperez]

Hi @angrymango . Thanks for your efforts. If you do not consider your fork to be production ready, what's missing yet ? There have been no commits since August so, wondering if you're still using it or not

ty

[dominik42]

Hi @laurentperez , we also using this component for our near-to-production project. So far, we saw no problems using it with Quarkus 1.6.1 and JOOQ 3.14.1. We have bought the JOOQ Pro license cause the customer uses MS SQLServer. So the only modification I've made was to set the proper dialect SQLDialect.SQLSERVER within io.quarkus.ext.jooq.runtime.AbstractDslContextProducer. We've created an extra git repo in our gitlab instance and install/deploy the artifacts build by maven in a libs-like directory configured as mvn local-repo in our main project. Due to some build process obstacles outside the scope of JOOQ and this quarkus extension, we can't deploy into the maven artifacts repo (Nexus) but this should be the usually possible.

[gastaldi]

This extension sounds like a nice candidate to be in the Quarkiverse organization, where it would (hopefully) bring better visibility.

@angrymango if you are interested, I can set up a repository in there for you.

[angrymango]

Hi @gastaldi, I'm interested. I had a look at the required changes and they should be fine.

@dominik42 I'm not really sure of the best way to handle the pro features/dialects. Any suggestions?

[maxandersen]

Look in hibernate extension how dialect's are chosen. Should be good as inspiration.

[gastaldi]

@angrymango repository created in https://github.com/quarkiverse/quarkus-jooq and added you as the lead maintainer of this project.

Please follow the guidelines in https://github.com/quarkiverse/quarkiverse/wiki for naming your groupId, artifactId and package names.

Closing this issue now

[dominik42]

@angrymango hey, would be cool if we could maintain an official quarkus jooq extension... The only change regarding the use of PRO version of jooq is the follwing line in io.quarkus.ext.jooq.runtime.AbstractDslContextProducer:

} else if ("SQLServer".equalsIgnoreCase(sqlDialect) || "MSSQL".equalsIgnoreCase(sqlDialect)) { context = DSL.using(ds, SQLDialect.SQLSERVER);

The mentioned dialect SQLDialect.SQLSERVER comes from a jar of the pro package

I've a look to the suggested hibernate extension but I assume, that we need 2 extensions (free, pro) with dependencies similar the jooq packages itself. All quarkus extension related stuff we could move into a common/core package used by both extensions.

additional sources we may have to create a possibility for customization: io.quarkus.ext.jooq.runtime.JooqCustomContext

If you like, we could have a Zoom call or similar to kickoff .... is much simpler than writing on early saturday morning

[angrymango]

@dominik42 I've thought about it a bit more and the blocking issue I can see is that the jars are only available behind a login screen. That means they aren't accessible to the Github actions that build the extension. I'm not sure there is a way around it because they can't be publicly.hosted.

The best I can think of is setting the extension up to allow people to build it themselves with their own privately hosted versions of the pro jars.

Anyway, I've created this issue to track the progress https://github.com/quarkiverse/quarkus-jooq/issues/3