Skip to main content

Rails 7: Configuring Multi-Database Setup

Setting up a Rails application to work with multiple databases requires careful configuration and consideration. In this guide, we’ll walk through the steps to configure your application for multiple databases, using an example scenario involving a primary database and a secondary database for books.

Why Use Multiple Databases?

There are several reasons why you might choose to use multiple databases in your Rails application. Here are a couple of common scenarios:

  • Separating Frequently Accessed Data: If your application has some data that is accessed much more frequently than other data, you can store it in a separate database to improve performance.
  • Scalability: As your application grows, you may reach the limitations of a single database. Using multiple databases can help distribute the load and improve scalability.

1. Update database.yml

Begin by updating the database.yml file to include configurations for both the primary and secondary databases. Here’s an example configuration:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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
  books:
    database: my_books_database
    username: books_admin
    password: <%= ENV['BOOKS_ADMIN_PASSWORD'] %>
    adapter: mysql2
    migrations_paths: db/books_migrate
  books_replica:
    database: my_books_database
    username: books_readonly
    password: <%= ENV['BOOKS_READONLY_PASSWORD'] %>
    adapter: mysql2
    replica: true

Important points:

  • The database name for the primary and replica databases should be the same (e.g., primary and primary_replica).
  • Replica users should have read-only permissions.
  • Define migrations_paths for new writer databases to specify the directory for their migrations.

2. Define Connection Models

Create connection models to interact with each database. Here’s how you can define them:

1
2
3
4
5
6
7
8
9
10
11
12
13

# Primary Database Connection Model
class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

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

# Books Database Connection Model
class BooksRecord < ApplicationRecord
  self.abstract_class = true

  connects_to database: { writing: :books, reading: :books_replica }
end

3. Database Management

Use Rails tasks to manage your databases. For example:

1
2
3

rails db:create:books
rails db:migrate:books
rails db:rollback:books