Flyway and jOOQ for Unbeatable SQL Development Productivity

When performing database migrations, we at Data Geekery recommend using jOOQ with Flyway – Database Migrations Made Easy. In this post, we’re going to look into a simple way to get started with the two frameworks.

Philosophy

There are a variety of ways how jOOQ and Flyway could interact with each other in various development setups. In this tutorial we’re going to show just one variant of such framework team play – a variant that we find particularly compelling for most use cases.

The general philosophy and workflow behind the following approach can be summarised as this:

  • 1. Database increment
  • 2. Database migration
  • 3. Code re-generation
  • 4. Development

The four steps above can be repeated time and again, every time you need to modify something in your database. More concretely, let’s consider:

  • 1. Database increment – You need a new column in your database, so you write the necessary DDL in a Flyway script
  • 2. Database migration – This Flyway script is now part of your deliverable, which you can share with all developers who can migrate their databases with it, the next time they check out your change
  • 3. Code re-generation – Once the database is migrated, you regenerate all jOOQ artefacts (see code generation), locally
  • 4. Development – You continue developing your business logic, writing code against the udpated, generated database schema

0.1. Maven Project Configuration – Properties

The following properties are defined in our pom.xml, to be able to reuse them between plugin configurations:

<properties>
    <db.url>jdbc:h2:~/flyway-test</db.url>
    <db.username>sa</db.username>
</properties>

0.2. Maven Project Configuration – Dependencies

While jOOQ and Flyway could be used in standalone migration scripts, in this tutorial, we’ll be using Maven for the standard project setup. You will also find the source code of this tutorial on GitHub, and the full pom.xml file here.

These are the dependencies that we’re using in our Maven configuration:

<!-- We'll add the latest version of jOOQ 
     and our JDBC driver - in this case H2 -->
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.4.0</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.177</version>
</dependency>

<!-- For improved logging, we'll be using 
     log4j via slf4j to see what's going
     on during migration and code generation -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.16</version>
</dependency>
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.7.5</version>
</dependency>

<!-- To esnure our code is working, we're
     using JUnit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.11</version>
    <scope>test</scope>
</dependency>

0.3. Maven Project Configuration – Plugins

After the dependencies, let’s simply add the Flyway and jOOQ Maven plugins like so. The Flyway plugin:

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>3.0</version>

    <!-- Note that we're executing the Flyway
         plugin in the "generate-sources" phase -->
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>migrate</goal>
            </goals>
        </execution>
    </executions>

    <!-- Note that we need to prefix the db/migration
         path with filesystem: to prevent Flyway
         from looking for our migration scripts
         only on the classpath -->
    <configuration>
        <url>${db.url}</url>
        <user>${db.username}</user>
        <locations>
            <location>filesystem:src/main/resources/db/migration</location>
        </locations>
    </configuration>
</plugin>

The above Flyway Maven plugin configuration will read and execute all database migration scripts from src/main/resources/db/migrationprior to compiling Java source code. While the official Flyway documentation suggests that migrations be done in the compile phase, the jOOQ code generator relies on such migrations having been done prior to code generation.

After the Flyway plugin, we’ll add the jOOQ Maven Plugin. For more details, please refer to the manual’s section about the code generation configuration.

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${org.jooq.version}</version>

    <!-- The jOOQ code generation plugin is also 
         executed in the generate-sources phase,
         prior to compilation -->
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>

    <!-- This is a minimal working configuration. 
         See the manual's section about the code
         generator for more details -->
    <configuration>
        <jdbc>
            <url>${db.url}</url>
            <user>${db.username}</user>
        </jdbc>
        <generator>
            <database>
                <includes>.*</includes>
                <inputSchema>FLYWAY_TEST</inputSchema>
            </database>
            <target>
                <packageName>org.jooq.example.flyway.db.h2</packageName>
                <directory>target/generated-sources/jooq-h2</directory>
            </target>
        </generator>
    </configuration>
</plugin>

