DO NOT READ THIS FILE ON GITHUB, GUIDES ARE PUBLISHED ON https://guides.rubyonrails.org.

Multiple Databases with Active Record

This guide covers using multiple databases with your Rails application.

After reading this guide you will know:

• How to configure multiple databases and connect models to them.
• How to run database tasks for multiple databases.
• How to use read replicas and how automatic connection switching works.
• How to use horizontal sharding.

Overview

As your application grows, a single database may no longer be sufficient for all of its data and traffic. High read volumes, large datasets, or reporting workloads can all place pressure on a single database server. The need to isolate different parts of an application is another reason to use multiple databases. Rails supports connecting to multiple databases so reads, writes, or specific models can use separate database connections when needed.

General Terminology

Choosing a Setup

The right setup depends on what you are trying to accomplish:

Caveats and Operational Considerations

Multiple database applications have a few important limitations and production considerations.

Connection Pool Size

Each database configuration can have its own connection pool. Roles, replicas, and shards can increase the total number of database connections your application may open. Make sure the pool sizes in config/database.yml match your web server and job worker concurrency, and that your database servers can handle the total number of connections.

No Replica Load Balancing

Rails does not automatically load balance reads across multiple replicas. If your application needs replica load balancing, handle it in your database infrastructure or with application-specific connection logic.

No Transactions Across Databases

Rails does not provide distributed transactions across database clusters. A transaction is scoped to one database connection. If a workflow writes to multiple databases, design it so it can tolerate partial failure, retry safely, or reconcile data later.

No Foreign Keys Across Databases

Database-level foreign keys generally cannot span separate database clusters. Keep data that requires strict database-level integrity in the same database when possible, or enforce cross-database consistency in your application.

Setting Up Multiple Databases

Setting up multiple databases starts with config/database.yml, where each entry names a database configuration that Rails can connect to. Rails uses those configuration names later when you connect models, run database tasks, and switch between writers, replicas, or shards.

Rails handles the application-side plumbing for these configurations: connection definitions, abstract connection classes, role and shard switching, and database tasks.

NOTE: Rails does not provision database servers, create database users, manage replication, balance traffic across replicas, or provide distributed transactions across database clusters. Those responsibilities remain with your database infrastructure and application architecture.

Rails supports two layouts for config/database.yml. If an environment has one database configuration, the database settings can live directly under the environment:

production:
  database: my_primary_database
  adapter: mysql2

This is called a two-tier configuration because the YAML has two levels: the environment, and then the database settings.

When an environment has multiple database configurations, add another level under the environment. This third level names each database configuration:

production:
  primary: # database configuration name
    database: my_primary_database
  animals: # database configuration name
    database: my_animals_database

This is called a three-tier configuration because the YAML has three levels: the environment, the database configuration name, and then the database settings. The database configuration names, such as primary and animals, are used later when you connect models, run database tasks, and switch between writers and replicas.

In a default Rails application, development and test often use a two-tier configuration, while production may already use a three-tier configuration for primary, cache, queue, and cable.

The examples in this guide start with one primary production database. The application then adds a second database named animals. Models such as User will continue to use the primary database, while models such as Dog will use the animals database. Later sections build on this example by adding replicas and showing how Rails switches between them.

Database Configurations

Consider the following database configuration with a single primary database:

production:
  primary:
    database: my_primary_database
    adapter: mysql2
    username: root
    password: <%= ENV['ROOT_PASSWORD'] %>

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

The default configuration uses the default Rails filenames for schema and schema cache files. For example, a primary configuration will use db/schema.rb for the schema file and db/schema_cache.yml for the schema cache file. Other entries use names based on the database configuration name, such as db/[CONFIGURATION_NAMESPACE]_schema.rb and db/[CONFIGURATION_NAMESPACE]_schema_cache.yml.

Let's now add an animals database for the production environment:

