On a recent grails project using postgreSQL 9.4 that took advantage of the jsonb datatype, we ran into an issue mapping data back and forth to our domain objects. We had several columns in postgreSQL set as jsonb and our domain class mapped like so:

When attempting to save this object using myModel.save(), the following exception is thrown:

Basically, hibernate has no idea what a jsonb column is and how to get that data into a postgreSQL database. Having the column mapped to TEXT allows hibernate to read the data fine, but will puke when writing.

The first thing we did was to write a custom hibernate UserType and override the getter and setter for this type.

This tells hibernate to set this data as either a null type or as OTHER. When retrieving the data, we use rs.getObject() to ensure we deserialize the data from the databsase. After that you can now map your model class to this new hibernate usertype:

Now you can save your data when running against a postgres 9.4 database with column types of jsonb. So we are done right?, Not quite. Guess what, H2 has no idea what a jsonb column type is either and when running tests we run into these lovely errors:

The problem now is when H2 comes across a jsonb mapping it will puke and wont create the table this column resides in and tests using this domain class will fail. After looking through the H2 docs a bit you can create a custom data type with the following sql statement.

The terminology is a bit funky here, but a domain is a user defined column type, and if jsonb doesn’t exists then create it as type OTHER. Super, now we can throw this sql statement on the url of our H2 datasource configuration and tell H2 to create this custom data type when initializing the database.

H2 now knows what a jsonb column type is and everyone is happy.

Happy coding!