Combining the power of Gradle, Flyway and JOOQ for seamless development with databases

Peter DaumApril 27th, 2016Last Updated: April 26th, 2016

0 399 4 minutes read

Hi there, in this post I will show you how easily you can combine the power of Gradle, with Flyway and JOOQ. At the end you will have a recipe for a build that automatically updates the models whenever you update your database.

The problem

When developing applications with database access, we usually face the problem, that we need to adopt our code to changes on the database schema or vice versa.

As we try not to repeat things, a great idea would be, that we can generated one of the both from the other. For example, generation database access and transfers objects from the actual database schema. As the database should be source of truth for schema data, the example looks like a good way to go. But more on this later.

Another challenge is, the connection to the database for generating the classes from the schema. Generating the schema on the fly before the class generating kicks in, feels like an attractive idea. This would free the developer from ensuring to have a database connection, when he regenerates the access classes. It also would be handy in the sense that he can easily bootstrap the environment on his local machine.

Let’s see how we can proceed with this requirements / challenges.

The tooling

A solution for the second challenge, could be Flyway, as it is a smart tool for managing and executing schema upgrades for a lot of relational databases. The development cycle with Flyway is (in very short) described as: Define the schema change in a SQL file, boot your application and flyway will take care of it.

No we just need a generator from the schema data to data access / transfer objects. I find here JOOQ very handy, as it is a library that provides a wrapper for relational database access via a powerful DSL where the SQL is visible in your code and not hidden behind some OR-mapper.

JOOQ comes along we the required code generator, so that we don’t have to write and change the nasty and sometimes cumbersome code over and over again.

As we now have made the tech/library – choices, let’s proceed with the

Gradle integration

I will introduce the new Gradle task generateJOOQ. It will take care of schema migration / creation with Flyway and code generation with JOOQ and should be executed before every compileJava run.

Let’s start with the necessary configurations and sourceSets. The classes will be generated into the source set generated which requires the following snippet to be available in the build file:

configurations {
	compile.extendsFrom generatedCompile
}
 
sourceSets {
	generated
	main {
		compileClasspath += generated.output
	}
}

The next step is now to make Gradle aware of both, Flyway and JOOQ by adding the necessary dependencies to the buildscript block of our build.gradle

buildscript {
	repositories {
		mavenCentral()
	}
 
	dependencies {
		classpath 'org.jooq:jooq-codegen:3.6.1'
		classpath 'com.h2database:h2:1.4.188'
		classpath 'org.flywaydb:flyway-core:3.2.1'
	}
}

The H2 database is chosen for the sake of this example, but you can use any database driver that can be used in conjunction with Flyway and JOOQ.

Now we must provide the configuration for JOOQ, so that it can generate the classes into the correct folder and classpath

def writer = new StringWriter()
new groovy.xml.MarkupBuilder(writer)
		.configuration('xmlns': 'http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd') {
	jdbc() {
		driver('org.h2.Driver')
		url("jdbc:h2:file:${project.projectDir}/build/generator/${project.name}")
		user('sa')
		password('')
	}
	generator() {
		database() {
		}
		generate() {
		}
		target() {
			packageName('com.coderskitchen.example')
			directory('src/generated/java')
		}
	}
}

As the next step, we can create the generateJOOQ task:

import org.flywaydb.core.Flyway
 
task generateJOOQ() {
	doLast {
		def Flyway flyway = new Flyway();
		flyway.setDataSource("jdbc:h2:file:${project.projectDir}/build/generator/${project.name}", "sa", null);
		flyway.setLocations("filesystem:${project.projectDir}/src/main/resources/db/migration")
		flyway.migrate();
		org.jooq.util.GenerationTool.generate(
				javax.xml.bind.JAXB.unmarshal(new StringReader(writer.toString()), org.jooq.util.jaxb.Configuration.class)
		)
	}
}

As you can see the migration files are taken from src/main/resources/db/migration. This has the advantage that, if we like / have to, can deliver the migration SQL files within our app, so that we can migrate the database in the production / staging environment directly. This is very useful, if the environment is not under our control, for example, when the application runs on external customers machines.

The dependencies between the Gradle tasks will be compileJava -> compileGeneratedJava -> generateJOOQ && clean:

compileGeneratedJava.dependsOn clean
compileGeneratedJava.dependsOn generateJOOQ
compileJava.dependsOn compileGeneratedJava

Finally we must add the JOOQ dependencies to the dependencies to the generatedCompile configuration, otherwise our project will not build:

dependencies {
	generatedCompile 'org.jooq:jooq:3.6.1',
			'org.jooq:jooq-meta:3.6.1',
			'org.jooq:jooq-codegen:3.6.1'
}

The whole minimal build file looks like this

buildscript {
    repositories {
        mavenCentral()
    }
 
    dependencies {
        classpath 'org.jooq:jooq-codegen:3.6.1'
        classpath 'com.h2database:h2:1.4.188'
        classpath 'org.flywaydb:flyway-core:3.2.1'
    }
}
 
import org.flywaydb.core.Flyway
 
group 'com.coderskitchen'
version '1.0.0'
 
apply plugin: 'java'
apply plugin: 'idea'
 
sourceCompatibility = 1.8
 
repositories {
    mavenCentral()
}
 
configurations {
    compile.extendsFrom generatedCompile
}
 
sourceSets {
    generated
    main {
        compileClasspath += generated.output
    }
}
 
dependencies {
    generatedCompile 'org.jooq:jooq:3.6.1',
        'org.jooq:jooq-meta:3.6.1',
        'org.jooq:jooq-codegen:3.6.1'
 
    testCompile group: 'junit', name: 'junit', version: '4.11'
}
 
def writer = new StringWriter()
new groovy.xml.MarkupBuilder(writer)
    .configuration('xmlns': 'http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd') {
    jdbc() {
        driver('org.h2.Driver')
        url("jdbc:h2:file:${project.projectDir}/build/generator/${project.name}")
        user('sa')
        password('')
    }
    generator() {
        database() {
        }
        generate() {
        }
        target() {
            packageName('com.coderskitchen.example')
            directory('src/generated/java')
        }
    }
}
 
task generateJOOQ() {
    doLast {
        def Flyway flyway = new Flyway();
        flyway.setDataSource("jdbc:h2:file:${project.projectDir}/build/generator/${project.name}", "sa", null);
        flyway.setLocations("filesystem:${project.projectDir}/src/main/resources/db/migration")
        flyway.migrate();
        org.jooq.util.GenerationTool.generate(
            javax.xml.bind.JAXB.unmarshal(new StringReader(writer.toString()), org.jooq.util.jaxb.Configuration.class)
        )
    }
}
 
compileGeneratedJava.dependsOn clean
compileGeneratedJava.dependsOn generateJOOQ
compileJava.dependsOn compileGeneratedJava

Finally we have all things together to try it out. Everything? From the setup point of view, this is correct, just some example is missing.

The running example

The example is about a simple sailing race calendar, written in two iterations

the first will just contain the table for the race dates with all the information about the place
the second iteration will migrate this schema to a more normalized one, where the place is separated from the date

I published the example at github (https://github.com/coders-kitchen/gradle-jooq-flyway-example) and has two tags matching the mentioned iterations.

Bye and have nice week

Reference:

Combining the power of Gradle, Flyway and JOOQ for seamless development with databases from our JCG partner Peter Daum at the Coders Kitchen blog.