Hsing-Hui Hsu

H is for Hstore


Using Postgres, like, for Real

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 :development, :test do
  gem "sqlite3"
end

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 your database.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!


Further reading

Contributing to Open source...again.

Contributing to an Open Source Project, Part the Second

Ruby v. Python

In which H experiences her first Technical Interview Question

Contributing to an Open Source Project, Part the First

H is for Hstore

Using an Interpreted language to Interpret Language

Is it secret, is it safe?

In which H loses her keys

In which circles are drawn

In which things heard the first time are heard for the first time

Observations from the Field

In which H still likes Ruby

Xconomy Forum: Big Insight—Making Sense of Big Data in Seattle

In which it so happens that H likes Ruby

In which H encounters Rubyists in the Wild