Subscribe to GoRails to get access to this episode and all other pro episodes, and new awesome content every month.
Unlimited access. Cancel anytime.
Use ActiveRecord::Store and ActiveRecord-TypedStore to save user preferences and settings to a single column in the database
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.
you're saying we can query items inside the Preferences attribute.
in other words..
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'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:
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...
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:
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.