JOOQ, Flyway, Spring Boot and Gradle

When I came across each of the frameworks/tools mentioned above, I read about each of them individually. It took me sometime to get them all working together. Especially, on how this could fit into a development workflow where a CI and different test environments are involved. If you understand each of these technologies already, you might want to skip down below, where I have listed a development workflow. Else, just follow the blog.

JOOQ

Jooq allows you to write SQL in java. All SQL clauses such as Select, Insert, Where are all represented as fluent Java functions. So you could quite literally paste SQL into your Java code and instead of it being a quoted string it will actually be ‘compiling’ Java code.

The below is taken from the JOOQ website.

SELECT * FROM BOOK
   WHERE BOOK.PUBLISHED_IN = 2011
ORDER BY BOOK.TITLE
create.selectFrom(BOOK)
      .where(BOOK.PUBLISHED_IN.eq(2011))
      .orderBy(BOOK.TITLE)

When I came across JOOQ it was like a breath of fresh air. For years I had been struggling with ORMs like Hibernate. Hibernate is great at what it does, don’t get me wrong. But I don’t agree with entire premise that Hibernate or any other ORM is built on.

Hibernate or any good ORM should abstract away the database from a developer and you should have to deal only with Objects.

First off, you cannot abstract away a database! Its always going to be there. It has powerful features which includes a powerful query language that has been built specifically to work with it. I am surprised to the extent that developers are willing to go to NOT write SQL.

And yet, even though Hibernate abstracts away the database from you, people often find themselves writing Named Queries, JoinColumns with SQL in them, or sometimes plain sql-queries when nothing else works. You have to deal with n+1 select problems, deal with objects in the cache and understand when an object gets dirty.

If you have ever seen the SQL monstrosity that Hibernate generates, you will begin to appreciate what JOOQ provides.

What JOOQ gives you is full control over your SQL. It provides compile time error checking of your SQL. Now that even your database cannot give you. All the SQL clauses are represented as fluent functions and the tables and columns are Java classes / properties that can either be generated from your database or written by you. Finally when you execute the SQL its going to be exactly what you wrote in Java.

For more information, take a look at the Getting Started guide for JOOQ here: http://www.jooq.org/doc/3.8/manual/getting-started/

Flyway

Flyway allows you to keep the structure of your DB and your application in sync. The traditional way of applying database changes was manually applying it before your app is deployed and then deploy the app to your servers. With microservices slowly becoming a de-facto standard you would use for developing enterprise software, it makes sense to automate DB migrations as well. By DB migrations I mean, applying changes to your DB structure in line with your app.

Flyway automates that. It makes it really easy to apply any sort of database migration.

All you need to do is create a .sql file with your DDL (or DML), basically a file with multiple sql statements separated by a ‘;’, just like you would create any sql script.

Filename: V1__create_users_orders.sql
create table users (
    emailid varchar(250) not null primary key,
    password_hash varchar(500) not null,
    password_validated boolean default false,
    name varchar(500),
    address json,
    practice_name varchar(500),
    primary_user boolean
);
create table orders (
    order_id VARCHAR(36) not null PRIMARY KEY,
    emailid VARCHAR(250),
    .....
)

There is a file naming convention.

It needs to start with a V, followed by a version number like a running serial number, then 2 underscores and finally a descriptive name of the file. You would place this file in a db/migration folder on your classpath. When your app starts up, you can make a call to Flyway’s API such that the DB migration sql files in the db/migration folder is executed first. Once that is executed your app can then be available for use.

What Flyway does, is create a table called schema_version. It puts an entry in that table every time a sql file is executed. It records the version number along with the hash of the file. The next time your app starts up, it will only execute newer sql files you have placed in the db/migration folder.

If you have a more complicated DB migration step, which cannot be realistically done via a SQL file, such as, you need to read data from a table, modify it in a complicated way before inserting it elsewhere, you also have a way of using Java + JDBC as an alternative to the SQL file. You could have V1, V2 and V3 as SQL files. Then a V4 could be a Java file and so on.

Spring Boot

Spring boot integrates really well with both Jooq and Flyway. It supports flyway out of the box. All you need to do is include a dependency

compile('org.flywaydb:flyway-core:4.0.3')

If you create a datasource in your application.yml like the below:

#Database Configuration
spring:

  datasource:
    url: jdbc:postgresql://localhost:7654/csmartci
    username: csmartuser
    password: csmartuser

Spring boot now finds the flyway dependency added and invokes it when it starts up. All you need to do is include the DB migration scripts under the db/migration folder. You can always customise all of this. Look at the spring boot properties, there is a separate section for the flyway configuration.

Now - JOOQ, Flyway, Spring Boot and Gradle

If you are building your Spring boot app with Gradle, there are plugins for both Jooq and Flyway that you can use. The build file could look like this:

