Using Multiple Databases with Ruby On Rails

article by Srirangan

With Ruby On Rails, you can connect to multiple databases. This is usually done by setting up class-specific connections. The same has been explained in ActiveRecord API Doc [1].

Now I wouldn't generally want to do the same for my web applications, there are pro's and con's and while I don't want to preach I'ld want to mention them atleast. The decision is then upto you. :-)

app/controllers/abstract_application.rb defines a class called AbstractApplicationController. All other user-created controllers descend through this. We?re going to place a before_filter to step in front of the default database mechanism. Because ActiveRecord?s connection works on a first-come-first-serve basis, if we?re able to set the establish_connection before the default does, we succeed.

class AbstractApplicationController < ActionController::Base
# here, we hop into the front of the request-handling
# pipeline to run a method called hijack_db
before_filter :hijack_db

# here, we?re going to manually establish a connection
# to the database we feel like connecting to.
def hijack_db
# completely ridiculous condition
is_odd = Time.now.hour % 2 == 1

# determine the database name
db_name = is_odd ? ?dis_odd_db? : ?dat_even_db?

# lets manually connect to the proper db
ActiveRecord::Base.establish_connection(
:adapter => ?mysql?,
:host => ?localhost?,
:username => ?mr_roboto?,
:password => ?secret_secret_i_got_a_secret?,
:database => db_name
)
end
end


Pro's

1. Complete isolation ? you can have different database schemas and different functionality without affecting the others
2. The ActiveRecord code requires fewer relationships and the SQL is much cleaner when you don?t have to add `WHERE team_id = 123` to every query (granted, much of this ActiveRecord handles more than gracefully)
3. Less data and smaller indices mean faster SQL queries (in theory)
4. No central database means each database could be located on a different box
5. Backing up a single football team (in the above example) means dumping one database, not writing writing sql that says `WHERE team_id = 69`
6. If you go the extra step and deploy one Rails app per customer, taking someone offline doesn?t mean taking everyone offline. Furthermore, if you are deployed on multiple isolated machines and one dies, you get fewer simultaneous irate support calls.
7. If you?re wildly successful and want to scale up by having a bunch of read-only databases and a master writable database, this technique might be the way to go. See the LiveJournal scaling article
8. You are crreating an app that integrates multiple existing databases into a single web front end

Con's

1. When changing the database schema, you?ve got to change it in all the databases
2. When backing up, you?ve got multiple databases to manage
3. Database caching is non-existant (read: small performance hit)
4. Creating statistics and aggregating data becomes more of a challenge
5. If you hijack the database connection logic, your functional and unit tests won?t be able to switch to the test database properly
6. Even if you don?t hijack the database connection logic, functional and unit tests expect to push the data into the main test db, which may cause Unexpected Results.

Pro's & Con's List borrowed from the Rails Wiki.

References

[1] http://rails.rubyonrails.com/classes/ActiveRecord/Base.html