Connecting to multiple databases using ActiveRecord

Question

I have a situation where my app needs to connect to a database on request. The data model of the databases is identical, but I need to connect to different ones in parallel. For instance, my RESTful route might look like this

/database/:database_id/user/:user_id # Get user :user_id from database :database_id

I've looked at similar questions, and they all recommend using ActiveRecord::Base#establish_connection. These questions, however, don't seem to handle the case of accessing multiple databases simutaeously while using the same model classes. My worry is that I will get one request, establish a connection, start reading from the database, but then have a new request come in and change the connection of ActiveRecord. Is this a legitimate worry? I'm running this code on Ruby 1.8.7 using Rails 3.0.

Thanks. - Max

score 4 · Answer 1 · edited May 23 '17 at 12:23

So, I was facing a similar problem where the connections needed to be somewhat dynamic. Instead of using ActiveRecord, I'd suggest just using the db driver. In my case, it was a legacy database, and I didn't need the record to turn into an object that I could manipulate and save back to the db, it was more for reporting purposes--I just needed the results of an aggregate query (select only).

For this case, just using the MySQL2 gem works great. This approach allows Rails to be opinionated about some stuff in the app (for which I want/need an ORM), but gives me the flexibility to avoid AR when I really just need to connect to an arbitrary DB to grab some quick data.

So here are some questions you'll want to ask yourself:

Do I need ORM-like functionality to map DB rows to objects? Do I need CRUD operations?
- If so, where will you define those? You may be better off with a metaprogramming and creating classes at runtime (check out this question and answer on StackOverflow about creating classes dynamically).
Do I need persistent connections, or is it ok if the database connection goes away after the request?
- (of course you can create your own pools and persistent connections if you want/need)

If you don't need ORM-like functionality with CRUD operations and you can get by with a few simple operations, AND if you don't need persistent connections, I'd recommend just going with using the DB driver for your database.

HTH

I'll only be reading from the database, but it would be might convenient to use some of the relational features active record gives you, since the data is highly relational. Also, you get free JSON seialization, which I need. I will post an update tomorrow once I start implementing this stuff. Thanks for the great answer. — Max, May 16 '14 at 06:18

score 1 · Answer 2 · answered Jun 16 '21 at 00:49

In Rails 6 you can use Multiple Database feature:

class ShardRecord < ActiveRecord::Base
  self.abstract_class = true

  connects_to shards: {
    club_1: { writing: :club_1, reading: :club_1 },
    club_2: { writing: :club_2, reading: :club_2 }
  }
end

class ApplicationController < ActionController::API
  before_action :set_club
  around_action :connect_to_shard

  private

  def set_club
    @club = SelectClubByDomain.new(request).slug
  end

  def connect_to_shard
    ActiveRecord::Base.connected_to(role: :writing, shard: @club.slug) do
      yield
    end
  end
end

Sources:

https://guides.rubyonrails.org/active_record_multiple_databases.html https://www.freshworks.com/horizontal-sharding-in-a-multi-tenant-app-with-rails-61-blog/ https://api.rubyonrails.org/classes/ActiveRecord/ConnectionHandling.html#method-i-connected_to_many

Connecting to multiple databases using ActiveRecord

2 Answers2