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:

  class MyModel {

    String data

    static mapping = {
      data sqlType: 'TEXT'
    }
  }

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

    ERROR: column "data" is of type jsonb but expression is of type character varying
    Hint: You will need to rewrite or cast the expression. Position: 46

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.

public class JSONBType implements UserType {
    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
        return rs.getObject(names[0])
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
        st.setObject(index, value, (value == null) ? Types.NULL : Types.OTHER)
    }
}

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:

static mapping = {
    data type: JSONBType, sqlType: 'jsonb'
}

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:

ERROR hbm2ddl.SchemaUpdate - Unknown data type: "JSONB"

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.

CREATE domain IF NOT EXISTS jsonb AS other;

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.

test{
    dataSource {
        dbCreate = "create"
        driverClassName = "org.h2.Driver"
        url = "jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;INIT=create domain if not exists jsonb as other;"
    }
}

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

Happy coding!