Rails Migration Table Check

  Have you ever been stuck in a multiple table migration that you can't roll forward or backwards because your database and your migration are not in sync.  This very problem has come up for me numerous times and one day I finally decided to fix it. 

  Here is a quick example of what I am talking about.  Lets take a simple 2 table migration file:

class EmployeeJobs < ActiveRecord::Migration
  def self.up
    create_table employees do |t|
      t.column :age, :integer
      t.column :name, :string, :length=>255
    end

    create_table :jobs, :id => false do |t|
      t.column :employee_id, :integer
      t.column :status, :integer
      t.column :name, :string, :length=>255
    end
  end

  def self.down
    drop_table :employees
    drop_table :jobs
  end
end
 
This migration file will create 2 tables and seems pretty innocent.  But lets say later down the line you decide to change the jobs table to add a category field.  But since you are a newbie to Ruby on Rails like myself, you have a typo and the migration fails 1/2 way through execution.  Now in the database you have an employees table but no jobs table.  When you try to rollback you get an error saying the employees table could not be found.  When you try to roll forward you get an error saying the jobs table already exists.  If you are like me, most of the time you drop the database and re-create it to get around the problem... I mean this is only development. 

Solution:
Here is a simple solution that will help prevent this situation from happening.  Add a simple function that will check to see if the table exists before trying to drop the table.  This will stop migration failing if it tries to drop a table that does not exist in the database.   

  def self.check_table(name)
    begin
      User.connection.execute("select 1 from #{name}")
      say "Checking For Table: #{name} => Table Found"
      return false;
    rescue
      say "Checking For Table: #{name} => Table Not Found"
      return true;
    end
  end

Now in your down block add a simple unless clause:

  def self.down
    drop_table :employees unless self.check_table('employees')
    drop_table :jobs unless self.check_table('jobs')
  end

Conclusion:
While this method does work, it does have some flaws.  For one, you need to have the "check_table" function in each of your migration files.  I am sure there is a way to make this a global function, but in my get it down now world I have no had time to figure that out.  Being a DBA in a former life whose job it was to create migration scripts, simple checks to see if the table exists or if there is data in the table would be nice to have as options on the drop_table commands.   But in the meantime hopefully the above function will help you out.

A sample migration file with the download check can be downloaded here: sample_migration.rb (0.8K)

Comments

[cory]

You can also do it like this, which is a little cleaner

def self.table_exists?(name)
ActiveRecord::Base.connection.tables.include?(name)
end

[Eliot Sykes]

To make it reusable you can create a module or class in your lib directory, e.g. database_util.rb:

class DatabaseUtil
def self.table_exists?(table_name)
ActiveRecord::Base.connection.tables.include? table_name.to_s
end
end

[Daniel Rosenstark]

Don't bother.

drop_table :events rescue puts("events doesn't exist, no worries")

[neil]

For me the rescue approach leaves my transaction trashed, and nothing works afterwards. So I have to do a check first, rather than letting it fail.

But I we have some custom transaction stuff to make sure our migrations roll back and don't leave everything trashed if they fail part way.

[Raf]

Here is my approach to solve the problem:

http://github.com/rafmagana/database_utils

after using that code you can do this in a migration:

:users.table_exists?
"users".table_exists?
:users.column_exists? :name

see you