Is it possible to have multiple ActiveRecord connections?

Question

I am using ActiveRecord along with Sqlite3 to do some data mining of large text files where there are a number of text files, and each text file represents an "instance" of a data model.

For any given text file, I parse it and ultimately load it into a ActiveRecord compatible Sqlite3 database. For example each table can be represented by a subclass of ApplicationRecord.

So what I end up with is a bunch of Sqlite3 databases (files). What I would like to do is to be able to run queries on multiple "instances" at the same time so I can compare records between "instances".

Currently I do something like this:

  data_from_db1 = nil
  data_from_db2 = nil        

  ActiveRecord::Base.establish_connection(
    :adapter => "sqlite3",
    :database => db_1,
  )

  // code to extract data from the established connection
  // save data to data_from_db1 object

  ActiveRecord::Base.remove_connection

  ActiveRecord::Base.establish_connection(
    :adapter => "sqlite3",
    :database => db_2,
  )

  // code to extract data from the established connection
  // save data to data_from_db2 object

  ActiveRecord::Base.remove_connection

  // work with saved data_from_dbN objects 

While this works OK, it would be much nicer if I could somehow get a handle for each database connection and then just direct my queries to specific databases.

Is there anyway I can keep multiple connections active and specify which one to use for a given query?

This does not seem possible, since queries are tied to models and I don't see a way to specify a connection to use, or even how to get a handle for a given connection.

For example, I can do queries such as:

MyModel.where(....)

but I think I would need to be able to do something like:

MyModel.with_connection(foo).where(....)

Note: its really important that I close the first connection before I establish the next connection otherwise if I ask for a property of a model I retrieved from the first connection, its likely that the property is actually an ActiveRecordRelation, and the property would be data returned from the current connection rather than the prior connection. Closing the connection means I will raise an exception rather than get the wrong data.

Slight Update

I know I can just combine all these smaller databases into one big database, but I would rather not do that since there are many combinations of databases that I might want to do calculations on, and the composite database would just be huge and overkill

Answer 1

Yes, it is possible:

# app.rb
require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'activerecord'
  gem 'sqlite3'
end

require 'active_record'

class User < ActiveRecord::Base; end

User.connects_to database: {
  primary: { database: 'db1.sqlite', adapter: "sqlite3" },
  secondary: { database: 'db2.sqlite', adapter: "sqlite3" }
}


ActiveRecord::Base.connected_to(role: :primary) do
  User.connection.execute("CREATE TABLE IF NOT EXISTS `users` (name VARCHAR (255))")
  User.create name: 'Komodo'
end

ActiveRecord::Base.connected_to(role: :secondary) do
  User.connection.execute("CREATE TABLE IF NOT EXISTS `users` (name VARCHAR (255))")
  User.create name: 'Cendrawasih'
end

ActiveRecord::Base.connected_to(role: :primary) do
  puts 'Primary Table Records: ', User.all.inspect
end

ActiveRecord::Base.connected_to(role: :secondary) do
  puts 'Secondary Table Records: ', User.all.inspect
end

output:

$ ruby app.rb
Primary Table Records:
#<ActiveRecord::Relation [#<User name: "Komodo">]>
Secondary Table Records:
#<ActiveRecord::Relation [#<User name: "Cendrawasih">]>

ps:

It is a very interesting question for me, I had to dig down into ActiveRecord test cases to get something like above.

Thanks to elieencodes and Ryuta Kamizono for the effort to supporting multi-database on active record. I get the names from git blame on the test cases. There is probably another contributor.

Answer 2

Yes I've done this a few times, you'll need to do the following

In your database.yml define the new databases

production:
  username: 
  password: 
  host: 
  database: database

db2_production:
  username: 
  password: 
  host: 
  database: database2

Next create a new file, I normally put it in lib/customclasses/db2_database_record.rb

class SecondDatabaseRecord < ActiveRecord::Base
  self.abstract_class = true
  establish_connection :"db2_#{Rails.env}"
end

Make sure those files are included in the rails application in application.rb eg:

config.paths.add Rails.root.join('lib/customclasses').to_s, eager_load: true

Then ina model that is in that database update the model.rb file to look at the new custom class you have created

class TableInDb2 < SecondDatabaseRecord
end

Obviously set the class names to be something meaningful to you. I have one application that talks to 3 different databases like this.