Executing raw sql against multiple data bases

Question

This is a followup to executing raw sql. I am working on a project where I need to stream in information form multiple databases. How would I do something similar to

sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)

but have support for choosing the connection??

score 5 · Accepted Answer · answered Mar 25 '17 at 10:49

You may use ActiveRecord::Base.establish_connection to switch database connection. Code should be like this:

#database.yml
development:
  adapter: postgresql
  host: 127.0.0.1
  username: postgres
  password: postgres
  database: development_db

development_another_db:
  adapter: postgresql
  host: 127.0.0.1
  username: postgres
  password: postgres
  database: another_db


ActiveRecord::Base.establish_connection :development_another_db
sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)

ActiveRecord::Base.establish_connection :development
sql = "Another select"
records_array = ActiveRecord::Base.connection.execute(sql)

You may find details about establish_connection in Rails documentation.

Can this be done across connections to select from one database and insert into another? — Sawyer Merchant, Mar 28 '18 at 14:54
@SawyerMerchant, Yes, definitely. Since `sql` here is just a plain string without any relation to database connection or active record. — Ilya Lavrov, Mar 29 '18 at 21:05

Executing raw sql against multiple data bases

1 Answers1