How to use jOOQ with Testcontainers and, Flyway

When using a database the question is how to manage the versions of the schema and how to use the database in testing. Container technologies became a defacto standard. Why not use containers for development and your database?

Testcontainers is a Java library that supports JUnit tests, providing lightweight, throwaway instances of common databases, Selenium web browsers, or anything else that can run in a Docker container.
(Source: www.testcontainers.org)

Sounds perfect, right? And what about database versioning?

Flyway. Version control for your database. Robust schema evolution across all environments. With ease, pleasure, and plain SQL. (Source: flywaydb.org)

Awesome! Now let’s have a look at how we can use these two technologies in combination with jOOQ to generate the Java code and test the application. The example project uses MariaDB but would work with any database. You’ll find the source code on GitHub: github.com/simasch/jooq-mariadb

The Build

The example uses Maven as the build tool. And the process is as follows

  1. Start MariaDB
    A Testcontainer with MariaDB is started using the Groovy plugin
  2. Execute Flyway migrations
    Flyway migrations are applied to the MariaDB
  3. Generate jOOQ Code
    jOOQ generates code from the MariaDB objects
  4. Compile
    Code and generated classes are compiled
  5. Run Tests
    For each test, Testcontainers starts a MariaDB container

Bellow, you can see how the Maven plugins are configured.

