How to Integration Test Stored Procedures with jOOQ – Java, SQL and jOOQ.

When you write stored procedures and functions in your database, you want to ensure their correctness, just like with your Java code. In Java, this is done with unit tests, typically with JUnit. For example, if you have the following code in Java:

public static int add(int a, int b) {
    return a + b;
}

Then, you might write a test like this:

@Test
public void testAdd() {
    assertEquals(3, add(1, 2));
}

But how do we do this when writing stored procedures? While there exist some unit test libraries for some database products (e.g. utPLSQL for Oracle) they may suffer from the following limitations:

• They might not be as tightly integrated with your Maven/Gradle build as JUnit
• They might not be supported by your IDE with additional views such as in Eclipse/IntelliJ
• There might not be any such utility for your database products at all
• You might have to support multiple database products and have to maintain tests for all of them, ideally written in Java
• Your procedure integration tests may interact with some Java code, so you want to write the test in Java anyway.

We’d like to re-use our Java test infrastructure instead, but without the hassle of binding to procedures via JDBC directly.

Using jOOQ with testcontainers

Testcontainers is an increasingly popular framework for database integration testing in Docker. You can quickly spin up a database instance and deploy your database schema including your stored procedures, functions, packages, user defined types, etc. For example, you might decide to move the above method into your database using PostgreSQL:

CREATE OR REPLACE FUNCTION add(a integer, b integer)
RETURNS integer AS
$$
BEGIN 
  RETURN a + b;
END;
$$
LANGUAGE PLPGSQL;

Now, you could call this function with JDBC, as such:

try (CallableStatement s = connection.prepareCall(
    "{ ? = call add(?, ?) }"
)) {
    s.registerOutParameter(1, Types.INTEGER);
    s.setInt(2, 1);
    s.setInt(3, 2);
    s.executeUpdate();
    System.out.println(s.getInt(1));
}

The above prints

3

But that’s a lot of manual plumbing. Whenever you refactor your procedure, your test fails at runtime, rather than at compile time. And you’ll have to tediously update the test code above.

So, why not just use jOOQ’s code generator to generate a Routines class for you, containing an add() method. That one, you can call like this:

System.out.println(Routines.add(configuration, 1, 2));

Where configuration is a jOOQ type wrapping your JDBC Connection. You can now set up your JUnit test like this, for example, e.g. using JUnit’s ClassRule:

@ClassRule
public static PostgreSQLContainer<?> db = 
    new PostgreSQLContainer<>("postgres:14")
        .withUsername("postgres")
        .withDatabaseName("postgres")
        .withPassword("test");

Another option of how to configure testcontainers with JUnit is described here.

And use db.getJdbcUrl() to connect to the PostgreSQL testcontainers database with jOOQ. Your final test then just looks like this:

@Test
public void testAdd() {
    try (CloseableDSLContext ctx = DSL.using(
        db.getJdbcUrl(), "postgres", "test"
    )) {
        assertEquals(3, Routines.add(ctx.configuration(), 1, 2));
    }
}

You’d obviously move the DSLContext logic out of the individual tests into a @Before block. Not much different from what you’re used to, right? Note that even for code generation, we recommend using testcontainers as well, as part of your build. You can then re-use the code generator testcontainers instance for your integration tests, if you want.