Grails, PostgreSQL 9.4 and JSONB
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.
One thought on “Grails, PostgreSQL 9.4 and JSONB”
I don’t know if you’re aware of the “Grails Postgresql Extensions” plugin. The plugin provides HibernateUserTypes to define Postgres native types such as array, hstore, json and jsonb inside your Grails domain classes. It also provides new criterias to query this native fields.
Disclaimer: I’m one of the developers of the plugin.
we actually intend on using this plugin, but still need to override the jsonb usertype as we ran into an issue deserializing the data
The plugin does provide a much more flexible data binding, but still requires the dance with H2. Also, the current project I’m working on has some concerns on performance using the plugin and binding jsonb data directly to a map. They want to run some tests and see if there is any performance degradation.
We have some pending PRs to review and merge. I will do it during the next week hackergarten at GR8Conf and I’ll release a new version with all the PRs merged.
I you want to discuss something about the plugin or the performance about json, please feel free to open an issue.