Connecting to multiple databases in ruby on rails

Question

I have a ruby on rails application working fine and connected to a database. Now i want to connect to a different database from the same application. The data model can be exactly the same. In fact if i connect to the different database the application works fine. However I want to connect to two different databases. Is it possible in ruby on rails?

Answer 1

For multiple database connection, you need to add the following codes to the database.yml file. Here, I am giving the example of connecting two databases from a rails application

config/database.yml

development:
  adapter: mysql2
  database: db1_dev
  username: root
  password: xyz
  host: localhost

development_sec:
  adapter: mysql2
  database: db2_dev
  username: root
  password: xyz
  host: localhost

production:
  adapter: mysql2
  database: db1_prod
  username: root
  password: xyz
  host: your-production-ip

production_sec:
  adapter: mysql2
  database: db2_prod
  username: root
  password: xyz
  host: your-production-ip

Here I have used two databases for the development and production environment.

Now we need to connect the model to databases. When you are running your application in development and production mode, all the models will be mapped through the development and production db parameters those been mentioned in your database.yml. So for some model we need to connect to other database.

Lets assume that, we have two models User and Category. The users table is in db1_dev and db1_prod, the categories table in db2_dev and db2_prod.

Category model

class Category < ActiveRecord::Base
  establish_connection "#{Rails.env}_sec".to_sym
end

Similarly, when you adding the new migration for the second database, need to add following code to it.

class CreateRewards < ActiveRecord::Migration
  def connection
    ActiveRecord::Base.establish_connection("#{Rails.env}_sec".to_sym).connection
  end

  def change
    # your code goes here.
  end
end

Hope it will work for you :) .

Answer 2

Use establish_connection to switch to a different database:

ActiveRecord::Base.establish_connection(
  :adapter  => "mysql",
  :host     => "localhost",
  :username => "myuser",
  :password => "mypass",
  :database => "somedatabase"
)

You can also pass a preconfigured environment from database.yml like so:

ActiveRecord::Base.establish_connection(ActiveRecord::Base.configurations['other_env'])

You can also set it for a specific model:

MyClass.establish_connection(...)

Answer 3

You might like to note that as of Rails 6 (2019), Rails has support for multiple primary databases!

https://guides.rubyonrails.org/active_record_multiple_databases.html

The database.yml file will now look something like this:

development:
  primary:
    database: primary_db
    user: root
  primary_replica:
    database: primary_db
    user: ro_user
    replica: true
  animals:
    database: my_animals_db
    user: root
    migrations_path: db/animals_migrate
  animals_replica:
    database: my_animals_db
    user: ro_user
    replica: true

And then it's as simple as specifying in your model files:

class AnimalsModel < ApplicationRecord
  self.abstract_class = true
  connects_to database: { writing: :animals_primary, reading: :animals_replica }
end

class Dog < AnimalsModel
  # connected to both the animals_primary db for writing and the animals_replica for reading
end

(These examples were taken from this helpful tutorial.)