The H2 database is an immensely fashionable in-memory database product principally utilized by Java builders for testing. In case you 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
All the above are supported by jOOQ as nicely.
SQL Standardisation
A easy instance of making an attempt to jot down customary 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 mounted, we run into the following 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 necessary in SQL Server’s thought of the usual SQL OFFSET .. FETCH clause, so we have now to jot 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, for those who’re utilizing jOOQ, you (nearly) by no means have to fret about these particulars, as jOOQ generates the right SQL for you each time wanted. Writing customary SQL is difficult sufficient. Writing SQL dialect agnostic SQL could be very laborious!
Fortunately, that is nonetheless customary SQL, so it nonetheless works on H2 as nicely.
H2’s compatibility modes
Chances are high, 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 based mostly utility may run a press release 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:take a look at;MODE=MSSQLServer
However for those who’re making an attempt to run such T-SQL statements on H2, higher allow the compatibility mode, which can 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 scenario is a fairly totally different. jOOQ doesn’t find out about H2’s compatibility modes. This is a crucial factor to know – once you run jOOQ queries on H2, jOOQ will assume the native H2 dialect and generate SQL straight for H2.
Typically, 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 a substitute 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 possibly can 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 turbines comparable to jOOQ
A a lot better different: Testcontainers
At this level, I’d wish to level out that perhaps, utilizing H2 as a take a look at database product is out of date anyway. Whereas it added loads of worth 10 years in the past, the strategy is not viable due to newer options.
In case your utility 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 utility?
Today, testcontainers is a well-liked choice to rapidly 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 features, and many others.)
- You’ll be able to cease worrying about these painful compatibility issues
We even advocate to make use of testcontainers for jOOQ code technology, so you may reverse engineer your precise schema (together with saved procedures, knowledge sorts, 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 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 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 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 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.
