▌ Phosphor

Two Dialects, One Schema: Supporting H2 and Postgres Without Losing Your Mind

August 7, 2026

A BBS should boot on a laptop with no database server installed. It should also run a production community for years on a real database with real operational tools. Those are different requirements, and Phosphor satisfies both by maintaining one schema in two dialects: PostgreSQL for production, embedded H2 for zero-config installs.

This post is the field guide to what that actually costs — the failure modes, the patterns that keep them manageable, and the bugs we hit so you don’t have to.

The layout

Two DDL files, bbs_schema.sql (Postgres) and bbs_schema_h2.sql (H2), mirror each other table for table. Every service that talks SQL is dialect-aware: it receives a Dialect enum and branches only where the SQL genuinely differs. Everything else — schema strings, query shape, table names — is identical, which is the point. The closer the two files drift, the more bugs only exist on one install type.

if (dialect == Dialect.POSTGRES) {
    // INSERT INTO bbs.settings (key, value, updated_at)
    // VALUES (?, ?, now())
    // ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value
} else {
    // MERGE INTO bbs.settings ("key", "value", updated_at)
    // KEY("key") VALUES (?, ?, CURRENT_TIMESTAMP)
}

Failure mode one: H2 reserves more words than Postgres

The sneakiest bug we hit: settings writes silently failed on H2 — a warning in the log, no crash, no data. The SQL looked fine:

MERGE INTO bbs.settings (key, value, updated_at) ...

H2 rejects this because value — and key, and user, and count, and year, and left, right, and many other common words — are reserved keywords in H2 even in PostgreSQL compatibility mode. Postgres doesn’t reserve them; H2 does. The fix is quoting both columns in every H2 statement that touches them, which means the “same SQL, two dialects” rule isn’t paranoia — it’s load-bearing.

Diagnostic tip that saved us time: H2’s syntax errors point at the exact offending token with [*] markers. If a settings write fails quietly on H2, grep the log for JdbcSQLSyntaxErrorException and look for the marker.

Failure mode two: types that don’t exist

TIMESTAMPTZ, INET, TEXT[] — the Postgres type system has conveniences H2 lacks, even in compat mode. Each one became a rule:

  • Timestamps with time zone: TIMESTAMPTZ in Postgres, plain TIMESTAMP in H2 — the same column in both files, different declarations.
  • IP addresses: INET in Postgres becomes VARCHAR(45) in H2 (long enough for IPv6).
  • A codepath that used a ?::inet cast only ran on Postgres. On H2 it threw Unknown data type: INET — at runtime, on a page load. The rule now: every service constructor receives the dialect explicitly. Silent defaulting to Postgres SQL is how a service works in every test and then dies on a fresh H2 install.

Failure mode three: same intent, different upsert

Upserts are the classic split: Postgres gets INSERT ... ON CONFLICT (col) DO NOTHING; H2 gets MERGE INTO ... KEY(col). But the two aren’t semantically identical — H2’s MERGE ... KEY updates the existing row on a key match, where Postgres DO NOTHING keeps the first row. If your code assumes “re-add means keep original,” H2 will surprise you. Decide the semantics per use case and assert the divergence in tests rather than discovering it in production.

There’s also a DDL-ordering trap: default-data INSERTs must come after their table’s CREATE TABLE in the schema file — sounds obvious until a refactor moves a block and a fresh install starts failing on line 39.

The test strategy: never fake a dialect

The costly mistake we made once was writing tests against an in-memory database with H2-compatible SQL subclasses overriding the production queries. Those tests passed while testing the mock. Production ON CONFLICT and INTERVAL SQL was never exercised, and the schema files weren’t either.

The rule now: dialect tests run the real dialect against the real schema files. H2-mode tests boot an in-memory H2 that loads bbs_schema_h2.sql from the classpath; parity tests assert that table X exists in both schema files with the same columns — because the two files are maintained by hand, and the parity test is the only thing standing between a sysop and a fresh install that’s missing a table. One such test caught a missing table within a week of being written.

What it bought us

The dual-dialect tax is real but small: two schema files, a Dialect parameter, and a handful of SQL branches. What it buys is deployment freedom. A fresh community boots with ./start.sh and zero infrastructure decisions; a serious one moves to Postgres with a config line. Same code, same features, same tests — and the H2 path doubles as a fast, hermetic test substrate for CI.

Full post: https://phosphorbbs.net/blog/two-dialects-one-schema-h2-and-postgres/

#BBS #database #Java #opensource #jterm #selfhosted