- Set-up database.yml
- Direct connection
- 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
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
- Direct connection:
ActiveRecord::Base.establish_connection(:adapter=>"adapter_name",:host=>"localhost",
:username =>"user_name",:password => "*********",:database => "database_name")
- Writing in module:
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.
- If application is simple with less users
- Maintenance of code for the rest if any changes on database structure.