Using Rails Migration on different database than standard "production" or "development"

Question

I have a rails project running that defines the standard production:, :development and :test DB-connections in config/database.yml

In addition I have a quiz_development: and quiz_production: definition pointing to a differnet host/db/user/password

My goal now is to define a Migration that uses "quiz_#{RAILS_ENV}`" as its database configuration.

What I have tried (and failed):

Setting ActiveRecord::Base.connection in the Migration file
Changing the db:migrate task in rails to set ActiveRecord::Base.connection there

Question:

How can I make rake db:migrate use that other database definition?

Thanks, Frank

user_91365 · Answer

There's a much easier answer. Add this to your migration:

def connection
  ActiveRecord::Base.establish_connection("quiz_#{Rails.env}").connection
end

That's for Rails 3.1. For Rails 2.X or 3.0 it's a class function instead (eg def self.connection)

user_1416381 · Answer

For example, I have a study_history model:

rails g model study_history lesson:references user:references history_type:references

Define mysql section in database.yml

player_records:
  adapter: mysql2
  encoding: utf8
  host: 1.2.3.4
  username: root
  password: 
  timeout: 5000
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 20 } %>
  database: player_records

Modify the StudyHistory model, add establish_connect, it will connect your mysql database player_records above (I added this database in mysql server first):

class StudyHistory < ApplicationRecord
  establish_connection :player_records
  
  belongs_to :lesson
  belongs_to :user
  belongs_to :history_type
end

Use connection in the migration file to create table:

class CreateStudyHistories < ActiveRecord::Migration[6.0]
  def change
    StudyHistory.connection.create_table :study_histories do |t|
      t.references :lesson, null: false
      t.references :user, null: false
      t.references :history_type, null: false

      t.timestamps
    end
  end
end

now, you can run

rails db:migrate

That's it, I tested in rails 6, it works like a charm, you can get your data from different databases combined( local sqlite3 and remote mysql).

irb(main):029:0> StudyHistory.first.lesson
   (42.5ms)  SET NAMES utf8,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_Z
ERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
  StudyHistory Load (30.0ms)  SELECT `study_histories`.* FROM `study_histories` ORDER BY `study_histories`.`id` ASC LIMIT 1
   (0.0ms)  
 SELECT sqlite_version(*)
  Lesson Load (0.1ms)  SELECT "lessons".* FROM "lessons" WHERE "lessons"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
=> #

Using Rails Migration on different database than standard "production" or "development"

20 Answers