When Testing with a Different Database Chokes on Your DDL
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:
CREATETABLE foo (
The only thing interesting about this table is the jsonb column, but it’s that column that breaks
when run under H2:
SQL Error  [HY004]: Unknown data type: "JSONB"
The fix is a simple one-liner:
CREATE domain IFNOTEXISTS 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! :)