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

  1. Set-up database.yml
  2. Direct connection
  3. Writing in module

1. Set-up database.yml:

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
user_name: user_name
password: ******
other_test:
adapter: adapter_name (mysql2, postgresql, oracle, Mssql, etc.,)
database: database_name_test
user_name: user_name
password: ******
other_production:
adapter: adapter_name (mysql2, postgresql, oracle, Mssql, etc.,)
database: database_name_production
user_name: user_name
password: ******

After setting up database.yml we can connect it in 2 ways based on the below cases

  • Known database structure
  • 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.

Example:

class OtherTable < ActiveRecord::Base
self.abstract_class = true
establish_connection “other_#{Rails.env}”
end

This can also be inherited by another model

class Astronaut < OtherTable
has_many :missions
has_many :shuttles, through: :missions
end

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.

Example:

class ExternalDatabaseConnection < ActiveRecord::Base
self.abstract_class = true # this class doesn’t have a table
establish_connection(:database_name)
end
  1. Direct connection:

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.

Example:

ActiveRecord::Base.establish_connection(:adapter=>”adapter_name”,:host=>”localhost”,
:username =>”user_name”,:password => “*********”,:database => “database_name”)
  1. Writing in module:

We can also connect the database from module and included in model as shown below.

Example:

module SecondDatabaseMixin
extend ActiveSupport::Concern
included { establish_connection “other_#{Rails.env}” }
end

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.

Example:

adapter: adapter_name (mysql2, postgresql, oracle, Mssql, etc.,)
host: external_db_server_ip (192.168.1.1)
username: user_name
password: *******
database: db_name

Note: There are few gems available to magic_multi_connections, Db-charme etc.,.

Pros and cons:

Pros

  • 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.

Cons

  • If application is simple with less users
  • Maintenance of code for the rest if any changes on database structure.

Visit our blog to read similar articles or check out our website to contact us.