This configuration will now read the FLYWAY_TEST schema and reverse-engineer it into the target/generated-sources/jooq-h2 directory, and within that, into the org.jooq.example.flyway.db.h2 package.

1. Database increments

Now, when we start developing our database. For that, we’ll create database increment scripts, which we put into thesrc/main/resources/db/migration directory, as previously configured for the Flyway plugin. We’ll add these files:

  • V1__initialise_database.sql
  • V2__create_author_table.sql
  • V3__create_book_table_and_records.sql

These three scripts model our schema versions 1-3 (note the capital V!). Here are the scripts’ contents

-- V1__initialise_database.sql
DROP SCHEMA flyway_test IF EXISTS;

CREATE SCHEMA flyway_test;
-- V2__create_author_table.sql
CREATE SEQUENCE flyway_test.s_author_id START WITH 1;

CREATE TABLE flyway_test.author (
  id INT NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE,
  year_of_birth INT,
  address VARCHAR(50),

  CONSTRAINT pk_t_author PRIMARY KEY (ID)
);
-- V3__create_book_table_and_records.sql
CREATE TABLE flyway_test.book (
  id INT NOT NULL,
  author_id INT NOT NULL,
  title VARCHAR(400) NOT NULL,

  CONSTRAINT pk_t_book PRIMARY KEY (id),
  CONSTRAINT fk_t_book_author_id FOREIGN KEY (author_id) REFERENCES flyway_test.author(id)
);


INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'George', 'Orwell', '1903-06-25', 1903, null);
INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'Paulo', 'Coelho', '1947-08-24', 1947, null);

INSERT INTO flyway_test.book VALUES (1, 1, '1984');
INSERT INTO flyway_test.book VALUES (2, 1, 'Animal Farm');
INSERT INTO flyway_test.book VALUES (3, 2, 'O Alquimista');
INSERT INTO flyway_test.book VALUES (4, 2, 'Brida');

2. Database migration and 3. Code regeneration

The above three scripts are picked up by Flyway and executed in the order of the versions. This can be seen very simply by executing:

mvn clean install

And then observing the log output from Flyway…

[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] Database: jdbc:h2:~/flyway-test (H2 1.4)
[INFO] Validated 3 migrations (execution time 00:00.004s)
[INFO] Creating Metadata table: "PUBLIC"."schema_version"
[INFO] Current version of schema "PUBLIC": <>
[INFO] Migrating schema "PUBLIC" to version 1
[INFO] Migrating schema "PUBLIC" to version 2
[INFO] Migrating schema "PUBLIC" to version 3
[INFO] Successfully applied 3 migrations to schema "PUBLIC" (execution time 00:00.073s).

… and from jOOQ on the console:

[INFO] --- jooq-codegen-maven:3.5.0-SNAPSHOT:generate (default) @ jooq-flyway-example ---
[INFO] Using this configuration:
...
[INFO] Generating schemata      : Total: 1
[INFO] Generating schema        : FlywayTest.java
[INFO] ----------------------------------------------------------
[....]
[INFO] GENERATION FINISHED!     : Total: 337.576ms, +4.299ms

4. Development

Note that all of the previous steps are executed automatically, every time someone adds new migration scripts to the Maven module. For instance, a team member might have committed a new migration script, you check it out, rebuild and get the latest jOOQ-generated sources for your own development or integration-test database.

Now, that these steps are done, you can proceed writing your database queries. Imagine the following test case

import org.jooq.Result;
import org.jooq.impl.DSL;
import org.junit.Test;

import java.sql.DriverManager;

import static java.util.Arrays.asList;
import static org.jooq.example.flyway.db.h2.Tables.*;
import static org.junit.Assert.assertEquals;

public class AfterMigrationTest {

    @Test
    public void testQueryingAfterMigration() throws Exception {
        try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
            Result<?> result =
            DSL.using(c)
               .select(
                   AUTHOR.FIRST_NAME,
                   AUTHOR.LAST_NAME,
                   BOOK.ID,
                   BOOK.TITLE
               )
               .from(AUTHOR)
               .join(BOOK)
               .on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
               .orderBy(BOOK.ID.asc())
               .fetch();

            assertEquals(4, result.size());
            assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));
        }
    }
}

