Dynamically Select Databases in Rails!

Background

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!