Utilizing H2 as a Take a look at Database Product with jOOQ – Java, SQL and jOOQ.

The H2 database is an immensely well-liked in-memory database product largely utilized by Java builders for testing. For those who try 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 effectively.

SQL Standardisation

A easy instance of making an attempt to put in writing commonplace 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 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 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 thought of the usual SQL OFFSET .. FETCH clause, so now we have to put in writing 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

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

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

H2’s compatibility modes

Likelihood is, nevertheless, that your utility must run on SQL Server first, and you considered testing your utility on H2. That’s the place H2’s compatibility modes attempt to assist. Your T-SQL primarily based utility may run a press release like this one, as an alternative of the earlier commonplace 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:take a look at;MODE=MSSQLServer

However when you’re making an attempt to run such T-SQL statements on H2, higher allow the compatibility mode, which can deal with a number of 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 employ jOOQ, the scenario is a fairly totally different. jOOQ doesn’t find out about H2’s compatibility modes. This is a vital factor to know – while you run jOOQ queries on H2, jOOQ will assume the native H2 dialect and generate SQL straight 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 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 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 take a look at database product to simulate SQL Server, there may be 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 corresponding to jOOQ

A significantly better different: Testcontainers

At this level, I’d prefer to level out that perhaps, utilizing H2 as a take a look at database product is out of date anyway. Whereas it added quite a lot of worth 10 years in the past, the strategy is now not viable because of newer alternate options.

In case your utility runs on SQL Server solely, then why undergo all that hassle of sustaining vendor agnosticity simply to have the ability to integration take a look at your utility?

As of late, 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 utility. The advantages are actually apparent:

• It simplifies your code
• You should utilize all types of vendor particular options (like T-SQL’s highly effective desk valued capabilities, and many others.)
• You’ll be able to cease worrying about these painful compatibility issues

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

Exception: Your utility is RDBMS agnostic

An exception to the above is when your utility is a product that helps a number of RDBMS, in case of which you like 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’ll be able to nonetheless profit from H2 being a bit quicker to spin up than a testcontainers primarily based database product.

For instance, jOOQ’s personal integration checks first run towards H2, appearing as a “smoke take a look at.” If the H2 integration checks fail, we will get early suggestions about one thing which 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 type. And once more, most purposes should not like that, they’re tied to a single RDBMS, so including H2 to the stack has a lot increased prices. Take into consideration testcontainers, once more.