If you run the mvn clean install again, the above integration test will now compile and pass!

Reiterate

The power of this approach becomes clear once you start performing database modifications this way. Let’s assume that the French guy on our team prefers to have things his way (no offense intended ;-) ):

-- V4__le_french.sql
ALTER TABLE flyway_test.book 
  ALTER COLUMN title RENAME TO le_titre;

They check it in, you check out the new database migration script, run

mvn clean install

And then observe the log output:

[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] Database: jdbc:h2:~/flyway-test (H2 1.4)
[INFO] Validated 4 migrations (execution time 00:00.005s)
[INFO] Current version of schema "PUBLIC": 3
[INFO] Migrating schema "PUBLIC" to version 4
[INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.016s).

So far so good, but later on:

[ERROR] COMPILATION ERROR :
[INFO] -------------------------------------------------------------
[ERROR] C:\...\AfterMigrationTest.java:[24,19] error: cannot find symbol
[INFO] 1 error

When we go back to our Java integration test, we can immediately see that the TITLE column is still being referenced, but it no longer exists:

public class AfterMigrationTest {

    @Test
    public void testQueryingAfterMigration() throws Exception {
        try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
            Result<?> result =
            DSL.using(c)
               .select(
                   AUTHOR.FIRST_NAME,
                   AUTHOR.LAST_NAME,
                   BOOK.ID,
                   BOOK.TITLE
                   //   ^^^^^ This column no longer exists. 
                   //   We'll have to rename it to LE_TITRE
               )
               .from(AUTHOR)
               .join(BOOK)
               .on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
               .orderBy(BOOK.ID.asc())
               .fetch();

            assertEquals(4, result.size());
            assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));
        }
    }
}

Conclusion

This tutorial shows very easily how you can build a rock-solid development process using Flyway and jOOQ to prevent SQL-related errors very early in your development lifecycle – immediately at compile time, rather than in production!

Visit the Flyway website and the jOOQ website.

The Top 10 Productivity Booster Techs for Programmers

This is the list we’ve all been waiting for. The top 10 productivity booster techs for programmers that – once you’ve started using them – you can never do without them any longer.

Here it is:

1. Git

logo@2x Before, there were various version control systems. Better ones, worse ones. But somehow they all felt wrong in one way or another.

Came along Git (and GitHub, EGit). Once you’re using this miraculous tool, it’s hard to imagine that you’ll ever meet a better VCS again.

You’ve never used Git? Get started with this guide.

2. Stack Overflow

stackoverflow

No kidding. Have you ever googled for anything tech-related back in 2005? Or altavista’d something back in 2000? Or went to FidoNet in search for answers in 1995? It was horrible. The top results always consisted in boring forum discussions with lots of un-experts and script kiddies claiming wrong things.

These forums still exist, but they don’t turn up on page 1 of Google search results.

Today, any time you search for something, you’ll have 2-3 hits per top 10 from Stack Overflow. And chances are, you’ll look no further because those answers are 80% wonderful! That’s partially because of Stack Overflow’s cunning reputation system, but also partially because of Stack Overflow’s even more cunning SEO rewarding system. (I already got 98 announcer, 19 booster, and 5 publicist badges. Yay).

While Stack Overflow allows its more active user to pursue their vanity (see above ;-) ), all the other users without any accounts will continue to flock in, finding perfect answers and clicking on very relevant ads.

Thumbs up for Stack Overflow and their awesome business model.

3. Office 365

excel We’re a small startup. Keeping costs low is of the essence. With Office 365, we only pay around $120 per user for a full-fledged Office 2013 suite, integrated with Microsoft Onedrive, Sharepoint, Exchange, Access, and much more.

In other words, we get enterprise-quality office software for the price of what students used to pay, before.