production:
  primary:
    database: my_primary_database
    username: root
    password: <%= ENV['ROOT_PASSWORD'] %>
    adapter: mysql2
  animals:
    database: my_animals_database
    username: animals_root
    password: <%= ENV['ANIMALS_ROOT_PASSWORD'] %>
    adapter: mysql2
    migrations_paths: db/animals_migrate

The updated configuration defines two writer databases, primary and animals. A writer database is the database Rails uses for inserts, updates, and deletes.

Database Connection URLs

A database connection URL is a single string that contains the adapter, username, password, host, and database name for a database connection. Connection URLs can also be configured using environment variables.

The variable name is formed by concatenating the database configuration name with _DATABASE_URL. For example, because the configuration name is animals, Rails looks for an environment variable named ANIMALS_DATABASE_URL:

ANIMALS_DATABASE_URL="mysql2://animals_root:password@localhost/my_animals_database"

Rails matches the environment variable with the corresponding database.yml entry. The URL supplies the connection details, while database.yml can keep Rails-specific options such as migrations_paths.

For example, database.yml does not need to include the username, password, host, or database name if those details are provided by the connection URL:

production:
  animals:
    adapter: mysql2
    migrations_paths: db/animals_migrate

When Rails loads the animals configuration, it uses ANIMALS_DATABASE_URL for the connection details and keeps migrations_paths from database.yml.

SeeConfiguring a Database for details about how the configurations are merged.

Migration Paths and Schema Dumps

For new writer databases, you need to set the migrations_paths key to the directory where you will store migrations for that database. We'll look more at migrations_paths later inGenerating Migrations.

production:
  animals:
    migrations_paths: db/animals_migrate

You can also configure the schema dump file by setting schema_dump to a custom schema file name. By default, the primary configuration uses db/schema.rb, and other configurations use db/[CONFIGURATION_NAMESPACE]_schema.rb. For example:

production:
  animals:
    database: my_animals_database
    adapter: mysql2
    schema_dump: db/animals_schema.rb

If you want to skip dumping the schema for a database entirely, set schema_dump: false.

Configuring a Replica

Read replicas are database servers that keep a copy of a writer database's data and structure. The database system, not Rails, handles replication from the writer to the replica. Replication can be delayed, so applications need to account for the possibility that a replica has not received the latest write yet. Refer to your database documentation for replication setup and behavior.

To add read replicas for the primary and animals databases, add a configuration for each replica to config/database.yml:

production:
  primary:
    database: my_primary_database
    username: root
    password: <%= ENV['ROOT_PASSWORD'] %>
    adapter: mysql2
  primary_replica:
    database: my_primary_database
    username: root_readonly
    password: <%= ENV['ROOT_READONLY_PASSWORD'] %>
    adapter: mysql2
    replica: true
  animals:
    database: my_animals_database
    username: animals_root
    password: <%= ENV['ANIMALS_ROOT_PASSWORD'] %>
    adapter: mysql2
    migrations_paths: db/animals_migrate
  animals_replica:
    database: my_animals_database
    username: animals_readonly
    password: <%= ENV['ANIMALS_READONLY_PASSWORD'] %>
    adapter: mysql2
    replica: true

Here, primary_replica copies data from primary, and animals_replica copies data from animals. Rails can then send read queries to a replica when appropriate.

When configuring a replica, there are a few important settings.

First, the database name for a writer and its replica should be the same because they contain the same data. This means primary and primary_replica should have the same name, as should animals and animals_replica.

production:
  primary:
    database: my_primary_database
  primary_replica:
    database: my_primary_database

Second, use a different username for each replica, and set the replica user's database permissions to only read and not write.

production:
  primary:
    database: my_primary_database
    username: root
  primary_replica:
    database: my_primary_database
    username: root_readonly

When using a replica database, you need to add replica: true to the replica configuration in config/database.yml. Rails otherwise has no way of knowing which configuration is the replica and which one is the writer. Rails will not run certain tasks, such as migrations, against configurations marked as replicas. If you forget replica: true, Rails will treat the replica as another writer database and may run database tasks against it.

