How to execute direct SQL code on a different database in Rails

Question

I'm writing a Rails application which will monitor data quality over some specific databases. In order to do that, I need to be able to execute direct SQL queries over these databases - which of course are not the same as the one used to drive the Rails application models. In short, this means I can't use the trick of going through the ActiveRecord base connection.

The databases I need to connect to are not known at design time (i.e.: I can't put their details in database.yaml). Rather, I have a model 'database_details' which the user will use to enter the details of the databases over which the application will execute queries at runtime.

So the connection to these databases really is dynamic and the details are resolved at runtime only.

Answer 1

You can programmatically establish a connection using a call like this

ActiveRecord::Base.establish_connection(
   :adapter  => "mysql",
   :host     => "localhost",
   :username => "myuser",
   :password => "mypass",
   :database => "somedatabase"
)

As you see you can replace the somedatabase by a database_model.database_name value. The same is true with the adapter and all.

See ActiveRecord::Base.establish_connection documentation for more information.

Then you can use:

ActiveRecord::Base.find_by_sql("select * ") 

to execute your SQL query.

See ActiveRecord::Base.find_by_sql documentation for more information.

Mr Matt was right if incomplete.

More information, which is outdated but still useful for the design approach, can be found here and remember to reconnect to the normal database when you are done.

Answer 2

I had a situation like this where I had to connect to hundreds of different instances of an external application, and I did code similar to the following:

  def get_custom_connection(identifier, host, port, dbname, dbuser, password)
      eval("Custom_#{identifier} = Class::new(ActiveRecord::Base)")
      eval("Custom_#{identifier}.establish_connection(:adapter=>'mysql', :host=>'#{host}', :port=>#{port}, :database=>'#{dbname}', " +
      ":username=>'#{dbuser}', :password=>'#{password}')")  
    return eval("Custom_#{identifier}.connection")
  end

This has the added benefit of not changing your ActiveRecord::Base connection that your models inherit from, so you can run SQL against this connection and discard the object when you're done with it.