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

Your options are:

• Magic Models

• Connection Ninja

• DB Charmer

• Overriding establish_connection in your model to connect to the desired db

Answer 2

UPDATED with an expanded example showing foreign keys on the tables and a couple examples showing how to identify none standard column names to route in rails properly.

inside your app

models
  |_ legacy
     |_ base.rb
     |_ user.rb
     |_ company.rb
  |_ user.rb

class code

module Legacy
  class Base < ActiveRecord::Base
    self.abstract_class :true
    establish_connection "database here"
  end
end

module Legacy
  class User < Legacy::Base
    :has_many :companies, :class_name => 'Legacy::Company', :foreign_key => 'user_id'
  end
end

module Legacy
  class Company < Legacy::Base
    set_table_name 'companies'
    set_primary_key 'someId'
    belongs_to :user, :class_name => 'Legacy::User', :foreign_key => 'operator'
  end
end

and scope it where you need it

Legacy::User.new
User.new

Legacy::User.first.companies #=> returns array of Legacy::Companies

Answer 3

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.