info.michael-simons.eu

So you learned how to setup a Spring Boot project that supports jOOQ in this post and then created a Docker container on your local host that provides you with an Oracle Database instance that you’re ready to use in your project but the instance doesn’t have any tables, data or procedures yet.

You could login into the database via SQL*Plus and create tables manually. And so would your colleague on her machine.

It doesn’t matter if you use an ORM or a code generator at this point, the day you’re gonna move to your production database, stuff will break. One of your team forgot to apply his scripts to production and you are screwed. Either your JPA mappings are broken, SQL queries fail or jOOQs generated code doesn’t match the database.

In this part of the series you’ll learn how to solve this challenge and which workflow we use for database centric applications with jOOQ.

Content

1. Database migrations with Flyway and Maven
2. Generation of a Java based database schema
3. Database migrations revised
4. Summary

Database migrations with Flyway and Maven

jOOQ has a code generator that provides a Java based schema of your database. Although you can use jOOQ without a Java based schema, you would miss a lot of the goodies, especially compile time checking of queries. jOOQ should be setup in such a way that the code generator runs during the build process, reads the database and generates the Java based schema.

So it’s really important that the database schema is always at known state for the generator to work reliable.

We already did the first step in the previous post: Each developer has it’s own development database. Next step is migrations:

In software engineering, schema migration (also database migration, database change management[1][2]) refers to the management of incremental, reversible changes to relational database schemas. A schema migration is performed on a database whenever it is necessary to update or revert that database’s schema to some newer or older version.

We use with great success Flyway by Boxfuse in several projects and not only in jOOQ projects (see for example this post about the Euregio JUGs backend which is Hibernate based).

Flyway doesn’t offer an easy way of rolling back migrations but has the advantage that migrations can be written in plain SQL. Liquibase on the other hand offers rollbacks but the migrations are – in my opinion – much harder to write.

(c) FlywayDB

There’s one big difference here: In a JPA / Hibernate project I’d only use Flyway during runtime. To make it work with jOOQ it has to be run as a build time solution. In my DOAG K+A demo it’s done like this inside the maven build descriptor:

<build>
	<plugins>
		<plugin>
			<groupId>org.flywaydb</groupId>
			<artifactId>flyway-maven-plugin</artifactId>
			<version>${flyway.version}</version>
			<executions>
				<execution>
					<phase>generate-sources</phase>
					<goals>
						<goal>migrate</goal>
					</goals>
				</execution>
			</executions>			
			<configuration>
				<url>${db.url}</url>
				<user>${db.username}</user>
				<password>${db.password}</password>
				<locations>
					<location>filesystem:src/main/resources/db/migration</location>
				</locations>
			</configuration>
		</plugin>
	</plugins>
</build>

Things to explain: Flyway is run in the “generate-sources” phase due to the fact that I want to make sure it runs before the jOOQ code generator which must be run during that phase so that I can use the Java based schema. You’ll hopefully recognize the Maven properties from the last post containing the database configuration. This ensures that the same database is used for migration as for development.

Flyway usually looks on the classpath for migrations if you omit a prefix, which is not what I want, so I’ll explicitly use the filesystem: prefix.

So, you do basically a mvn clean package and the migration runs. What does it actually do in my demo? I have 4 scripts creating tables and another bunch adding data. The functional topic for the demo is based on this schema:

This is from Daily Fratze providing an API similar to Last.FM for scrobbling and storing music play data into very few tables: A track table containing all tracks and a play table containing entries for every time a track was played.

No big data, no NoSQL here, just plain relational data. You can come very far with those tables, generating impressive queries but we’ll come later to that.

Running the migration will look like this:

[INFO] Flyway 4.0.3 by Boxfuse
[INFO] Database: jdbc:oracle:thin:@//localhost:1521/ORCLPDB1 (Oracle 12.1)
[INFO] Successfully validated 8 migrations (execution time 00:00.089s)
[INFO] Creating Metadata table: "DOAG2016"."schema_version"
[INFO] Current version of schema "DOAG2016": << Empty Schema >>
[INFO] Migrating schema "DOAG2016" to version 1 - Create table artists
[INFO] Migrating schema "DOAG2016" to version 2 - Create table genres
[INFO] Migrating schema "DOAG2016" to version 3 - Create table tracks
[INFO] Migrating schema "DOAG2016" to version 4 - Create table plays
[INFO] Migrating schema "DOAG2016" to version 5 - Insert artists data
[INFO] Migrating schema "DOAG2016" to version 6 - Insert genres data
[INFO] Migrating schema "DOAG2016" to version 7 - Insert tracks data
[INFO] Migrating schema "DOAG2016" to version 8 - Insert plays data
[INFO] Successfully applied 8 migrations to schema "DOAG2016" (execution time 00:14.130s).

