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