3

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?

Improve this question
0

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I've looked at what you are describing, but I need to have the same model pull data from multiple databases (all the databases having the exact same schema, only the data inside are different).
2

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)!

Improve this answer

Comments

1

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
Improve this answer

5 Comments

Seems nice, but won't it reconnect to each database for each request?
Yea, I suppose you could get around that by dynamically defining a base class for each connection (maybe in an initializer?) and then using with_connection. That should take advantage of the connection pool
I tried it, but ActiveRecord is complaining about Anonymous class not being allowed... Any way to fixe that?
Ok, I've got this a little bit further, by adding a |klass| block parameter to Class.new and naming the class using Object.const_set(spec[:database].classify, klass) inside the block. The problem is, when inside the "model" loop, ActiveRecord throw a ConnectionNotEstablished error
Are you still calling establish_connection in the class definition?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.