Rails + PostgreSQL JSONB (Part 1)
Tags: rails, postgres
Published: 2019-12-28 16:31:55

| Rails + PostgreSQL JSONB Series |
| --------------------------- |
| **Part 1: Migration and CRUD** |
| [Part 2: store_accessor, Query, and Index](https://dev.to/kputra/rails-postgresql-jsonb-part-2-2fil) |
| [Part 3: Paginate JSONB Data](https://dev.to/kputra/rails-postgresql-jsonb-part-3-708) |
*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](https://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails)

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](#chapter-1)
[2. Create](#chapter-2)
[3. Show](#chapter-3)
[4. Validation](#chapter-4)
[5. Update](#chapter-5)
[6. Final Word](#chapter-6)
<a name="chapter-1"></a>
##1. Migration
It is as simple as any other column types.
```ruby
# 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.
```ruby
# 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.
<a name="chapter-2"></a>
##2. Create
Create a record is very simple too:
```ruby
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!
<a name="chapter-3"></a>
##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.**
```ruby
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:
```ruby
# 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
```
```ruby
Book.last.payload[:title]
# => "Hacking Growth"
```
<a name="chapter-4"></a>
##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:
```ruby
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:
1. Using JSON Schema
2. Whitelist params

**First step, we are using JSON Schema.** I won't cover what JSON Schema is. The official page [here](https://json-schema.org/understanding-json-schema/index.html) 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](https://github.com/mirego/activerecord_json_validator). (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](https://json-schema.org/learn/getting-started-step-by-step.html) and [this](https://json-schema.org/understanding-json-schema/index.html). Those two are enough.*
```javascript
// 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:
```ruby
gem 'activerecord_json_validator'
```
Then we update our model. (*To learn about this gem, their [github page](https://github.com/mirego/activerecord_json_validator) is more than enough.*)
```ruby
# 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:
```ruby
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:
```ruby
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!
```ruby
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:
```ruby
# 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.
<a name="chapter-5"></a>
##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:
```ruby
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`!
```ruby
Book.last.payload
# => {"title"=>"Blue Ocean"}

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

Book.last.payload['authors']
# => nil
```
This is the right way:
```ruby
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:
```ruby
# 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:
```ruby
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"}]}
```
<a name="chapter-6"></a>
##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