Seed Rails database (MySQL) from TSV file

Question

While developing my Rails app, I've been manually seeding its database with sample data. However, after I run rake db:migrate:reset I need to re-seed everything, which is tedious.

How can I get rake to seed the database for me?

The relevant model is generated in this migration:

class CreateKeyRecords < ActiveRecord::Migration
  def change
    create_table :key_records, :id => false do |t|
      t.string :name
      t.integer :parentID, :limit => 8
      t.primary_key :ID
      t.integer :accesses, :limit => 8
      t.integer :dt
      t.integer :hour
    end
    change_column :key_records, :ID, 'bigint'
  end
end

Currently, I'm seeding the db by directly altering the app's database in mysql, with the command:

LOAD DATA LOCAL INFILE '/tmp/input.tsv' INTO TABLE key_records (name, parentID, ID, accesses) SET dt=20141231, hour=23;

where input.tsv has the form:

key   1    2    9493878

How might I get rake to do this 'manual seeding' for me?

Answer 1

I don't think there's a "rails-ey" way of doing this, but you should be able to run a SQL statement in seeds.rb. You can run raw SQL statements against your database with ActiveRecord::Base.connection.execute. Your seed file would look something like this:

stmt = "LOAD DATA LOCAL INFILE '/tmp/input.tsv' 
    INTO TABLE key_records (name, parentID, ID, accesses) 
    SET dt=20141231, hour=23;"

ActiveRecord::Base.connection.execute stmt

You would then call it with rake db:seed.