Hey, everyone!
Automatically switching database connections has been discussed before, including on @eileencodes’s PR that added the API for connection switching. That said, Rails’ multiple database logic has been thoroughly battle tested since then and we wanted to see if there’s a new appetite for this discussion.
At Zendesk we think there’s a use case for an opt-in option for Rails to automatically switch to the writing role when Rails knows the query is a write. Currently, the default role in ActiveRecord is the :writing role. This allows for you to read (Foo.first) and write (Foo.create!) outside of a connected_to(...) block.
However, Rails allows you to change the default role for a connection class and doing so can be quite beneficial for multiple database setups. Let’s say you have one Foo writer database instance and many Foo replica instances that are distributed closer to the end users. Defaulting to the writer database degrades performance: all traffic gets routed to the single database instance rather than distributed to the nearest replica; the request transit time usually takes longer because the end user typically has a shorter hop to a replica database, not the writer.
If we instead change the default_role we can still call Foo.first but Foo.create! will raise an error unless it’s inside a .connected_to(role: :writing) block. Here’s a gist that demonstrates this behavior.
Ideally, Rails would understand that Foo.create! is a write query and, because our default role is :reading, switch to the writer database for us and seamlessly create the record. This is something we’re thinking of implementing internally and, if it’s of interest, trying to upstream.
We’re unsure of the best approach. The one thing we’re certain of: due to the unexpected (and difficult to debug) issues that arise from replication lag, any automatic database switching should be opt-in.
In the gist above, Rails raises an ActiveRecord::ReadOnly error when you attempt to write to a replica database, implying that Rails already knows if a query is a write or not. Should we hook into that logic (#check_if_write_query) to switch connections if necessary? It’s quite far down in the request cycle though and may not be the ideal place to switch connections.
In the previous discussion, @madbomber used ActiveRecord callbacks to switch connections but what about queries that skip callbacks altogether?
The ActiveRecord::Middleware::DatabaseSelector::Resolver can switch connections based on the HTTP verb but some apps violate this by writing during GET requests.
We’re very interested in any thoughts folks might have. Thank you!
