Store collections in JSONB field
Published 20. february 2019
You've probably heard about the JSON and JSONB datatypes that PostgreSQL database provides. When it comes to Rails the usual use of these types is to store key-value data (i.e. hashes) in it, either directly or using something more sophisticated like Virtus or Storext gems.
In this post I'm going to show you how you can use this datatype to store not just hashes, but collections of them and also take advantage of validations, casting and other nice features that Rails' models and attributes provide.
In other words we're going to mimic the has_many relationship but instead of using a separate table for the secondary model we're going to store the child models in a JSON(B) field in the primary table.
Let's say we want to store comments to a blog post. Normally we would create a normal Comment model with it's own table and create a
has_many :comments association on Post model and
belongs_to :post association on the Comment model. In this example we're going to store the Comment instances directly in the
First we need to create the
posts table with a
comments JSONB field:
class CreatePosts < ActiveRecord::Migration[5.2]
create_table :posts do |t|
add_index :posts, :comments, using: :gin
Models and Serializers
Once we have the table we're going to write a very simple Post model:
class Post < ApplicationRecord
serialize :comments, CommentsSerializer
The interesting part is line XXX where we're telling ActiveRecord to serialize comments into our
comments field using a custom serializer that takes care of transforming our array of Comment objects into the JSONB field when we save the Post instance and transforming the JSON from the DB back into array of Comment objects upon read:
# save to database
(comments || ).map &:to_hash
# load from database
(comments || ).map do |c|
Now let's take a look at the Comment model:
attribute :author, :string, default: 'Anonymous'
attribute :body, :string
validates_presence_of :author, :body
As you can see we're not inheriting from the ActiveRecord::Base class as we don't need all the standard machinery for working with a dedicated table. All we need to include is the ActiveModel::Model for validations and include ActiveModel::Attributes which allows us to define types and default values.
That's pretty much all on the model side. Now we can store Comment instances inside of the Post instances:
post = Post.new(title: 'Hello!', body: 'World!')
post.comments << Comment.new(author: 'Rick', body: 'https://is.gd/2aLkKB')
Now let's see how we would handle the comment form on the blog post page. We're going to create a new route for adding new Comment to a Post. This route will live inside of the
posts resource and will be defined on a member of this resource, since a Comment always belongs to a particular Post.
resources :posts, only: [:index, :show] do
post 'comments', to: 'posts#create_comment', on: :member
Let's create the Posts controller with the create_comment action that takes care of adding the submitted Comment to the parent Post:
class PostsController < ApplicationController
before_action :load_post, only: [:show, :create_comment]
@posts = Post.all.order(created_at: :desc).limit(10)
@comment = Comment.new
@comment = Comment.new(comment_params)
@post.comments << @comment
@post = Post.find(params[:id])
Finally, we need the views. View for the
index action is pretty straightforward (we just need it it list the posts while rendering link to the
show action for each), the
show view is more interesting since it both lists the existing comments and displays the comment form (possibly with validation errors):
<h2><%= @post.title %></h2>
<p><em>Published: <%= l @post.created_at %></em></p>
<%= @post.body %>
<% @post.comments.each do |comment| %>
<b><%= comment.author %></b> said: <%= comment.body %>
<% end %>
<h4>Add your comment!</h4>
<%= form_for @comment, url: comments_post_path, local: true do |f| %>
<% @comment.errors.full_messages.each do |msg| %>
<p><%= msg %></p>
<% end %>
<%= f.text_field :author %><br>
<%= f.text_area :body %><br>
<%= f.submit "Submit comment" %>
<% end %>
Now if you visit the post page you'll be able to see the full list of comments and also add a new one. Try to submit an invalid comment to see that the validation works.
If you look into the
posts table in your database you'll see that the comments data is nicely stored as a JSON object on which you can then perform complex queries thanks to the index we've created.
So to recap: this is a nice and simple way to store embedded collections in a JSONB field while keeping rails goodies like validations, casting, etc. All plumbing like form helpers also work as you're used to. All you really need is a JSONB field, serializer and a lightweight non-AR model.
You can see the complete source code of a working demo project on my GitHub: michalvalasek/rails_jsonb_collections
Have an idea on how to improve this? Let me know!