Data Loaders in Migrations

Two cases of data usage at RHG (as outlined in the DB Migrations With a Twist post) - migration consolidations and test data creation via migrations, led us to having migrations that just load and execute SQL statements from a file. Another use case is mass-loading of referential data. To help with those tasks we use a simple module that provides a method for data loading:

module DataLoader

  def load_data(name = 'data', ext = 'sql')
    file_name = data_file(name, ext) || fail("Cannot find the data file for #{ name }")
    execute_sql_from_file(file_name)
  end

  def execute_sql_from_file(file_name)
    say_with_time("Executing SQL from #{ file_name }") do
      IO.readlines(file_name).join.gsub("\r\n", "\n").split(";\n").each do |s|
        execute(s) unless s == "\n"
      end
    end
  end

private
    
  def data_file(name, ext)
    mode = ENV['RAILS_ENV'] || 'development'
    [ 
      data_file_name(mode + '_', name, ext),
      data_file_name('', name, ext)
    ].detect { |file_name| File.exists?(file_name) }
  end 

  def data_file_name(prefix, name, ext)
    "#{ RAILS_ROOT }/db/migrate/data/#{ prefix }#{ name }.#{ ext }"
  end
  
end

A sample migration using the method for loading the consolidated data to all DBs looks like this:

class ConsolidatedMigrations < ActiveRecord::Migration

  extend DataLoader

  def self.up
    all_dbs.each_key do |name|
      run_on_db(name) { load_data "consolidated_for_#{ name }" }
    end
  end

end

It loads Rail-environment specific files from the db/migrate/data/ directory:

development_consolidated_for_main.sql
development_consolidated_for_portal_referential.sql
...
production_consolidated_for_main.sql
production_consolidated_for_portal_referential.sql
...
test_consolidated_for_main.sql
test_consolidated_for_portal_referential.sql