CODEWITHSUNDEEP
mysqlruby-on-railsrubyruby-1.8
Max Williams
Max Williams

Reputation: 32933

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

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

Upvotes: 2

Views: 67

Answers (2)

Max Williams
Max Williams

Reputation: 32933

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

Upvotes: 0

NM Pennypacker
NM Pennypacker

Reputation: 6942

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

Upvotes: 1

Related Questions