Ruby on Rails - multiple database connections

Question

I'm developing an application where it lets users to establish many number of database connections with a has-many relationship between user and connections. The connections are passive until the user manually connects each. The motive is to perform queries on them, parallely.

I don't find good tutorials related to this, can you help me with some tips on how to accomplish this ?

You want users to be able to dictate how many connections they make to one or more databases? Also, when you say parallelism do you mean concurrency? — bkunzi01, Oct 05 '16 at 12:33
I sure hope you put in limits otherwise a malicious, or ignorant, user could take you down. — the Tin Man, Oct 05 '16 at 20:43

score 2 · Accepted Answer · answered Oct 10 '16 at 09:17

After researching for a bit, turns out there's a much simpler approach using ActiveRecord Connection pool.

Setup a relationship between users and the database connections, in this case user 1 .. n connections.

Make sure the model record can individually connect using

obj = ActiveRecord::Base.establish_connection(...spec...) 

obj.connection.exec_query("Select * from users") 
# the response is in the form of ActiveResult, which allows flexible operations around the result.

Close the connection once done with the database.

References:

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/ConnectionHandler.html

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/ConnectionPool.html

score 1 · Answer 2 · answered Oct 05 '16 at 14:13

1

For my application I use this gem

https://github.com/thiagopradi/octopus

have good documentation and examples.

answered Oct 05 '16 at 14:13

Goko Gorgiovski

1,364
2
13
20

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

Assuming you want to use activerecord you can go two ways:

dynamic class creation: see Dynamically create a class inherited from ActiveRecord?
define multiple classes and use the one needed, if they are in different schemes you need to specify the connection into the model itself, only feasable if the number of tables/models is not too high see my answer at Connecting to two different database in ruby at runtime

Here an example how to use such a model, the class_name parameter is the name of the model to be used. It's from a Sinatra app but I'm sure you'll be able to adapt it for Rails. It's a backend for a ExtJs javascript app using multiple models expecting JSON as result.

 # controller

%w(create read update destroy).each do |action|
  [:get, :post].each do |method|
      send(method, "/path/#{action}") do
        response.headers['Access-Control-Allow-Origin'] = '*'
        content_type :json
        if params[:store]
          store = Object.const_get(params[:store]) 
        else
          store = Signal
        end
        resp = send(action, store, params)
        jsonp(resp)
      end
  end
end

# in helper.rb

def read (class_name, params)
  params = params.symbolize_keys
  default = {store: 'Signaal', limit: 10, sort: 'id', order: 'ASC', start: 0, user: '0'}
  params = default.merge params
  generic_data_getter(class_name, params, params[:start], params[:limit], params[:sort], params[:dir])
end

def generic_data_getter (class_name, params, start=0, limit=10, sort='id', dir='ASC')
  selection = build_selection(class_name, params)
  data = class_name.where(selection).offset(start).limit(limit).order("#{sort} #{dir}")
  {:success => true, :totalCount => data.except(:offset, :limit, :order).count, :result => data.as_json}
end

If not or for simple predefined searches or speed you can connect and disconnect as a connection is needed. Here an example for Oracle.

require 'oci8'
CONN = OCI8.new('scheme','password','dbserver')
sql = '....'
CONN.exec(sql) {|record|puts record.join(',')}
CONN.logoff

Be aware of malicius use like code injection.

Ruby on Rails - multiple database connections

3 Answers3