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

Resilience

Answer 1

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

Tadashi

Thanks, with a little correction: I changed require 'activerecord' to require 'active_record' – Barsac

Answer 2

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)

Eleaseeleatic

This seems close to the real answer, but fail with an error: relation "schema_migrations" does not exist. – Chaos

I used this, using Rails 3.1.4, and it did indeed run the migration. However, it will not do the down migration and (even without attempting down) running rake db:migrate keeps running this same migration, which it cannot because the table already has that column added. – Alburnum

Got rake to not re-run the migration. See my answer below. (Unfortunately #{$!} stackoverflow doesn't support code examples in comments.) – Alburnum

This still works for rails 4.2, however I got a deprecation warning: "Passing a string to ActiveRecord::Base.establish_connection for a configuration lookup is deprecated..." to avoid this issue cast the string to a symbol: ActiveRecord::Base.establish_connection("quiz_#{Rails.env}".to_sym).connection – Tilla

If you use #change method in migration, rollback won't work. I'm using rails-4.1.14.1. Originally in my case #connection method is described as @connection || ActiveRecord::Base.connection. When you rollback migration, @connection get replaced by instance of ActiveRecord::Migration::CommandRecorder, and #connection method will return it. But in your case default connection will be returned, so rollback won't work, because it will try to perform 'up' migration. – Turbosupercharger

Answer 3

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.

Alburnum

Yes, this is absolutely needed to get Bryan Larsen's solution to work. Thanks – Allfired

Hi, I still have problems with this. It is working ok, migrating and adding the migration version to the migration table in the main DB, but then returns with an error: connection is closed... strangely everything works, except for this error. – Bumboat

@Bumboat I exactly have the same problem. Have you found any solution for this? – Christopherchristopherso

@RajaVarma, no sorry. In the end I just decided to live with the error, everything worked fine. Also I stopped working on that project so I dunno if someone else found another solution :/ – Bumboat

@Bumboat Actually I had two migrations with separate establish_connection connections and I already had one establish_connection in my model for it to make use of the different database. Now I removed established_connection from migration and used the one from model, like this Model.connection and it worked fine. I`m not sure if it is the one which corrected the problem, but making redundant connections may be the reason for failure. – Christopherchristopherso

confirm that this works with rails 2.3 if you will exchange def connection with def self.connection as Bryan Larsen noted – Yard

For me it doesn't work. Every time #connection get called @connection is already initialized, so new connection will not be established. Using rails-4.1.14.1 – Turbosupercharger

Change ||= to just =. I think I made an assumption that in a migration, it would only be called once. However, if that was true, why use ||=, so it would be more correct code to use =. I will edit the answer. – Alburnum

This solution worked for me, working with three databases. Heads up: converting the parameter of establish_connection to a symbol might be needed https://mcmap.net/q/267039/-activerecord-adapternotspecified-database-configuration-does-not-specify-adapter – Remodel

Answer 4

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

Finer

Answer 5

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.

Peppel

Answer 6

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

Chobot

When we have config.active_record.schema_format = :sql in application.rb, running a rake store:db:migrate overwrites the current db/structure.sql instead of updating db_store/structure.sql. Any ideas why? – Vassalize

Regarding the comment above, changing ENV['SCHEMA'] to ENV['DB_STRUCTURE'] got it working. More details here - github.com/rails/docrails/blob/master/activerecord/lib/… – Vassalize

@rafael your Gist is no longer available. Can you repost it or extend your answer with complete details on how you did this? – Trace

Gist is no longer available, depending on an external resource reduces the quality of the answer, specially over time when these disappear – Alphonsoalphonsus

Wayback Machine to the rescue: web.archive.org/web/20140803122810/https://gist.github.com/… – Briarroot

Answer 7

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

Thomson

Answer 8

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

Diverge

We did something similar, but with a ConnectionMigration superclass with a #with_connection(connection) method, so we could use the functionality in different migrations with different connections without duplication. Also, the override for #connection is not needed (see api.rubyonrails.org/classes/ActiveRecord/…). – Cotquean

NOTE: this doesn't work with the change method; you have to use up and down. – Paten

if you want to migrate separately, see https://mcmap.net/q/266425/-using-rails-migration-on-different-database-than-standard-quot-production-quot-or-quot-development-quot – Paten

Answer 9

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.

Mistrot

Answer 10

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

Rhone

Separating the schema.rb/structure.sql and the migrations is critical and all the other responses seem to miss this. This SO post takes this a step further and adds other helpful rake tasks. – Trace

Answer 11

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

Matrass

Answer 12

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

Bullpup

Answer 13

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.

Enthetic

Answer 14

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

Maynor

Answer 15

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

Huldahuldah

Answer 16

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.

Hallah

Answer 17

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

Angloindian

Answer 18

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

Enthetic

Answer 19

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.

Psychosocial

Answer 20

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">

Pinto