Database Migration Can't Assign/Update Newly Created Columns

I wrote a database migration today that I ran in to some problems with.  The migration simply adds a column to a table, and then assigns a value to the new column.  All appeared to work when the migration ran.  But, when I checked the database afterwards, the column was added to the table but the value that I assigned was not set.  Here is my example:

class AddAndSetColumn < ActiveRecord::Migration

def self.up    
add_column :nodes, :new_column_1, :boolean, :null => false, :default => false

Node.find(:all).each do |n|
n.new_column_1 = true
n.save!
end

end

def self.down
remove_column :nodes, :new_column_1
end
end

After running the migration, and looking at the database, all nodes had new_column_1 set to false, even though I explicitly set it to true in the migration.

After searching around to see why this happens, I found a posting that described this problem at http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/573cbb96b84f3306/a0d6a3c7dddbbbcb?lnk=raot. The solution for this is to call reset_column_information on the model for the table that you added the column to before you assign values. So I changed my migration to:

class AddAndSetColumn < ActiveRecord::Migration

def self.up    
add_column :nodes, :new_column_1, :boolean, :null => false, :default => false
Node.reset_column_information    

Node.find(:all).each do |n|
n.new_column_1 = true
n.save!
end

end

def self.down
remove_column :nodes, :new_column_1
end
end

And it works!

Applying individual timestamp migrations

I posted in my previous entry (Timestamp based migrations in Rails 2.1) about the new UTC timestamp based migrations in Rails 2.1. After working with them a little more, I found how to apply or remove individual migrations. Simply run rake db:migrate:up VERSION=YYYYMMDDHHMMSS to apply a single specific migration, or rake db:migrate:down VERSION=YYYYMMDDHHMMSS to remove a specific migration.

However, I found a bug with db:migrate:down as noted at http://rails.lighthouseapp.com/projects/8994/tickets/369-db-migrate-down-does-not-remove-migration-version-from-schema_migrations-table. When you run this, the self.down function of the migration is called and the database is changed. However, the entry in schema_migrations for this migration is not removed. So if you run rake db:migrate later, this migration will not be applied because Rails thinks that the migration has already been applied. My work around for this is to manually delete the row in schema_migrations after I run db:migrate:down.

Timestamp based migrations in Rails 2.1

One really cool feature added to Rails 2.1 is UTC timestamp based migrations. Now, when you generate a new migration, instead of the migration number being sequential, it's a UTC timestamp. That way if you're working on different branches of code, you don't need to coordinate between the braches who gets what version number.

But the best part of this is how the migrations are tracked. Instead of just keeping a current database schema number like before, Rails keeps track of each migration that has been run. Then when you run rake db:migrate, Rails only applies the migrations that haven't been run, they don't have to be in order.

As an example, say I added a migration two days ago called add_new_column with script/generate migration. 20080610012942_add_new_column.rb gets generated. My co-worker John is working off of another code branch, and yesterday he added a new migration called change_accounts. 20080610225643_change_accounts.rb gets generated. Then today I add a new migration called delete_state. 20080612020139_delete_state.rb gets generated. I have two migrations, and John only has his one. When I run rake db:migrate, my two migrations get applied, and when John runs it his one migration gets applied. When we sync our code branches, we get each others migration files. Even though John's migration was created AFTER the first one that I created, when he runs migrate, both of my migrations will get added, and his migration that was already added will not get run again. And when I run migrate, only John's migration will get added, even though it was generated before the last migration that I ran.

The way Rails does this is that the first time you run a migration with Rails 2.1, a new table gets created called schema_migrations. An entry is added to this table for each migration that gets run. The old schema_info table is deleted. That way any time you run a migration, it checks each migration file against the database to see if it's been applied to your database yet.

This feature is already proving very useful for me. I no longer have to have migrations completely syncronized between myself and the other developer working on my project. We each work on our own branch, and we merge our migrations together when we're ready to merge all of our code.

A good Railscast video showing this is up at http://railscasts.com/episodes/107.

Assigning custom data types to new columns during migration, part 2

