As applications grow in complexity and scale, there often arises a need to manage multiple databases. This can be due to several reasons, such as separating read and write operations, using different databases for different parts of the application, or integrating legacy systems. Ruby on Rails, a popular web framework, provides robust support for multiple databases starting from version 6.0. In this post, we'll explore how to configure and use multiple databases in a Rails application.

Why Use Multiple Databases?

There are several scenarios where using multiple databases can be beneficial:

1. Read/Write Separation: Distributing read and write operations across different databases can help in scaling the application and improving performance.

2. Microservices Architecture: Different services can have their own databases, making the system more modular and easier to manage.
3. Data Segmentation: Different kinds of data (e.g., user data, logs, analytics) can be stored in separate databases to optimize storage and retrieval.
4. Legacy Integration: Integrating with legacy systems that have their own databases.

Setting Up Multiple Databases in Rails

First, update the config/database.yml file to define the connections for each database. Here is an example configuration:
 

development:
primary:
adapter: postgresql
database: blog_development
username: postgres
password: <%= ENV['PASSWORD'] %>
primary_replica:
adapter: postgresql
database: blog_development
username: postgres_readonly
password: <%= ENV['READONLY_PASSWORD'] %>
replica: true
album:
adapter: postgresql
database: album_development
username: postgres
password: <%= ENV['ALBUM_PASSWORD'] %>
migrations_paths: db/album_migrate
album_replica:
adapter: postgresql
database: album_development
username: postgres_readonly
password: <%= ENV['ALBUM_READONLY_PASSWORD'] %>
replica: true

 

Key Points to Remember:

1. The database name for both primary and primary_replica should be the same because they contain the same data. The same case is for the second database.

2. The username of primary and replica should be different, the permission for replica user is set to only read not write.

3. When using a replica database, you need to add an replica: true entry to the replica in the database.yml. This is because Rails do not have any way of knowing which one is a replica and which one is the writer database. Rails will not run specific tasks, such as migrations, against replicas.

4. Other than primary writer databases, you need to set the migrations_paths to the directory where you will store migrations for that database.

We have a new database now, so let’s set up the connection model. To use the new database, we need to create a new Abstract class or inherit the class with ActiveRecord::Base class to connect to the album database.

Setting Up Connection Model

To use the new database, create a new abstract class that inherits from ActiveRecord::Base and connects to the album database:

 

class AlbumRecord < ApplicationRecord
self.abstract_class = true
connects_to database: { writing: :album, reading: :album_replica }
end

You need to update yourApplicationRecord class, to let it know about the new replica:

 

class ApplicationRecord < ActiveRecord::Base
self.abstract_class = true
connects_to database: { writing: :primary, reading: :primary_replica }
end

Generators and Migrations
Migrations for multiple databases should live in their folders prefixed with the name of the database key in the configuration.

You also need to set the migrations_paths in the database configurations to tell Rails where to find the migrations.

For example, the album database would look for migrations in the db/allbum_migrate directory and primary would look in db/migrate. Rails generators now take an --database option so that the file is generated in the correct directory. The command can be run like so:

 

bin/rails generate migration image --database album

The above command will generate migrations in the db/album_migrate/ folder.
If you are using Rails generators, the scaffold and model generators will create the abstract class. Simply pass the database key to the command line.

 

bin/rails generate model image --database album

A class with the database name plus Record keyword will be created. In this example, the database is Album so we end up with AlbumRecord:

 

class AlbumRecord < ApplicationRecord
self.abstract_class = true
connects_to database: { writing: :album }
end 

 Generating a Migration for a Specific Database

To generate a migration for the album database, you can use the following command:

bin/rails generate migration AddImageToAlbums image:string --database=album

Running Migrations:

 

bin/rails db:migrate:album

 Rolling Back Migrations:

 

bin/rails db:rollback:album

Running Migrations Up or Down:

• Up Migration: To run a specific migration up for the album database, use:

bin/rails db:migrate:up:album VERSION=20230601000000

• Down Migration: To run a specific migration down for the album database, use:

bin/rails db:migrate:down:album VERSION=20230601000000

 Conclusion

Managing multiple databases in a Ruby on Rails application can greatly enhance the performance, scalability, and modularity of your system. By following the steps outlined in this post, you can easily set up and manage multiple databases in your Rails application. Whether you're looking to separate read and write operations, integrate with legacy systems, or structure your data more effectively, Rails provides the tools you need to succeed.