Dynamically Select Databases in Rails!
February 09, 2016Background
You may be familiar with having a model connect to a non-default database, which can be handled by something like:
class AlternateUser < ActiveRecord::Base
establish_connection "#{Rails.env}_alternate".to_sym
...
end
When there are a set of models that access the non-default database, the base model class is made abstract, having each of the set of models inherit from this base model.
class AlternateDbModel < ActiveRecord::Base
self.abstract_class = true
establish_connection "#{Rails.env}_alternate".to_sym
end
class AlternateUser < AlternateDbModel
...
end
However, in both of the above cases, the database connection is hard-coded by the developer (or possibly set by a fixed environment variable). What if we want to select at runtime the database the model (or set of models) is to access? Beyond that, how about having the ability to dynamically switch databases throughout the entire session? That’s what this post is about.
Summary
Two approaches are outlined for dynamic database selection. The first approach extends the standard way shown above, allowing for database selection to occur at the beginning of a session. This approach is best when:
- The selection must remain fixed for the duration of the session
- All models can be a subclass of the alternate model base class
The second approach covered overcomes the restrictions of the first approach by:
- Allowing the database selection to be changed at any time
- Models do not require any special subclassing (which has distinct advantages as will be discussed in Approach Two)
Objective
Enable a single Rails instance to dynamically select from multiple database sources at runtime.
Goals
Single Codebase
In the first approach, all appropriately subclassed models will access the selected data source. For the second approach, this is generalized to include all models and they will automatically use the selected data source. Further, should a different data source be subsequently selected, the models will begin accessing the new selection.
Simple Migration
There remain only the three standard Rails environments with two or more databases available in each. When provisioning the databases within an environment, a custom rake task enables the typical rake db:migrate
command to also run the (pending) migrations for the additional database(s) within that environment.
Transparency
Users of the models will not need to be aware of the particular database being accessed or change their interactions with the model’s instance once the database has been set.
Use Case
Let’s say there are two databases, one for Doctors and another for Patients. Assume we want to keep all of the user information separated by different data sources. We also want to use the exact same codebase / models when obtaining data, and expect the models to negotiate which database to use based upon the kind of user.
Setup Steps
Specify Additional Database(s)
For each of the three Rails environments in config/database.yml, add the new / non-default database(s). Let the rails environment name serve to prefix each new database name as shown. In the example, the default database is used for Doctors while the alternate is for Patients.
default: &default
adapter: sqlite3
pool: 5
timeout: 5000
development:
<<: *default
database: db/development.sqlite3
development_patient:
<<: *default
database: db/development_patient.sqlite3
...
Extend db:migrate
Create rake task lib/tasks/db.rake
such as the following.
namespace :db do
desc "Run db migrations for additional database"
task migrate: :environment do
# Invoking rake db:migrate executes against the usual/default db
# first, then this gets executed.
puts 'Patient migration'
ActiveRecord::Base.establish_connection "#{Rails.env}_patient".to_sym
ActiveRecord::Migrator.migrate('db/migrate')
end
end
Selection Indicator
For this example we’re going to have an initial landing page for all users that requires no database access. From this page, the user will select the link that pertains to their type, whether Patient or Doctor. Each link contains a parameter that identifies the user type chosen. We’ll use this mechanism to identify which database to be selected in our example.
Identify the Selection
The ApplicationController
is modified to make the most recently selected user type available to models. Since the session
is not visible to models, the selection is stored on the executing thread. Note that the call to UserTypeModel.set_connection
is only used for Approach Two.
class ApplicationController < ActionController::Base
protect_from_forgery with: :exception
before_action :set_user_type
private
def set_user_type
session[:user_type] = whitelist_params[:user_type] if whitelist_params[:user_type]
Thread.current[:user_type] = session[:user_type] # keep populated with the most recent type
UserTypeModel.set_connection #omit this line for Approach One
end
def whitelist_params
params.permit(:user_type)
end
end
Model - Approach One
The first time a subclass of this model is used, the following script will execute, specifying the selected database for all subsequent data access for any child of this class.
Important! Models that do not inherit from this class will use the default database.
class UserTypeModel < ActiveRecord::Base
self.abstract_class = true
if(Thread.current[:user_type] == 'patient')
establish_connection "#{Rails.env}_patient".to_sym
else
establish_connection "#{Rails.env}".to_sym
end
end
Model - Approach Two
This approach has a number of advantages over the first approach, such as:
- The database that all models will access may be changed at any time
- The technique described can be dropped into an existing app without modification to the existing models
- Models created by third-party gems, such as Devise, will also use the dynamically selected database
class UserTypeModel < ActiveRecord::Base
self.abstract_class = true
# Dynamically sets the database connection.
# The connection is used for all subsequent model request
# (until changed by calling again with a different user_type)
def self.set_connection
if changing_connection?
if(Thread.current[:user_type] == 'patient')
ActiveRecord::Base.establish_connection "#{Rails.env}_patient".to_sym
else
ActiveRecord::Base.establish_connection "#{Rails.env}".to_sym
end
end
end
private
def self.changing_connection?
patient_connection = ActiveRecord::Base.connection_config[:database].include?('_patient')
patient_thread = Thread.current[:user_type] == 'patient'
( patient_connection && !patient_thread ) || ( !patient_connection && patient_thread )
end
end
And There You Have It!
The ability to dynamically select a database can be achieved with very little effort. Depending upon your use case, either of the two approaches outlined above allows you to easily include this functionality within your app. Extending the sample code for two or more additional data sources is also an easy matter, but left for you to implement. Enjoy!