When Testing with a Different Database Chokes on Your DDL

Friday, Dec 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:

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:

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 problematicCREATE TABLE statement, and Bob's...uhhh.. my uncle! :)

Hope that helps!