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

2. 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")


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

Read More : 

A Simple Way To Increase The Performance Of Your Rails App

An Introduction to Rails API

Image Manipulation

Polymorphic Associations With Active Record

 Get in touch with us.

Manasa Heggere

Senior Ruby on Rails Developer