in Rails, how do I execute direct SQL code on a different database?

Question

Here is the case.

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.

How can I do that?

Thanks,
Rollo

Answer 1

You may be able to do this through self.establish_connection

Answer 2

You can programmatically establish 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. same with the adatpter and all

ActiveRecord::Base.establish_connection documentation

Then you can use

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

to execute your sql query.

ActiveRecord::Base.find_by_sql documentation

Mr Matt was right if incomplete

More information (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 3

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.