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.

UUIDs

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 }
end

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

hstore

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'
     end
   end
   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'
   p.save
   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'] }
   end


   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;')
    end
  end


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

Arrays

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!