And do note, Office 2013 is better than any other Microsoft (or Libre) Office suite before. While not a 100% Programmer thing, it’s still an awesome tool chain for a very competitive price.

4. IntelliJ

intellij

While Eclipse is great (and free), IntelliJ IDEA, and also phpStorm for those unfortunate enough to write PHP are just subtly better in almost every aspect of an IDE. You can try their free community edition any time, but beware, you probably won’t switch back. And then you probably won’t be able to evade the Ultimate edition for long ;-)

PHPStorm is the only way to survive working with PHP
PHPStorm is the only way to survive working with PHP

5. PostgreSQL

pg PostgreSQL claims to be the world’s most advanced Open Source database, and we think it’s also one of the most elegant, easy, standards-compliant databases. It is really the one database that makes working with SQL fun.

We believe that within a couple of years, there’s a real chance of PostgreSQL not only beating commercial databases in terms of syntax but also in terms of performance.

Any time you need a data storage system with a slight preference for SQL-based ones, just make PostgreSQL your default choice. You won’t be missing any feature in that database.

Let’s hear it for PostgreSQL.

6. Java

duke Java is almost 20 years old, but it’s still the #1 or #2 language on the TIOBE index (sharing ranks with C), for very good reasons:

  • It’s robust
  • It’s mature
  • It works everywhere (almost, really too bad it has never succeeded in the browser)
  • It runs on the best platform ever, the JVM
  • It is Open Source
  • It has millions of tools, libraries, extensions, and applications

While some languages may seem a bit more modern or sexy or geeky, Java has and will always rule them all in terms of popularity. It is a first choice and with Java 8, things have improved even more.

7. jOOQ

jooq-logo-black-100x80 Now, learning this from the jOOQ blog is really unexpected and a shocker, but we think that jOOQ fits right into this programmer’s must-have top-10 tool chain. Most jOOQ users out there have never returned back to pre-jOOQ tools, as they’ve found writing SQL in Java as simple as never before.

Given that we’ve had Java and PostgreSQL before, there’s only this one missing piece gluing the two together in the most sophisticated way.

And besides, no one wants to hack around with the JDBC API, these days, do they?

8. Less CSS

less When you try Less CSS for the first time, you’ll think that

Why isn’t CSS itself like this!?

And you’re right. It feels just like CSS the way it should have always been. All the things that you have always hated about CSS (repetitiveness, verbosity, complexity) are gone. And if you’re using phpStorm or some other JetBrains product (see above), you don’t even have to worry about compiling it to CSS.

As an old HTML-table lover who doesn’t care too much about HTML5, layout, and all that, using Less CSS makes me wonder if I should finally dare creating more fancy websites!

Never again without Less CSS.

9. jQuery

jqueryWhat Less CSS is for CSS, jQuery is for JavaScript. Heck, so many junior developers on Stack Overflow don’t even realise that jQuery is just a JavaScript library. They think it is the language, because we’ve grown to use it all over the place.

Yes, sometimes, jQuery can be overkill as is indicated by this slightly cynical website: http://vanilla-js.com

joox-logo-blackBut it helps so much abstracting all the DOM manipulation in a very fluent way. If only all libraries were written this way.

Do note that we’ve also published a similar library for Java, in case you’re interested in jQuery-style DOM XML manipulation. Along with Java 8’s new lambda expressions, manipulating the DOM becomes a piece of cake.

10. C8H10N4O2

764px-Caffeine.svgC8H10N4O2 (more commonly known as Caffeine) is probably the number one productivity booster for programmers.

Some may claim that there’s such a thing like the Ballmer Peak. That might be true, but the Caffeine Peak has been proven times and again.

Have Dilbert’s view on the matter:

http://dilbert.com/strips/comic/2006-10-19/

More productivity boosters

We’re certainly not the only ones believing that there is such a thing as a programmer-productivity-booster. Enjoy this alternative list by Troy Topnik here for more insight:

http://www.activestate.com/blog/2010/03/top-ten-list-productivity-boosters-programmers