Rails adds ActiveRecord API for switching multiple database connections

Rails has introduced support for multiple databases in application.

Rails now provides a method connects_to which we need to specify at the model level and mention the databases as shown below:

class User < ApplicationRecord
  connects_to database: { writing: :primary, reading: :replica }
end

As specified in the connects_to block above, all the update and insert queries will be executed on the primary database and select queries on replica.

connects_to method must be specified in the common base class, let’s say ApplicationRecord and this can be inherited by other models as needed. Database clients have a limit on the number of open connections and hence we need to specify connects_to in a single model base class. If this is not done, the number of open connections will add up since Rails uses the model class name for the database connection.

The database.yml for the above configuration may look like as below:

development:
  primary:
    database: my_primary_database
    user: root
    adapter: mysql

  replica:
    database: my_primary_database
    user: root_readonly
    adapter: mysql
    replica: true

We need to specify replica: true in our replica configuration because Rails need a way to identify which one is a replica and which one is primary. The primary and replica database name will be the same, but the user permissions should be different.

Outside the model, we might want to connect to a completely different database.

Let’s say for the above example, if we have another archive database for storing deactivated user, we can use Rails connected_to method as shown below:

ActiveRecord::Base.connected_to(database: :archive_db) do
  User.fetch_all_deactivated
end

:archive_db is the database key in our database.yml config file.

We can connect to a different database by using the role parameter too.

ActiveRecord::Base.connected_to(role: :reading) do
  # finds user from replica connected to ApplicationRecord
  User.first
end

By default the :writing role will be used since all connections must be assigned a role. If you would like to use a different role you can pass a hash to the database:

ActiveRecord::Base.connected_to(database: { readonly_slow: :replica }) do
  # runs a long query while connected to the +replica+ using the readonly_slow role.
  User.run_a_huge_query
end

Rails by default expect database roles to be writing and reading for the primary and replica respectively. If we want to have our roles, we need to create them and specify them in our application config.

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

NOTE:

• When using the database key, a new connection will be established every time. It is not recommended to use this outside of one-time scripts.