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. For those who try the DB-Engines rating, it ranks fiftieth, which is sort 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 effectively.

SQL Standardisation

A easy instance of attempting to jot down normal SQL throughout RDBMS is the next, which makes use of native H2 syntax:

strive (
    Connection c = DriverManager.getConnection(
        "jdbc:h2:mem:take a look at", "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 in opposition to 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 fastened, 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 we have now to jot down this, as an alternative:

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 (virtually) by no means have to fret about these particulars, as jOOQ generates the right SQL for you each time wanted. Writing normal SQL is tough sufficient. Writing SQL dialect agnostic SQL may be very onerous!

Fortunately, that is nonetheless normal SQL, so it nonetheless works on H2 as effectively.

H2’s compatibility modes

Likelihood is, nevertheless, 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 primarily based software may run a press release like this one, as an alternative of the earlier normal 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

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

jdbc:h2:mem:take a look at;MODE=MSSQLServer

However when you’re attempting 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 these items change incompatibly between patch releases in H2, so higher watch out.

Utilizing H2 with jOOQ

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

Usually, customers in some way 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 consumer bumped into a difficulty the place jOOQ produced H2 SQL on H2 in MODE=MSSQLServer. jOOQ nonetheless generates LIMIT as an alternative of FETCH for H2 (see pending characteristic 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 take a look at 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 might 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 comparable to jOOQ

A significantly better various: Testcontainers

At this level, I’d wish to level out that possibly, utilizing H2 as a take a look at database product is out of date anyway. Whereas it added lots of worth 10 years in the past, the method is not viable because of newer options.

In case your software runs on SQL Server solely, then why undergo all that bother of sustaining vendor agnosticity simply to have the ability to integration take a look at 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 need to use all kinds of vendor particular options (like T-SQL’s highly effective desk valued features, and so forth.)
• You may cease worrying about these painful compatibility issues

We even advocate to make use of testcontainers for jOOQ code technology, so you possibly can reverse engineer your precise schema (together with saved procedures, knowledge sorts, 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 possibly can nonetheless profit from H2 being a bit sooner to spin up than a testcontainers primarily based database product.

For instance, jOOQ’s personal integration checks first run in opposition to H2, performing as a “smoke take a look at.” If the H2 integration checks fail, we will get early suggestions about one thing that may as effectively fail in all the opposite dialects as effectively, 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 are usually not like that, they’re tied to a single RDBMS, so including H2 to the stack has a lot greater prices. Take into consideration testcontainers, once more.