Jack comes to code

Ruby / Rails / Sinatra / APIs

'NoSqlize' PostgreSql With HSTORE in Rails (3.2+)

Many people asked why Mac OSX, Rails and Heroku prefer PostgreSql to MySql. This post is talking about one of its outstanding features: hstore

One year ago, Ryan Bates’s post had introduced hstore, but there are a few updates for Rails 3.2. If you did not know what hstore is, this is its usage in Rails:

1

user = User.create(:interests => {male: false, femal: false, it_is_my_little_secret: true})

It’s easy to find there are json-like key/value pairs in the example above. It’s more awesome that the key it_is_my_littler_secret is dynamic, which can be customized by front end users. Let’s see how it could happen with Rails now:

Get Started

Gemfile
1

gem 'activerecord-postgres-hstore'
config/application.rb
1

config.active_record.schema_format = :sql #As the schema for hstore can't be represented by ruby
Terminal
1

rails g hstore:setup # Run 'CREATE EXTENSION IF NOT EXISTS hstore'
Migration File
1
2

add_column :users, :interests, :hstore
execute 'CREATE INDEX users_interests ON users USING GIN(interests)'  #Index on the hstore field
Termial
1

rake db:migrate

The basic setup is done, now we are looking into how to use hstore in Rails’ MVC framework:

Usage in Rails

app/models/user.rb
1
2
3

  attr_accessible :interests #To make sure interests is not mass assignment protected
  store_accessor :interests, %w{male, female} #Put any default keys showing in html forms
  serialize :interests, ActiveRecord::Coders::Hstore
app/views/users/edit.html.haml
1
2
3
4
5
6
7
8
9
10

  =form_for @user do |f|
   .row
    =f.label :male
    =f.check_box :male
   .row
    =f.label :female
    =f.check_box :female
   .row
    =label :new_key, "Other"
    =text_field :interests, :new_key
app/controllers/users_controller.rb
1
2
3
4
5
6
7
8
9
10
11
12

def update
  @user = User.find(params[:id])
  if @user.update_attributes(params[:user])
    if params[:interests].present? and params[:interests][:new_key].present?
      @user.interests = @user.interests.merge(params[:interests][:new_key] => true) #Add the new key to the interests
      @user.save
    end
    render "#{some_path_with_success_message}"
  else
    render "#{some_path_with_fail_message}"
  end
end

Yeah, that’s it. You could be more flexible by not fixing any keys for the hstore field.

Querying hstore fields is not pain at all, here’s the full list of hstore operations

Wana comment or punch on the post?