PostgreSQL migrations in Ruby on Rails

1. Introduction

Ruby on Rails has a built-in database migration system. It allows you to use Active Record models and Rails methods to create your database schema. When using the generator script to generate a model, it automatically generates a migration as well, to create the database table the model represents.

I quickly realized that this doesn't work very well. Very early on in our project, all our Ruby on Rails migration files ever did, was calling an external file to be executed directly by PostgreSQL. This resulted in a very cumbersome workflow when creating new migrations; we had to create a .pgsql source file, and an accompanying .rb file to call it. We decided it was time to replace Ruby on Rails' built-in migration system with our own. One that is specifically made for PostgreSQL, one reason being that PostgreSQL supports transactional DDL statements, so migrations can be executed blindly, and if they fail, they can simply be rolled back.

I copied the code in this article out of our own project source tree, so it is possible that it depends on some other modifications we made to the default Rails installation. But, I don't think this is the case. In any event, be sure to check if it works correctly for you (as you always should).

The code is licensed under the GNU General Public License, version 3.

2. Theory

There is not much more theory involved than what you probably already know from Rails. Every migration is stored in a numbered .pgsql file and the database keeps track of which migrations have been executed. All the migration logic has to do, is find out which migrations have not been executed and then execute them. For a more detailed explanation, see my article about migrations in MySQL.

3. Implementation

The necessary database table should already exist, as we simply use Ruby on Rails' own "schema_info" table. All that should be necessary, is creating a file called database.rake in "./lib/tasks/", containing the following:

# Copyright 2007, Rowan Rodrik van der Molen, 
# Wiebe Cazemier (http://www.halfgaar.net)
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
 
 
$yellow_begin = "\033[01;33m"
$green_begin = "\033[01;32m"
$red_begin = "\033[01;31m"
$color_end = "\033[00m"
 
namespace :db do
 
  task :pg_migrate => :environment do
    last_version = nil
 
    Dir["#{RAILS_ROOT}/db/migrate/[0-9]*_*.pgsql"].sort.each do |f|
      unless f =~ /_down.pgsql$/
        file_version = f.scan(/([0-9]+)_([_a-z0-9]*).pgsql$/).first.first.to_i
 
        raise "duplicate versioned migration found." if file_version == last_version
 
        current_version = ActiveRecord::Base.connection.select_value('SELECT version FROM public.schema_info LIMIT 1').to_i
 
        if file_version > current_version 
          puts "#{$yellow_begin}Migrating from version #{current_version} to #{file_version}...#{$color_end}"
 
          begin
            # Because our migration files already contain begin and commit statements, this can no longer
            # be executed in a migration. It's not too much of a problem, but it could theoretically cause
            # problems.
            ActiveRecord::Base.connection.execute( File.open(f).read )
            ActiveRecord::Base.connection.execute("UPDATE public.schema_info SET version = #{file_version}")
          rescue
            puts "#{$red_begin}Migrating from version #{current_version} to #{file_version} failed!#{$color_end}"
            raise
          end
        end
 
        last_version = file_version
      end
    end
 
  end
 
end

You should then be able to call the task by running "rake db:pg_migrate". It will look for numbered .pgsql files like "0004_add_cars_table.pgsql" which have a higher version than the latest version indicated by the database, and then executes it.

4. More information

For some additional information about database migrations, I invite you to read my article about migrations in MySQL as well. I didn't want to repeat some of the info in it here, so it is advised reading.

5. References

• Migrations in MySQL