Redo your migrations in Rails and keep your data

9 months ago

Quite often early in a project I find myself editing migration scripts, and wanting to keep my development data.

So for example I have the following script:

 
class CreateCustomers < ActiveRecord::Migration
  def self.up
    create_table :customers, :force => true do |t|
      t.string :name
    end
  end

  def self.down
    drop_table :customers
  end
end

And I want to add a last_name column. Well, the rails way, is to create a new migration like the following:

 
class AddLastNameToCustomer < ActiveRecord::Migration
  def self.up
    add_column :customers, :last_name, :string
  end

  def self.down
    remove_column :customers, :last_name 
  end
end

This is the correct way of doing things, it ensures that when you go and run your migrations elsewhere you don’t get voodoo.

However, if I am early in the development process and I have not deployed anything anywhere and I discover a typo in one of my migrations. I do not want to be reminded of my typo forever, I just want to fix it. The rails way would be to fix it, migrate down to the version before the mistake and then migrate back up.

rake db:migrate VERSION=2 
rake db:migrate

Great I just destroyed all my wonderful development data in my database after version 2. I don’t like that.

So, here is a hack I put together that allows you to fix up a typo in an early migration and keep your data. It is heavily based off the the backup script on the too biased blog

Place the following file in your lib/tasks folder

 
def interesting_tables
  rval = ActiveRecord::Base.connection.tables.sort
  rval.reject! do |tbl|
    ['schema_migrations','schema_info', 'sessions', 'public_exceptions'].include?(tbl)
  end
  rval
end

namespace :db do
  namespace :backup do

    desc "Reload the database and rerun migrations" 
    task :redo do 
      Rake::Task['db:backup:write'].invoke
      Rake::Task['db:drop'].invoke
      Rake::Task['db:create'].invoke
      Rake::Task['db:migrate'].invoke
      Rake::Task['db:backup:read'].invoke 
    end 

    desc "Dump entire db." 
    task :write => :environment do 

      dir = RAILS_ROOT + '/db/backup'
      FileUtils.mkdir_p(dir)
      FileUtils.chdir(dir)
      interesting_tables.each do |tbl|
        klass = tbl.classify.constantize
        puts "Writing #{tbl}..." 
        File.open("#{tbl}.yml", 'w+') { |f| YAML.dump klass.find(:all).collect(&#38;:attributes), f }      
      end
      FileUtils.chdir(RAILS_ROOT)
    end

    desc "Loads the entire db." 
    task :read => [:environment, 'db:schema:load'] do 

      dir = RAILS_ROOT + '/db/backup'
      FileUtils.mkdir_p(dir)
      FileUtils.chdir(dir)

      interesting_tables.each do |tbl|

        ActiveRecord::Base.transaction do 

          begin 
            klass = tbl.classify.constantize
            klass.destroy_all
            klass.reset_column_information

            puts "Loading #{tbl}..." 
            YAML.load_file("#{tbl}.yml").each do |fixture|
              data = {}
              klass.columns.each do |c|
                # filter out missing columns 
                data[c.name] = fixture[c.name] if fixture[c.name]
              end         
              ActiveRecord::Base.connection.execute "INSERT INTO #{tbl} (#{data.keys.map{|kk| "#{tbl}.#{kk}"}.join(",")}) VALUES (#{data.values.collect { |value| ActiveRecord::Base.connection.quote(value) }.join(",")})", 'Fixture Insert'

            end        
          rescue 
            puts "failed to load table #{tbl}" 
          end 
        end
      end

    end

  end
end

You will then have a few new rake tasks the new one that is not described in too biased blog is:

rake db:backup:redo

This will export your data from the db to disk, recreate the db, re-run the migrations and re-import your data.

Posted by: Sam Permalink | Comments (3)

Comments

Erik

about 1 month ago

This looks like something super valuable to me, thank you for writing it.

I am getting the following error when I try to list rake tasks for db. Hopefully you have some thoughts:

thoraxe@thoraxe ~/railsapps/imoblio $ rake -D db (in /home/thoraxe/railsapps/imoblio) rake aborted! /home/thoraxe/railsapps/imoblio/lib/tasks/backup_db.rake:29: syntax error /home/thoraxe/railsapps/imoblio/lib/tasks/backup_db.rake:31: syntax error /home/thoraxe/railsapps/imoblio/lib/tasks/backup_db.rake:46: syntax error YAML.load_file(”#{tbl}.yml”).each do |fixture| ^ /home/thoraxe/railsapps/imoblio/lib/tasks/backup_db.rake:48: syntax error klass.columns.each do |c| ^ /home/thoraxe/railsapps/imoblio/lib/tasks/backup_db.rake:56: syntax error /home/thoraxe/railsapps/imoblio/Rakefile:10 (See full trace by running task with—trace)

Erik

about 1 month ago

File.open(”#{tbl}.yml”, ‘w+’) { |f| YAML.dump klass.find(:all).collect(&:attributes), f }

This command appears to maybe be messed up. The syntax highlighting on both your blog and in my VIM editor appear to think something is off…

Sam

about 1 month ago

Erik,

I just updated the script with my latest version. I think it used to have some issues with rails 2.0

Cheers Sam