<!-- Start Testcontainer -->
<plugin>
    <groupId>org.codehaus.gmaven</groupId>
    <artifactId>groovy-maven-plugin</artifactId>
    <version>2.1.1</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>execute</goal>
            </goals>
            <configuration>
                <source>
                    db = new org.testcontainers.containers.MariaDBContainer("mariadb:10.7.3")
                            .withUsername("${db.username}")
                            .withDatabaseName("${db.database}")
                            .withPassword("${db.password}")
                    db.start()
                    project.properties.setProperty('db.url', db.getJdbcUrl())
                </source>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>mariadb</artifactId>
            <version>${testcontainers.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>${mariadb.version}</version>
        </dependency>
    </dependencies>
</plugin>
<!-- Migrate schema -->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>7.14.0</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>migrate</goal>
            </goals>
            <configuration>
                <url>${db.url}</url>
                <user>${db.username}</user>
                <password>${db.password}</password>
                <locations>
                    <location>filesystem:src/main/resources/db/migration</location>
                </locations>
            </configuration>
        </execution>
    </executions>
</plugin>
<!-- Generate jOOQ code -->
<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <executions>
        <execution>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <jdbc>
            <driver>${db.driver}</driver>
            <url>${db.url}</url>
            <user>${db.username}</user>
            <password>${db.password}</password>
        </jdbc>
        <generator>
            <database>
                <inputSchema>sakila</inputSchema>
                <forcedTypes>
                    <forcedType>
                        <name>BOOLEAN</name>
                        <includeTypes>(?i:TINYINT\(1\))</includeTypes>
                    </forcedType>
                </forcedTypes>
            </database>
            <target>
                <packageName>ch.martinelli.sakila</packageName>
            </target>
            <generate>
                <pojos>true</pojos>
                <pojosToString>false</pojosToString>
                <daos>true</daos>
            </generate>
        </generator>
    </configuration>
</plugin>

That’s it. Easy, isn’t it?

If you are new to jOOQ don’t miss my introduction video:

Visualization of the Message Flow between Business Functions with Vaadin and Neo4j

The Project

Analyzing and visualizing the message flow between business functions was the goal of my current project. At first, we considered using a UML tool for this job, but we came to the conclusion that it might not be as flexible as we need it to be. Finally, I’ve got the assignment to create a custom web application.

Since business functions and messages are related to each other, it made sense to represent them as a graph. That’s why I chose Neo4j as the database. Now the question was how to manage and visualize the graph. As I’m experienced with the Vaadin framework I want to use it also in this project. 

Vaadin has a lot of great UI components but in my case, there was no match. Finally, I’ve found vis.js. The network diagram seemed appropriate for the visualization. Luckily Vaadin provides the Vaadin Directory, a place to publish 3rd party components. From the Vaadin directory, a component called vis-network-vaadin is available that provides a Java API on top of vis.js

The Graph

The graph below is a simplified model of what my client wants to manged in the application. A business function can send many messages and a message can be received by many business functions.

The Implementation

First I created a Vaadin project on start.vaadin.com and added the vis-network-vaadin dependency for the visualization. As Vaadin uses Spring Boot by default I could just add spring-boot-starter-data-neo4j for the data access.

Data Access

Spring Data Neo4j provides easy access to Neo4j. As I already know Spring Data JPA and the programming model are very similar it was easy to get started. First I’ve mapped the nodes and defined the relationships using the Neo4j annotations.

@Node
public class BusinessFunction {

    @Id
    @GeneratedValue
    private Long id;

    private String nameDE;
    private String actorsDE;
    private String descriptionDE;
}
@Node
public class Message {

    @Id
    @GeneratedValue
    private Long id;

    private String nameDE;
    private String descriptionDE;

    @Relationship(type = "SENDS", direction = Relationship.Direction.INCOMING)
    private Set<BusinessFunction> senders = new HashSet<>();

    @Relationship(type = "RECEIVES")
    private Set<BusinessFunction> receivers = new HashSet<>();
}

To read and write the data you can use repositories and make use of interface methods that will be used to generate the queries for you. Remark: I didn’t care about the performance so the generated queries were good enough in the first phase.

public interface BusinessFunctionRepository extends Neo4jRepository<BusinessFunction, Long> {

    Optional<BusinessFunction> findByNameDE(String name);

    List<BusinessFunction> findAllByNameDELike(String name, Pageable pageable);
}
public interface MessageRepository extends Neo4jRepository<Message, Long> {

    Optional<Message> findByNameDE(String name);

    List<Message> findAllByNameDELike(String name, Pageable pageable);
}

Diagram

Finally, I had to visualize the graph with a network diagram. Using the vis-network-vaadin API made it quite simple. I just had to map BusinessFunction and Message to nodes and create edges from the relationships.

var networkDiagram = new NetworkDiagram(Options.builder().build());
networkDiagram.setSizeFull();

var businessFunctionNodes = businessFunctionRepository.findAll().stream()
        .map(businessFunction -&gt; createNode("b-", businessFunction.getId(), businessFunction.getNameDE(), "DodgerBlue"))
        .toList();
var nodes = new ArrayList<>(businessFunctionNodes);

var messages = messageRepository.findAll();
var messageNodes = messages.stream()
        .map(message -&gt; createNode("m-", message.getId(), message.getNameDE(), "Orange"))
        .toList();

nodes.addAll(messageNodes);

var dataProvider = new ListDataProvider<>(nodes);
networkDiagram.setNodesDataProvider(dataProvider);

var edges = new ArrayList<Edge>();
for (Message message : messages) {
    for (BusinessFunction sender : message.getSenders()) {
        edges.add(createEdge("b-", sender.getId(), "m-" + message.getId().toString(), getTranslation("sends")));
    }
    for (BusinessFunction receiver : message.getReceivers()) {
        edges.add(createEdge("m-", message.getId(), "b-" + receiver.getId(), getTranslation("receives")));
    }
}

networkDiagram.setEdges(edges);

These are the helper methods to create nodes and edges:

private Edge createEdge(String prefix, Long id, String name, String label) {
    var edge = new Edge(prefix + id.toString(), name);
    edge.setColor("black");
    edge.setArrows(new Arrows(new ArrowHead(1, Arrows.Type.arrow)));
    edge.setLength(300);
    return edge;
}

private Node createNode(String prefix, Long id, String name, String color) {
    var node = new Node(prefix + id, name);
    node.setShape(Shape.circle);
    node.setColor(color);
    node.setFont(Font.builder().withColor("white").build());
    node.setWidthConstraint(new WidthConstraint(100, 100));
    return node;
}

Finally, the graph is displayed in the application.

Conclusion

The application is still in an early stage. The graph will be extended and the diagram must be improved. Especially the behavior when dragging around the edges seems to be quite tricky and vis.js provides a lot of configuration.

As a Java developer creating UIs with Vaadin makes it very efficient. There are even 3rd party libraries that wrap components in a Java API. On the other side, I was impressed by how easy it is to start with Neo4j and to integrate it into a Spring Boot application.

Btw. If you want to learn more about Spring Boot check my video below.

Introduction to Database Migrations with Spring Boot and Flyway

If you are working with Spring Boot there are several ways to initialize the database.

You could create a schema.sql, add it to the classpath, and set spring.datasource.initialization-mode=true. Spring will execute the SQL script on startup.

Or you could use the Hibernate built-in functionality and set spring.jpa.hibernate.ddl-auto to create or update.

But both approaches are very limited and if you have to deal with an evolving database you may look for something more sophisticated.
That’s where database migration tools fill the gap.

Database migration tools

In the Java environment, there are two open-source projects available: Flyway and Liquibase. The difference between these two is that Flyway uses SQL for the migrations and Liquibase supports additional abstractions in XML, YAML, or JSON.

Both tools are very well integrated with Spring Boot. You simply have to add the right dependency in your pom.xml or Gradle build file.
Read more about why you should care about database migrations here.

Flyway with Spring Boot

In my example, I use Flyway with Spring Boot. To use it simply add this dependency and Spring Boot will auto-configure Flyway right away.

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

Now you’re ready to start adding your migration SQL files to your classpath in the path classpath:db/migration

Naming Conventions

To have Flyway execute your SQL scripts you have to follow some naming conventions.

  • Prefix (V, U or R)V = Versioned migrations that are executed only once
  • U = Undo migrations (only supported in the commercial version)
  • R = Repeatable migrations. Scripts that could run with every execution
  • Version
  • Separator __
  • Description
  • Suffix: (.sql)

Example: V1__Init.sql

Example Migration

V1__Init.sql could look like

CREATE TABLE address (
    id integer NOT NULL,
    city character varying(255),
    state character varying(255),
    street character varying(255),
    zip character varying(255),
    created_date timestamp,
    last_modified_date timestamp
);

How it Works

Flyway uses a table to store the version of the database. The default name of this table is flyway_schema_history and the content looks like this:

As you can see Flyway stores the actual version and is, therefore, able to know which migrations scripts have to be executed.

Further Reading

That was just a brief introduction. Flyway has way more to offer and you will find all you need to know in the official documentation on flywaydb.org