Connecting to two or more databases from a Rails app

January 18, 2011 by Syed Aslam

The current project I am working on is a re-write of an old application, which essentially was build as an prototype. At that time it was put off due to reasons unknown and now the client has come back for the same application with a new set of requirements and changes to the prototype.

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 inforation 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
        :adapter  => "postgresql",
        :host       => "localhost",
        :username => "*****",
        :password => "*****",
        :database => "old_database"

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| 
            location_new =
                :pincode    => location_old.pincode,
                :name       =>,
                :lat            =>,
                :lng            => location_old.lng

            # Then I map the district, state to this location


        rescue ActiveRecord::RecordInvalid => invalid
            puts invalid.record.errors
            f.puts invalid.record.errors

And I am set.

© 2017 | Follow on Twitter