CODEWITHSUNDEEP
ruby-on-railsrubysql-serveractiverecordmultiple-databases
Sylvain Huguet
Sylvain Huguet

Reputation: 51

ruby - ActiveRecord multiple databases for the same model

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?

Upvotes: 3

Views: 1616

Answers (3)

Sylvain Huguet
Sylvain Huguet

Reputation: 51

I finally found a gem that I was able to hack to do what I need.

I used Octopus and its sharding capabilities, and I added a simple method to my base controller class (that all my other controllers are based off of) that looks like this:


def on_all_shards(&block)
  Octopus.config[:shards].each.collect do |shard, shard_config|
    Octopus.using(shard) do
      yield
    end
  end
end

Then in my controllers, whenever I need to aggregate the results of all my shards if just do:


on_all_shards do
  Model.first
end

In this case, I would get an array of the first record of this Model/table from each database (1 record / database).

Thank you guys for pointing me to the right direction (ActiveRecord connection/connection_pool management)!

Upvotes: 2

bridiver
bridiver

Reputation: 1714

If you had a list of all the connections you could do something like

connection_specs.collect do |spec|
  Model.establish_connection(spec)
  Model.all
end

or initialize some base classes somewhere

db_klasses = connection_specs.collect do |spec|
  Class.new(ActiveRecord::Base) do
    establish_connection(spec)
  end
end

and then

db_klasses.collect do |db_klass|
  db_klass.connection_pool.with_connection do
    Model.all
  end
end

Upvotes: 1

Richard Peck
Richard Peck

Reputation: 76784

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

Upvotes: 2

Related Questions