Connecting to multiple DB Replicas in Rails

IT TOOK ME THREE MONTHS TO FIGURE THIS SHIT OUT.

How to connect Ruby on Rails to multiple databases and not die of frustration.

Why I have to ruby-debugger deep into rails internals to find this out???

A new rabbit hole

I started this journey on June 14th, wondering: How could I connect to a readonly replica? This definitely could help me out dealing with high traffic

So I went directly to the official guide for Ruby on Rails for “Multiple Databases with Active Record” as you should. Right?

“Multiple Databases with Active Record” Steps

Went through all the steps:

1. Prepare config/database.yml

Start by preparting your config/database.yml for the multiple connections

production:
  primary:
    database: my_primary_database
    username: root
    password: <%= ENV['ROOT_PASSWORD'] %>
    adapter: postgresql
  replica:
    database: my_primary_database
    username: root_readonly
    password: <%= ENV['ROOT_READONLY_PASSWORD'] %>
    adapter: postgresql
    replica: true
    database_tasks: false # this is important, the replica doesn't need db:migrate and friends.

2. Configure your ApplicationRecord

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  connects_to database: { writing: :primary, reading: :replica }
end

3. Run the Role Switching Generator

bin/rails g active_record:multi_db

and uncomment:

Rails.application.configure do
  config.active_record.database_selector = { delay: 2.seconds }
  config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
  config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session
end

4. Run your app and… it should work…

Spoiler alert: It didn’t.

[2023-09-27T11:39:47.316+02:00] FATAL: 
ActiveRecord::ConnectionNotEstablished - No connection pool for 'ActiveRecord::Base' found for the 'reading' role.:

So why did it break?

As the error says: ConnectionNotEstablished, now why didn’t? we did everything as the manual suggested… except no manual page at all tells you that rails will establish a database connection just when ActiveRecord is loaded.

These sneaky detail is on the railtie for ActiveRecord in Rails 6 & Rails 7. (They’re pretty much the same code, I’ll stick to Rails 7 from here on)

# [...]
initializer "active_record.initialize_database" do
  ActiveSupport.on_load(:active_record) do
    if ActiveRecord::Base.legacy_connection_handling
      self.connection_handlers = { writing_role => ActiveRecord::Base.default_connection_handler }
    end
    self.configurations = Rails.application.config.database_configuration
    establish_connection # Here, a connection to ActiveRecord::Base.default_role is established
  end
end
# [...]

The default ActiveRecord::Base.default_role is always the writing role. See it yourself in ActiveRecord::Core.

The first connection established by rails automatically will always be the writing connection (aka: connection to the primary database).

How to fix it?

Just warm up the connection using the same technique as rails. In the very same config/initializers/multi_db.rb created by rails, in the end:

ActiveSupport.on_load(:active_record) do
  # When ActiveRecord loads, establish connections to all roles.
  #
  # This way the first request reaching the server will be ready to be served.
  %i[reading writing].each do |role|
    connected_to(role: role) { establish_connection }
  end
end

It’s probably not the prettiest, but now all connections are initialized and you will be able to use them on server boot.

Took 3 month, 3 months… why there’s no documentation on when Active Record does things?
Why do I have to put debuggers deep into railties? why?? -.-