ActiveRecord talk to two databases?

Question

We've recently revamped a project, and are looking to bring all our old data into the new system. The problem is that the schema is marginally different, so a straight SQL import isn't possible. Due to some denormalization and database changes, we'll need to do some massaging of the data before it's ready for import. I was hoping for something like this:

OldUser.all.each do |ou|
  NewUser.create({
    :first_name   => ou.first_name
    :last_name    => ou.last_name
    :login        => ou.login
    :company_name => ou.company.name
  })
end

In the example above, OldUser is reading from the old database, and NewUser is working on the new database. I need both sets of models (new and old) to retain their associations to properly denormalize some of that data.

Is there any project/library that can help me do this?

Answer 1

You should simply specify your connection options either in the model or in database.yml. Let's go the 1st route initially:

# This is the new users table - connects to development/test/production
# DB from database.yml
class User < ActiveRecord::Base
end

class OldUser < ActiveRecord::Base
  establish_connection :adapter  => "postgresql",
                       :database => "legacy_users",
                       :username => "whatever",
                       :password => "something"
  set_table_name "u_users" # Whatever you require
  belongs_to :company, :class_name => "OldCompany", :foreign_key => "fk_company_id"
end

class OldCompany < ActiveRecord::Base
  establish_connection :adapter  => "postgresql",
                       :database => "legacy_users",
                       :username => "whatever",
                       :password => "something"
  set_table_name "u_company" # Whatever you require
  has_many :users, :class_name => "OldUser", :foreign_key => "fk_company_id"
end

From regular code, you use the models like you're used to:

OldUser.find_each do |ouser|
  User.create!(:username => ouser.username, :company_name => ouser.company.name)
end

ActiveRecord will handle all the details for you.

Now, if you're like me, you don't like putting such level of details in your models - username, passwords, etc. Simple - move that config to database.yml and connect using the correct establish_connection syntax:

# database.yml
development:
  adapter: postgresql
  # go on as usual, for all 3 envs

legacy_users_development:
  adapter:  postgresql
  database: legacy_users
  username: whatever
  password: something

Note the naming convention - legacy_users_#{Rails.env} is what I'm aiming for here, and here's how to do it:

class OldUser < ActiveRecord::Base
  establish_connection "legacy_users_#{Rails.env}"
  set_table_name "u_users" # Whatever you require
  belongs_to :company, :class_name => "OldCompany", :foreign_key => "fk_company_id"
end

Bingo, everything else will work just fine.