Connecting to multiple databases in a Rails app

A traditional Rails application uses a single application. We interact with it by writing models that inherit from ActiveRecord::Base which translate connection details (from config/database.yml) via the method establish_connection.

Its a spatial application where everything is centered around 'location' and location information (pincodes etc.,). In the prototype application Location information is stored is stored in 'pinlocations' table. It takes a lot of effort to fill this table with lat, lng and other information and I didn't want to repeat it and the schema didn't change as much for this table. So, I decided to populate my new tables from the data in this table.

There are plugins (connection_ninja, secondbase, octopus) available which provide ways to connect to two (or more) databases from a rails application. But, my case was simple (one table) which I could do neatly and easily without a gem overkill.

I had to define temporary models in the application to work with the old database and employ the 'establish_connection' method of the ActiveRecord::Base. This method accepts a hash as input through which you provide the connection information.

class Pinlocation < ActiveRecord::Base
  establish_connection(
    :adapter  => "postgresql",
    :host     => "localhost",
    :username => "*****",
    :password => "*****",
    :database => "old_database"
  )
end

You can check whether the connection is established in the rails console:

$> location = Pinlocation.first

This loads the first record from the "old_database" connection.

Then a script to read data from the old database and write to new database.

require 'rubygems'
out_file = "db/data/scripts/output.txt"
# I like the output (messages, errors ets.,) to be written to a file
# instead of the console.
open(out_file, 'w') do |f|
  f.puts "Total no. of record to be imported: #{Pinlocation.count}"
  ...
  Pinlocation.all.each do |location_old|
    begin
      location_new = PinLocation.new(
        :pincode => location_old.pincode,
        :name => location_old.name,
        :lat => location_old.lat,
        :lng => location_old.lng
      )
      # Then I map the district, state to this location
      ...
      location_new.save
    rescue ActiveRecord::RecordInvalid => invalid
      puts invalid.record.errors
      f.puts invalid.record.errors
    end
  end
end

And I am set.