production:
  primary_replica:
    database: my_primary_database
    replica: true

Connecting Models

Adding a database configuration tells Active Record how to connect to each database, but an Active Record model also needs to know which connections to use. To do that, define the connection on the primary abstract class and have the models inherit from it.

For example, an application with primary and primary_replica databases might define the writing and reading roles for the connection:

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

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

Inconnects_to</a>, writing and reading are role names. The writing role points to the writer database, and the reading role points to the read replica:

connects_to database: { writing: :primary, reading: :primary_replica }

config.active_record.writing_role = :default
config.active_record.reading_role = :readonly

connects_to database: { default: :primary, readonly: :primary_replica }

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

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

Models that inherit from ApplicationRecord will use the primary database for writes and the primary_replica database for reads when Rails is switched to the reading role.

If you're using a custom name for the base class of your database-backed models, set primary_abstract_class on that class. This marks it as the primary abstract class that shares a connection with ::ActiveRecord::Base:

class PrimaryApplicationRecord < ActiveRecord::Base
  primary_abstract_class

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

In that case, classes that connect to primary/primary_replica can inherit from your primary abstract class like standard Rails applications do with ApplicationRecord:

class Person < PrimaryApplicationRecord
end

Connecting Models to Another Database

If multiple models should use another database, define another abstract class and have the models for that database inherit from it.

Each abstract class owns the connection for one database, or for one writer and read replica pair. Concrete models then inherit from the abstract class for the database where their table lives.

For example, an application with primary and animals databases might have one abstract class for each database:

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

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

class AnimalsRecord < ApplicationRecord
  self.abstract_class = true

  connects_to database: { writing: :animals, reading: :animals_replica }
end

Models stored in the animals database should inherit from that common abstract class:

class Dog < AnimalsRecord
  # Talks automatically to the animals database.
end

Dog now uses the animals database for writes and the animals_replica database for reads when Rails is switched to the reading role.

WARNING: Define connects_to on a single abstract class for each database and have the models stored in that database inherit from it. connects_to can only be called on ::ActiveRecord::Base or abstract classes; calling it on a concrete model raises NotImplementedError. Defining the same database connection on multiple abstract classes creates separate connection pools, because Rails uses the class name for the connection specification name. Models that inherit from a common abstract class share its connection pools instead.

Generating Migrations

Each managed writer database needs a migration path. Keeping migrations separate lets Rails migrate one database without running migrations intended for another database. Replicas do not need migration paths because Rails does not run migrations against configurations marked with replica: true.

For example, a migration that creates a table in the primary database belongs in db/migrate, while a migration that creates a table in the animals database belongs in db/animals_migrate:

db/migrate/20240501000000_create_users.rb
db/animals_migrate/20240501000000_create_dogs.rb

Migrations for multiple databases should live in their own folders prefixed with the name of the database key in the configuration. You also need to set migrations_paths in the database configuration to tell Rails where to find those migrations.

The primary database uses the default db/migrate path, so you usually only need to set migrations_paths for additional writer databases. In the example configuration, the animals database uses db/animals_migrate:

production:
  animals:
    migrations_paths: db/animals_migrate

Rails generators take a --database option so that generated migration files are placed in the correct directory:

$ bin/rails generate migration CreateDogs name:string --database animals

This creates the migration in db/animals_migrate. Without --database animals, the migration would be generated in the default migration path.

Generating Models and Scaffolds

The model and scaffold generators also take the --database option:

$ bin/rails generate scaffold Dog name:string --database animals

When you pass --database, Rails generates an abstract class for that database unless one already exists. The class name is the camelized database name followed by Record. In this example, the database is animals, so Rails generates AnimalsRecord:

class AnimalsRecord < ApplicationRecord
  self.abstract_class = true

  connects_to database: { writing: :animals }
end

The generated model will automatically inherit from AnimalsRecord.

class Dog < AnimalsRecord
end

Since Rails doesn't know which database is the replica for your writer, add the replica to the abstract class after it's generated:

