Multiple Databases with Active Record

byGink▻ Wed, 10 Aug 2022

In previous versions of Rails, database connections were managed globally and shared across the entire application. This meant that if you needed to connect to multiple databases, you had to switch the active connection manually by calling establish_connection on each model that needed to use a different database.

Since Rails 6, connections are managed on a per-model basis, which allows you to connect to multiple databases more easily and in a more organized way. However, this change in the way connections are handled can cause compatibility issues with older applications that were built with the old-style connection handling.

1. `legacy_connection_handling` configuration

The legacy_connection_handling option provides a way to handle these compatibility issues by allowing you to choose between the old-style and the new-style connection handling. If you set this option to true, then the old-style connection handling will be used, which is compatible with older applications. If you set it to false, then the new-style connection handling will be used, which provides better support for connecting to multiple databases.

config/application.rb

module MyApp
  class Application < Rails::Application
    # ... other configurations ...

    config.active_record.legacy_connection_handling = false
  end
end

Then setup your config/database.yml

production:
  primary_db:
    database: my_primary_database
    host: "writing_database_url"
    adapter: mysql2

  replica_db:
    database: my_replica_database
    host: "read_replica_url"
    adapter: mysql2
    replica: true

If a primary configuration is provided, it will be used as the "default" configuration. If there is no configuration named "primary", Rails will use the first configuration as default for each environment.

When using a replica database, you need to add a replica: true entry to the replica in the database.yml. This is because Rails otherwise has no way of knowing which one is a replica and which one is the writer. Rails will not run certain tasks, such as migrations, against replicas.

2. Control to switch the database

Come along with legacy_connection_handling option in Rails 6.0, we have a new method in ActiveRecord, which is connects_to to handle connection.

class User < ApplicationRecord
  connects_to database: { writing: :primary_db, reading: :replica_db }

  # all read operations in this model will use the replica_db connection
  def self.all_users
    all
  end

  # this write operation will use the primary_db connection
  def update_email(email)
    update(email: email)
  end
end

The :primary_db or :replica_db symbol refers to a database configuration that has been defined in the config/database.yml file.

connects_to method takes a hash as its argument, where you can specify the database connections for reading (:reading) and writing (:writing) operations.

3. Another update since Rails 6.1

The connected_to method is a new feature in Rails 6.1 that allows you to specify a separate database connection for a specific block of code. This is useful in cases where you want to run a specific set of operations against a different database than the default one for a particular model.

ActiveRecord::Base.connected_to(role: :writing) do
  User.first # Lookup record from primary db 
end

ActiveRecord::Base.connected_to(role: :reading) do
  User.first # Lookup record from replica db
end

3.1 Some key differences between `connects_to` and `connected_to`

Model-level vs Block-level: The first method is used to specify a separate database connection for a particular model, and all operations on that model will use the specified connection by default. While connected_to is used to specify a separate database connection for a specific block of code.
Persistence: The connection specified with the connects_to method will persist for the lifetime of the model, and all operations on that model will use the specified connection until it's changed. The connection specified with the connected_to method, on the other hand, only applies to the block of code where it is used and will automatically revert back to the default connection after the block is executed.

In summary, depends on the scenarios you would want to choose which way to control connection that's best suited. With all of these methods and configuration, Rails has been stable and well-supported for multiple databases.

Hope this article will help you understand how to work with your database replication in Rails. Make sure to test your application carefully to capture any error ActiveRecord::ReadOnlyError before deploying 😘