[Ruby] HOW TO use multiple databases with ActiveRecord

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.

6 thoughts on “[Ruby] HOW TO use multiple databases with ActiveRecord

  1. Derek

    This works like a charm with mysql, unfortunately postgres croaks as it seems to be trying to build the table config for base_dbs in BaseDB :(

    Trying to figure out how to make it work with postgres as well, but any suggestions would be greatly appreciated.

    Thanks.

  2. ice

    Thanks for the alternative solution and simple example. I wasn’t aware of that blog post.

    I tested the simple example given (with rails 2.3.2, mysql and WEBrick 1.3.1) and it does work. However ActiveRecord tries to connect to the database on each ActiveRecord::Base sub-class load.

    If I use 4 ActiveRecord sub-classes, there will be 4 times the number of connection requests. (maybe class cache feature helps here?) So we need to consider this and maybe DRY too when we put establish_connection into each ActiveRecord.

    Thanks!

Comments are closed.