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.

Happy coding!

About the Author

Object Partners profile.

One thought on “Grails, PostgreSQL 9.4 and JSONB

  1. Iván López says:

    Hi.

    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.

    https://github.com/kaleidos/grails-postgresql-extensions

    Regards, Iván.

    Disclaimer: I’m one of the developers of the plugin.

    1. zach legein says:

      Hi Iván,

      we actually intend on using this plugin, but still need to override the jsonb usertype as we ran into an issue deserializing the data

      https://github.com/kaleidos/grails-postgresql-extensions/pull/66

      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.

      -zach

      1. Iván López says:

        Hi Zach.

        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.

        Regards, Iván.

Leave a Reply

Your email address will not be published.

Related Blog Posts
Natively Compiled Java on Google App Engine
Google App Engine is a platform-as-a-service product that is marketed as a way to get your applications into the cloud without necessarily knowing all of the infrastructure bits and pieces to do so. Google App […]
Building Better Data Visualization Experiences: Part 2 of 2
If you don't have a Ph.D. in data science, the raw data might be difficult to comprehend. This is where data visualization comes in.
Unleashing Feature Flags onto Kafka Consumers
Feature flags are a tool to strategically enable or disable functionality at runtime. They are often used to drive different user experiences but can also be useful in real-time data systems. In this post, we’ll […]
A security model for developers
Software security is more important than ever, but developing secure applications is more confusing than ever. TLS, mTLS, RBAC, SAML, OAUTH, OWASP, GDPR, SASL, RSA, JWT, cookie, attack vector, DDoS, firewall, VPN, security groups, exploit, […]