ruby - ActiveRecord multiple databases for the same model

Question

We have a vendor-provided application that is using a MSSQL database as backend. We have multiple instances of this application, and thus multiple databases / databases servers hosting the same database model but with different data.

I'm trying to develop a very simple "browsing tool" to provide a single view of all theses databases (read only).

I'm using Sinatra/ActiveRecord/ActiveRecord-SqlServer-Adapter and I have my models overloaded to comply with the database model.

What I'm now looking for is a way to request all the databases at once and aggregate all the results.

Is there any way to do this in vanilla ActiveRecord? Using a gem? I found the db-charmer gem that does something like that, but's it's only compatible with MySQL and I can't get it to work without rails anyway.

Any idea?

Answer 1

This won't be a specific answer to your question, but may help you nonetheless

--

Models

We've been working on a multi-tenancy system lately, and needed to access multiple databases

The "way" to do it is actually rather simple - but only if you wanted to do it at model level. Anything else & you've lost me, sorry.

At model level, each time you initialize an instance of an object (IE load a model), Rails will access the database, and consequently populate the ruby object with the required attributes. A "trick" to making a multi-tenant system (with multiple database connections) is to inherit from different models

Here's an example:

#app/models/option.rb
Class Option < Admin
   ...
end

#lib/admin.rb
Class Admin < ActiveRecord::Base
  self.abstract_class = true 
  establish_connection "#{Rails.env}_admin"
end

#config/database.yml
production_admin:
  your: ____
  database: ____
  values: ____

This allows you to connect to a different database for your Option model; providing you with the ability to load the various files / settings to make it work correctly

Whilst I don't think this will address your issue directly (you want to access multiple databases simultaneously), I hope it will give you an idea

--

Database

The bottom line here is that if you want to connect to multiple database simultaneously, you'll likely want to look at identifying the database for the model, and then setting the connection type in the model.

I'm not exactly sure as to whether you're trying to load the data into different model objects, or the same one. If it's through multiple object, you might be able to pull off what I mentioned above, dynamically setting the database as required

--

That's all I've got I'm afraid