Scaling Applications with Multiple Database Connection
Business requirements keep changing day by day and we always keep optimizing or scaling our applications based on the usage, new feature additions or subtractions. Over all the agile development adds challenges every now and then.
Applications depending on databases can be scaled by separating the database layer and scaling it independently. The OPS team does take care of such infrastructure changes based on the application deployment architecture.
As a programmer, we can configure our application to work with multiple databases. In this document we are going to explain how we can achieve this in a Rails application.
There are 3 different ways to connect extra database to an application
As we know database.yml will be having 3 database connection by default for development, test and production. We can connect another database to all three environments by adding the code shown below.
other_development:
adapter: adapter_name (mysql2, postgresql, oracle, Mssql, etc.,)
database: database_name_development
adapter: adapter_name (mysql2, postgresql, oracle, Mssql, etc.,)
database: database_name_test
adapter: adapter_name (mysql2, postgresql, oracle, Mssql, etc.,)
database: database_name_production
After setting up database.yml we can connect it in 2 ways based on the below cases
Un-known database structure
Known database structure:
If we are aware of the database structure, we can create models for each and we can establish the connection in the model.
class OtherTable < ActiveRecord::Base
self.abstract_class = true
establish_connection “other_#{Rails.env}”
This can also be inherited by another model
class Astronaut < OtherTable
has_many :shuttles, through: :missions
Un-known database structure:
When we don’t know the database structure we can write only one model and we can make the connection to it. We can do the crud based on the dynamic parameters.
class ExternalDatabaseConnection < ActiveRecord::Base
self.abstract_class = true # this class doesn’t have a table
establish_connection(:database_name)
In case 2nd database has not much importance and is used in one or two places we can directly call the
ActiveRecord::Base.establish_connection with credentials and we can interact with that database.
ActiveRecord::Base.establish_connection(:adapter=>"adapter_name",:host=>"localhost",
:username =>"user_name",:password => "*********",:database => "database_name")
We can also connect the database from module and included in model as shown below.
module SecondDatabaseMixin
extend ActiveSupport::Concern
included { establish_connection “other_#{Rails.env}” }
External database connection:
Database to be connected can be exists on any server. In case it is not on the same server we can give host as IP address of the server where it exists.
adapter: adapter_name (mysql2, postgresql, oracle, Mssql, etc.,)
host: external_db_server_ip (192.168.1.1)
Note: There are few gems available to magic_multi_connections, Db-charme etc.,.
If the application has multiple clients and each wants a different database for their customers.
Helps in backups for each client.
Another database may be used in another application which may have different adapter.
When users report that access is slow, easy to know which DB is causing the trouble.
If application is simple with less users
Maintenance of code for the rest if any changes on database structure.
