Multiple database connections in Rails

Question

I'm writing a simpler version of phpMyAdmin in Rails; this web app will run on a web server (where users will be able to indicate the database name, hostname, username, password, and port number of one of the database servers running on the same network). The user will then be connected to that machine and will be able to use the UI to administer that database (add or remove columns, drop tables, etc).

I have two related questions (your help will greatly aid me in understanding how to best approach this):

1. In a traditional Rails application I would store the database info in database.yml, however here I need to do it dynamically. Is there a good way to leave the database.yml file empty and tell Rails to use the connection data provided by the user at run time instead?

2. Different users may connect to different databases (or even hosts). I assume that I need to keep track of the association between an established database connection and a user session. What's the best way to achieve this?

Thank you in advance.

Answer 1

1. To prevent Rails from initializing ActiveRecord using database.yml, you can simply remove :active_record from config.frameworks in config/environment.rb. Then, to manually establish connections, you use ActiveRecord::Base.establish_connection. (And maybe ActiveRecord::Base.configurations)

   ActiveRecord stores everything connection related in class variables. So if you want to dynamically create multiple connections, you also have to dynamically subclass ActiveRecord::Base and call establish_connection on that.

   This will be your abstract base class for any subclass you'll use to actually manage tables. To make ActiveRecord aware of this, you should do self.abstract_class = true within the base class definition.

   Then, each table you want to manage will in turn dynamically subclass this new abstract base class.

2. This is more difficult, because you can't really persist connections, of course. The immediate solution I can think of is storing a unique token in the session, and use that in a before_filter to get back to the dynamic ActiveRecord::Base subclass, which you'll probably be storing in a hash somewhere.

   This gets more interesting once you start running multiple Rails worker processes:

   • You will have to store all of the database connection information in the session, so other workers can use it.
   • You probably want a consistent unique token across workers, so use a hash function on a combination of database connection parameters.
   • Because a worker may be called with a token it doesn't yet know about, your subclassing and establish_connection logic will probably happen in the before_filter. (Rather than the moment of login, for example.)
   • You will have to figure out some clever way of garbage collecting connections and classes, for when user doesn't properly log out and the session expires. (Sorry, I don't know this one.)