Utilizing H2 as a Check Database Product with jOOQ – Java, SQL and jOOQ.

The H2 database is an immensely common in-memory database product principally utilized by Java builders for testing. When you take a look at the DB-Engines rating, it ranks fiftieth, which is kind of spectacular, as this rank outperforms merchandise like:

• CockroachDB
• Ignite
• Single Retailer (beforehand MemSQL)
• Interbase (which was forked as Firebird)
• Ingres (which is a predecessor to the superior PostgreSQL)
• Google BigTable

The entire above are supported by jOOQ as nicely.

SQL Standardisation

A easy instance of making an attempt to write down customary SQL throughout RDBMS is the next, which makes use of native H2 syntax:

strive (
    Connection c = DriverManager.getConnection(
        "jdbc:h2:mem:check", "sa", "");
    Assertion 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
        """
    )
) {
    whereas (rs.subsequent())
        System.out.println(rs.getInt(1));
}

The question produces, as anticipated:

1
2

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

SQL Error [156] [S0001]: Incorrect syntax close to the key phrase ‘VALUES’.

In SQL Server, the VALUES desk constructor must be parenthesised as follows:

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

As soon as that’s mounted, we run into the subsequent error:

SQL Error [153] [S0002]: Invalid utilization of the choice FIRST within the FETCH assertion.

For causes solely the T-SQL gods can think about, the OFFSET clause is obligatory in SQL Server’s concept of the usual SQL OFFSET .. FETCH clause, so now we have to write down this, as a substitute:

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

Be aware, when you’re utilizing jOOQ, you (nearly) by no means have to fret about these particulars, as jOOQ generates the right SQL for you at any time when wanted. Writing customary SQL is tough sufficient. Writing SQL dialect agnostic SQL could be very arduous!

Fortunately, that is nonetheless customary SQL, so it nonetheless works on H2 as nicely.

H2’s compatibility modes

Likelihood is, nonetheless, that your software must run on SQL Server first, and you considered testing your software on H2. That’s the place H2’s compatibility modes attempt to assist. Your T-SQL based mostly software would possibly run an announcement like this one, as a substitute of the earlier customary SQL assertion:

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

It’s precisely equal, and nonetheless produces this output:

1
2

Curiously, H2 additionally helps the TOP 2 clause natively, even with out specifying the compatibility mode within the JDBC URL like this:

jdbc:h2:mem:check;MODE=MSSQLServer

However when you’re making an attempt to run such T-SQL statements on H2, higher allow the compatibility mode, which is able to deal with just a few edge circumstances. Historical past has proven that this stuff change incompatibly between patch releases in H2, so higher watch out.

Utilizing H2 with jOOQ

As soon as you utilize jOOQ, the state of affairs is a fairly totally different. jOOQ doesn’t learn about H2’s compatibility modes. This is a crucial factor to know – while you run jOOQ queries on H2, jOOQ will assume the native H2 dialect and generate SQL immediately for H2.

Typically, customers by some means assume that they need to proceed utilizing the compatibility mode like within the above JDBC use-case. For instance, on this Stack Overflow query, a person bumped into a problem the place jOOQ produced H2 SQL on H2 in MODE=MSSQLServer. jOOQ nonetheless generates LIMIT as a substitute of FETCH for H2 (see pending function request right here), however each don’t work like that on SQL Server or on H2 with MODE=MSSQLServer!

If you wish to proceed utilizing H2 as your check database product to simulate SQL Server, there’s solely actually 1 legitimate configuration:

• Use jOOQ’s SQLDialect.H2
• Use H2 with none compatibility mode

As a result of jOOQ implements the compatibility mode for you. You could possibly be tempted to make use of SQLDialect.SQLSERVER on H2, however jOOQ will then assume an precise SQL Server database that understands all of T-SQL, and also you’ll run into limitless limitations of H2’s MODE=MSSQLServer

In different phrases:

H2’s compatibility modes are helpful for plain SQL utilization solely, not for utilization with SQL mills corresponding to jOOQ

A a lot better various: Testcontainers

At this level, I’d prefer to level out that possibly, utilizing H2 as a check database product is out of date anyway. Whereas it added plenty of worth 10 years in the past, the method is now not viable due to newer alternate options.

In case your software runs on SQL Server solely, then why undergo all that hassle of sustaining vendor agnosticity simply to have the ability to integration check your software?

Lately, testcontainers is a well-liked choice to shortly spin up an precise SQL Server occasion in Docker for the sake of integration testing (and even growing) your software. The advantages are actually apparent:

• It simplifies your code
• You should use all types of vendor particular options (like T-SQL’s highly effective desk valued capabilities, and so on.)
• You may cease worrying about these painful compatibility issues

We even advocate to make use of testcontainers for jOOQ code era, so you may reverse engineer your precise schema (together with saved procedures, information varieties, and what not)

Exception: Your software is RDBMS agnostic

An exception to the above is when your software is a product that helps a number of RDBMS, in case of which you’re keen on jOOQ much more for abstracting over your SQL dialect.

As a result of in that case, you have already got to fret about painful compatibility issues, so including H2 doesn’t harm you that a lot, and in that case, you may nonetheless profit from H2 being a bit sooner to spin up than a testcontainers based mostly database product.

For instance, jOOQ’s personal integration assessments first run towards H2, appearing as a “smoke check.” If the H2 integration assessments fail, we will get early suggestions about one thing that may as nicely fail in all the opposite dialects as nicely, so we get faster suggestions.

However even then, when utilizing jOOQ, H2’s compatibility modes are pointless, so use H2 solely in its native kind. And once more, most functions should not like that, they’re tied to a single RDBMS, so including H2 to the stack has a lot larger prices. Take into consideration testcontainers, once more.