ChrisCodes.com

I recently had a need to connect a Rails model to a secondary database (external from the application database) for authentication purposes. The basics of this are covered in the Agile book, and covered in more detail in the Rails Recipes book.

Unfortunately, neither mentions how to make unit testing work with the external database. It's also not clearly documented that I could find, so here it is.

Connect Rails model to secondary database, external from application database

This covers using an external, or secondary database for a model in a Rails application

Specify additional info in config/database.yml

If there's only one database, and you're not running unit tests, you can do something like this (after your applications database configuration).

external_db:
  adapter: mysql
  database: secondary_database_name
  username: secondary_database_user
  password: secondary_database_password
  host: secondary_database_host

Then make the connection in the model...

	class Employee
    establish_connection :external_db
  end

Using an external database with Rails development, test, and production environments

In addition to the applications database configuration, you will add:

external_db_development:
  adapter: mysql
  database: secondary_database_for_development
  username: secondary_database_user
  password: secondary_database_password
  host: secondary_database_host
  
external_db_test:
	adapter: mysql
  database: secondary_database_for_test
  username: secondary_database_user
  password: secondary_database_password
  host: secondary_database_host

external_db_production:
	adapter: mysql
  database: secondary_database_for_production
  username: secondary_database_user
  password: secondary_database_password
  host: secondary_database_host

Then the model we'd have..

class Employee
  establish_connection "external_db_" + RAILS_ENV
end

This will append the current rails environment onto your external db name

Loading fixtures into an external database for Rails tests

By default fixtures are not smart enough to use the connection specified in the model. This results in lots of fun errors when your test calls the fixture to be loaded, and the table is not found on the default connection.
To fix that, in your unit test (or test_helper.rb if these fixtures will be used in many tests) add the following

  set_fixture_class :employees => Employee

Now you can call 'fixtures :employees' as normal and be a good little tester.

Additional tips

Most likely the external database will not be using id as the primary key. If not, set it in the model like so:

  self.primary_key = "employee_id"

If you're stuck on the simplicity of Rails naming conventions, but your external database isn't, you can create methods to return the "un-rails-like" names.
Here's a complete example of a model I used to connect to a seperate external database for authentication.

class Employee < ActiveRecord::Base
  establish_connection "external_db_" + RAILS_ENV
  self.primary_key = "employee_id"

  has_many :documents
  has_many :folders
  belongs_to :level

  # railsify columns!
  def username
    self.employee_username
  end

  def password
    self.employee_password
  end

  def name
    self.employee_name
  end

  def email
    self.employee_email
  end

  def active
    self.employee_active
  end

  def self.authenticate(username, password)
    employee = self.find(:first, :conditions => {:employee_username => username})
    if employee.blank? || Digest::MD5.hexdigest(password) != employee.password
      employee = nil
    end
    employee
  end

  def has_right_for?(controller, action)
    self.level.rights.detect{ |right| right.controller == controller && right.action == action }
  end
end

There we have it. Employee's can now use their already existing login for this application, and my role based authentication scheme ties in to the existing level_id column in the external database.
More importantly, I can write and run unit and functional tests that can verify everything works as intended.

has_many and belongs_to work as expected, just add the employee_id column to the table, then specify the relationship in the models.
Things get slightly (but not incredibly) trickier if you need a has_and_belongs_to_many relationship with a table in an external database.
I'll leave that for you to figure out. (Hint... if you don't feel like thinking, see the Rails Recipe book)