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

Solution 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

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

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

Solution 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

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

View more solutions

Comments

[kenn]

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

[Marlin Pierce]

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.

[Marlin Pierce]

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

[muirbot]

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

[Tallmaris]

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.

[RajaRaviVarma]

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

[Tallmaris]

@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 :/

[RajaRaviVarma]

@Tallmaris 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.

[paddle42380]

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?

[paddle42380]

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/…

[connec]

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

[s2t2]

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

[s2t2]

if you want to migrate separately, see stackoverflow.com/a/16542724/670433

[morkevicius]

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

[valk]

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

[jwadsack]

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

[jwadsack]

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.

[Leonel Galán]

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

[Fred Willmore]

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

[Aleksander Ryhlitski]

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

[Aleksander Ryhlitski]

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.

[Marlin Pierce]

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.

[Percy]

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

[Jong Bor Lee]

This solution worked for me, working with three databases. Heads up: converting the parameter of establish_connection to a symbol might be needed stackoverflow.com/a/38970270/474693