K Putra

Posted on Dec 28, 2019 • Updated on Jun 28, 2020

Rails + PostgreSQL JSONB (Part 1)

#rails #postgres

Rails + PostgreSQL JSONB Series
Part 1: Migration and CRUD
Part 2: store_accessor, Query, and Index
Part 3: Paginate JSONB Data

If you continue to read this article, I assume that you know Ruby, OOP in Ruby, RoR, and Active Record.

Not so many article cover this topic. Even the existing articles did not cover in details. I have to read many articles before I can perform well.

This is the first time I'll deep dive into a topic.

You need Rails >= 4.2, PostgreSQL >= 9.4

PostgreSQL has JSON and JSONB column type. They are look the same, but there are many differences, especially in functionality. I recommend to use JSONB instead of JSON, as JSONB column type is the upgraded version of JSON.

Why we should use JSONB column type?

The advantage of using jsonb is that you can easily integrate relational and non-relation data, with performance that can be better than most non-relational databases like MongoDB.

source: this article

Let's start our journey! (I use Rails API-only as example, but this article can be implemented in normal Rails as well)

Table of Contents:
1. Migration
2. Create
3. Show
4. Validation
5. Update
6. Final Word

1. Migration

It is as simple as any other column types.

# db/migrate/*_create_books.rb
class CreateBooks < ActiveRecord::Migration[5.2]
  def change
    create_table :books do |t|
      t.integer :user_id
      t.jsonb :payload, null: false, default: '{}'
    end
  add_index :books, :payload, using: :gin
  end
end

Notice:

First, you don't have to use null: false, default: '{}', but I recommend to use it. Simply because you don't have to check if it is nil or not.

# if you allow nil OR dont't state default value to a hash
Book.create(user_id: 1)
Book.last.payload['title']
# => NoMethodError (undefined method `[]' for nil:NilClass)

# if you don't allow nil AND state default value to a hash
Book.create(user_id: 1)
Book.last.payload['title']
# => nil

Second, we’re also defining a GIN index. I'll cover this in Part 2.

Third, I named the column name as payload, but you can named it as you want as long as ruby, rails, and postgresql allowed it.

2. Create

Create a record is very simple too:

book_payload = {
  title: 'Hacking Growth',
  publisher: 'Currency',
  published_date: '2017-04-07',
  authors: [
    {
      id: 1,
      name: 'Sean Ellis'
    },
    {
      id: 2,
      name: 'Morgan Brown'
    }
  ]
}

Book.create(user_id: 1, payload: book_payload)

There you go!

3. Show

It is very simple to show the data. Note that any column defined as JSONB will be represented as a hash, with all keys are returned as string.

Book.last.user_id
# => 1

Book.last.payload['title']
# => "Hacking Growth"

Book.last.payload['authors'][0]['name']
# => "Sean Ellis"

Now, if you want a custom serializer so you can access your JSON object using symbols as well, you can do like this:

# app/models/book.rb
class Book < ApplicationRecord
  belongs_to :user
  serialize :payload, JsonbSerializers
end

# app/serializers/jsonb_serializers.rb
class JsonbSerializers
  def self.dump(hash)
    hash.to_json
  end

  def self.load(hash)
    (hash || {}).with_indifferent_access
  end
end

Book.last.payload[:title]
# => "Hacking Growth"

4. Validation

The problems with JSOB column type is if you don't give validation then it will be schema free. You can input any schema to this column. Take a look at the code below:

book_payload = {
  title: 'Getting to Plan B',
  pubs: 'Harvard Business Review Press',
  authors: 'John W Mullins'
  rating: 3.5
}

Book.create(user_id: 1, payload: book_payload)

The record above will be committed in DB. In most scenario, this is a big problem.

How to validate the params given by API consumer is right or wrong? We have to do two steps:

Using JSON Schema
Whitelist params

First step, we are using JSON Schema. I won't cover what JSON Schema is. The official page here is perfect enough to be understood.

How to use JSON Schema in Rails? We are using gems. There are few gems, but the only one I've ever use is activerecord_json_validator gems. (Take note that this gem only provide using draft-04 of JSON Schema)

Let's make our JSON Schema for our Book model. We can put the schemas wherever we want, but I like to put them in app/models/schemas.

To learn about JSON Schema, you can use this and this. Those two are enough.

