How can I establish a connection to multiple databases, with the same adapter(postgres), using active_record gem?

Question

how are you? I've been having some troubles when I try to establish a connection to two databases in postres. I will try to describe the scenario:

This is my pg.yml:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  host: localhost
  user: xxxxx
  password: xxxxx

development:
  <<: *default
  database: not_legacy

legacy:
  <<: *default
  database: legacy

This is my Legacy::Base class:

#/models/legacy/base.rb

require 'active_record'
require 'erb'

module Legacy
  class Base < ActiveRecord::Base
    self.abstract_class = true

    conf_contents = File.read('config/pg.yml')
    conf_evaluated = ::ERB.new(conf_contents).result
    conf = YAML.load(conf_evaluated)

    ActiveRecord::Base.establish_connection conf['legacy']
  end
end

and this is my NotLegacy::Base class:

#/models/not_legacy/base.rb

require 'active_record'
require 'erb'

module NotLegacy
  class Base < ActiveRecord::Base
    self.abstract_class = true

    conf_contents = File.read('config/pg.yml')
    conf_evaluated = ::ERB.new(conf_contents).result
    conf = YAML.load(conf_evaluated)

    ActiveRecord::Base.establish_connection conf['not_legacy']
  end
end

Also, I have two classes that inherit from the classes previously described. This is Legacy::Company:

#/models/legacy/company.rb    

require_relative 'base'

module Legacy
  class Company < Base
    self.table_name = "company"
  end
end

and NotLegacy::Company:

#/models/not_legacy/company.rb    

require_relative 'base'

module NotLegacy
  class Company < Base
    self.table_name = "company"
  end
end

Now, if I go to the console and do something like(I'm printing conf value):

irb(main):001:0> load 'app/models/legacy/company.rb'
CONFS: {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>5, "host"=>"localhost", "user"=>"xxxxx", "password"=>"xxxxx", "database"=>"legacy"}
=> true
irb(main):002:0> Legacy::Company.count
=> 8
irb(main):003:0> load 'app/models/not_legacy/company.rb'
CONFS: {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>5, "host"=>"localhost", "user"=>"xxxxx", "password"=>"xxxxx", "database"=>"not_legacy"}
=> true
irb(main):004:0> NotLegacy::Company.count
=> 1

At this point everything seems to work correctly since in the legacy database there are 8 records for company and in the not_legacy database there is only 1 record. But if I call Legacy::Company again, I get:

irb(main):005:0> Legacy::Company.count
=> 1
irb(main):005:0> NotLegacy::Company.count
=> 1

It seems that the second connection (made to the not_legacy database) is overwriting the first one (the one made to the legacy database).

If anyone of you can explain me why this is happening and how to fix it I will be immensely grateful

Thanks.

please have a look, I guess you will get your answer here https://stackoverflow.com/a/1832441/3445936 — Rohit Lingayat, Feb 11 '19 at 05:37
@rohit Yes, I guess it is perfect reference, but rails can handle multiple database but switching is little difficult. — ray, Feb 11 '19 at 05:46
I would suggest looking into open source gems like [octopus](https://github.com/thiagopradi/octopus) or [active_record_shards](https://github.com/zendesk/active_record_shards) because of the thread safety issues and complexity involved in writing your own sharding logic. — Vbp, Feb 11 '19 at 05:58

score 0 · Answer 1 · answered Feb 11 '19 at 05:44

First I would like to go with basic about how you are going through code flow,

When you load any class, its code is executed & methods defined, your connection is created through static code written & establish_connection is executed.

Single instant of rails app can have connection to single database

You better to write code to switch through different database to have access for different database connectivity.

Legacy::Company or NotLegacy::Company both models have same table name companies from different databases. But when you connect particular database both model (which got loaded) will point to companies table in current database.

So what is current database mean to both model ? -> The one which is loaded last time by establish_connection.

So this is little tedious but it is working that if you deal with different databases, you have to keep switching from one database to another one.

score 0 · Answer 2 · answered Feb 11 '19 at 12:46

0

on config/database.yml

other_base:
  adapter: postgresql
  encoding: unicode
  pool: 5
  host: localhost
  user: xxxxx
  password: xxxxx

on your model

class YourModel < ActiveRecord::Base
   self.establish_connection :other_base
end

answered Feb 11 '19 at 12:46

thiaguerd

807
1
7
16

How can I establish a connection to multiple databases, with the same adapter(postgres), using active_record gem?

2 Answers2