Store an array in PostgreSQL hstore in Rails4

Let’s consider the following situation. You’re building an app, where each user should be able to select what are one’s favourites colors. The gotcha is that (as more and more women register to our app …. :P) the available set of colours can be modified over time. How to tackle this task in Rails?

Well, as often when working with Ruby, there are several ways you can solve this task. If you happen to be using a NoSQL, document-oriented database (eg. MongoDB, Redis) then the task is actually not a problem at all. However, if you happen to be stuck to some relational DB, then you might consider to create a separate table to store colours and then some many-to-many relationship with users… nahh, awful, it is not definitely not worth it, as the only thing we need to store is colour. The other thing you might do is to store the colours array as a serialized attribute at users table. But this has some drawbacks, among the most painful is that you cannot efficiently search users basing on their serialized attributes (1, 2).

The efficient way to solve this task is to use the PostgreSQL hstore module. It is designed to store a key-value pairs of strings. As for PostgreSQL 9.3, it is not possible to natively store arrays of values in hstore. However not for long! As announced here, the version 9.4 will be shipped with a build-in support for nesting and array storage! But we have to wait for this another couple of months (~ October 2014). As for now, all we can do is to make a use of key-value store and a build-in support for hstore that came with Rails4 ActiveRecord (before it was a separate gem).

There a a couple of blog posts describing how to integrate hstore into your Rails app (eg. 1, 2), so there is no point to repeat it. Rails4 ships as a standard with a support for a handy store_accessor, which usage is described clearly here.  Please note however, that this method allows us to store only single value for every hash key. If you need to store an array of values for every hash key, keep on reading

Let’s assume we have a working PostgreSQL [email protected], we have enabled hstore extension and have migrated the Users table, that contain the settings hstore field.

First thing, as a good habbit when using hstore (might save you time debugging!), we might want to do is to override a getter for settings in User model, as hstore save booleans as strings and we need to convert them back:

class User < ActiveRecord::Base
  #...
  def settings
    return (super == "true") if %w{true false}.include? super
    super
  end
  #...
end

Then we define the actual getter and setter for colors. It should be constructed so when we provide an array, let’s say: [‚purple’,’orange’], it should be properly serialized to the value of favourite_colors key of the settings field. Let’s try it in the Rails console:

user = User.new #<User id:nil, settings: nil>
user.settings = {favourite_colors: ['purple','orange']} #{:favourite_colors => ['purple','orange']}
user.settings #{"favourite_colors"=>"[\"purple\", \"orange\"]"}

Cool! The array is automaticaly converted to string. How can we retrieve the array back? Well, basically we can treat it as JSON, so type in console:

colors_array =
JSON.parse(user.settings['favourite_colors']) #['purple','orange']

Voila! Now we are ready to construct our getter and setter:

class User < ActiveRecord::Base
  #...
  def settings
    return (super == "true") if %w{true false}.include? super
    super
  end

  def favourite_colors
    if settings.present? && (colors=settings['favourite_colors']).present?
      JSON.parse(colors)
    end
  end

  def favourite_colors=(colors)
    self.settings = {:favourite_colors => colors}
  end
end

Let’s get further and construct a form and controller action to actually set this colors.

#app/views/users/_form.html.slim
== simple_form_for @user do |f|
  .form-inputs
    = f.input :favourite_colors, as: :check_boxes, collection: @available_colors
  .form-actions
    == f.button :submit

Then in the controller all we need to do is to set propertly the strong parameters to accept our custom data format:

#app/views/controllers/users_controller.rb
class UsersController < ActionController::Base

#...

private

  def user_params
    params.require(:user).permit(:favourite_colors => [])
  end

end

I hope it was helpful, good luck!