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

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

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)

I got this to work with the following code.

class AddInProgressToRefHighLevelStatuses < ActiveRecord::Migration
  def connection
    @connection = ActiveRecord::Base.establish_connection("sdmstore_#{Rails.env}").connection
  end

  def change
    add_column :ref_high_level_statuses, :is_in_progress, :boolean, :default => true

    @connection = ActiveRecord::Base.establish_connection("#{Rails.env}").connection
  end
end

It was necessary to set the connection back to get it to write the migration to the schema_migrations table so rake would not try to re-run the migration the next time. This assumes that you want the schema_migrations table in the default database configuration to keep track of the migrations checked into version control for the corresponding project.

I was unable to get the down migration to work.

You should define the other databases/environments in /config/environments.

After that you can use the following command to migrate that specific environment.

rake db:migrate RAILS_ENV=customenvironment

I recently struggled with the same problem. The goal was to split off a histories table to a different database since it was already so large and still growing very quickly.

I started trying to resolve it by doing ActiveRecord::Base.establish_connection(:history_database), but could not get any variations of that way to work without the connection being closed. Then finally I discovered the solution below.

In the History model after making this change:

class History < ActiveRecord::Base

  # Directs queries to a database specifically for History
  establish_connection :history_database

  ...
end

I was able to do this in the migration and it worked perfectly:

class CreateHistoriesTableInHistoryDatabase < ActiveRecord::Migration
  def up
    History.connection.create_table :histories do |t|
      ...
    end
  end

  def down
    History.connection.drop_table :histories
  end
end

This will create the table in a different database, yet modify the schema_migrations table in the original database so the migration does not run again.

A bit late, but I was dealing with this problem today and I came up with this custom rake task:

namespace :db do
  desc "Apply db tasks in custom databases, for example  rake db:alter[db:migrate,test-es] applies db:migrate on the database defined as test-es in databases.yml"
  task :alter, [:task,:database] => [:environment] do |t, args|
    require 'activerecord'
    puts "Applying #{args.task} on #{args.database}"
    ActiveRecord::Base.establish_connection(ActiveRecord::Base.configurations[args.database])
    Rake::Task[args.task].invoke
  end
end

Hey I been digging into this for a few days and I ended up with this solution, just wanted to share it, it might help someone.

Here the complete gist for it. https://gist.github.com/rafaelchiti/5575309 It has details ans explanation. But find below more details if you need them.

The approach is based on adding a namespace to the already known rake tasks db:migrate, db:create, db:drop and perform those tasks with a different database. And then in adding a base active record (AR) class for connecting based on the configuration of the new database.yml file. This way you don't need to hack around the migrations with connection stuff and you get a clean directory structure.

Your structure will end up like this

config
  |- database.yml
  \- another_database.yml (using the same nomenclature of 'development', 'test', etc).

db
  |- migrate (default migrate directory)
  |- schema.rb
  |- seed.rb

another_db
  |- migrate (migrations for the second db)
  |- schema.rb (schema that will be auto generated for this db)
  |- seed.rb (seed file for the new db)

Then in your code you can create a base class and read the config from this new database.yml file and connect to it only on the models that inherit from that AR base class. (example in the gist).

Best!.

Following on from @Bryan Larsen, if you're using an abstract Class to attach a series of models to a different database, and would like to migrate schemas on them, then you can do this:

class CreatePosts < ActiveRecord::Migration
    def connection
      Post.connection
    end
    def up
      ...
    end
end

with a model set up something like:

class Post < ReferenceData
end

and

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

For Rails 3.2, this is what we did, works with migrating up and down:

class CreateYourTable < ActiveRecord::Migration

  def connection
    @connection ||= ActiveRecord::Base.connection
  end

  def with_proper_connection
    @connection = YourTable.connection
    yield
    @connection = ActiveRecord::Base.connection
  end


  def up
    with_proper_connection do
      create_table :your_table do |t|
      end
    end
  end

  def down
    with_proper_connection do
      drop_table :your_table
    end
  end

end

module ActiveRecord::ConnectionSwitch
  def on_connection(options)
    raise ArgumentError, "Got nil object instead of db config options :(" if options.nil?
    ActiveRecord::Base.establish_connection(options)
    yield
  ensure
    ActiveRecord::Base.establish_connection ActiveRecord::Base.configurations[Rails.env]
  end
end

ActiveRecord.send :extend, ActiveRecord::ConnectionSwitch

If you place this inside config/initializers/ you'll be able to do something like this:

ActiveRecord.on_connection ActiveRecord::Base.configurations['production'] do
  Widget.delete_all
end

This will delete all widgets on the production db and make sure the connection to the current Rails env's db is re-established after that.

If you just want to make it available in your migrations insead extend the ActiveRecord::Migration class.

In rails 3.2, adding a connection method to your migration does NOT work. So all of the answers like

def connection
 @connection ||= ActiveRecord::Base.establish_connection
end

simply won't work (can't down, doesn't work with change, connection lost, etc.) The reason for this is that the ActiveRecord::Migration and Migrator class have connections hard-coded to ActiveRecord::Base all over the place.

Fortunately this post pointed me to this ticket which has a good solution, namely overriding the actual rake task.

I ended up using a slightly different rake task so that I could be specific about the migrations I run on the different database (we were trying to support multiple db versions):

Here's my lib/task/database.rake

# Augment the main migration to migrate your engine, too.
task 'db:migrate', 'nine_four:db:migrate'

namespace :nine_four do
    namespace :db do
        desc 'Migrates the 9.4 database'
        task :migrate => :environment do
            with_engine_connection do
                ActiveRecord::Migrator.migrate("#{File.dirname(__FILE__)}/../../nine_four/migrate", ENV['VERSION'].try(:to_i))
            end
        end
    end
