DEV Community

K Putra
K Putra

Posted on • Edited on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
Book.last.payload[:title] # => "Hacking Growth"
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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 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" } } } } }
Enter fullscreen mode Exit fullscreen mode

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

gem 'activerecord_json_validator'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Now your payload only consist of title!

Book.last.payload # => {"title"=>"Blue Ocean"} Book.last.payload['publisher'] # => nil Book.last.payload['authors'] # => nil
Enter fullscreen mode Exit fullscreen mode

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"}]}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"}]}
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
pedromschmitt profile image
Pedro Schmitt •

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
Collapse
 
bcotteret profile image
Bruno Cotteret •

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"
Enter fullscreen mode Exit fullscreen mode

should be:

PAYLOAD_SCHEMA = Rails.root.join("app","models","schemas", "book_payload.json")
Enter fullscreen mode Exit fullscreen mode

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#",
Enter fullscreen mode Exit fullscreen mode
Collapse
 
kamalpanhwar profile image
Kamaluddin Panhwar • • 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 }
Enter fullscreen mode Exit fullscreen mode

So change it to

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

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
Enter fullscreen mode Exit fullscreen mode
Collapse
 
superails profile image
Yaroslav Shmarov •

Very good knowledge. Thank you

Collapse
 
hammady profile image
Hossam Hammady •

Great article. A small correction in the migration:

t.jsonb :payload, null: false, default: {}
Enter fullscreen mode Exit fullscreen mode

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