Read more

How to load an SQL dump from a migration

Henning Koch
February 05, 2015Software engineer at makandra GmbH

If you want to load an SQL dump from an ActiveRecord migration, you might find this to be harder than you thought. While you can call ActiveRecord::Base.connection.execute(sql) to execute arbitrary SQL commands, the MySQL connection is configured to only accept a single statement per query. If you try to feed it multiple statements, it will die with You have an error in your SQL syntax.

Illustration online protection

Rails professionals since 2007

Our laser focus on a single technology has made us a leader in this space. Need help?

  • We build a solid first version of your product
  • We train your development team
  • We rescue your project in trouble
Read more Show archive.org snapshot

You can work around this by opening a second MySQL connection that does accept multiple statements per call.

Below is an example for a migration that loads a dump from db/production_structure.sql:

class LoadDump < ActiveRecord::Migration
  def up
    config = YAML.load_file('config/database.yml')[RAILS_ENV].symbolize_keys
    config[:flags] = Mysql2::Client::MULTI_STATEMENTS
    client = Mysql2::Client.new(config)
    sql = File.read(File.join(File.dirname(__FILE__), '..', 'production_structure.sql'))
    client.query sql
  end

  def down
    # no way
  end
end
Posted by Henning Koch to makandra dev (2015-02-05 08:41)