connects_to database: { writing: :animals, reading: :animals_replica }

Rails will only generate AnimalsRecord once. It will not be overwritten by new scaffolds or deleted if the scaffold is deleted, so your changes to the abstract class are preserved.

If you already have an abstract class and its name differs from AnimalsRecord, pass the --parent option to tell Rails which class the model should inherit from:

$ bin/rails generate scaffold Dog name:string --database animals --parent {Animals::Record}

This will skip generating AnimalsRecord since you've indicated to Rails that you want to use a different parent class.

Database Tasks

Rails creates database tasks for each managed database configuration. Running a task without a database name applies it to all managed databases, for example:

$ bin/rails db:create
$ bin/rails db:migrate
$ bin/rails db:schema:dump

To run a task for one database, append the database configuration name:

$ bin/rails db:create:animals
$ bin/rails db:migrate:animals
$ bin/rails db:schema:dump:animals

The same pattern applies to the primary database:

$ bin/rails db:migrate:primary

Rails does not create database tasks for replicas. For example, primary_replica and animals_replica do not get migration tasks because they are marked with replica: true.

Running bin/rails db:create will create both the primary and animals databases. Rails does not create database users; create writer and read-only replica users in your database system.

Other Rails commands can also target a database configuration by name. For example, bin/rails dbconsole --database=animals opens a database console for the animals database, and bin/rails query "Dog.count" --database animals runs a read-only query against that database configuration.

For the full list of Rails database commands, see theCommand Line guide.

External Databases

Some applications connect to databases that Rails should use but not manage. For example, you might connect to a legacy database, a reporting database, or a database owned by another application.

In those cases, Rails needs the database configuration so models can connect to the database, but Rails should not run database management tasks such as creating, dropping, migrating, seeding, or dumping the schema for that database. Set database_tasks: false on the database configuration to opt out of those tasks. By default, database_tasks is true.

production:
  primary:
    database: my_database
    adapter: mysql2
  reporting:
    database: my_reporting_database
    adapter: mysql2
    database_tasks: false

In this example, Rails will still connect to the reporting database when a model uses that configuration, but commands such as bin/rails db:create, bin/rails db:migrate, and bin/rails db:schema:dump will skip it.

Role Switching

Role switching lets Rails choose between the writing and reading roles. These roles usually point to a writer database and its read replica.

Automatic Role Switching

Automatic role switching lets Rails choose between the writer and replica for each request. This is sometimes called automatic connection switching.

You may want automatic role switching when your application uses replicas to reduce read traffic on the writer, but still needs users to see their own changes immediately after they write. It is implemented by theDatabaseSelector</a> middleware, so it applies to web requests.

$ bin/rails g active_record:multi_db

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

The delay controls how long Rails keeps sending a user's reads to the writer after that user writes. By default, the delay is set to 2 seconds. Change this based on your database infrastructure and expected replication lag.

Rails does not guarantee that other users will immediately see a recent write. For users who did not perform the write, GET and HEAD requests can still go to the replica and may be affected by replication lag.

Automatic role switching is intentionally simple. It does not inspect actual replication lag, load balance across multiple replicas, or decide per query which database should be used. The default behavior is designed to be a small, customizable starting point for applications.

You can change how Rails tracks the last write. By default, Rails stores the last write timestamp in the session. If you want to use a cookie instead, you can write your own resolver context:

class MyCookieResolver < ActiveRecord::Middleware::DatabaseSelector::Resolver
  def self.call(request)
    new(request.cookies)
  end

  def initialize(cookies)
    @cookies = cookies
  end

  attr_reader :cookies

  def last_write_timestamp
    self.class.convert_timestamp_to_time(cookies[:last_write])
  end

  def update_last_write_timestamp
    cookies[:last_write] = self.class.convert_time_to_timestamp(Time.now)
  end

  def save(response)
  end
end

And then pass it to the middleware:

config.active_record.database_selector = { delay: 2.seconds }
config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
config.active_record.database_resolver_context = MyCookieResolver

