Not able to access different databases in rails console in production environment

Question

I have database.yml as,

database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  socket: /var/run/mysqld/mysql.sock

development:
  tp:
    <<: *default
    database: tp
    host: xxx.xxx.xxx.xxx
    username: test
    password: test
    migrations_paths: db/tp_migrate
  mi:
    <<: *default
    database: mi
    host: xxx.xxx.xxx.xxx
    username: test
    password: test
    migrations_paths: db/mi_migrate


production:
  tp:
    <<: *default
    database: tp
    host: xxx.xxx.xxx.xxx
    username: test
    password: test
    migrations_paths: db/tp_migrate
  mi:
    <<: *default
    database: mi
    host: xxx.xxx.xxx.xxx
    username: test
    password: test
    migrations_paths: db/mi_migrate

tp db has a table called servant. mi db has a table called landlord. I have model as,

application_record.rb

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
  connects_to database: { writing: :mi, reading: :mi }
end

tp_base.rb

class TpBase < ActiveRecord::Base
  self.abstract_class = true
  connects_to database: { writing: :tp, reading: :tp }
end

landlord.rb

class Landlord < ApplicationRecord
end

servant.rb

class servant < TpBase
end

When I open the development console, I am able to access both the table i.e. servant and landlord.

development console

raj@Raj:~/Desktop/testing_app(development)$ RAILS_ENV=development rails c
Running via Spring preloader in process 51558
Loading development environment (Rails 6.0.4.1)
2.6.3 :001 > Landlord.first
  Landlord Load (263.3ms)  SELECT `landlord`.* FROM `landlord` ORDER BY `landlord`.`id` ASC LIMIT 1
 => #<Landlord id: 44> 
2.6.3 :002 > Servant.first
  Servant Load (276.7ms)  SELECT `servant`.* FROM `servant` ORDER BY `servant`.`id` ASC LIMIT 1
 => #<servant id: 1> 

When I open the production console then I am only able to access servant table but could not able to access landlord table.

production console

raj@Raj:~/Desktop/testing_app(development)$ RAILS_ENV=production rails c
Loading production environment (Rails 6.0.4.1)
2.6.3 :001 > Landlord.first
  Landlord Load (248.7ms)  SELECT `landlord`.* FROM `landlord` ORDER BY `landlord`.`id` ASC LIMIT 1
Traceback (most recent call last):
        1: from (irb):1
ActiveRecord::StatementInvalid (Mysql2::Error: Table 'tp.landlord' doesn't exist)
2.6.3 :002 > Servant.first
  Servant Load (265.9ms)  SELECT `servant`.* FROM `servant` ORDER BY `servant`.`id` ASC LIMIT 1
 => #<Servant id: 1> 

But when I establish connection manually for mi database in production console then only able to access landlord table

manually connection

ActiveRecord::Base.establish_connection(:mi).connection

raj@Raj:~/Desktop/testing_app(development)$ RAILS_ENV=production rails c
Loading production environment (Rails 6.0.4.1)
2.6.3 :001 > Landlord.first
  Landlord Load (264.9ms)  SELECT `landlord`.* FROM `landlord` ORDER BY `landlord`.`id` ASC LIMIT 1
Traceback (most recent call last):
        1: from (irb):1
ActiveRecord::StatementInvalid (Mysql2::Error: Table 'tp.landlord' doesn't exist)
2.6.3 :002 > Servant.first
  Servant Load (263.6ms)  SELECT `servant`.* FROM `servant` ORDER BY `servant`.`id` ASC LIMIT 1
 => #<Servant id: 0, name: "", phone: "", company: "", active: "\x00", has_license: "\x01", license_class: "", default_truck_id: 8, gps_color: "00FFFF", gps_device_id: nil, rate_per_hour: 0.0, rate_per_km: 0.0, rate_per_drop: 0.0, rate_per_run: 0.0, exclude_accounting: "\x00"> 
2.6.3 :003 > ActiveRecord::Base.establish_connection(:mi).connection
2.6.3 :004 > Landlord.first
  Landlord Load (243.3ms)  SELECT `landlord`.* FROM `landlord` ORDER BY `landlord`.`id` ASC LIMIT 1
 => #<Landlord id: 44>
 2.6.3 :006 > Servant.first
  Servant Load (243.7ms)  SELECT `servant`.* FROM `servant` ORDER BY `servant`.`id` ASC LIMIT 1
 => #<Servant id: 1> 

My question here is, why in production mode console is not loading both the database by default? Am I doing anything wrong or this is the expected behaviour there is nothing wrong here?

Answer 1

Looks like you have mixed up 2 concepts: replication and sharding. When using replication you need to use connects_to database: { writing: :tp, reading: :tp } where writing: is the master and reading: is the read replica. If you don't have a replica than you only have to specify writing:

Looking at your code you want sharding. In this case you should use connects_to shards: { }

For example:

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
  connects_to shards: { 
    default: { writing: :mi }
  }
end


class TpBase < ActiveRecord::Base
  self.abstract_class = true
  connects_to shards: { 
    shard: { writing: :tp }
  }
end

More info: https://edgeguides.rubyonrails.org/active_record_multiple_databases.html#horizontal-sharding

Not sure if this fixes your problem though.