Database first is on the core of jOOQ’s design. jOOQ has been made primarily for traditional programs the database is all the time there and all the time has been and can by no means go away. It is because we expect “knowledge have mass”
This not solely interprets to shifting logic nearer to the information (see our earlier posts about the price of JDBC spherical journeys or producing vendor agnostic procedural logic), but in addition avoiding shifting knowledge round between migrations (e.g. of RDBMS merchandise).
In comparison with how “heavy” knowledge 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’ve got a pre-existing schema, which you handle with Flyway or Liquibase, and then you definitely use jOOQ to reverse engineer your up to date schema utilizing the code generator.
The previous days
Within the previous days, organising an Oracle occasion was very heavy, and in addition onerous. I keep in mind working at an organization the place we had shared growth and check situations. The schema was all the time in flux. We couldn’t assume a secure dev model.
As such, pointing the jOOQ code generator in the direction of a dwell database was once a little bit of a problem, which is why jOOQ affords different, connection-free code era modes, together with:
- The
JPADatabase, if in case you have a pre-existing JPA entity primarily based meta mannequin. - The
XMLDatabase, if in case you have some type of XML model of your schema, which you’ll be able to XSL rework to jOOQ’s format - The
DDLDatabase, which may interpret your DDL scripts, e.g. those you cross 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 all the above have the identical limitation. You possibly can’t actually use many vendor-specific options, equivalent to superior saved procedures, knowledge varieties, and many others.
A contemporary strategy utilizing testcontainers
Ideally, until you’re supporting a number of RDBMS merchandise (most individuals don’t), it is best to work solely together with your manufacturing database product, say PostgreSQL.
Because of testcontainers.org, it’s very straightforward to programmatically, or configuratively, begin up a PostgreSQL occasion of any model in a Docker container. You probably have a SQL script that accommodates your database, you possibly can 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 assist. Now, add the testcontainers dependency in your venture classpath, e.g.
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>postgresql</artifactId>
</dependency>
And use the ContainerDatabaseDriver as an alternative of the particular PostgreSQL driver in your code era configuration in jOOQ, e.g. when utilizing Maven:
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<executions>
<execution>
<id>java-generator</id>
<part>generate-sources</part>
<targets>
<aim>generate</aim>
</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! Try the jOOQ-testcontainers-example for a runnable instance that makes use of testcontainers for code era utilizing the above strategy.
Including database change administration
An actual world instance can be utilizing once more Flyway or Liquibase, and many others. to use a whole database migration to your PostgreSQL occasion within testcontainers, previous to producing code and/or working your integration assessments.
This simply barely complicates issues, however doesn’t produce any unattainable issues. As a substitute of making throwaway containers with a single TC_INITSCRIPT, you’ll now have to verify the next steps are executed consecutively in your construct someway:
- 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 assessments additionally reuse the database of that container
In fact it is best to integration check your code! However for the sake of this dialogue, that is likely to be an non-compulsory step, as you might have totally different preferences on the right way to run these assessments, e.g. extra globally than simply for this module. However in our instance, let’s embody the assessments.
You could 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 it is best to upvote: https://github.com/testcontainers/testcontainers-java/points/4397.
However we will 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 part earlier than the precise code
era is required, i.e. on the newest in
generate-sources -->
<part>generate-sources</part>
<targets>
<aim>execute</aim>
</targets>
<configuration>
<supply>
db = new org.testcontainers.containers.PostgreSQLContainer(
"postgres:newest")
.withUsername("${db.username}")
.withDatabaseName("postgres")
.withPassword("${db.password}");
db.begin();
// After you have began the container, gather its generated
// JDBC URL (which accommodates a random port)
venture.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 working till the construct terminates. I received’t present a sleek shutdown, as a result of it’s not wanted for the instance, however you might implement that as nicely, in fact.
Now, migrate your database
The above database is empty. Now to run the migration, within the instance utilizing Flyway, however it is going to 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 part, earlier than jOOQ's
code era -->
<part>generate-sources</part>
<targets>
<aim>migrate</aim>
</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 should 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>
<!-- Identical part as above, however the earlier plugins have already
executed, so we're producing the db publish migration -->
<part>generate-sources</part>
<targets>
<aim>generate</aim>
</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 at last, optionally, integration check
If you wish to re-use the above container with migrated database additionally in your integration assessments, you might simply cross 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 numerous methods to attain the identical factor, that is one in all them that works decently out of the field. You possibly can 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 study extra about testcontainers, see our interview with Richard North right here.