// app/models/schemas/book_payload.json
{
  "$schema": "http://json-schema.org/draft-04/schema",
  "type": "object",
  "required": [ "title", "publisher", "published_date", "authors" ],
  "properties": {
    "title": { "type": "string" },
    "publisher": { "type": "string" },
    "published_date": { "type": "string" },
    "authors": {
      "type": "array",
      "items": {
        "$ref": "authors.json#/definitions/data"
      },
      "minItems": 1,
      "uniqueItems": true
    }
  }
}

// app/models/schemas/authors.json
{
  "definitions": {
    "data": {
      "type": "object",
      "required": [ "id", "name" ],
      "properties": {
        "id": { "type": "integer" },
        "name": { "type": "string" }
      }
    }
  }
}

Then, we put to our Gemfile, and bundle install:

gem 'activerecord_json_validator'

Then we update our model. (To learn about this gem, their github page is more than enough.)

# app/models/book.rb
class Book < ApplicationRecord
  ...

  PAYLOAD_SCHEMA = "#{Rails.root}/app/models/schemas/book_payload.json"
  validates :payload, presence: true, json: { message -> (err) { err }, schema: PAYLOAD_SCHEMA }
end

First step is finish. Now, look at the code below:

book_payload = {
  title: 'Getting to Plan B',
  pubs: 'Harvard Business Review Press',
  authors: 'John W Mullins'
  rating: 3.5
}

Book.create!(user_id: 1, payload: book_payload)

The code above will throw validation error, as the json is not valid. There are no publisher param, no publisher_date param, and authors param is not an array.

Then take a look at this code:

book_payload = {
  title: 'Getting to Plan B',
  publisher: 'Harvard Business Review Press',
  published_date: '2009-09-08',
  pubs: 'Harvard Business Review Press',
  authors: [
    {
      id: 3,
      name: 'John W Mullins',
      web: 'http://www.johnwmullins.com'
    }
  ],
  rating: 4.2
}

Book.create!(user_id: 1, payload: book_payload)

The code above will NOT throw validation error! Because it is a valid json. And you can call pubs and rating too!

Book.last.payload['pubs']
# => "Harvard Business Review Press"

Book.last.payload['rating']
# => 4.2

Book.last.payload['authors'][0]['web']
# => "http://www.johnwmullins.com"

Imagine if API Consumer give 100 parameters for every request, then your data storage will be used for garbages that you'll never need!

That's why we need the whitelisting params.

Second step, we are whitelisting params in controllers. This is necessary not only for JSONB column type, but for any other column types.

Let's update our controller:

# app/controllers/books_controller.rb
class BooksController < ApplicationController
  def create
    book = Book.create!(book_params)
    render json: { status: "OK", message: "Book created!", object: book }, status: 201
  end

  private

  def book_params
    params.permit(
      payload: [
        :title,
        :publisher,
        :published_date,
        authors: [
          :id,
          :name
        ]
      ]
    )
  end
end

You know how whitelisting params works. I think I don't have to explain the code above.

That's it. Now you have validation for your jsonb column.

5. Update

Note: All of the examples in this chapter are assuming you don't have validation for payload column in Book.

Updating JSONB column is a little tricky. Let say you only want to change the title of the last record of Book. If you do this:

Book.last.payload
# => {"title"=>"Hacking Growth", "publisher"=>"Currency", "published_date"=>"2017-04-07", "authors"=>[{"id"=>1, "name"=>"Sean Ellis"}, {"id"=>2, "name"=>"Morgan Brown"}]}

book_payload = {
  title: 'Blue Ocean'
}

Book.last.update(payload: book_payload)

Now your payload only consist of title!

Book.last.payload
# => {"title"=>"Blue Ocean"}

Book.last.payload['publisher']
# => nil

Book.last.payload['authors']
# => nil

This is the right way:

book = Book.last
book.payload['title'] = 'Blue Ocean'
book.save!

Book.last.payload
# => {"title"=>"Blue Ocean", "publisher"=>"Currency", "published_date"=>"2017-04-07", "authors"=>[{"id"=>1, "name"=>"Sean Ellis"}, {"id"=>2, "name"=>"Morgan Brown"}]}

Tips for updating JSONB column:

# app/controllers/books_controller.rb
class BooksController < ApplicationController
  def update
    UpdateBookPayload.new(update_params).call
    render json: { status: "OK", message: "Book updated!" }, status: 200
  end

  private

  def update_params
    params.permit(
      :book_id,
      :title,
      :publisher,
      :published_date,
      authors: [
        :id,
        :name
      ]
    )
  end
