[Ruby] HOW TO use multiple databases with ActiveRecord

» Posted by ice on Feb 16, 2009 in Blog | 6 comments

Sometimes you need to access multiple databases in one application.
For example, using different application’s database.  But usually ActiveRecord is configured to ActiveRecord::Base directly, and so establishes a connection with ActiveRecord::Base, so you can’t use multiple database with AR.

So how to do it?

Just create sub-classes of ActiveRecord::Base.

Sub-classes of ActiveRecord::Base can access parent class configurations and connections. So create a sub-class of ActiveRecord::Base BaseDB then set configurations to it.  Create sub-classes of BaseDB then establish database connection each of them. It’s better than to set configurations and establish connection on ActiveRecord::Base directly for future.

• ActiveRecord::Base
  • BaseDB < ActiveRecord::Base (set database configurations to this)
    • MyDB < BaseDB (establish database connection to “mydb” on this)
    • YourDB < BaseDB (establish database connection to “yourdb” on this)

Sample code below are to access two different databases.

Environment:

• database(1)
  • databasename : my_db
  • username : myname
  • password : mypass

• database(2)
  • databasename : your_db
  • username : yourname
  • password : yourpass

Directory Structure:

• bin/ar-multi-connections-test.rb : main program
• config/database.yml : configuration file for databases
• lib/base_db.rb : base class of *_db and common configuration holder
• lib/my_db.rb : for database(1) my_db
• lib/my_table.rb : for database(1)’s table
• lib/your_db.rb : for database(2) your_db
• lib/your_table.rb : for database(2)’s table

Code Listings

bin/ar-multi-connections-test.rb

#!/usr/bin/env ruby
# -*- coding: utf-8 -*-
$LOAD_PATH << File.expand_path(File.join('..', 'lib'), File.dirname(__FILE__))

require 'messa/engine/parser'
require 'messa/messa_helper'
require 'messa/logger_factory'

# path to database configuration file
config_file = File.expand_path(File.join('..', 'config', 'database.yml'), File.dirname(__FILE__))

BaseDB.load_database_configurations(config_file)

MyDB.establish_connection(:mydb_test)
YourDB.establish_connection(:yourdb_test)

config/database.yml

mydb_test:
  adapter: mysql
  encoding: utf8
  database: mydb
  username: myname
  password: mypass
  socket: /var/run/mysqld/mysqld.sock

yourdb_test:
  adapter: mysql
  encoding: utf8
  database: yourdb
  username: yourname
  password: mypass
  socket: /var/run/mysqld/mysqld.sock

lib/base_db.rb

# -*- coding: utf-8 -*-
require 'rubygems'
require 'activerecord'
require 'erb'
require 'yaml'

class BaseDB < ActiveRecord::Base
  #
  #=== load configurations from YAML file
  #
  #_config_ :: path to a YAML configuration file
  #
  def self.load_configurations(config)
    # like ActiveRecord::Base.configurations = ...
    self.configurations = YAML::load(ERB.new(IO.read(config)).result)
  end # def
end # class

lib/my_db.rb

# -*- coding: utf-8 -*-
require 'base_db'

class MyDB < BaseDB
end # class

lib/my_table.rb

# -*- coding: utf-8 -*-
require 'my_db'

class MyTable < MyDB
  set_table_name 'my_table'
end # class

lib/your_db.rb

# -*- coding: utf-8 -*-require 'base_db'

class YourDB < BaseDB
end

lib/your_table.rb

# -*- coding: utf-8 -*-
require 'your_db'

class YourTable < YourDB
  set_table_name 'your_table'
end # class

That’s it.  Please feel free to comment or ask questions about this.