Seeding data as part of Rails database migrations

So, we stumbled across a slight problem when trying to seed data as part of our database migration scripts. The de-facto standard seems to be to keep the seed data in yaml files and load them in using fixtures. This is a perfectly adequate way of doing it for seed data that will never change, but doesn’t really fit into the infrastructure of migrations. There are also many plugins out there to help with using fixtures to seed data, but none of them allow you to have different seed data for different migration versions.

After bumping our heads together, we came up with a way of managing seed data using the same philosophy as data structure migrations. The only prerequisite to your data structure is to add a column named “came_from_migration” to any table that is going to contain seeded data. This way when moving down migrations it is possible to determine what data that shouldn’t be there (and delete it).

  1. Add a “fixtures” folder to /db/migrate. Your seed data yaml files will be saved here.
  2. Create your seed data yaml file and name it in a similar fashion to your migration script using the table name as the descriptor e.g. 005_table_name_to_seed.yml. Save this in the folder you created above. This means that you can seed several tables as part of one migration version and it’s nice and easy to see what’s going on from just the file name.
  3. Now I needed to write a couple of helper methods to move to and from the seed data. I’ve placed these in a MigrationsHelper module which is included in any migration that needs to seed data. And because we are so nice, here they are:
  def seed_from_yaml(table_name)
    info = get_migration_file_info(caller[0])
    fixture_folder = RAILS_ROOT + "/db/migrate/#{info[:type]}_fixtures"
    fixture_file = "#{info[:version]}_#{table_name}"
    puts "Seeding #{table_name} from #{info[:type]} fixture version #{info[:version]}"

    require 'active_record/fixtures'    
    connection = ActiveRecord::Base.connection
    seed_data =, table_name.to_s, nil, File.join(fixture_folder, fixture_file))
  def deseed(table_name)
    info = get_migration_file_info(caller[0])
    puts "Deseeding #{table_name} with anything newer than #{info[:version]}"
    execute %{DELETE FROM #{quote_table_name(table_name)} WHERE #{quote_column_name('came_from_migration')}=#{info[:version].to_i}}
    def get_migration_file_info(file)
      file.gsub!(/:.*/, '') # get rid of everything after the colon
      migration = file.split '/'
      db_type = migration.include?("global") ? "global" : "private"
      migration_file = migration[migration.length-1].split '_'
      migration_number = migration_file[0]
      { :type => db_type, :version => migration_number, :file => migration_file }

Seeding data when not using migrations

What about when running tests or deploying, i.e. seeding data when not using migrations and coming from a empty database? Well, the schema should be held for you in your _schema.rb file, but that doesn’t cover any seed data. The answer is to create a *_seed.yml containing all the data in the database (in the same way the *_schema.rb file contains the complete data structure schema of the database). This way you can load in the seed data after the tables have been created. We have yet to write this method, so is left as an exercise for the reader at present. The basic premise is to go through every table in the schema, check for the existence of the “came_from_migration” column, do a “select * from table where came_from_migration is not null” on tables with that column and then concatenate the output from the select statements to a *_seed.yml and save in /db. There may also be problems with referential integrity with seed data and to what order it is inserted into the database. No doubt we will come across these difficulties soon.



2 Responses to “Seeding data as part of Rails database migrations”

  1. Seeding data in Rails Migrations (Part 2) « Workbooks Technical Blog Says:

    […] May 9, 2008 — Chris We’ve had a significant number of hits on our first article on Seeding data as part of Rail database migrations which just goes to show that people are finding this a bit of a problem! As we have been using our […]

  2. Seeding data as part of Rails database migrations « Fringley's Blog Says:

    […] reading Seeding data as part of Rails database migrations on my other blog… Tags migrations, rails, seed data Categories […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: