1408436825 58

Renewed Life for STI with PostgreSQL JSON type

The most common argument against STI (Single Table Inheritance) is that you have to create an additional column for every new field used in child classes. If other child classes don’t use this column, it’s a waste of resources.

Resources

As you probably know, PostgreSQL introduced JSON data type support in version 9.3. If you are not familiar with JSON data type, you can read about it here: www.postgresql.org/docs/9.3/static/datatype-JSON. Supported functions and operators can be found at www.postgresql.org/docs/9.3/static/functions-JSON. Ruby on Rails introduced support for this data type in version 4.

The Social Media Example

Any way, moving on. In this post, I’ll show you a way to avoid creating additional columns for child classes using RoR 4 and PostgreSQL 9.3, with an example based on social media users data.

Currently, when many social media providers give us the ability to authenticate users, we have a lot of user types. So let’s see how we can use STI to store those users.

User Types By Social Media

Every social media provider gives us information about users in a different format. Note that the following data I made up...I hope it doesn’t use someone’s real profile by coincidence!

For example, Facebook:

And then there’s Twitter:

How do we keep data disparity in one table?

JSON Column Creation

The idea is to save this data in a JSON column. Let’s try this. We will create only an example, so if you see possible improvements please do that.

First, we have create a User model:

...and run migrations.

rake db:migrate

We also want to create separate classes for Twitter users and Facebook users.

Nothing special so far.

Lets create example users and find them by social_id

a Facebook user:

and a Twitter user:

Success! But how do we use it?

We have commonly shared data like name and social_id in columns for which we can create indexes and all other data in one JSON column. We may also remove information that we already have in columns from the rest of the data, but I will leave it as a sort of data backup.

How do we access additional data? We use it like hash in RoR.

Cool right?

To do it a little better, we can use gem like hashie (or one with similar functionality) to use custom_fields almost like normal attributes.

Now we can do something like this:

Impressive, right?

Now we can use STI without worrying about extending child classes with custom attribute.

Final Word

There are some consequences when using JSON. You are limited to what you can and can’t do with JSON. For example, if you want to search or sort a JSON column, you have to implement it using PostgreSQL functions and operators.

It’s time to stop thinking STI is cumbersome and start getting creative with it.

If you see additional pros and cons of STI with JSON, please leave a comment or tweet us at @netguru!

You can find all code examples in the repository: JSON_webinar.Take care!

On 19.08.2014 in dev and postgresql