Using Postgres' hstore in Rails 4 with scopes

Hstore has been around for a while now but its Rails support has become impressive since Rails 4. In addition to that, it’s now supported on Heroku Postgres.

Hstore allows you to store custom data against a row in the database. I’ve used it a little in the past but have often relied on Rails’ serializer to do the same thing. Hstore is an improvement on that, and I’ll show you why.

Hstore is an extension to Postgres which has to be enabled. This can be done through a migration.

class EnableHstore < ActiveRecord::Migration
  def change
    enable_extension 'hstore'
  end
end

Next we need to add an hstore column to a model.

class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
      t.string :email
      t.hstore :detail

      t.timestamps null: false
    end
  end
end

Now let’s set up the model to make use of the new hstore column.

class User < ActiveRecord::Base
  store_accessor :detail, :name, :likes_cats
  
  validates :name, :likes_cats, presence: true
  validates :likes_cats, inclusion: { in: %w(y n) }

  def self.like_cats
    where("detail @> hstore(?, ?)",'likes_cats','y')
  end
end

The store_accessor tells rails what pseudo columns we want to use via the detail hstore column. As you can see validations can be used and do use them since there are no database restrictions in place for the hstore column.

The best thing about it all is that we can still query the hstore pseudo columns. In this example, we can find all the user who like cats by running User.like_cats. This is pretty awesome!

A few things to keep in mind:

• You can only work with strings. So for dates, booleans, and others you'll want to put a bit of thought into how to manage those.
• You might want to set up defaults for the columns. This will have to be done within your model.
• Use validations to ensure data integrity. Don't use hstore for core fields though, use non-nullable columns.
• You can also add indexes if you need.

I hope this was useful to someone other than me!