When Testing with a Different Database Chokes on Your DDL
Friday, December 13, 2019 |I recently found myself writing a test that needed a database. Unfortunately, our testing database, H2, doesn’t support all of the features of our production database, PostgreSQL. This meant that the Flyway migrates used to manage the production database broke in the testing environment. The fix for this turned out to be pretty simple.
To set the scenario, imagine you have this table:
1
2
3
4
CREATE TABLE foo (
id bigint PRIMARY KEY,
some_json JSONB
);
The only thing interesting about this table is the jsonb column, but it’s that column that breaks
when run under H2:
SQL Error [50004] [HY004]: Unknown data type: "JSONB"
The fix is a simple one-liner:
1
CREATE domain IF NOT EXISTS jsonb AS character varying (8192);
In my case, I added that to a Flyway migrate file, named in such a way that it ran before the problematic
CREATE TABLE statement, and Bob’s…uhhh.. my uncle! :)
Hope that helps!
My name is Jason Lee. I am a software developer living in the middle of Oklahoma. I’ve been a professional developer since 1997,
using a variety of languages, including Java, Javascript, PHP, Python, Delphi, and even a bit of C#. I currently work for Red Hat
on the WildFly/EAP team, where, among other things, I maintain integrations for some MicroProfile specs, OpenTelemetry, Micrometer,
Jakarta Faces, and Bean Validation. (Full resume 