SA

Connecting to multiple databases in a Rails app

Photo on Unsplash.

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.