This article was originally written by Jonathan Miles on the Honeybadger Developer Blog.

Rails is a large framework with a lot of handy built-in tools for specific situations. In this series, we'll take a look at some of the lesser-known tools hidden in Rails' large codebase.

In this article, we'll focus on ActiveRecord's store and store_accessor methods. Both of these methods are aimed at the use case of storing structured data in a database column, such as JSON or YAML. While store_accessor gives us a handy way to grab values from these data without clogging up a model with getter methods, store goes a step further and transparently serializes/deserializes data to our chosen format. To understand where this can be useful, we'll also take a look at options for storing JSON in relational databases and some of the reasons you may want to do so.

JSON in the Database

I should clarify that when I say 'database' in this article, I'm referring to relational databases, particularly PostgreSQL and MySQL, as they are the most widely used in the Rails community.

One might ask why you would want to store JSON in a relational database. Indeed, the way to leverage the benefits of relational databases is to break up data so that relationships between them can be enforced by the database (e.g., foreign keys), and the data can be indexed to improve query performance.

One drawback of the relational database model is that the data structure has to be both known ahead of time and identical for each row in a table. If your application is built around data that doesn't meet these requirements, you may want to investigate NoSQL databases. For most web apps, though, we want to stick to the devil relational databases we know for most of the data and just "sprinkle" in these dynamic data structures judiciously. In these cases, something like a JSON column can make a lot of sense.

JSON vs. JSONB

PostgreSQL has two kinds of JSON columns: json and jsonb. The main difference is that jsonb is parsed at write-time, which means the data are stored in a format the database can query faster. The caveat is that because the JSON is already parsed, when output as text, it may no longer match exactly what the user has entered. For example, duplicate keys might be removed, or the key order may not match the original.

The PostgreSQL documentation states that in most cases, jsonb is what you want unless you have a specific reason otherwise.

MySQL's json column behaves similarly to jsonb in PostgreSQL. To support a 'just what the user entered' output, you would probably have to use a varchar column or something similar.

JSON vs. Text

In addition to allowing the data to be pre-parsed, using a JSON column instead of storing the same data in a text field allows queries that use the data itself. For example, you could query all records where a particular key-value pair exists in the column. Note that Rails itself doesn't support many (if any) JSON-specific queries, as they are database-specific. Thus, if you want to leverage these features, you'll have to use SQL queries to do so.

JSON Columns in Rails

Rails has support for creating json (and jsonb on PostgreSQL) columns in migrations:

class CreateItems < ActiveRecord::Migration[7.0]
  def change
    create_table :items do |t|
      t.jsonb :user_attributes

    ...
    end
  end
end

When reading this column, the returned result is a Hash:

> Item.first.user_attributes
  Item Load (0.6ms)  SELECT "items".* FROM "items" ORDER BY "items"."id" ASC LIMIT $1  [["LIMIT", 1]]
=> {"color"=>"text-red-400"}
> Item.first.update!(user_attributes: {color: "text-blue-400"})
> Item.first.user_attributes.dig(:color)
=> "text-blue-400"

Now that we have a Hash attribute, you might be tempted to add some helper methods to the model to read/write the values:

class Item < ApplicationRecord
  def color=(value)
    self.user_attributes["color"] = value
  end

  def color
    user_attributes.dig("color")
  end
end

Methods like this function perfectly well but could quickly become unwieldy if you have a lot of JSON keys to deal with; fortunately, Rails has us covered.

ActiveRecord's store and store_accessor

There are two aspects of storing JSON in the database: serialization and access. If you're using a json-type column in your database, then you don't need to worry about the serialization aspect. Rails and the database adapter will handle it for you (you can skip straight to store_accessor). If you're storing the data in a text column, then ActiveRecord's store method is for you, which ensures that the data you write to the column is serialized into your chosen format.

ActiveRecord's store

ActiveRecord has a store method to automatically serialize the data we read or write to our column:

class Item < ApplicationRecord
  store :user_attributes, accessors: [:color], coder: JSON
end

Here, :user_attributes is the column we want to use, while accessors is the list of keys we want to access (just color in our case here), and lastly, we specify how we want the data to be encoded. We're using JSON, but you could use anything you like here, including things like YAML or custom encodings. This method just handles the serialization (with your chosen coder) and calls store_accessor under the hood.

ActiveRecord's store_accessor

We create the get/set methods in our model by using store_accessor:

class Item < ApplicationRecord
  store_accessor :user_attributes, :color
  store_accessor :user_attributes, :name, prefix: true
  store_accessor :user_attributes, :location, prefix: 'primary'
end

Here again, user_attributes is the database column we want to use, followed by the key we want to use in the JSON data, and lastly, we have the option to use a prefix (or suffix). Note that store_accessor does not support nested data, only top-level key-value pairs. The prefix and suffix options take either a boolean, string, or symbol. If a boolean true is passed, then the name of the column is used as the prefix/suffix.

=>item = Item.create!(color: 'red', user_attributes_name: 'Jonathan', primary_location: 'New Zealand')
>#<Item:0x000055d63f4f0360
 id: 4,
 user_attributes: {"color"=>"red", "name"=>"Jonathan", "location"=>"New Zealand"}>
=>item.color
>"red"
=> item.user_attributes_name
>"Jonathan"
=> item.name
>NoMethodError: undefined method `name'...
=> item.primary_location
>"New Zealand"

Real-World Usage

I've only occasionally needed to stray from the typical known-ahead-of-time relational database schema. The few times I have, it made the database structure both cleaner and simpler than it might be without these options.

One example I've come across is supporting multiple APIs where the user connects their own accounts. This becomes tricky when the APIs do not use the same authentication schemes. Some may use username+password, while others use an API key, and still others have an API key, a secret, and a merchant ID. One approach is just to keep adding columns to the table, where many of them will be null for most providers. Using json, however, we can store only the values that a particular API needs.

A side project I'm working on also uses JSON storage to allow users to set arbitrary attributes on items, including user-defined attributes. Given the fluid and unpredictable nature of this data, something like JSON storage (with store_accessors for known attributes) is a natural fit.

Summary

JSON data (and ActiveRecord's helpers around it) can be very useful when the data and data structure are changeable or unknowable. Of course, this kind of data storage is, like most things, a trade-off. While you get a lot of flexibility in the data structure for particular records, you give up some of the data integrity that database constraints can give you. You also reduce your ability to query across records with typical ActiveRecord queries, joins, etc.

Here are a few rules-of-thumb, if you:

1. Know that the JSON keys will be the same for all rows, or
2. Are storing the ID (primary key) of any other database table, or
3. Are storing a value used to look up a record from a table in the JSON

Then, you may be better off creating a new table that can leverage the database to enforce the data integrity for you. If, however, you are storing row-specific data that do not relate directly to other tables, then JSON might help you simplify your database structure.