In the context of the previous post an alternative option for doing migrations would be to hook into the “initdb.sh” script and do more stuff there. This can be very useful if you have a “legacy” database baseline that you can’t mark as version 1 for reasons.

Generation of a Java based database schema

This is how the jOOQ code generator works:

To fully utilize the jOOQ DSL and to be able to write a query like

TracksRecord track = create.selectFrom(TRACKS).where(TRACKS.NAME.eq("Looking for freedom")).fetchOne();

you have to generate the Java Based schema. You can still use jOOQ but than your queries won’t be type safe in regard of table, column, procedure names and datatypes. The jOOQ documentation has a large chapter for configuring the code generator (see chapter 6), but I’ll keep it simple here. From my pom.xml

<build>
	<plugins>	
		<plugin>
			<groupId>org.jooq.pro</groupId>
			<artifactId>jooq-codegen-maven</artifactId>
			<version>${jooq.version}</version>
			<executions>
				<execution>
					<phase>generate-sources</phase>
					<goals>
						<goal>generate</goal>
					</goals>
				</execution>
			</executions>
			<configuration>
				<jdbc>
					<driver>oracle.jdbc.OracleDriver</driver>
					<url>${db.url}</url>
					<user>${db.username}</user>
					<password>${db.password}</password>
				</jdbc>
				<generator>
					<database>
						<name>org.jooq.util.oracle.OracleDatabase</name>
						<schemaVersionProvider>SELECT :schema_name || '_' || MAX("version") FROM "schema_version"</schemaVersionProvider>
						<includes>.*</includes>
						<excludes>schema_version</excludes>
						<schemata>
							<schema>
								<inputSchema>${db.schema}</inputSchema>
								<outputSchemaToDefault>true</outputSchemaToDefault>
							</schema>
						</schemata>
					</database>
					<generate>
						<deprecated>false</deprecated>
						<pojos>true</pojos>
						<immutablePojos>true</immutablePojos>
					</generate>
					<target>
						<packageName>ac.simons.doag2016.db</packageName>
						<directory>target/generated-sources/jooq</directory>
					</target>
				</generator>
			</configuration>
		</plugin>
	</plugins>
</build>

This runs in the same phase as Flyway but ordered after the migration. So I can use the Flyway schema_version table to determine if a new generation is necessary. Apart from that, important is here again the reuse of database properties for the connection as well as the target directory, placed under “target/generated-sources/jooq” so that is recognized by the Maven lifecycle.

Please take note that <groupId>org.jooq.pro</groupId> group id everywhere as I run jOOQ against an Oracle Enterprise Edition.

With that in place, you can use the generated records with your DSL context like so:

import static ac.simons.doag2016.db.Tables.ARTISTS;
 
final DSLContext create;
create
        .select()
        .from(ARTISTS)
        .orderBy(ARTISTS.ARTIST.asc())
        .fetch();

(You wouldn’t have to use jOOQ for a query like that, we’ll cover awesome queries later).

Database migrations revised

So far those migrations have been applied during build time. But in the end, the application must go into production and that seamless and automatic.

You have merged your changes into master, fixed conflicts if any and also combined your migrations and those of your colleagues. If conflicting migrations lead to conflicts in the jOOQ code you’ll notice that most of the time already during compile time and if not, I assume there’s an integration test somewhere.

But how to migrate that database or your target database? Actually, that is pretty easy as the premise of this project is “Spring Boot + jOOQ”. Spring Boot does support Flyway migrations out of the box. The only thing you have to do is add Flyway as a runtime dependency if you put your migrations in the path mentioned above:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

Spring Boot fires up the flyway migration inside the exact same database connection as the application has before the context starts. This way you make sure that the target database is on the same version as the one you used when creating your jOOQ objects.

In my demo its the same database anyway, but you can change that in no time.

Summary

So far, after three posts, you have the following situation

• A consistent Spring Boot project supporting jOOQ that can be effective and efficiently be deployed
• A local database instance for developing, unique to each developer so that you won’t interfere with each other
• A consistent way of database migrations each time you build the app
• The guarantee that the jOOQ code generator always runs against the correct version
• And further, that the target database is also at the same version

We have been been using this approach now for quite some time now at ENERKO INFORMATIK and are quite content with it.

In the next post, learn how to write awesome SQL queries with jOOQ.