Storing settings as JSONB using Rails and PostgreSQL

You can store native JSON data in PostgreSQL databases version 9.4+, and ActiveRecord allows taking advantage of that format.

It’s important to note that the JSONB format allows indexing and searching, and is not much slower than plain JSON, so you’re better off using JSONB everywhere.

I highly recommend Nando Vieira’s blog post Using PostgreSQL and jsonb with Ruby on Rails if you want to read more about database performance and how to query using attributes stored inside JSONB columns.

The store_accessor class method is defined in ActiveRecordStoreClassMethods module, which is automatically included with ActiveRecord. It takes a column name, keys, and allows prefixing or suffixing the generated accessors. This allows validations and callbacks to work seamlessly with JSON data for instance. This method generates accessor and change-tracking methods, so that data stored in a JSON/JSONB column is as easy to work with as regular columns.

As with everything, you’ll need to weigh the pros and cons before deciding to use dedicated columns or dumping everything as JSONB, but here’s a situation where I find JSONB is appropriate: tenant settings.

Why store tenant settings as JSONB

In a single-tenant situation, it would not make much sense to add/rename columns on a table which never holds more than one record.

In an app with multiple tenants, it’s often the case that each tenant wants some kind of customisation, and the ‘tenants’ table would need to have columns for each and every setting requested by any number of tenants.

In both cases, storing values as JSONB allows saving any kind of data without having to mess with migrations, so you have the best of both worlds.

Here is the JSONB-related code in my Tenant model.

# app/models/tenant.rb
class Tenant < ApplicationRecord
  KEYS = [:name, :blog_url, :contact_email].freeze
  store_accessor :settings, *KEYS

  validates :name, presence: true
  validates :contact_email, format: { with: /\A([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})\z/i }

  class << self
    def fields
      @fields ||= KEYS.collect { |key| [key, key.match(/(?:.*)_(.*)/) { |type| type[1].to_sym } || :text] }
    end
  end
end

I start by defining the list of keys saved in JSONB as a class constant because I use it twice. It also documents the keys saved for each tenant.

Then I tell store_accessor to generate accessors for the list of keys I’ve just defined.

I can then add validations for attributes stored in JSONB as if they were regular columns.

Finally, I add the Tenant.fields method which lists every key and its type, inferred from the key suffix (‘url’, ‘email’, etc). This method makes it easy to generate the relevant fields in my tenant form using the following code:

# app/views/tenants/_form.html.erb
<%= form_with(model: tenant) do |f| %>
  <% Tenant.fields.each do |key, type| %>
    <div class="field">
      <%= f.label key %>
      <%= f.text_field key, type: type %>
    </div>
  <% end %>

  <%= f.button "Save" %>
<% end %>