Manual Role Switching

Manual role switching lets you choose the database role for a block of code. Use it when automatic role switching would choose the wrong role for a specific case, or when automatic role switching does not apply, such as in scripts, console sessions, and background jobs.

For example, automatic role switching sends POST requests to the writer, but you may have a POST request that only reads data and should use a replica.

For example, to read from the writer even when automatic role switching would use the read replica, wrap the code in connected_to:

ActiveRecord::Base.connected_to(role: :writing) do
  # All code in this block uses the writing role.
  Person.last
end

The role passed to connected_to must match a role defined by connects_to. In the examples above, the reading role points to the replica configuration:

connects_to database: { writing: :primary, reading: :primary_replica }

If you pass an unknown role, Rails raises an error:

ActiveRecord::Base.connected_to(role: :nonexistent) do
  # ...
end

The error will look like:

ActiveRecord::ConnectionNotEstablished (No connection pool for 'ActiveRecord::Base' found for the 'nonexistent' role.)

If you want Active Record to prevent write queries while using a role, pass prevent_writes: true. This prevents queries that look like writes from being sent to the database:

ActiveRecord::Base.connected_to(role: :reading, prevent_writes: true) do
  # The following will raise an error:
  Person.create
end

This Rails-level check is not a substitute for database permissions. You should also configure replica users as read-only in your database.

Horizontal Sharding

Horizontal sharding splits records across multiple databases that share the same schema. Each database is called a shard. For example, one shard might store customers 1 through 100, while another shard stores customers 101 through 200.

Sharding is different from replication. A replica copies the same data from its writer. A shard stores a different subset of records. Applications commonly use sharding when one database has too much data or traffic, or when tenant or account data should be distributed across database servers.

Rails uses the same connects_to and connected_to APIs for sharding.

Configuring Shards

Shards are declared as database configurations in config/database.yml. Each shard needs a writer configuration, and can also have a replica configuration:

production:
  primary:
    database: my_primary_database
    adapter: mysql2
  primary_replica:
    database: my_primary_database
    adapter: mysql2
    replica: true
  primary_shard_one:
    database: my_primary_shard_one
    adapter: mysql2
    migrations_paths: db/migrate_shards
  primary_shard_one_replica:
    database: my_primary_shard_one
    adapter: mysql2
    replica: true
  primary_shard_two:
    database: my_primary_shard_two
    adapter: mysql2
    migrations_paths: db/migrate_shards
  primary_shard_two_replica:
    database: my_primary_shard_two
    adapter: mysql2
    replica: true

In this example, primary_shard_one and primary_shard_two are database configuration names. Later, the model connection maps those configurations to Rails shard names, such as shard_one and shard_two.

Each shard can also have its own replica. primary_shard_one stores one subset of records, and primary_shard_one_replica is a read replica of that same shard. primary_shard_two stores a different subset of records, and primary_shard_two_replica is a read replica of that second shard. Shards split records across databases; replicas copy one shard's data so reads for that shard can be sent away from its writer.

Connecting Models to Shards

Models are connected to shards with the connects_to API using the shards key:

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

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

class ShardRecord < ApplicationRecord
  self.abstract_class = true

  connects_to shards: {
    shard_one: { writing: :primary_shard_one, reading: :primary_shard_one_replica },
    shard_two: { writing: :primary_shard_two, reading: :primary_shard_two_replica }
  }
end

class Customer < ShardRecord
end

In this example, Customer inherits from ShardRecord, so Customer records can be stored on either shard_one or shard_two. The shard_one name maps to the primary_shard_one writer and primary_shard_one_replica replica. The shard_two name maps to the primary_shard_two writer and primary_shard_two_replica replica.

Generating Migrations for Shards

Shards usually share a schema, so shard migrations should use the same migrations_paths and schema_dump settings across all shards.

When generating a migration or scaffold, pass --database with one of the shard database configuration names. Since both shards use the same migration path, it does not matter which shard configuration you choose.

