Skip to content

The H2 Backend — Peculiarities

H2 supports the H2 database engine (2.x), embedded, in-memory or in server mode. H2 is the backend Tentackle's own tests run on (see tentackle-test-pdo), and it is the natural choice for an application's unit tests as well. This document collects what deviates from the other backends, PostgreSQL in particular.

The backend is selected by a JDBC URL containing :h2: (org.h2.Driver). There is no version check.

Type Mapping

SqlType H2 type Note
VARCHAR(n) VARCHAR(n) n is capped at 1048576 (getMaxSize)
VARCHAR VARCHAR(1048576) an attribute without a size gets the maximum size
CHAR CHAR(1)
CLOB CLOB
XML CLOB H2 backs its SQLXML implementation by a CLOB, hence isSqlxmlSupported() is true
JSON JSON native, see below
BLOB BLOB
BIT BOOLEAN
TINYINT TINYINT
FLOAT REAL DOUBLE is declared as DOUBLE PRECISION
DECIMAL DECIMAL(19) without a size, see below; the maximum precision is 99999
UUID UUID native; UUID is therefore a reserved word

Decimals

An unsized DECIMAL needs a precision. A bare DECIMAL is DECIMAL(100000,0) in H2 and would silently round away every fraction digit, so getDefaultSize supplies 19 — the same remedy the MySQL backend applies (see Decimals). As there, only PostgreSQL, Oracle and SAP HANA keep the scale of a decimal column the model declares no size for.

JSON

H2 has a native JSON type, but it parses only bytes as a document: a Java string would be stored as a JSON string value. setJson therefore binds the UTF-8 bytes, which H2 validates on the way in. H2 reports a JSON column as Types.OTHER with the type name JSON, which the type-name-aware jdbcTypeToSqlType overload uses to tell it from a UUID column. Like PostgreSQL's jsonb, H2 drops insignificant whitespace from the stored document.

Queries

  • Paging and locking follow the SQL standard, not PostgreSQL's order: buildSelectSql emits OFFSET ? ROWS FETCH FIRST ? ROWS ONLY FOR UPDATE. H2 rejects any other order as a syntax error, which is why the trailing parameters are bound offset-first.
  • Sequences are read with SELECT NEXTVAL('name').
  • Array parameters are supported with the ANY and ALL operators.
  • Sub-selects need an alias (isAliasRequiredForSubSelect()).

Schema and Migration

  • Names may be up to 256 characters long. information_schema is a reserved schema, while public — H2's default schema — remains a legal schema name.
  • Comments are created with COMMENT ON for tables, columns, indexes and sequences.
  • Column alterations change the type, the null constraint and the default in separate statements (ALTER COLUMN ... TYPE, SET/DROP NOT NULL, SET DEFAULT).
  • DROP ... IF EXISTS is supported (see setDropIfExistsEnabled).
  • No filtered and no function-based indexes: the model is refused for H2 if it uses them, unless the index is marked optional, in which case it is skipped (see Index Lines).
  • An unsized VARCHAR column is not migrated again. H2 reports it with the size it was created with (or as unbounded), which the migrator recognizes as the maximum size.

Runtime

  • In-memory databases. isDatabaseInMemory() returns true for a URL containing :mem:. The test base classes use it to decide whether the schema must be created before the tests run.
  • Transient errors. Besides the standard SQLState 40001, the states 50200 (lock timeout) and 90131 (concurrent update) mark a transaction as worth retrying.
  • XA is supported via org.h2.jdbcx.JdbcDataSource (see JTA/XA).
  • The backend id is H2's session id (SESSION_ID()).