Spring Boot H2 vs Postgres dialect "JdbcSQLSyntaxErrorException" in CI

Your tests use an embedded H2 database while production is Postgres or MySQL. H2 does not understand vendor-specific SQL (types, functions, DDL), so a query or migration valid on the real engine throws on H2.

What this error means

A repository test or migration fails with "org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement" or an unknown function/type that Postgres accepts.

Spring Boot

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement
"CREATE TABLE orders (id BIGSERIAL[*] PRIMARY KEY ...)"; expected "identifier"

Common causes

Vendor-specific SQL runs on H2

Postgres types like BIGSERIAL or jsonb, or MySQL functions, are not valid H2 syntax, so DDL/queries fail only in the H2-backed test.

The Hibernate dialect does not match the test DB

A configured Postgres dialect generates SQL H2 cannot run, or H2's compatibility mode is not enabled.

How to fix it

Test against the real engine with Testcontainers

Run tests on the same database as production so vendor SQL is validated correctly.

src/test/java

@Container
static PostgreSQLContainer<?> db = new PostgreSQLContainer<>("postgres:16");

Enable an H2 compatibility mode if you keep H2

For lightweight tests, run H2 in the closest compatibility mode to your engine.

application-test.properties

spring.datasource.url=jdbc:h2:mem:test;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE

How to prevent it

Prefer Testcontainers so tests match the production database engine.
If using H2, enable the matching compatibility MODE.
Keep migrations engine-neutral or gate vendor SQL per database.