Using H2 as a Test Database Product with jOOQ

The H2 database is an immensely popular in-memory database product mostly used by Java developers for testing. If you check out the DB-Engines ranking, it ranks 50th, which is quite impressive, as this rank outperforms products like:

• CockroachDB
• Ignite
• Single Store (previously MemSQL)
• Interbase (which was forked as Firebird)
• Ingres (which is a predecessor to the awesome PostgreSQL)
• Google BigTable

All of the above are supported by jOOQ as well.

SQL Standardisation

A simple example of trying to write standard SQL across RDBMS is the following, which uses native H2 syntax:

try (
    Connection c = DriverManager.getConnection(
        "jdbc:h2:mem:test", "sa", "");
    Statement s = c.createStatement();
    ResultSet rs = s.executeQuery("""
        SELECT v
        FROM VALUES (1), (2), (3) AS t (v)
        ORDER BY v
        FETCH FIRST 2 ROWS ONLY
        """
    )
) {
    while (rs.next())
        System.out.println(rs.getInt(1));
}

The query produces, as expected:

1
2

If we paste this query into a SQL editor and run it against SQL Server, then there are 2 syntax errors:

SQL Error [156] [S0001]: Incorrect syntax near the keyword ‘VALUES’.

In SQL Server, the VALUES table constructor needs to be parenthesised as follows:

SELECT v
FROM (VALUES (1), (2), (3)) AS t (v)

Once that’s fixed, we run into the next error:

SQL Error [153] [S0002]: Invalid usage of the option FIRST in the FETCH statement.

For reasons only the T-SQL gods can imagine, the OFFSET clause is mandatory in SQL Server’s idea of the standard SQL OFFSET .. FETCH clause, so we have to write this, instead:

SELECT v
FROM (VALUES (1), (2), (3)) AS t (v)
ORDER BY v
OFFSET 0 ROWS
FETCH FIRST 2 ROWS ONLY

Note, if you’re using jOOQ, you (almost) never have to worry about these details, as jOOQ generates the correct SQL for you whenever needed. Writing standard SQL is hard enough. Writing SQL dialect agnostic SQL is very hard!

Luckily, this is still standard SQL, so it still works on H2 as well.

H2’s compatibility modes

Chances are, however, that your application needs to run on SQL Server first, and you thought about testing your application on H2. That’s where H2’s compatibility modes try to help. Your T-SQL based application might run a statement like this one, instead of the previous standard SQL statement:

SELECT TOP 2 v
FROM (VALUES (1), (2), (3)) AS t (v)
ORDER BY v;

It’s exactly equivalent, and still produces this output:

1
2

Interestingly, H2 also supports the TOP 2 clause natively, even without specifying the compatibility mode in the JDBC URL like this:

jdbc:h2:mem:test;MODE=MSSQLServer

But if you’re trying to run such T-SQL statements on H2, better enable the compatibility mode, which will handle a few edge cases. History has shown that these things change incompatibly between patch releases in H2, so better be careful.

Using H2 with jOOQ

Once you use jOOQ, the situation is a quite different. jOOQ doesn’t know about H2’s compatibility modes. This is an important thing to know – when you run jOOQ queries on H2, jOOQ will assume the native H2 dialect and generate SQL directly for H2.

Often, users somehow think that they should continue using the compatibility mode like in the above JDBC use-case. For example, in this Stack Overflow question, a user ran into an issue where jOOQ produced H2 SQL on H2 in MODE=MSSQLServer. jOOQ still generates LIMIT instead of FETCH for H2 (see pending feature request here), but both don’t work like that on SQL Server or on H2 with MODE=MSSQLServer!

If you want to continue using H2 as your test database product to simulate SQL Server, there is only really 1 valid configuration:

• Use jOOQ’s SQLDialect.H2
• Use H2 without any compatibility mode

Because jOOQ implements the compatibility mode for you. You could be tempted to use SQLDialect.SQLSERVER on H2, but jOOQ will then assume an actual SQL Server database that understands all of T-SQL, and you’ll run into endless limitations of H2’s MODE=MSSQLServer

In other words:

H2’s compatibility modes are useful for plain SQL usage only, not for usage with SQL generators such as jOOQ

A much better alternative: Testcontainers

At this point, I’d like to point out that maybe, using H2 as a test database product is obsolete anyway. While it added a lot of value 10 years ago, the approach is no longer viable thanks to newer alternatives.

If your application runs on SQL Server only, then why go through all that trouble of maintaining vendor agnosticity just to be able to integration test your application?

These days, testcontainers is a popular option to quickly spin up an actual SQL Server instance in Docker for the sake of integration testing (or even developing) your application. The benefits are really obvious:

• It simplifies your code
• You can use all sorts of vendor specific features (like T-SQL’s powerful table valued functions, etc.)
• You can stop worrying about these painful compatibility problems

We even recommend to use testcontainers for jOOQ code generation, so you can reverse engineer your actual schema (including stored procedures, data types, and what not)

Exception: Your application is RDBMS agnostic

An exception to the above is when your application is a product that supports multiple RDBMS, in case of which you love jOOQ even more for abstracting over your SQL dialect.

Because in that case, you already have to worry about painful compatibility problems, so adding H2 doesn’t hurt you that much, and in that case, you can still benefit from H2 being a bit faster to spin up than a testcontainers based database product.

For example, jOOQ’s own integration tests first run against H2, acting as a “smoke test.” If the H2 integration tests fail, we can get early feedback about something that might as well fail in all the other dialects as well, so we get quicker feedback.

But even then, when using jOOQ, H2’s compatibility modes are unnecessary, so use H2 only in its native form. And again, most applications are not like that, they’re tied to a single RDBMS, so adding H2 to the stack has much higher costs. Think about testcontainers, again.