How do i work with two different databases in rails with active records?

Question

I need to use different database connections in different Rails models. Is there a not-so-hacky way to do that?

Any links or search keywords would be great :)

Answer 1

Add new sections to your database.yml e.g.

other_development:
  adapter: mysql
  database: otherdb_development
  username: root
  password:
  host: localhost

other_production:
  adapter: mysql
  database: otherdb_production
  username: root
  password:
  host: localhost

Add a class in lib/other_database.rb

class OtherDatabase < ActiveRecord::Base
  establish_connection "other_#{RAILS_ENV}"
end

and then for each model which isn't in the default database subclass from OtherDatabase e.g.:

class MyModel < OtherDatabase
   # my model code...
end

Answer 2

I have been using the following to connect to 2 db in the same app. I put them in lib folder since everything in there is loaded.

require 'active_record'

class OldDatabase < ActiveRecord::Base
  self.abstract_class = true
  establish_connection(
  :adapter  => 'mysql',
  :database => 'weather',
  :host     => 'localhost',
  :username => 'root',
  :password => 'password'
  )
end

class NewDatabase < ActiveRecord::Base
  self.abstract_class = true
  establish_connection(
  :adapter  => 'mysql',
  :database => 'redmine',
  :host     => 'localhost',
  :username => 'root',
  :password => 'password'
  )
end

class WeatherData < OldDatabase
end

class Board < NewDatabase
end

Hope that helps

Answer 3

mikej is right. I did however write a gem that makes the model code to connect a little bit cleaner, check it out.

Answer 4

Update for Rails 3.x:

class MyModel < ActiveRecord::Base
  establish_connection "other_#{Rails.env}"
end

Answer 5

Rails 6 added native support for multiple databases: https://edgeguides.rubyonrails.org/active_record_multiple_databases.html

database.yml

development:
  one:
    <<: *default
  other:
    <<: *default

Model base classes:

class OneModelBase < ActiveRecord::Base
  around_action :set_db

  private

  def set_db
    ActiveRecord::Base.connected_to(database: :one) do
      yield
    end
  end
end

class OtherModelBase < ActiveRecord::Base
  around_action :set_db

  private

  def set_db
    ActiveRecord::Base.connected_to(database: :other) do
      yield
    end
  end
end

You'll also have different migrations and schemas per DB. Running a command like rails db:create will create all databases. You can scope commands, e.g. rails db:create:other.

Answer 6

I think that the prettiest way to connect to another database with active model is creating base class for external database, and then inherite from that base in your model. This method works fine with rails 4.2.6 and 5.0.4

For example:

# in /models/external_db/base.rb
require 'active_record'

class ExternalDb::Base < ActiveRecord::Base
  self.abstract_class = true
  establish_connection "external_db_#{Rails.env}".to_sym
end

And in your model class:

# in /models/external_db/some_model.rb
class ExternalDB::SomeModel < ExternalDb::Base
  # your code
end

But you must define external database in /config/database.yml

# in /config/database.yml
external_db_development:
  adapter: sqlite3
  pool: 5
  timeout: 5000
  database: db/external_db_development.db

external_db_production:
  adapter: sqlite3
  pool: 5
  timeout: 5000
  database: db/external_db_production.db

Answer 7

In rails 4.1+ establish_connection now takes a symbol:

class OtherDbModel < ActiveRecord::Base
  establish_connection :"other_#{Rails.env}"
end