Multiple Database in Rails

Multiple Database Connection with ActiveRecord

Off-late I see many questions on StackOverflow on multiple database, also quite a number of approaches doing it wrongly, while I have answered a few, here I'm quite briefing about it.

Making a connection when necessary and interacting with secondary database is quite a common approach and poor way of dealing with.

Keeping the configurations at one best place.

database.yml

development:
 adapter: [ adapter ]
 database: [DB name]
 username: [your username]
 password: [your password] 
 host: [host name]

secondary:
 adapter: [ adapter ]
 database: [ DB name ]
 username: [ username ]
 password: [ password ] 
 host: [ host name ]

This keeps the secondary database connection open, which could be a good thing for performance as well allows you to use ActiveRecord of Rails.

Then comes the connection with AR.

Class ExternalDatabaseConnection < ActiveRecord::Base
   establish_connection(:secondary)
   self.table_name = "the_table"
end

Usage

ExternalDatabaseConnection.first
ExternalDatabaseConnection.create(.......)

Dynamic way of table setting

In case when table is dynamically set by user. In class you must have to use

self.abstract_class = true
attr_protected

self.abstract_class

This tells ActiveRecord that it shouldn’t look for a table to go with that class

attr_protected

This will permit all attributes related to a table without defining it manually, in Rails 4, there must be different way.

ExternalDatabaseConnection.table_name = "table1"

ExternalDatabaseConnection is ready to serve as in AR object, with the related table set.

The Problem with dynamic way.

Dynamic way of changing table name, Consider..

ExternalDatabaseConnection.table_name = "users"
ExternalDatabaseConnection.column_names => ["name", "location", "email", "verified"]

ExternalDatabaseConnection.table_name = "posts"
ExternalDatabaseConnection.column_names => ["name", "location", "email", "verified"]

You see the problem there, its shows the attributes of initially set table, hence you no longer hold the instance of new object.

The Fix.

ExternalDatabaseConnection.reset_column_information

To override the previous object - table information, reset_column_information method on the class object must be called before setting new table.

Dealing with multiple database in Rails.