You may be familiar with having a model connect to a non-default database, which can be handled by something like:
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.
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.
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)
Enable a single Rails instance to dynamically select from multiple database sources at runtime.
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.
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.
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.
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.
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.
Create rake task
lib/tasks/db.rake such as the following.
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
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.
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.
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
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!