Skip to main content

Preferences & Settings with ActiveRecord::Store Discussion

General • Asked by Chris Oliver

I created this for Rails 4 https://rubygems.org/gems/h... using JSON columns. It is the same idea.

I say that purely to vouch for this method of prefs/settings storage.

This is awesome, thanks for sharing that John!


you're saying we can query items inside the Preferences attribute.

in other words..
User.where(playback_rate: "0.75")

...would work?

You can as long as you're using hstore or json column in Postgres, but it's not quite the normal where syntax like you mentioned. You have to use the SQL syntax I showed in the episode for querying JSON columns (note the "->>" part at the end).

Hstore has its own syntax as well that's a bit different and uses "@>" and some other syntax.


Great video Chris. Pro's / Con's JSONB vs JSON pure PG column? I know Codeship had a good write-up on the two ( https://blog.codeship.com/u... ), but was interested in anything you had on that front? Cheers, Nick

I believe that JSONB is probably the best format for this because it's a binary representation. Should be more efficient for most everything. There's some more information on this stuff here: https://www.citusdata.com/b...


I've fallen into using this pattern several times, but I'd very down on it now. It's usually because I've been inappropriately afraid of column or migration proliferation, but that's actually a false alarm. PostgreSQL can handle hundreds of columns, and migrations aren't that scary.

Each time, I've ended up with an awful lot of internal infrastructure and un-rails-y configuration-over-convention (like those typed stores) to reinvent something that PostgreSQL, in conjunction with ActiveRecord, does exceptionally well by design: storing typed scalars. The code smell is compounded by heaps of nil-whacking and existence-checking and validations which using a simple DB column would make wholly unnecessary.

The most shameful case is boolean attributes, which no longer have two states to deal with, but five: true, false, nil, nonexistent, and oops a string. Yes, that last cropped up for me because some other app used a string during an import. When you see code handling a five-state boolean, you a) have a little cry, and then b) deal with the stench.

For my major app, I recently threw away the JSON preferences store and made them all regular columns. The diff is mostly lines removed.