$ bin/rails g scaffold Customer name:string --database primary_shard_one

This command generates the scaffold and places the migration in the shared db/migrate_shards path.

Shard Switching

Active Record needs to know which shard to use for each operation. You can choose the shard manually with connected_to, or configure middleware to choose the shard for each request.

Manual Shard Switching

Use manual shard switching when you need to run a block of code against a specific shard. This is common in background jobs, data migrations, scripts, or service objects where there is no request middleware to choose the shard.

To switch shards manually, use connected_to with both a role and a shard:

ShardRecord.connected_to(role: :writing, shard: :shard_one) do
  @customer = Customer.create! # Creates a record in shard shard_one
end

ShardRecord.connected_to(role: :writing, shard: :shard_two) do
  Customer.find(@customer.id) # Can't find record, doesn't exist because it was created
                   # in the shard named ":shard_one".
end

The shard name must match one of the shard keys passed to connects_to, such as shard_one or shard_two.

The horizontal sharding API also supports read replicas. To read from a shard's replica, use the reading role with the shard:

ShardRecord.connected_to(role: :reading, shard: :shard_one) do
  Customer.first # Lookup record from read replica of shard one.
end

Automatic Shard Switching

Applications can automatically switch shards per request using theShardSelector</a> middleware. This is useful for tenant-based applications where each request can be mapped to a shard, for example from a subdomain or current account.

$ bin/rails g active_record:multi_db

Rails.application.configure do
  config.active_record.shard_selector = { lock: true }
  config.active_record.shard_resolver = ->(request) { Tenant.find_by!(host: request.host).shard }
end

Applications must provide a resolver because Rails cannot know how your application assigns tenants or accounts to shards. An example resolver that uses a subdomain to determine the shard might look like this:

config.active_record.shard_resolver = ->(request) {
  subdomain = request.subdomain
  tenant = Tenant.find_by_subdomain!(subdomain)
  tenant.shard
}

The behavior of ShardSelector can be altered through some configuration options.

• lock is true by default and prevents the request from switching shards after the resolver chooses one. If lock is false, shard swapping is allowed during the request. For tenant-based sharding, lock should usually be true to prevent application code from accidentally switching between tenants.

• class_name is the name of the abstract connection class to switch. By default, the ShardSelector uses ActiveRecord::Base. If the application has multiple abstract connection classes, set class_name to the sharded database's abstract connection class.

Options may be set in the application configuration. For example, this configuration tells ShardSelector to switch shards using ShardRecord.connected_to:

config.active_record.shard_selector = { lock: true, class_name: "ShardRecord" }

Granular Role and Shard Switching

By default, calling connected_to on ::ActiveRecord::Base switches the role or shard for all abstract connection classes. In a multiple database application, you may only want to switch one database connection while leaving the others unchanged.

Granular role and shard switching lets you call connected_to on a specific abstract class. This is useful when you want AnimalsRecord queries to read from the replica while ApplicationRecord queries continue using the primary writer.

AnimalsRecord.connected_to(role: :reading) do
  Dog.first # Reads from animals_replica.
  Person.first  # Reads from primary.
end

Inside the block, only models that inherit from AnimalsRecord switch to the reading role. Models that inherit from ApplicationRecord keep using their current role.

You can also switch one sharded abstract class to a specific shard:

ShardRecord.connected_to(role: :reading, shard: :shard_one) do
  Customer.first # Reads from primary_shard_one_replica.
  Person.first  # Reads from primary writer.
end

If no connection exists for the requested role and shard, Rails raises ::ActiveRecord::ConnectionNotEstablished.

To switch only the primary database cluster, call connected_to on ApplicationRecord:

ApplicationRecord.connected_to(role: :reading) do
  Person.first # Reads from primary_replica.
  Dog.first # Reads from animals writer.
end

Use ActiveRecord::Base.connected_to when you want to switch all abstract connection classes globally.

Handling Associations Across Databases

