How to access multiple databases in rails 3.1.0 app?

Question

In our rails 3.1.0 app, we would like to access multiple databases based on user credential. There are multiple solutions. One is to install one app for each user and every app only accesses one database. The problem is that if there are many databases (such as hundreds), there would be equal number of app installations. Maintenance of large number of apps may be a hassle. Another approach is to access multiple databases within the app. However this approach seems not readily supported in rails 3.1.0. Certainly there would be a code within the app to decide which database to access for the user who just logs in. Can someone provide solutions and insights about pros and cons of the approache? thanks so much.

Answer 1

You should be able to use ActiveRecord::Base.establish_connection to dynamically connect to any database you like within a certain context. How you implement this is entirely application-specific, but Rails does have this capability.

Here's a very contrived example:

class User
  def books
    ActiveRecord::Base.establish_connection(
      :adapter  => "mysql",
      :host     => "localhost",
      :username => self.username,
      :password => self.password,
      :database => self.database
    )

    Book.all
  rescue Exception => e
    # ...
  end
end

You would want to do actual error handling and probably establish the connection somewhere outside of an instance method, but that's up to you to decide.

Answer 2

The apartment gem might be exactly what you're looking for:

Apartment provides tools to help you deal with multiple databases in your Rails application. If you need to have certain data sequestered based on account or company, but still allow some data to exist in a common database, Apartment can help.