Spring-boot: Spring boot, flyway and jooq integration

[Opalo]

Few months ago when I was starting a greenfield project I thought of using both flyway and jooq for DB schema management and ORM. It quickly turned out that while flyway has pretty cool integration with spring-boot - just add an appropriate dependency to the classpath and it works out-of-the-box - it is not the same with jooq.

With jooq I need to:

1. add a plugin to my build tool (gradle in this case) to generate the classes
2. move flyway to build tool as well - since on startup it's too late to migrate the schema
3. keep DB connection details (user, pass) in both build tool and the application itself - both require access

Ideally, it would be great to just put jooq and flyway on the classpath and both libraries just work together. To be honest I'm not even sure if this is possible, since the DB classes will be generated on-the-fly while application is getting up, they also need to be added to the classpath. What's more, setting up development environment might be problematic as well.

That said, I wonder if you have any plans or thoughts on it.

[philwebb]

@lukaseder and @michael-simons might have thoughts on this, but from my limited understanding jOOQ needs to generate the classes early so that you can refer to them in your own code.

If there's something we can do easily that will help we obviously will consider it, I just don't know what that could be.

[Opalo]

Thanks for prompt response @philwebb! That's what I thought when stating that I don't know it that's even possible ;)

[philwebb]

I'll tag this as waiting-for-feedback to see if there are any suggestions. The GitHub bot will close the issue after a few weeks if nobody comments.

[Opalo]

I've already talked to Lukas, so I guess he'll provide some input.

[michael-simons]

Hey everybody. I remember this question popping up some time ago already.

Flyway is - in contrast to jOOQ - not applicable to the runtime as well as too the build time in the same way.

jOOQ is applied as a code generator during build time. The result is a Java based schema as I call it. See this slide of mine: https://speakerdeck.com/michaelsimons/live-with-your-sql-fetish-and-choose-the-right-tool-for-the-job?slide=26.

The idea is that you code against this "schema", that is: Use the classes to write type safe SQL.

There's no point generating those classes at startup, apart from maybe hitting errors like a database that has been mutated after the Java schema has been generated. But than, I highly recommend using a migration tool during build and runtime anyway if your organization / project permits that.

Here's a video that shows my approach https://www.youtube.com/watch?v=4pwTd6NEuN0 and the code is here https://github.com/michael-simons/bootiful-databases. I have another demo for my upcoming Spring Boot Book here https://github.com/springbootbuch/database_examples.

No, I'm not affiliated with jOOQ, i just happen to like it a lot and the ideas behind it. Also, Lukas is great fun, too.

[philwebb]

Thanks @michael-simons! I think there's probably not much that we can do easily to improve the build-time experience, and there's not much point in generating jOOQ classes only a runtime. I'm going to close this issue for now. Thanks!

[Opalo]

@michael-simons thanks for the input! I thought - maybe not that it is pointless - but problematic and difficult to implement. Thanks for the provided materials. Will definitely look at it.

It seems that I'd need to change my deployment process to:

1. migrate
2. generate
3. bootRun

Is that correct? Also, you mentioned running migration during both build and runtime. In this particular example there would be gradle task for flyway migration and flyway would be also added to project classpath?

[michael-simons]

Hi @Opalo Yes, that would be recommendation that works best with a dedicated developer database, which is a good idea anyway.
This way you make sure

• Migrations actually work
• You working with jOOQ code that fits the schema
• Prod database can be at a different migration level, so you need to update that too!

There are some options to do this. Spring Boot even supports a differenet DB user for Flyway task that is allowed to do schema changes. Best thing for configuring that used in prod is getting his credentials from Vault or ConfigServer.

Regarding flyway and gradle: I have no experience there (yet).

[lukaseder]

Also, Lukas is great fun, too.

You just say that because you know I'm lurking and because you want another t-shirt. ;-)

While I agree that the jOOQ code generation might not make sense in production, it certainly does in CI (which includes integration testing). If someone removes a column, for instance, it would be great if CI already fails early because of a compilation error due to jOOQ code having been regenerated without that column, which is still referenced in code.

