Populating Tables with Data Using Migrations

Posted by g. | Filed under Ruby on Rails, Technology

I can think of very few Rails sites that don’t need some kind of data pre-populated in their tables. When you talk to a Rails developer, the first thing that comes to mind when talking about populating the db is fixtures. This is not what we’re talking about here. Fixtures populate your TEST database with sample data for testing purposes. What I mean is what if you have a table that holds all 50 US states? Or a table that holds user roles like “admin”, “moderator”, “member”, and so forth? Ideally, you want this data populated as soon as your app is deployed and running, and inputting it manually is of course out of the question.

There are certainly many ways to do this, but I want to focus on what I’ve found to be a very elegant solution. We’re going to use migrations, so that when you do your initial rake db:migrate, things are setup and ready to go. And if you want to rollback a specific populating of a table, you could do that as well!

The example I’m going to use is a table that needs to hold the 50 US states. Create a migration like the following:

script/generate migration add_states

Now open up the migration file you just created, and make it look something like this (I’m not going to fill in all 50 states for this example):

class AddStates < ActiveRecord::Migration
  def self.up
    %w(AL AK AZ AR CA CO CT DE DC FL).each do |s|
        State.new(:name => s).save
    end
  end
 
  def self.down
    State.destroy_all
  end
end

In the “up” migration you’re iterating through an array of states (which are Strings), and for each one creating a new State object in memory (with whatever attributes you need, in our case just the name), then saving the record to the db with the save method. Easy and elegant.

The beauty is that in the “down” migration, we wipe out everything in the states table, as it should be. Be aware that when you run the down migration, if you have an auto-incrementing primary key id, it does not reset. So if you populated the 50 states, ran the down migration, then ran it again, the id would start counting at 51, not 1.

Tags: , , , ,

Permalink | March 17th, 2009

Leave a Reply