Saturday, June 30, 2018

How to map PostgresSQL JSON column with Hibernate value type and Kotlin

At makkajai, there has been no dearth of challenging problems :). Recently we moved our analytics partner. I will not bore you with details on why we had to move, but what is significantly more interesting is how we executed the move. Some key requirements for the move where:

  • Migrate all the data collected by previous analytics partner i.e. around 40 Million events to the new partner.
  • Honour concurrency limits of old and new analytics partners. Because if we didn't honour them, they will stop responding for a period of 10 minutes (which would be costly 10 minutes)
  • Old analytics partner had a limit of 3 concurrent requests.
  • New analytics partner had a limit of sending 1000 events per second. 
  • Migration had to be reliable and fault tolerant. For e.g. we could run the migration multiple times during the migration window.
I am not going to go into details of how we solved the whole problem (may be some other time), in this blog I am going to focus on a very small part of the problem. 

The Problem

PostgresSQL JSON column type has great querying features, I wanted to use it to save parts of events JSON response received from our old analytics partner. For this to happen, I needed to map the PostgresSQL JSON column type to Hibernate value type. This blog post is to document the steps needed to achieve this using Kotlin.

The Solution


There are 4 steps involved to make things work.

  • Adding a custom PostgreSQL dialect to register the JSON column type with Kotlin String.
  • Registering the custom PostgreSQL dialect in application.properties.
  • Adding a custom user type class to map kotlin String to PostgreSQL JSON column.
  • Annotating the model classes, to use the custom user type class.
Here is the exact code needed to achieve all the 4 steps mentioned above

Above is the custom PostgresSQL Dialect to register the JSON column type with a Kotlin String.

Sample Application properties to register the custom dialect.

Above is the custom user type mapping class. This will be used to map Kotlin String to PostgreSQL JSON column.

Above is the simple UserEvent model class that uses the string property properties and maps it to the PostgreSQL JSON column data type.

Thats about it! When we create the instance of UserEvent class and set the value of properties it will be correctly saved in PostgreSQL JSON column type. PostgreSQL will also validate that its a valid JSON String before saving the information.

No comments:

Post a Comment

Have some Fun!