If these things are done manually, jOOQ can integrate with flyway quite easily:
https://blog.jooq.org/2014/06/25/flyway-and-jooq-for-unbeatable-sql-development-productivity

Essentially, if both jOOQ and flyway are being used by a project, there could be a jOOQ SchemaVersionProvider auto-configured, which checks flyway's "schema_version" table for any updates and re-generates the code only if such an update is available. This helps decrease build times for larger schemas. (note that identical classes aren't being re-written anyway to favour incremental compilation, but the in-memory code generation is still happening)

Now, if Spring Boot can reasonably make use of such an auto-integration, I don't know.

[ursjoss]

From what it's worth: One of my project also combines the infamous triple spring-boot, flyway and last but not least jOOQ. It took my quite a while until I had it all figured out, but now it's running as I'd like it to. The configuration is mostly on the build tool side, I don't see that much potential either for Spring Boot to make this easier.

Similar to (and also inspired by - thanks, guys!) the examples referred to by @lukaseder and @michael-simons, my approach has the following characteristics:

• during the maven build first the flyway-maven-plugin updates the DB schema, then the jooq-codegen-maven-plugin generates the classes that are included in and shipped with the artefact built with maven.
• the properties-maven-plugin helps to not have to configure the database connection details more than once.
• once the built artefact is started e.g. in production, flyway kicks in to update the db there (if necessary). If successful, the DB should match the classes that were generated by jOOQ during build time.
• I'm running the integration tests on the CI server on a dedicated database.

I'm using maven as build tool. No idea how you would achieve this with gradle or other build tools.

The project can be found here: https://github.com/ursjoss/scipamato. One of the relevant modules is e.g. scipamato-core-persistence-jooq

Cheers, Urs

[lukaseder]

Nice meeting you here, @ursjoss :)

I don't see that much potential either for Spring Boot to make this easier.

But do you see potential for either the flyway or the jOOQ Maven / Gradle plugins?

[ursjoss]

Nice meeting you here, @ursjoss :)

Same, Lukas :-)

IMHO all parts do what they should:

• the flyway-maven-plugin manages the databas migrations well. We just need to instruct it it do it's job before the jOOQ maven plugin kicks in (in the generate-sources phase).
• the jooq-codegen-maven plugin does what it should and is responsible for.
• Spring Boot is doing a nice job in integrating flyway and jOOQ during runtime - given that the build tool manages the dependency correctly (I just removed some jOOQ configuration classes on the week-end I had from the beginning of my ventures. I realized they are not necessary).

The tedious stuff is just aligning all those peaces in maven (or whatever build tool).

The only thing I could think of is to encapsulate this maven-wizardry into some encapsulated pom/bom whatever maven 'unit'. Not sure if this is possible for plugin configurations...

[Opalo]

@ursjoss could you please elaborate on how deployment is done? Do you deploy a built artifact (jar file) which has flyway in it's classpath and makes the schema migration? Or is migration done from the build tool?

[ursjoss]

@Opalo I am distributing the final fat jar containing everything including flyway. Once the application starts up, flyway kicks in, reads the flyway properties (url, user, password) from the application.properties file and checks if there are migrations that have not yet been applied. If so it runs them in time for the actual application to be able to work with the precompiled jOOQ classes - that have been pre-built by maven based on the migrated environement in my local database - and the the updated db that should now match the jOOQ classes.

For the flyway integration, see https://flywaydb.org/documentation/plugins/springboot

Some trickery was necessary in my case to avoid running flyway during integration tests on my developer machine but still run it during continuous integration. But spring boot does a great job with the whole property resolution with different application.property files (in my case involving spring profiles).

My documentation doesn't cover these details in the context of your question, but you might find something interesting on these pages:

• https://github.com/ursjoss/scipamato/wiki/Developer-Information
• https://github.com/ursjoss/scipamato/wiki/Operations

and maybe in the project itself.

HTH - cheers
Urs