buildscript {
   ext {
      springBootVersion = '1.4.1.RELEASE'
   }
   repositories {
      mavenCentral()
      jcenter()
      maven {
         url "https://plugins.gradle.org/m2/"
      }
   }
   dependencies {
      classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
      classpath 'org.postgresql:postgresql:9.4.1211.jre7'
      classpath 'nu.studer:gradle-jooq-plugin:1.0.5'
      classpath "gradle.plugin.com.boxfuse.client:flyway-release:4.0.3"
   }
}

apply plugin: 'java'
apply plugin: 'idea'
apply plugin: 'spring-boot'
apply plugin: 'nu.studer.jooq'
apply plugin: "org.flywaydb.flyway"

sourceCompatibility = 1.8
targetCompatibility = 1.8

repositories {
   mavenCentral()
}

dependencies {
   compile('org.springframework.boot:spring-boot-starter-jooq')
   compile('org.springframework.boot:spring-boot-starter-security')
   compile('org.springframework.boot:spring-boot-starter-web')
   compile('org.postgresql:postgresql:9.4.1211.jre7')
   compile('org.flywaydb:flyway-core:4.0.3')
   testCompile('org.springframework.boot:spring-boot-starter-test')
}

jooq {
   csmart(sourceSets.main) {
      jdbc {
         driver = 'org.postgresql.Driver'
         url = 'jdbc:postgresql://localhost:7654/csmartci'
         user = 'csmartuser'
         password = 'csmartuser'
         schema = 'public'
      }
      generator {
         name = 'org.jooq.util.DefaultGenerator'
         strategy {
            name = 'org.jooq.util.DefaultGeneratorStrategy'
         }
         database {
            name = 'org.jooq.util.postgres.PostgresDatabase'
            inputSchema = 'public'
            customTypes {
               customType {
                  name = 'com.fasterxml.jackson.databind.JsonNode'
                  converter = 'csmart.api.config.db.PostgresJSONJacksonJsonNodeBinding'
               }
            }
            forcedTypes {
               forcedType {
                  name = 'com.fasterxml.jackson.databind.JsonNode'
                  expression = 'public\\.users\\.address'
               }
            }
         }
         generate {
            relations = true
            deprecated = false
            records = true
            immutablePojos = false
            fluentSetters = true
         }
         target {
            packageName = 'csmart.db.gen'
            directory = 'src/main/generated/java'
         }
      }
   }
}

flyway {
   url = 'jdbc:postgresql://localhost:7654/csmartci'
   user = 'csmartuser'
   password = 'csmartuser'
   schemas = ['public']
   locations = ["filesystem:$project.projectDir/src/main/resources/db/migration"]
}

generateCsmartJooqSchemaSource.dependsOn flywayMigrate
test.dependsOn flywayClean

The jooq{} part describes how the code should be generated from the DB metadata. For more information look at the JOOQ codegen here — http://www.jooq.org/doc/3.5/manual/code-generation/

The flyway{} part describes where flyway should connect and execute the DB migrations.

The last 2 lines of the script says that the Jooq generation depends on Flyway running the migrations scripts first. Once the DB scripts are applied, then JOOQ will have the latest schema to generate classes from.

The last line says that the gradle test will depend on flywayClean, viz. flyway will clean up your schema for you, such as, drop all the tables before your test runs. When your test runs, specifically your DB integration tests, it will bring up your app, which will instruct flyway to recreate the schema from your scripts. This ensures that your tests are running against a clean DB instance.

Let’s take a look at a typical development workflow:

1. You need to install Postgres (or your database) locally on your development machine. Never rely on a central DB for your local development. Its way too much hassle with people stepping on each other always. With docker now, its even more easy to get a local instance of your db.
2. Write your DB script and place it under the folder db/migration. Typically under resources of a spring boot app.
3. Run a gradle clean build. This will make flyway run and apply the DB script to your DB. It will then run JOOQ and generate your classes for you.
4. Now you can do your development. Write DB integration tests without troubling yourself to clean up the data inserted as a part of the test. (ideally you would as a best practice). But you don’t have to, as the tests will run on a clean schema always.
5. Check in your code including your migration sql file. This will get shipped with your app jar. Do not check in the generated JOOQ code as your CI will generate this for you.
6. Your CI will then run gradle clean build as well. But against a separate CI DB instance. The same steps you did locally, will happen in the CI server. your CI server will then package your app with the generated JOOQ code and DB sql files.
7. Deploy your app onto your production server.
8. When your app starts up, flyway will first execute your DB scripts before your spring boot app is available for incoming requests to be served.

I have published an example of the code here:

Sethuraman/JooqFlywaySpringBoot

Hope this helps. Reach out to me via comments below.