Database first is on the core of jOOQ’s design. jOOQ has been made primarily for traditional methods the database is at all times there and at all times has been and can by no means depart. It’s because we expect “information have mass”
This not solely interprets to shifting logic nearer to the info (see our earlier posts about the price of JDBC spherical journeys or producing vendor agnostic procedural logic), but additionally avoiding shifting information round between migrations (e.g. of RDBMS merchandise).
In comparison with how “heavy” information is, functions and UIs come and go. Talking of go, possibly you’ll substitute your whole Java code tomorrow for some go code. However you will preserve the database if it isn’t trivial.
With this in thoughts, jOOQ assumes you could have a pre-existing schema, which you handle with Flyway or Liquibase, and you then use jOOQ to reverse engineer your up to date schema utilizing the code generator.
The outdated days
Within the outdated days, establishing an Oracle occasion was very heavy, and in addition onerous. I bear in mind working at an organization the place we had shared growth and check cases. The schema was at all times in flux. We couldn’t assume a secure dev model.
As such, pointing the jOOQ code generator in direction of a stay database was once a little bit of a problem, which is why jOOQ provides various, connection-free code technology modes, together with:
- The
JPADatabase, if in case you have a pre-existing JPA entity based mostly meta mannequin. - The
XMLDatabase, if in case you have some type of XML model of your schema, which you’ll XSL rework to jOOQ’s format - The
DDLDatabase, which may interpret your DDL scripts, e.g. those you go to Flyway, or those produced by pg_dump. - The
LiquibaseDatabase, which simulates a Liquibase database migration and makes use of the simulated database output as a supply for meta info of the code generator
However the entire above have the identical limitation. You may’t actually use many vendor-specific options, corresponding to superior saved procedures, information varieties, and so on.
A contemporary strategy utilizing testcontainers
Ideally, until you’re supporting a number of RDBMS merchandise (most individuals don’t), you must work solely together with your manufacturing database product, say PostgreSQL.
Due to testcontainers.org, it’s very straightforward to programmatically, or configuratively, begin up a PostgreSQL occasion of any model in a Docker container. When you have a SQL script that incorporates your database, you’ll be able to provide it to the testcontainers JDBC URL, e.g. like this:
jdbc:tc:postgresql:13:///sakila?TC_TMPFS=/testtmpfs:rw&TC_INITSCRIPT=file:${basedir}/src/principal/assets/postgres-sakila-schema.sql
For extra info, see their docs about JDBC help. Now, add the testcontainers dependency in your undertaking classpath, e.g.
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>postgresql</artifactId>
</dependency>
And use the ContainerDatabaseDriver as a substitute of the particular PostgreSQL driver on your code technology configuration in jOOQ, e.g. when utilizing Maven:
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<executions>
<execution>
<id>java-generator</id>
<section>generate-sources</section>
<targets>
<objective>generate</objective>
</targets>
<configuration>
<jdbc>
<driver>
org.testcontainers.jdbc.ContainerDatabaseDriver
</driver>
<url>${db.url}</url>
<person>${db.username}</person>
<password>${db.password}</password>
</jdbc>
<generator>
<database>
<inputSchema>public</inputSchema>
</database>
<goal>
<packageName>org.jooq.instance.tc.db</packageName>
<listing>src/principal/java</listing>
</goal>
</generator>
</configuration>
</execution>
</executions>
<dependencies>
<!-- Junit appears a transitive dependency of testcontainers? -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<model>4.13.1</model>
</dependency>
</dependencies>
</plugin>
So simple as that! Take a look at the jOOQ-testcontainers-example for a runnable instance that makes use of testcontainers for code technology utilizing the above strategy.
Including database change administration
An actual world instance can be utilizing once more Flyway or Liquibase, and so on. to use a whole database migration to your PostgreSQL occasion within testcontainers, previous to producing code and/or operating your integration checks.
This simply barely complicates issues, however doesn’t produce any unattainable issues. As an alternative of making throwaway containers with a single TC_INITSCRIPT, you’ll now have to verify the next steps are executed consecutively in your construct by some means:
- A testcontainers occasion of your database is began
- A Flyway or Liquibase migration is run within that database
- The jOOQ code generator reverse engineers that database
- Optionally, your integration checks additionally reuse the database of that container
In fact you must integration check your code! However for the sake of this dialogue, that is perhaps an elective step, as you could have completely different preferences on find out how to run these checks, e.g. extra globally than simply for this module. However in our instance, let’s embody the checks.
You’ll find the total instance utilizing testcontainers/flyway/jOOQ right here.
Begin the testcontainers occasion
Sadly, testcontainers doesn’t ship any Maven / Gradle plugins but to invoke container lifecycle administration throughout a construct. I’ve created a characteristic request for that right here, which you must upvote: https://github.com/testcontainers/testcontainers-java/points/4397.
However we are able to simply assist ourselves through the use of the ever highly effective Maven escape hatch that’s the groovy-maven-plugin (the concepts are the identical for gradle):
<plugin>
<groupId>org.codehaus.gmaven</groupId>
<artifactId>groovy-maven-plugin</artifactId>
<model>2.1.1</model>
<executions>
<execution>
<!-- Begin the container in any section earlier than the precise code
technology is required, i.e. on the newest in
generate-sources -->
<section>generate-sources</section>
<targets>
<objective>execute</objective>
</targets>
<configuration>
<supply>
db = new org.testcontainers.containers.PostgreSQLContainer(
"postgres:newest")
.withUsername("${db.username}")
.withDatabaseName("postgres")
.withPassword("${db.password}");
db.begin();
// After you've got began the container, gather its generated
// JDBC URL (which incorporates a random port)
undertaking.properties.setProperty('db.url', db.getJdbcUrl());
</supply>
</configuration>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>postgresql</artifactId>
<model>1.15.2</model>
</dependency>
</dependencies>
</plugin>
So, that begins a container and retains it operating till the construct terminates. I received’t present a sleek shutdown, as a result of it’s not wanted for the instance, however you can implement that as effectively, in fact.
Now, migrate your database
The above database is empty. Now to run the migration, within the instance utilizing Flyway, however will probably be the identical factor with Liquibase.
<plugin>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-maven-plugin</artifactId>
<model>7.14.0</model>
<executions>
<execution>
<!-- We run the migration in the identical section, earlier than jOOQ's
code technology -->
<section>generate-sources</section>
<targets>
<objective>migrate</objective>
</targets>
<configuration>
<!-- This URL has been set by groovy, above -->
<url>${db.url}</url>
<person>${db.username}</person>
<password>${db.password}</password>
<places>
<location>
filesystem:src/principal/assets/db/migration
</location>
</places>
</configuration>
</execution>
</executions>
</plugin>
Add all the extra complexity of your migration on this configuration in the event you like. jOOQ wouldn’t know something about it.
Now, generate the code
Once more, nothing particular right here:
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<executions>
<execution>
<id>java-generator</id>
<!-- Similar section as above, however the earlier plugins have already
executed, so we're producing the db submit migration -->
<section>generate-sources</section>
<targets>
<objective>generate</objective>
</targets>
<configuration>
<jdbc>
<!-- Once more, this URL has been set by groovy, above -->
<url>${db.url}</url>
<person>${db.username}</person>
<password>${db.password}</password>
</jdbc>
<generator>
<database>
<inputSchema>public</inputSchema>
</database>
<goal>
<packageName>org.jooq.instance.db</packageName>
</goal>
</generator>
</configuration>
</execution>
</executions>
</plugin>
And eventually, optionally, integration check
If you wish to re-use the above container with migrated database additionally in your integration checks, you can simply go alongside the generated JDBC URL to the maven-surefire-plugin as follows:
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<configuration>
<systemPropertyVariables>
<!-- Once more, this URL has been set by groovy, above -->
<db.url>${db.url}</db.url>
<db.username>${db.username}</db.username>
<db.password>${db.password}</db.password>
</systemPropertyVariables>
</configuration>
</plugin>
There are lots of methods to attain the identical factor, that is one among them that works decently out of the field. You may try a full instance from github right here, and mess around with it:
https://github.com/jOOQ/jOOQ/tree/principal/jOOQ-examples/jOOQ-testcontainers-flyway-example
Extra about testcontainers
To be taught extra about testcontainers, see our interview with Richard North right here.
