Using Multiple Databases on Rails

Larger applications will occasionally split managed data across more than one database. Since version 6.+, Rails has shipped with a default solution for this necessity. With Rails 7.1, the following features are supported:

• Multiple writer databases with a replica for each
• Automatic connection switching for the model you’re accessing
• Automatic swapping between the writer and replica, depending on the incoming HTTP verb and recent writes
• Rails tasks for creating, dropping, migrating, and interacting with the multiple connected databases

Base setup

Let’s imagine we have an application to register cars and we need to add a new database in order to register boats. The database.yml could look like this:

production:
  primary:
    database: cars_database
    username: root
    password: <%= ENV['ROOT_PASSWORD'] %>
    adapter: mysql2
  primary_replica:
    database: cars_database
    username: root_readonly
    password: <%= ENV['ROOT_READONLY_PASSWORD'] %>
    adapter: mysql2
    replica: true
  boats:
    database: boats_database
    username: boats_root
    password: <%= ENV['BOATS_ROOT_PASSWORD'] %>
    adapter: mysql2
    migrations_paths: db/boats_migrate
  boats_replica:
    database: boats_database
    username: boats_readonly
    password: <%= ENV['BOATS_READONLY_PASSWORD'] %>
    adapter: mysql2
    replica: true

Rails will consider (when provided) the primary configuration as the default. In the case no configuration was named as primary, Rails will use the first configuration as the default for each environment.

It’s important to mention that the usersnames for writers and replicas should be different, and the permissions for the replicas should be set to readonly.

With our new database let’s setup a model. Using the Rails convetion, we’ll need to create a new abstract class and connect it to the new boats database.

class BoatsRecord < ApplicationRecord
  self.abstract_class = true

  connects_to database: { writing: :boats, reading: :boats_replica }
end

The ApplicationRecord class should looks like this:

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

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

Now all the classes that will need read/write on the primary database, will inherit from your primary abstract class. To read/write on the boats database, your classes will inherit from the BoatsRecord class.

All is now in place, so we can create our new database. We can see the available commands by running bin/rails -T. As expected, we can see the bin/rails db:create.

Note: This command will create both the databases provided in database.yml. To create only the boats_database, you can run bin/rails db:create:boats.

Migration and generators

The default generators of Rails work like a charm. Rails generators now have a --database option that allows you to specify the correct database for a new model.

Example command:

$ bin/rails generate model tire name:string --database cars

The migrations for multiple databases will live in folders prefixed with the name of the database key in the configuration.

Swap between roles

The automatic switching middleware uses the incoming HTTP verb to switch from writer to replica and vice versa. So, for PATCH, DELETE, POST and PUT requests, the application will use the writer database. For GET and HEAD, Rails will use the read database. To activate this feature, run the command:

$ bin/rails generate active_record:multi_db

It’ll create a new initializer in the config/initializers directory called multi_db.rb. There you’ll uncomment these lines below:

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

It’s also possible to build custom logic around chosing a particular role to handle a request.

Manual connection switching

For the cases where automatic swithing isn’t a good fit, Rails provides a connected_to method that allows us to specify a connection.

  ActiveRecord::Base.connected_to(role: :reading) do
    # all your amazing logic here
  end

In the example, Rails will use the connection that consists of the reading role.

That’s it for today, thanks for reading. If you have any further questions & wish to seek any help from our developers, feel free to contact us.