Coming Up for Air

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:

    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 problematic CREATE TABLE statement, and Bob’s…​uhhh.. my uncle! :)

Hope that helps!



    Sample quote

    Quote source


    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 here. LinkedIn profile)

    I am the president of the Oklahoma City JUG, and an occasional speaker at the JUG and a variety of technical conferences.

    On the personal side, I’m active in my church, and enjoy bass guitar, running, fishing, and a variety of martial arts. I’m also married to a beautiful woman, and have two boys, who, thankfully, look like their mother.

    My Links