Automatic connection switches between primary/replica db based on SQL

I saw in this PR Part 4: Multi db improvements, Basic API for connection switching by eileencodes · Pull Request #34052 · rails/rails · GitHub there was talk of adding a feature where ActiveRecord would automatically switch between primary/replica database based off the SQL query. I’m curious if that has been implemented yet as I haven’t seen anything saying so in the latest documentation. I see Rails can currently switch (if configured) to automatically switch based off of HTTP request only. I ask because I’m currently going through an application and wrapping queries such as User.where in " ``` ActiveRecord::Base.connected_to(role: :reading

1 Like

The feature you are referring to, where ActiveRecord would automatically switch between primary and replica databases based on the type of SQL query, was proposed in the Pull Request you mentioned but it has not been implemented in Rails yet. As far as I am aware, the current version of Rails only allows you to switch between primary and replica databases based on the HTTP request.

To switch between primary and replica databases based on the type of SQL query, you can use the connected_to method, as you mentioned in your question. This method allows you to specify the :reading or :writing role when executing a query, and ActiveRecord will automatically switch to the appropriate database based on the role you specify.

Here is an example of how you could use the connected_to method to switch between primary and replica databases based on the type of SQL query:

Copy code

# execute a SELECT query on the replica database User.connected_to(role: :reading) doUser.where(...) end # execute an INSERT, UPDATE, or DELETE query on the primary databaseUser.connected_to(role: :writing) do User.create(...) end

In this example, the connected_to method is used to wrap the where and create queries, specifying the :reading and :writing roles, respectively. This will cause ActiveRecord to automatically switch to the appropriate database based on the type of query being executed.

You can read more about the connected_to method and how to configure ActiveRecord to use multiple databases in the Rails documentation: Multiple Databases with Active Record — Ruby on Rails Guides

I hope this helps! Let me know if you have any other questions.

I’ve implemented a database action-based connection switching approach by dynamically changing the default_role Its still in testing. I have no idea whether this will work in a production environment. Here is the GIST of what I’m currently testing in Rails v6.1.3.1

Hi Michael,

I think the automatic switching of database connections based on the type of SQL query would make the potential overhead cost. It’s because of constantly determining the query type (read or write) and switching connections accordingly.

However, there are other solutions available that offer this feature. One is the Multiverse gem, which provides automatic database connection switching. It switches to the primary database when a write operation is performed. This can be a more efficient solution, mainly if your application performs many read operations.

Before you decide on a solution, I recommend doing a benchmark test to compare the performance of Rails’ native multi-database connections with the Multiverse gem. This will give you a clear understanding of the performance and resource usage differences between the two approaches.

You can find the Multiverse gem here: GitHub - ankane/multiverse: Multiple databases for Rails.