jsonb and store_accessor
Treat a PostgreSQL jsonb column like a model attribute
PostgreSQL's jsonb is a column type that stores JSON as binary. Unlike the plain json type, it parses once on write, so it doesn't re-parse on read.
The real difference is indexing. jsonb supports GIN indexes, so searching by keys inside the JSON stays fast. The json type can't do that.
store_accessor โ jsonb as attributes
It lets you pack multiple values into one jsonb column and read/write them like regular columns on the model.
class User < ApplicationRecord
store_accessor :settings, :theme, :language
end
user.theme = 'dark'
user.language = 'ko'
user.save
# settings = { "theme": "dark", "language": "ko" }
You manage flexible settings through a single settings column without adding new columns. No migration every time a value is added.
When to use it
Use it for data whose schema changes often or is hard to fix up front โ user settings, cached external API responses, metadata.
For core data you search and join frequently, pull it out into real columns instead. Stuffing everything into jsonb makes queries messy fast.
On SQLite
store_accessor itself reads and writes fine on SQLite's json (TEXT) column. But GIN indexes and fast search via the @> and ->> operators are PostgreSQL-only. On SQLite you get the convenience but not the search performance.
json vs jsonb
| Aspect | json | jsonb |
|---|---|---|
| Storage | Raw text as-is | Parsed binary |
| Read speed | Parse every time | No parse โ fast |
| GIN index | โ | โ |
| Inner key search | Slow | Fast |
Search operators
User.where("settings->>'theme' = ?", 'dark')
Product.where("metadata @> ?", { category: 'book' }.to_json)
โ This project uses SQLite
On SQLite use t.json instead of jsonb. store_accessor convenience works, but GIN indexes and fast search are PostgreSQL-only.
Key Points
Add a jsonb column in migration โ t.jsonb :settings, default: {}, null: false
Declare store_accessor :settings, :theme, :language in the model
Add casting with attribute :theme, :string if a type is needed
For search, add a GIN index โ add_index :users, :settings, using: :gin
Query by inner key with the settings->>theme operator, or containment with settings @> {...}
Pros
- ✓ Manage flexible data without adding columns
- ✓ Fast inner-JSON search via GIN index (PostgreSQL)
- ✓ Access like normal attributes via store_accessor
Cons
- ✗ jsonb is PostgreSQL-only (SQLite has json only)
- ✗ Queries get messy if you stuff core searchable data in
- ✗ Can't apply constraints/foreign keys inside jsonb