end

# Hack to temporarily connect AR::Base to your engine.
def with_engine_connection
    original = ActiveRecord::Base.remove_connection
    ActiveRecord::Base.establish_connection("#{ Rails.env }_nine_four")
    yield
ensure
    ActiveRecord::Base.establish_connection(original)
end

This allows us to put migrations specific to one database in their own subdirectory (nine_four/migrations instead of db/migrations). It also gives each database total isolation in terms of their schema and migration versions. The only downside is having two rake tasks to run (db:migrate and nine_four:db:migrate).

In addition to running a migration in a different environment, I also want the schemas in separate files. You can do this from the command line:

RAILS_ENV=quiz_development SCHEMA=db/schema_quiz_development.rb rake db:migrate

But I like the custom rake task approach so I can type this instead:

rake db:with[quiz_development, db:migrate]

Here's the rake task:

namespace :db do
  desc "Run :task against :database"
  task :with, [:database,:task] => [:environment] do |t, args|
    puts "Applying #{args.task} to #{args.database}"
    ENV['SCHEMA'] ||= "#{Rails.root}/db/schema_#{args.database}.rb"
    begin
      oldRailsEnv = Rails.env
      Rails.env = args.database
      ActiveRecord::Base.establish_connection(args.database)
      Rake::Task[args.task].invoke
    ensure
      Rails.env = oldRailsEnv
    end
  end
end

I've found a great clean way to do this:

class CreateScores < ActiveRecord::Migration

  class ScoresDB < ActiveRecord::Base
    establish_connection("scores_#{Rails.env}")
  end

  def connection
    ScoresDB.connection
  end

  def up
    create_table :scores do |t|
      t.text :account_id
      t.text :offer
    end
  end

  def down
    drop_table :scores
  end
end

class Article < ActiveRecord::Base

    ActiveRecord::Base.establish_connection(
      :adapter  => "mysql2",
      :host     => "localhost",
      :username => "root",
      :database => "test"
    )
end

And:

class Artic < Aritcle
    self.table_name = 'test'

    def self.get_test_name()
        query = "select name from testing"
        tst = connection.select_all(query) #select_all is important!
        tst[0].fetch('name')
    end
end

You can call Artic.get_test_name in order to execute.

You could use this version, which also supports rake db:rollback:

class ChangeQuiz < ActiveRecord::Migration
  def connection
    ActiveRecord::Base.establish_connection("quiz_#{Rails.env}").connection
  end
  
  def reset_connection
    ActiveRecord::Base.establish_connection(Rails.env)
  end
  
  def up
    # make changes
    
    reset_connection
  end
  
  def self.down
    # reverse changes
    
    reset_connection
  end
end

Have you tried using quiz_development as a RAILS_ENV (instead of trying to get it to use "quiz_#{RAILS_ENV}")?

RAILS_ENV=quiz_development rake db:migrate

You can also move all your quiz_ related migrations into a separate subfolder in the db/ directory and then add rake tasks mirroring the regular migration functionality but that looks for the migrations in that subdirectory. Not super-elegant perhaps but it works. You can copy and paste the rake tasks already in rails and just modify them a bit.

Based on @TheDeadSerious's answer:

module ActiveRecord::ConnectionSwitch  
  def on_connection(connection_spec_name)
    raise ArgumentError, "No connection specification name specified. It should be a valid spec from database.yml" unless connection_spec_name
    ActiveRecord::Base.establish_connection(connection_spec_name)
    yield
  ensure
    ActiveRecord::Base.establish_connection(Rails.env)
  end
end

ActiveRecord.send :extend, ActiveRecord::ConnectionSwitch

Usage:

ActiveRecord.on_connection "sdmstore_#{Rails.env}" do
  Widget.delete_all
end

if you want to display the wordpress post to your rails website and you don't want to use mult-magic connection gem. you can use the below code in order to get the data from wordpress blog.

 class Article < ActiveRecord::Base

    ActiveRecord::Base.establish_connection(
     :adapter  => "mysql2",
     :host     => "localhost",
     :username => "root",
     :database => "blog"
    )

    self.table_name = 'wp_posts'

    def self.get_post_data()
        query = "select name from testing"
        tst = connection.select_all(query)
        tst[0].fetch('name')
    end
end

I got this working by creating separate connector classes for different databases and using them in the migrations.

class AddExampleToTest < ActiveRecord::Migration
  def connection
    @connection = OtherDatabaseConnector.establish_connection("sdmstore_#{Rails.env}").connection
  end
  def up
    add_column :test, :example, :boolean, :default => true

    @connection = MainDatabaseConnector.establish_connection("#{Rails.env}").connection
  end
  def down
    remove_column :test, :example

    @connection = MainDatabaseConnector.establish_connection("#{Rails.env}").connection
  end
end

We can define these connector classes in initializers.

class MainDatabaseConnector < ActiveRecord::Base
end
class OtherDatabaseConnector < ActiveRecord::Base
end

ActiveRecord::Base keeps a connection pool that is a hash indexed by the class. Read more here. So using separate classes for separate connections protects us from the closed connection error.

Also, using up and down instead of change allows us to rollback the migration without any issue. Still haven't figured out the reason for this.

For example, I have a study_history model:

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

1. 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

2. 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

3. 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]]
=> #<Lesson id: 1, title: "people", cn: nil, description: nil, version: nil, course_id: 1, created_at: "2020-03-01 23:57
:02", updated_at: "2020-05-08 09:57:40", level: "aa", ready: false, pictureurl: "/pictures/kiss^boy and girl^boy^girl.jp
g">