Rails - query a different mysql db to the standard app db

Question

I'm using Rails 2.2.2 and Ruby 1.8.6 with a legacy app, with MySQL. (please don't tell me that I need to upgrade ruby/rails).

I have backups of our live database installed as different databases within my local MySql. This is useful sometimes for querying historical data, as this doesn't always get preserved within our active database.

What I'd like to be able to do is something like this

school_ids = [123, 456, 789]
signin_counts = {}

#collect current data
school_ids.each do |school_id|
  signin_counts[school_id] ||= {}
  signin_counts[school_id][:now] = ActiveRecord::Base.connection.select_value("select count(*) from sign_ins where school_id = #{school.id}").to_i
end

#switch to the old database - how to do this?
CURRENT_DB = "my_old_backup_db_name"
school_ids.each do |school_id|
  signin_counts[school_id] ||= {}
  signin_counts[school_id][:then] = ActiveRecord::Base.connection.select_value("select count(*) from sign_ins where school_id = #{school.id}").to_i
end

#switch back
CURRENT_DB = "my_regular_db_name"

Does anyone know how to do the CURRENT_DB = part? Thanks

Don't know if this will apply but I found the following article: (http://www.thegreatcodeadventure.com/managing-multiple-databases-in-a-single-rails-application/) — DaveG, May 14 '18 at 13:22
Did you try [this](https://apidock.com/rails/ActiveRecord/Base/establish_connection/class), This is a [tutorial](https://medium.com/@primazp/multiple-databases-in-rails-part-1-setup-48f6d207685a) too, it could help. Also there is a gem called [octopus](https://github.com/thiagopradi/octopus) where you can use its functionality to control which DB you want to work on, Hope it helps. — Amr Adel, May 14 '18 at 13:27

score 1 · Answer 1 · answered May 14 '18 at 13:23

If you're in a model file that inherits ActiveRecord::Base you can do:

  mysql_database = establish_connection (
    adapter: "mysql",
    host: "your_db_host",
    username: "your_db_username",
    password: "your_db_password",
    database: "your_db_name"
  )

This post has a good explanation of how to use establish_connection http://ilikestuffblog.com/2012/09/21/establishing-a-connection-to-a-non-default-database-in-rails-3-2-2/ and is possibly old enough to be relevant for your version of Rails, though, a lot changed between versions 2 and 3

score 0 · Answer 2 · answered May 15 '18 at 07:48

Thanks to @DaveGoldberg's comment, I made these two methods which illustrate how to do it. Obviously there's cleaner ways of packaging this up, I just wanted to show one simple way.

def use_old_db
  config = {
    :adapter => 'mysql',
    :host => 'localhost',
    :username => 'my_username',
    :password => 'my_password',
    :database => 'name_of_database'
  }
  ActiveRecord::Base.establish_connection(config)
end

def use_new_db
  ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
end

Rails - query a different mysql db to the standard app db

2 Answers2