Using Postgres Hstore with Rails 4

I’m a huge fan of PostgresSQL given it’s performance and stability. One of it’s great features is the ability to store hashes in tables using the Hstore column type AND to query them! This is perfect for a column like user settings where you may need to add or change the type of settings you need as your app evolves. Here’s some steps and gotchas that I came across while using this column.

Setup

So first let’s start by creating a migration which would enable the Hstore extension on our database:

class AddHstore < ActiveRecord::Migration
  def up
    execute 'CREATE EXTENSION hstore'
  end

  def down
    execute 'DROP EXTENSION hstore'
  end
end

Next you’ll need to change your schema dump format from ruby to sql as the schema for hstore can’t be represented by ruby.

In config/application.rb:

config.active_record.schema_format = :sql

I’ve also come across a more cleaner way to enable this without using the native execute command (although I never got it to work):

class SetupHstore < ActiveRecord::Migration
  def self.up
    enable_extension "hstore"
  end
  def self.down
    disable_extension "hstore"
  end
end

With this approach you don’t have to even change the schema_format, it will automatically add enable_extension "hstore" in your schema.rb file. Again, I’ve yet to see this work but I’d try this first before using the native sql approach.

Usage

Now you are ready to start using this column. Let’s add a settings column to our users table.

class AddSettingsToUsers < ActiveRecord::Migration
  def change
     add_column :users, :settings, :hstore  
  end
end

If you know which keys you’ll store in the settings column, you can define accessors in your model and even validate them:

class User < ActiveRecord::Base
  store_accessor :settings, :theme, :language

  validates :theme, inclusion: { in: %w{white, red, blue} }
  validates :language, inclusion: { in: I18n.available_locales.map(&:to_s) }
end

Although, if you don’t know what kind of keys the client will send your controller, here’s a way to receive dynamic keys/value pairs using strong params:

def some_params
  params.require(:some).permit(:name, :title).tap do |whitelisted|
    whitelisted[:settings] = params[:some][:settings]
  end
end

We are now ready to query our users based on their settings:

rubyUser.where("settings -> 'language' = 'en'")

More query syntax can be found here in the hstore documentation.

Indexes

hstore provides index support for a few select operators such as @>, ?, ?& or ?|. So if you are planning to use any of those, it’s probably a good idea to add an index for better performance:

class AddIndexToUserSettings < ActiveRecord::Migration
  def up
    execute 'CREATE INDEX users_settings ON users USING gin(settings)'
  end

  def down
    execute 'DROP INDEX users_settings'
  end
end

Alternatively you can use the native DSL to accomplish the same thing:

class AddIndexToUserSettings < ActiveRecord::Migration
  def up
    add_index :users, [:settings], name: "users_gin_settings", using: :gin
  end

  def down
    remove_index :users, name: "users_gin_settings"
  end
end

I generally tend to use GIN index as the lookups are three times faster compared to GiST indexes, although they take three times longer to build. You can checkout their documentation to decide which is the best fit for your needs.

Heroku

If you are using Heroku, there is one small issue I ran across. As a consequence of switching to sql schema format, I kept getting this error when heroku ran db:migrate at the end of each deployment:

No such file or directory - pg_dump -i -s -x -O -f /app/db/structure.sql

It’s trying to dump a new structure.sql with the pg_dump command which is not present on Heroku’s environment. You really don’t need that in production anyway. So the way to get around it would be to just silent that task if it’s running in any environment other than development by adding this in your Rakefile:

Rake::Task["db:structure:dump"].clear unless Rails.env.development?

That’s a wrap! Let me know if I missed anything.