How can execute multiple statements in one query with Rails?

Question

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.

I take it that in the real example you're reading the script from a file, so you can't just loop over execute once for each statement without doing dodgy string splitting like the example? If it were directly in your code you could just put the statements in an array and loop over it. — Craig Ringer
– Craig Ringer, Commented Aug 20, 2012 at 8:09
Hope the edit accurately reflects your intention. Let me know - or re-edit - if not. — Craig Ringer
– Craig Ringer, Commented Aug 20, 2012 at 8:15
Like what Craig Ringer says—what's wrong with simply calling connection.execute multiple times with one statement each? I've done that enough times (once, running a legacy SQL schema dump of dozens of tables as the very first migration in a Rails app). — Alistair A. Israel
– Alistair A. Israel, Commented Aug 20, 2012 at 10:03
do not want to make multiple network calls. That is what i am doing right now though. Network calls are quite slow over the network, in this case, but i can live with it for deletes and truncates. Asked this question mostly to know. — deepak
– deepak, Commented Aug 20, 2012 at 11:43

Serge Balyuk · Accepted Answer · 2012-08-20 12:59:43Z

11

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.

answered Aug 20, 2012 at 12:59

Serge Balyuk

3,4721 gold badge25 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

deepak Over a year ago

thanks, 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 Over a year ago

Doesn't work for MySQL, though. ActiveRecord's execute succeeds w/ multiple on Postgres, fails on MySQL.

Community · Accepted Answer · 2017-05-23 12:25:55Z

6

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

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Apr 26, 2017 at 11:47

Envek

4,7743 gold badges37 silver badges44 bronze badges

4 Comments

ZiggyTheHamster Over a year ago

You didn't read the question. This is for Postgres, not MySQL.

Envek Over a year ago

Ah, yes. My bad.

Nitrodist Over a year ago

Still useful for me, a mysql user, to come across this answer after googling a generic multi-statement question! :)

Joshua Pinter Over a year ago

I couldn't get this MULTI_STATEMENTS flag to work. To be fair, I didn't try very hard. But I ended up just splitting my statements out and calling execute on each one. I only had a few so it ended up being fine and didn't require mucking with MySQL or ActiveRecord.

Edward Anderson · Accepted Answer · 2019-11-27 23:38:31Z

5

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

edited Nov 27, 2019 at 23:38

Edward Anderson

14k6 gold badges57 silver badges51 bronze badges

answered Mar 24, 2017 at 4:59

Fangxing

6,2252 gold badges55 silver badges58 bronze badges

Collectives™ on Stack Overflow

How can execute multiple statements in one query with Rails?

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related