Rails 4 PostgreSQL integration

Rails 4 and PostgreSQL

Rails 4 contains many improvements. One of the most appealing improvements is increaseed access to advanced PostgreSQL features such as HSTORE, Arrays, and UUIDs as Primary keys. In this post I'm going to explore getting started with these features and will update this post as needed.


To begin using UUIDs as primary keys you must enable to uuid-ossp extension that ships with PostgreSQL. Just FYI note the warning about OSPP UUID not being "well maintained" and act accordingly.

First let's create a new rails app called "rails4test" to play around with. Make sure to edit config/database.yml appropriately.

   createdb rails4test
   rails new rails4test -T --database postgresql

Next create a migration to enable the 'uuid-ossp' extention. Then open the created migration and call "enable_extension" with uuid-ossp as a parameter. And run the db:migrate task to apply our migration

  rails g migration enable_uuid_ossp_extension

class EnableUuidOsspExtension < ActiveRecord::Migration def change enable_extension 'uuid-ossp' end end

rake db:migrate

At this point we're ready to create our first model using UUIDs for primary keys. Use the rails model generator to create a model called 'post' (or choose something more relevant for your use case) and then edit the migration file and alter the create_table method to use uuids (id: :uuid). Finally run the db:migrate task again to create the new table.

  rails g model post title:string

class CreatePosts < ActiveRecord::Migration def change create_table :posts, id: :uuid do |t| t.string :title t.timestamps end end end rake db:migrate

At this point we have a model ('posts') configured to use UUIDs as primary keys. One important consideration to keep in mind is that certain helper methods like "first" and "last" will no longer work as they used to since the primary key is no longer an incrementing integer. If this is an issue for you (it's not really for me) you can create your own ActiveRecord scopes called 'first' and 'last' that sort by date (for example).

class Post < ActiveRecord::Base
  scope :first, -> { order("created_at").first }
  scope :last, -> { order("created_at DESC").first }

To experiment a bit open up a rails console, create a Post object and query it using the scope we just created

rails c

> Post.create(title: "Hello UUID") > Post.first > Post.last


Using hstore will allow you to set key/value pairs on a PostgreSQL row. This can be useful for semi-structured data that has an irregular shape -- something you might be tempted to store in a document database (such as MongoDB).

Similar to enabling the UUID extension we'll need to enable the 'hstore' extension before using it.

   rails generate migration enable_hstore_extension
   class EnableHstoreExtension < ActiveRecord::Migration
     def change
      enable_extension 'hstore'
   rake db:migrate

Now create a migration that will add a column called 'properties' to our Post model.

  rails g migration AddPropertiesToPost

class AddPropertiesToPost < ActiveRecord::Migration def change add_column :posts, :properties, :hstore end end

rake db:migrate

Now that hstore is enabled you can play around a bit in the rails console to get a feel for how it works. Note: All key/values are converted to strings before being persisted. So while you can save, for example, a property:boolean mapping (p.properties = {:published => true}) you would query for 'published' = 'true'. Also keep in mind that the hstore column ('properties' above) acts like a hash table.

   rails console
   p = Post.last
   p.properties = {'published' => 'true'}
   p.properties['author'] = 'me'
   Post.where("properties -> 'published' = 'true' ")
   Post.where("properties -> 'published' = 'false' ")

# Find all posts that have a key of 'author' in data Post.where("properties ? :key", :key => 'author')

You can also define accessors and validations for your hstore keys:

   class Post < ActiveRecord::Base
     store_accessor :properties, :status
     validates :status, inclusion: { in: ['published', 'draft'] }

rails console

p = Post.last p.status = 'draft' p.save p # see that status is an hstore property

p.status = 'unknown' p.save # see validation preventing save of invalid status p.errors

If you find yourself blowing away your database during development Tim Preston put together a helpful gist to re-enable hstore. This can be easily extented to also re-enabled the ossp-uuid extenstion...etc

Here is that same gist inline

namespace :db do
  namespace :enable do
    desc "enable hstore extension"
    task :hstore => [:environment, :load_config] do
      ActiveRecord::Base.connection.execute('CREATE EXTENSION IF NOT EXISTS hstore;')

Rake::Task['db:schema:load'].enhance ['db:enable:hstore'] end


PostgreSQL supports Array types. The most common use case being tagging. In our case we'll add a column called tags which will be an array of strings. Getting started with Arrays is pretty simple - just add :array => true to the column definition/migration.

Note: If you want to index the array column you'll have to choose between Gist or GIN indexing strategies.

   rails g migration AddTagsToPost

class AddTagsTopost < ActiveRecord::Migration def change add_column :posts, :tags, :string, array: true, default: [] add_index :posts, :tags, using: 'gin' end end

rake db:migrate rails console

Post.create(title: "PostgreSQL Rocks!", tags: ["pg","rails"], status: 'published') Post.where(" 'rails' = ANY (tags) ")

That's it for now. I'll update this post as needed. Happy Hacking!