H is for Hstore

Using Postgres, like, for Real

writting by Hsing-Hui, Ada student. Read the original post on her blog.

The vast majority of Rails apps we build are hosted on Heroku, which uses PostgreSQL to store database tables. Since starting a new project by running rails new defaults to using SQLite3, the way kind of hacked around this problem was by simply using SQLite in development and testing, and postgreSQL in production, like so:

(in Gemfile)

group :production do
  gem "pg"
end

This is clearly not ideal–it’s usually best practice to use the same kind of database in development as you will in production. So, to setup your Rails app to use postgres for real, simply require the gem ‘pg’ in your Gemfile without specifying a group. Then, in your config/database.yml file, change the adapter and database lines like so:

development:
  adapter: postgresql
  database: appname_development
  pool: 5
  timeout: 5000
  host: localhost

test:
  adapter: postgresql
  database: appname_test
  pool: 5
  timeout: 5000
  host: localhost


You don’t need to do anything with the production database, since Heroku sets all that up for you. Once yourdatabase.yml file is updated, run rake db:create in your command line, migrate if necessary, and boom! Your app is now configured to postgreSQL.

Reminder: After you deploy your app to Heroku, don’t forget to run heroku run rake db:migrate !

So now that I’m using PostgreSQL, what is it good for?

While postgreSQL lives in the world of relational databases, you can also get the benefits of a schema-less database through a nifty feature called HStore. Before jumping straight into a technical explanation of Hstore, let’s take a few steps back and look at how we usually work with model data in Rails.

Suppose you have a user model in your application, and you want to give a user the option of receiving emails from your application. In your schema, you might have a column named “Email Preference” set to a boolean. But suppose there are many different kinds of emails a user could receive, such as registration confirmation, or event updates, or new events. One way to do it using our traditional relational database schema is to have another model named “Preferences” that belongs to a user. In other words, you’d have something like this in your app/models/person.rb and app/models/preference.rb files:

class User < ActiveRecord::Base
    has_one :preference
  end
  

class Preference < ActiveRecord::Base
    belongs_to :user
  end
  

Then you’d have the following migrations:

class AddColumnsToUser < ActiveRecord::Migration
    def change
      add_column :users, :name, :string
      add_column :users, :email, :string
    end
  end
  

class AddColumnsToPreferences < ActiveRecord::Migration
    def change
      add_column :preferences, :registration, :boolean
      add_column :preferences, :new_event, :boolean
      add_column :preferences, :user_id, :integer
    end
  end
  

So now, to get a Person object’s language preference, you could call .preference.language on a person object. That’s all well and good, but suppose you want to add another field to a person’s preferences? That requires adding another migration, changing the schema, etc. etc…

With Hstore, however, you can eliminate the need for generating a whole other table associated to the User model. Basically, hstore allows you to store data in a table column as a hash. Better yet, you can just add key-value pairs dynamically, without having to plan them out in your database schema first. In other words, suppose one user only wants emails for new events. Then that user’s preferences would be stored as the hash { new_event: true }. But suppose another user wants to receive emails for new events as well as promos. Then his/her preferences can be stored as the hash { new_event: true, promos: true }.

Setting up Hstore

Assuming you’ve followed the steps above for setting up postgreSQL, setting up your app to use hstore is easy:

1. In your application.rb file, add the following line:

   config.active_record.schema_format = :sql

2. In your terminal, generate a migration the usual way:

   rails g migration add_hstore

   Then, in the migration file, enable the hstore extension:

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

3. Finally, add a column in your model that specifies hstore as the column type:

   class AddHstoreColumnToUsers < ActiveRecord::Migration
     def change
       add_column :users, :preferences, :hstore
     end
   end

To access the keys in the preferences hash, you can set them through a method called store_accessor in your model:

class User < ActiveRecord::Base
  store_accessor :preferences, :registration, :new_event, :event_update, :promos
end

This allows you to simply call any of the keys as you would an attribute on the user object. Easy!