Information Management Weblog

Grafting Rails Into an Existing Website: Handling multiple databases

Rails relies on convention, and one of the conventions is that you'll be connecting to just a single database. This, of course, makes plenty of sense in most situations. But, some of you may be facing a situation where you'd want to connect to multiple databases. The reasons aren't always clear, but they can usually be summed up in the word "legacy".

Rails doesn't make it easy to handle multiple databases. As far as I know, the way to handle this is to add the following bit of code to each model that is to be hooked to the second database:


ActiveRecord::Base.establish_connection(
:adapter => "mysql",
:socket => "/tmp/mysql.sock",
:username => "my_username",
:password => "my_password",
:database => "my_second_database"
)


This is all fine and good, if considerably more verbose than the standard configuration. And not too onerous if you're just talking about a model or two. But, sharp readers may notice that we're hardcoding in things like passwords and datasources. And, if you're like me and have separate databases for development, testing, and production, you might find yourself in a bit of a quandary. You really don't want to be hardcoding in "MyDatabase_Dev", and you really don't want to have to remember to change that to "MyDatabase_Production" when making your app live. Trust me. I learned the hard way.

But, Rails has a great feature that can help here: environments. Simply define a variable in config/environments/development.rb and config/environments/production.rb (and test.rb, assuming you want to run tests). Call it something along the lines of DATABASE_NAME, and assign it the name of the development DB in development.rb and the production database in production.rb Then, you can define things like:


ActiveRecord::Base.establish_connection(
:adapter => "mysql",
:socket => "/tmp/mysql.sock",
:username => "my_username",
:password => "my_password",
:database => DATABASE_NAME
)


That way, when you switch environments, the database will switch automatically. You can pull the same trick with usernames and passwords, too.

No, this isn't ideal, but it works *much* better than trying to remember to manually switch things!