How can execute multiple statements in one query with Rails?
Solution 1
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.
Solution 2
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
Solution 3
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
Related videos on Youtube
deepak
Updated on June 29, 2022Comments
-
deepak almost 2 years
I am using Ruby on Rails with ActiveRecord and PostgreSQL.
How can i execute multiple sql queries?
I need it for running a custom migration script, eg:
Foo.connection.execute <<-SQL.split(';').map(&:strip).join delete from metadata where record_type = 'Foo'; TRUNCATE table1 RESTART IDENTITY; TRUNCATE table2 RESTART IDENTITY; delete from schema_migrations where version > '20120806120823'; SQL
I am not accepting data from a user, so I'm not worried about sql-injection.
Something like
CLIENT_MULTI_STATEMENTS
in MySQL maybe ?From the MySQL/PHP docs:
CLIENT_MULTI_STATEMENTS: Tell the server that the client may send multiple statements in a single string (separated by “;”). If this flag is not set, multiple-statement execution is disabled. See the note following this table for more information about this flag.
-
deepak over 11 yearsthanks, i will check the code for migrations. But how does migration's execute work but ActiveRecord's does not? How to run multiple queries from ActiveRecord ?
-
Noah Gibbs almost 9 yearsDoesn't work for MySQL, though. ActiveRecord's execute succeeds w/ multiple on Postgres, fails on MySQL.