Dynamically connecting a model to databases in a running app?

Question

I've read many of the existing questions/threads on this subject, but keep in mind that none of them have directly addressed my issue. Also keep in mind that this is NOT a situation for database.yml as I won't know the DB info in advance.

That said, I need a solution for DYNAMICALLY connecting to multiple databases in a Rails app. My situation is that I have multiple data logging sites, all with a simple data table (EVENTS, TIMESTAMP, VALUE). These sites need to (and will) remain as they are due to local applications that need to use them.

What I need is to create an application that maintains a table of "MYSQL_LOGINS" and uses each of those logins to connect to any one of various databases and provide charts/graphs for the data therein.

My "data" model will remain the same for all MySQL databases to which I connect, I simply need to be able to tell my MODEL to connect to a different DB on the fly. I'm hoping this is amazingly simple, but we'll see.

Answer 1

You want ActiveRecord::Base#establish_connection. You'll probably want to use it in an abstract subclass so that models using the manual connections don't disturb the connection uses by the rest of your application:

class LogBase < ActiveRecord::Base
  self.abstract_class = true
end

class LogItem < LogBase
  ...
end

LogItem.establish_connection { ... }

LogItem.find_by_ ...

If you'll only ever have one model that makes manual connections the abstract class is unnecessary.

Do you know the full set of databases that you might connect to? If so, database.yml may still be useful to you. You can put all the information in there as a directory, giving each database a name, then dynamically chose which one to establish_connection with in the application using those names:

# database.yml
atlanta:
  host: atlantadb.foo.com

# foo.rb
LogItem.establish_connection :atlanta

Setting up connections can be pretty heavyweight, doubly so if the sites are separated by wide area network. I'd consider whether it makes more sense to use an extract-transform-load approach and use a hourly/daily cron job to copy the data to a centralized reporting site. This is the most basic form of a data warehouse, and is a very commonly used approach. If you're using MySql, Maatkit Sync is a neat little tool, like rsync for tables. Alternately you probably use a database that supports master-slave replication, though that can be considerably more complicated to maintain.