In my previous article, Assigning custom data types to new columns during migrations, I showed how you can create columns with custom data types during a database migration. Simply specify :"data type", instead of :integer, :string, :binary, etc. An example would be :"smallint", since there is no direct way to create a MySQL smallint during a Rails migration.

However, I recently found that this doesn't always work. It works fine if you are creating a new table in your migration. But this does NOT work if you are adding or modifying a column in an already existing table, you get the error:
rake aborted!
You have a nil object when you didn't expect it!
You might have expected an instance of Array.
The error occurred while evaluating nil.[]

Has anyone else run in to this problem? It's annoying to have to construct SQL statements to add columns, after all that's what migrations are supposed to get rid of.

Assigning custom data types to new columns during migration

The set of data types that you can assign to a new column during a database migration is pretty limited. However, it's easy to set a column to a data type specific to the database that you're using. Instead of specifying :string, :integer, etc for the data type, specify :"data type". As an example, if you have MySQL and you want to add an unsigned integer, there's no way to specify a :integer to be unsigned. But you can do this:

add_column :table, :newcolumn, :"int (10) unsigned"

Creating/deleting foreign keys during migrations

As far as I know, Rails doesn't use foreign key declarations in your database tables, and therefore has no way of creating and deleting foreign keys in migrations. If you want foreign keys to be added/deleted to/from your database during migration, you can do so by executing SQL statements directly during the migration. I won't go over the merits of having foreign keys, in many ways they are unnecessary with Rails but there may be reasons that you want to have them. I've created a mixin module for MySQL ONLY with two functions to add or remove a foreign key, here is the code:

# a mixin module for adding foreign keys to MySQL databases during migrations.
module ForeignKeyOps
  # adds a foreign key to the table.
  # Parameters:
  # table_name - the name of the table to add to
  # association_name - the name of the constraint to add to the database
  # local_column - the column in table_name that contains the key
  # foreign_table - the name of the foreign table to reference
  # foreign_column - the name of the primary key in the foreign table
  # options - an optional hash with additional parameters for the 
  #   constraint. If the following hash values are present, then the 
  #   additional constraints will be specified:
  #   - on_delete: specify "cascade" or "set null"
  #   - on_update: specify "cascade" or "set null"
  def add_foreign_key(table_name, constraint_name, local_column, 
                      foreign_table, foreign_column, options = {})
    st = "ALTER TABLE #{table_name} ADD CONSTRAINT #{constraint_name} "
    st += "FOREIGN KEY (#{local_column}) REFERENCES #{foreign_table} (#{foreign_column})"
    if options.has_key?(:on_delete)
      st += " ON DELETE #{options[:on_delete]}"
    end
    if options.has_key?(:on_update)
      st += " ON UPDATE #{options[:on_update]}"
    end
    execute st
  end

  # removes a foreign key constraint from a table. This will NOT delete the 
  # column in the table, it only deletes the constraint
  # Parameters:
  #   table_name - the name of the table to remove the foreign key contstraint 
  #                from
  #   constraint_name - the name of the constraint on the table to delete
  def remove_foreign_key(table_name, constraint_name)
    st = "ALTER TABLE #{table_name} DROP FOREIGN KEY #{constraint_name}"
    execute st
  end
end

 
To use this, in add a require for this file to your migration file. Then inside of the migration class, add extend ForeignKeyOps to the beginning of the class.

As an example, say you want to add a new table to your database for phones, and each phone belongs to a user. Here is the migration code, using ForeignKeyOps:

require 'db/migrate/foreign_key_ops'
class AddPhones < ActiveRecord::Migration
  extend ForeignKeyOps
  def self.up 
    create_table :phones do |t|
      t.column :account_id, :integer
      t.column :name, :string
      t.column :number, :string
      t.column :brand, :string
    end
    add_foreign_key('phones', 'fk_accounts_phones', 'account_id', 
                    'accounts', 'id')
  end

  def self.down
    drop_table :phones
  end
end

 
Again as a reminder this only works for MySQL.