Rails on PostgreSQL

Thanks to Robby on Rails I heard about Alexander Tretyakov's interesting RailsOnPg plugin. This plugin makes it a bit easier to create PostgreSQL functions, triggers, views, and foreign keys by providing a nicer front end to calls to ActiveRecord::Base.connection.execute.

For example, here's a migration to add a foreign key to a Comment model that belongs to a User:

class AddForeignKeyFromCommentsToUsers < ActiveRecord::Migration 
  def self.up
    add_foreign_key :comments, :user_id, :users
  end
  def self.down
    remove_foreign_key :comments, :user_id, :users
  end
end

This results in the following SQL:

ALTER TABLE comments
                 ADD CONSTRAINT fk_comments_user_id
                 FOREIGN KEY (user_id)
                 REFERENCES users(id) 
                 ON UPDATE NO ACTION 
                 ON DELETE NO ACTION 

As you can see, this provides some sensible defaults and a consistent naming scheme so that you can reliably roll back a migration that created a foreign key.

I ran into some problems when creating a function; my migration failed with a PGError. Turns out that the plugin attempts to execute CREATE LANGUAGE plpgsql before it creates a function; in my case that language was already in place. I commented out line 16 of railsonpg/lib/functions.rb (the call to setlang) and everything worked fine. It looks like this need for a CREATE LANGUAGE IF NOT EXISTS (or something) has come up before, but I'm not sure what the status is. I'm using PostgreSQL 8.4.1 and that statement doesn't seem to be supported.

At any rate, this looks like a handy plugin that could remove a lot of raw SQL from your migrations. Good stuff!