How to load an SQL dump from a migration

Posted . Visible to the public.

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.

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 =
    sql =, '..', 'production_structure.sql'))
    client.query sql

  def down
    # no way
Henning Koch
Last edit
Henning Koch
ActiveRecord, Rails
Source code in this card is licensed under the MIT License.
Posted by Henning Koch to makandra dev (2015-02-05 07:41)