end

# app/lib/update_book_payload.rb
class UpdateBookPayload
  def initialize(params)
    @params = params
    @book   = book
  end

  def call
    iterate_params
    @book.save!
  end

  private

  def book
    Book.find(@params[:book_id])
  end

  def iterate_params
    params = @params.delete('book_id')
    params.each do |key1, value1|
      if key1 == 'authors'
        iterate_authors(key1, value1)
      else
        @book.payload[key1] = (value1 || @book.payload[key1])
      end
    end
  end

  def iterate_authors(key1, value1)
    value1.each_with_index do |value2, key2|
      value2.each do |key3, value3|
        @book.payload[key1][key2][key3] = (value3 || @book.payload[key1][key2][key3])
      end
    end
  end
end

Therefor, the API Consumer can pass any json to BE. We can check the class in perform using rails console:

Book.last.id
# => 10

Book.last.payload
# => {"title"=>"Hacking Growth", "publisher"=>"Currency", "published_date"=>"2017-04-07", "authors"=>[{"id"=>1, "name"=>"Sean Ellis"}, {"id"=>2, "name"=>"Morgan Brown"}]}

params = {
  book_id: 10,
  title: 'Blue Ocean',
  authors: [
    {},
    { id: 5 }
  ]
}

UpdateBookPayload.new(params).call

Book.last.payload
# => {"title"=>"Blue Ocean", "publisher"=>"Currency", "published_date"=>"2017-04-07", "authors"=>[{"id"=>1, "name"=>"Sean Ellis"}, {"id"=>5, "name"=>"Morgan Brown"}]}

6. Final Word

This is the end of Part 1. In Part 2, I'll cover about store_accessor, Query and Indexing.

source: myself and extract from many source, I don't save them, I just write what I remember

Top comments (5)

Pedro Schmitt • Jan 2 '21

Thanks for this great article!

For Rails 6 I had this error:
undefined method 'with_indifferent_access' for "{}":String'

So I needed to change the serializer:

From:

# app/serializers/jsonb_serializers.rb
class JsonbSerializers
  def self.dump(hash)
    hash.to_json
  end

  def self.load(hash)
    (hash || {}).with_indifferent_access
  end
end

To:

# app/serializers/jsonb_serializers.rb

class JsonbSerializers
  def self.dump(hash)
    hash.to_json
  end

  def self.load(hash)
    if hash.nil?
      {}
    elsif hash.is_a?(Hash) && hash.empty?
      hash
    else
      JSON.parse(hash)
    end.with_indifferent_access
  end
end

Bruno Cotteret • Apr 25 '22

Nice article !
Just to mention, the schema requires a PathName not a string as per this isssue:
github.com/mirego/activerecord_jso...
So

PAYLOAD_SCHEMA = "#{Rails.root}/app/models/schemas/book_payload.json"

should be:

PAYLOAD_SCHEMA = Rails.root.join("app","models","schemas", "book_payload.json")

Finally there is a missing '#' for the schema path at the end:

// app/models/schemas/book_payload.json
{
  "$schema": "http://json-schema.org/draft-04/schema#",

Kamaluddin Panhwar • Aug 10 '20 • Edited

Very nice and detailed, I have seen following line giving error on rails 6

validates :payload, presence: true, json: { message -> (err) { err }, schema: PAYLOAD_SCHEMA }

So change it to

validates :payload, presence: true, json: { message: -> (err) { err }, schema: PAYLOAD_SCHEMA }

Also in class I am getting error so may be we need to remove to_json part of serializer. not sure but I think that would be solution. so I removed it from class

class JsonbSerializers
  require 'active_support/core_ext/hash/indifferent_access'
  def self.dump(hash)
    hash
  end

Hossam Hammady • Nov 1 '22

Great article. A small correction in the migration:

t.jsonb :payload, null: false, default: {}

Note the unquoting of the default value. Tested on Rails 5.

Yaroslav Shmarov • Nov 21 '20

Very good knowledge. Thank you

DEV Community

Rails + PostgreSQL JSONB (Part 1)

1. Migration

2. Create

3. Show

4. Validation

5. Update

6. Final Word

Top comments (5)

Read next

Let's Read - Eloquent Ruby - Ch 22

Let's Read - Eloquent Ruby - Ch 21

Creating a global production ready marketplace with Medusa JS

SQL-92 in TPC Benchmarks: Are They Still Relevant?