Multiple database connections: schema_migrations is looked up in the wrong database

I am trying to use a secondary database connection for some of my migrations in the following way:

# app/models/staging/migration.rb
class Staging::Migration < ActiveRecord::Migration
    def self.connection
        ActiveRecord::Base.establish_connection(:staging_db).connection
    end
end

# db/migrate/<timestamp>_create_foo.rb
class CreateFoo < Staging::Migration
    ....
end

In my database.yml the staging_db connection is configured.

When I run rake db:migrate, the table foo is created c开发者_StackOverfloworrectly in the staging_db schema, and the table schema_migrations is created in the RAILS_ENV=development connection. However db:migrate reports the following error (which fails subsequent migrations):

Table 'staging_db.schema_migrations' doesn't exist

Is there a way to tell Staging::Migration to look for the schema_migrations table in the current RAILS_ENV connection?

BTW, I am aware of the fact that staging_db is then not RAILS_ENV-aware. This is fine for me since every server has its environment configured through a separate database.yml which is not in my repo.

You should try do this before your first migration in the staging_db:

ActiveRecord::Base.connection.initialize_schema_migrations_table

This will create a schema migration table in the staging db. If this is not what you want you will have to manipulate some other things. The schema_migrations_table_name determines which table contains the migration versions:

def schema_migrations_table_name
  Base.table_name_prefix + 'schema_migrations' + Base.table_name_suffix
end

So if you have a table_name_prefix defined it will cause the schema_migration_table to look in the staging db.

I already did this but outside of rails, it should not be very different in rails, here is how I do it:

The first thing is to connect your database before your migrations are executed, in rails the best place may be in an initializer:

MyModel.establish_connection({
  :adapter => "mysql2",
  :database => "mydb",
  :username => "root",
  :encoding => 'utf8'
})

The hash will be usually loaded from an yml file but this is the result you want in the end.
MyModel can be an abstract class if you have multiple models in this database.

Next in your migration when you want to migrate this database you just have to do this:

class DoDomething < ActiveRecord::Migration
  def self.connection
    MyModel.connection
  end

  def self.up
    add_column [...]
  end
end

One thing to note when doing things this way is that there will be only one schema_migrations table and it will be in the "main" database.