St. Elsewhere

An ActiveRecord plugin to support relationships across different databases

The Scenario

For a variety of reasons, you might find yourself supporting multiple databases in your Rails application. Maybe you’re connecting to a legacy database for a few models. Perhaps you have divided your Rails application into two parts, one database for your online catalog system and another for transactional data. Multiple database connections in Rails is nothing new.

The Problem

While there may be great benefits to connecting to multiple databases in your app, there are also costs. One example is that has_many :children, :through => parent_children does not work.

You’ll encounter one of two errors, depending on your setup:

• If the schemas are different, you’ll see something like: ActiveRecord::StatementInvalid: Mysql::Error: Table 'appname_transactional.parent_children' doesn't exist

• If the schemas are the same but data only exists in one schema or the other, you’ll just get empty relationships.

The Solution

St. Elsewhere adds a new class method (has_many_elsewhere) to support basic association methods across different database connections for ActiveRecord models.

Example:

class Hospital < AcitveRecord::Base
  has_many :hospital_doctors
  has_many_elsewhere :doctors, :through => :hospital_doctors
end

class HospitalDoctor < ActiveRecord::Base
  belongs_to :hospital
  belongs_to :doctor
end

class TransactionalBase < ActiveRecord::Base
  self.abstract_class = true
  establish_connection "#{RAILS_ENV}-transactional"
end

class Doctor < TransactionalBase
  has_many :hospital_doctors
  has_many :hospitals, :through => :hospital_doctors
end

The following conventional methods are available for Hospital:
hospital.doctors, hospital.doctors=, hospital.doctor_ids, hospital.doctor_ids=

Inefficiencies

has_many_elsewhere is certainly much less efficient than a comparable has_many relationship. has_many :through relationships use SQL JOINs which while efficient, do not work across multiple database connections. St. Elsewhere implements much of the same resulting API methods in code, using less efficient SQL.