Databases cannot perform SQL joins across separate database clusters. If an association would require Rails to join tables that live in different databases, the query cannot be sent as a single SQL join.

For has_many :through and has_one :through associations, you can pass disable_joins: true to tell Rails to load the association with separate queries instead of a join.

In this example, Dog is stored in the animals database, while Human, Treat, Home, and Yard are stored in the primary database:

class Dog < AnimalsRecord
  has_many :humans
  has_many :treats, through: :humans, disable_joins: true

  has_one :home
  has_one :yard, through: :home, disable_joins: true
end

class Human < ApplicationRecord
  belongs_to :dog
  has_many :treats
end

class Treat < ApplicationRecord
  belongs_to :human
end

class Home < ApplicationRecord
  belongs_to :dog
  has_one :yard
end

class Yard < ApplicationRecord
  belongs_to :home
end

Notice that disable_joins: true is added to the through associations in the Dog model. The direct has_many :humans and has_one :home associations do not require joins and can query the associated model's database directly. The disable_joins option is only supported for through associations.

If these models used the same database connection, Rails would normally load the through associations with a SQL join. Since the associated models are backed by different database connections, Rails needs to avoid that join. Without disable_joins: true, calling @dog.treats or @dog.yard would raise an error because databases are unable to handle joins across clusters.

The disable_joins: true option tells Rails to skip the SQL join for that association and load the records with two or more queries instead.

Instead of building one query that joins the tables, Rails queries the intermediate association first, collects the IDs it needs, and then uses those IDs in a second query for the associated records.

In the example below, to get @dog.treats, Rails first loads the associated humans IDs, and then uses those IDs to load treats:

SELECT "humans"."id" FROM "humans" WHERE "humans"."dog_id" = ?  [["dog_id", 1]]
SELECT "treats".* FROM "treats" WHERE "treats"."human_id" IN (?, ?, ?)  [["human_id", 1], ["human_id", 2], ["human_id", 3]]

For @dog.yard, Rails first loads the associated home ID, and then uses that ID to load yards:

SELECT "home"."id" FROM "homes" WHERE "homes"."dog_id" = ? [["dog_id", 1]]
SELECT "yards".* FROM "yards" WHERE "yards"."home_id" = ? [["home_id", 1]]

There are some tradeoffs to be aware of with this option:

1. There may be performance implications because Rails performs two or more queries, depending on the association, rather than one join. If the query for humans returns many IDs, the query for treats may send a large IN list.
2. Since Rails is no longer performing a join, a query with an order or limit is sorted in-memory because an order from one table cannot be applied to another table.
3. This setting must be added to all associations where you want joining to be disabled. Rails can't guess this for you because association loading is lazy. To load treats in @dog.treats, Rails already needs to know what SQL should be generated.

Schema Caching

Schema caching stores a snapshot of database table and column metadata. Rails uses this information for features like attribute type casting, query generation, and dynamically defining model attributes. Without schema caching, Rails may need to query the database for schema information each time a connection is established. By loading this metadata from the cache instead, applications can reduce unnecessary database queries and improve boot and connection performance.

In a multiple database application, each database should use its own schema cache file. By default, the primary database uses db/schema_cache.yml, and other databases use db/[DATABASE_CONFIGURATION_NAME]_schema_cache.yml. For example, the animals database uses db/animals_schema_cache.yml.

You can configure the schema cache path explicitly with schema_cache_path:

production:
  primary:
    database: my_primary_database
    schema_cache_path: db/schema_cache.yml
  animals:
    database: my_animals_database
    schema_cache_path: db/animals_schema_cache.yml

To load schema cache files lazily as database connections are established, set config.active_record.lazily_load_schema_cache = true in your application configuration:

config.active_record.lazily_load_schema_cache = true

You can generate schema cache files with:

$ bin/rails db:schema:cache:dump

For multiple databases, this command dumps a schema cache for each managed database configuration. Each database needs its own cache file because each database can have different tables, columns, and metadata.

You can read more about schema management in theCommand Line guide.