I still use JSON stores, but reserved for three cases:
1. In which there's an application-level semantic difference between attribute having the null value, and attribute not present. In this case I find using the JSON stores are less hassle than implementing the EAV pattern.
2. In which there is a deep JSON structure to store & query e.g. when we have user-supplied attribute names (in which case we're not using store_accessors anyway)
3. When I have more columns than PostgreSQL can handle. (This has never happened)

I will never willingly use them as a first choice for settings/preferences again.

This actually makes a lot of sense. I've had frustrations with putting all my preferences in a single column. It bothers me to have a bunch of columns for preference data, but as I think about it, there's no "real" reason it should bother me; it's my own "preference" (no pun intended). I would have access to all of ActiveRecord's power, and the data would be treated like a first-class citizen instead of delegated to the sidelines. You're right that migrations aren't something to be scared about, and if you use a single column hash you have to re-deploy changes anyway, which should run migrations by default.

Adding 10-15 DB columns for preferences may be a better solution, because then you have full index/query power, even though JSONB can be indexed (I think). No reason to complicate things though, IMO.

One use case I've had for using single-column hashes is for unstructured data that you don't know ahead of time, such as someone uploads a JSON file with unknown data and you have to parse it out. But preferences are 99% of the time structured data that you know ahead of time and are building core logic around (ie, "send emails if this setting is true").

You can definitely do all of that with typed store and a json column. Like I showed in the example, it casts both going in and out of the store, so there's no chance you'll have booleans with 5 states.

One better example is probably feature flags that will often come and go. Adding and removing columns for every feature flag is going to be way overkill for something that can easily be handled through a store.


Great topic Chris!

Would you recommend this method if I want to store a lot of user information? Say I'm building a digital resume, experience, skills (tags), educations etc.. My end goal is to able to search this data either with angularjs or searchkick. Currently I have several tables user, user_jobs, user_experiences, user_skills etc.


I did find one "gotcha" with using a preferences hash like this. In my particular situation, I wanted a settings hash that stored a nested filters hash.

My use case is I have a page where I want to use URL params for filtering:

/contacts?first_name=John&last_name=Smith

I wanted to store the filters hash via:

# in controller
user.update_attribute(settings[:contact_filters], params.slice(:first_name, :last_name))

# What I want it to do...
settings: {
contact_filters: {
first_name: "John",
last_name: "Smith"
}
}

The problem is, that the nested hashes are stored as strings and it is nigh impossible to cast them cleanly to a normal Ruby hash (at least after 15 min of StackOverflow searching).

I was thinking that I could get it to somehow work with a combination of:

JSON.parse(user.settings[:contact_filters])

but no such luck. I do love the simplicity of the nested hashes, but it does seem to have some difficulties at least with nested hashes.


Hey Chris, is there a way to use the Rails 5+ attributes API to store settings in a jsonb column instead of using the activerecord-typedstore gem?  Trying to see if I can remove external dependencies and use built-in Rails features.

I've just been looking to add this to my app on a User model. Couple of questions:

  1. Can nested data be stored / accessed? If not what would be a better way to handle that?
  2. Does SQLite & MySQL support the JSON column type?

For anyone who may be wanting to use Typed_store in rails 6, there is currently no support.
https://github.com/byroot/activerecord-typedstore/issues/64

I'm currently upgrading from Rails 5.2 to 6.0.1 and this gem was one of the blockers for me since it has a dependency on Rails 5.2. Really the only problem is that Rail's built-in store persists these values as strings and this gem typecasts the values for you. Once you handle that, you don't need this gem anymore and can write your own module or small gem.

See below for working version that doesn't use the gem. Obviously I haven't addressed null values and defaults, but those are fairly simple. Once I have a working module I may update this comment with the code so others can use it.

I don't think it's worth making a gem to add a few lines of code, so I tend to store something like this in a lib/modules/typed_store.rb file and then include TypedStore in my model file to use it.

Code from the typed_store gem (from my live project)

typed_store :recurring_rules, coder: DumbCoder do |s|
    s.integer :recurring_interval, default: 1, null: true
    s.string :recurring_frequency, default: "day", null: true
    s.integer :recurring_days, array: true, default: nil, null: true
    s.integer :recurring_day_of_month, default: nil, null: true
  end

Using built-in Rails store

If you're using PG with jsonb column types, you can use store_accessor directly and don't have to use the store method.

Just override the accessor methods to handle typecasting; that's also where you would handle defaults, nulls, etc...

store_accessor :recurring_rules,
    :recurring_interval,
    :recurring_frequency,
    :recurring_days,
    :recurring_day_of_month

  def recurring_interval
    super.to_i
  end

  def recurring_frequency
    super.to_s
  end

  def recurring_days
    super.to_a.map(&:to_i)
  end

  def recurring_day_of_month
    super.to_i
  end

Can anybody point to some resources of using this with an array in a jsonB column

So For eg, if data was like this:

 equipment_required :jsonb

> Report.first.equipment_required
=> 
[
{"name"=>"Brushwood Chipping", "size"=>"6\"", "type"=>"Wheeled", "quantity"=>"1", "hours_required"=>"2"}, 
{"name"=>"Tipper", "type"=>"Normal", "quantity"=>"1", "hours_required"=>"2"}
]

Why settings is a text format and preferences is a json format ?


What is the syntax for querying a boolean value?

Trying the below doesn't seem to work? and throws the following error:

PG::UndefinedFunction: ERROR: operator does not exist: text = boolean

current_user.companies.where("holiday_request_settings ->> :key = :value", key: "enable_holiday_requests", value: true)

If I try the following I get no results:

current_user.companies.where("holiday_request_settings ->> :key = :value", key: "enable_holiday_requests", value: "true")

The company current has the following data:

holiday_request_settings: {"enable_holiday_requests"=>true, "holiday_allowance_days"=>20}

This is the code on the model:

typed_store :holiday_request_settings, coder: JSON do |s|
        s.boolean :enable_holiday_requests, default: false
        s.integer :holiday_allowance_days
end

Any ideas?


Login or Create An Account to join the conversation.

Subscribe to the newsletter

Join 27,623+ developers who get early access to new screencasts, articles, guides, updates, and more.

    By clicking this button, you agree to the GoRails Terms of Service and Privacy Policy.

    More of a social being? We're also on Twitter and YouTube.