How can execute multiple statements in one query with Rails?

It should work out of the box with PostgreSQL, checked with pg gem and rails 3.2:

class Multitest < ActiveRecord::Migration
  def up
    execute <<-SQL
      create table x(id serial primary key);
      create table y(id serial primary key, i integer);
    SQL
  end

  def down
  end
end

On a side note, manipulating schema_migrations directly looks strange.


For mysql

queries = File.read("/where/is/myqueries.sql")
# or
queries = <<-SQL
 TRUNCATE table1 RESTART IDENTITY;
 TRUNCATE table2 RESTART IDENTITY;
 delete from schema_migrations where version > '20120806120823';
SQL

queries.split(';').map(&:strip).each do |query| 
  execute(query)
end

You may want see this question too: Invoking a large set of SQL from a Rails 4 application


Yes, you need CLIENT_MULTI_STATEMENTS:

In database.yml:

development:
  adapter: mysql2
  database: project_development
  flags:
    - MULTI_STATEMENTS

Then in your code:

connection.execute(multistatement_query)
# Hack for mysql2 adapter to be able query again after executing multistatement_query
connection.raw_connection.store_result while connection.raw_connection.next_result

See https://stackoverflow.com/a/11246837/338859 for details