While ActiveRecord works pretty great across most scenarios, it struggles with multi-database architectures. Fortunately, the past few releases have had a lot of work done in this aspect.

One major drawback is the way transactions work in a multi-database architecture. A transaction is basically a set of database operations that are performed as a single unit. Here’s an example,

Though there are two database operations (Company.first.touch and Company.last.touch), they are performed within a single transaction.

However, when you use a transaction across multiple databases, it is essentially left up-to the developer to ensure that the transactions are distributed across the databases in a way that is consistent with the intended use case. This is because transactions work on a single database connection.

Before

Let’s take the example of a multi-database architecture where there is a Company model on the primary database and a Freelancer model on the secondary database. Both these models are connected via the has_many :through association via the Contract model on the primary database.

As you can see, two transactions take place to insert two records into the contract table. This is because the company.freelancers = [freelancer1, freelancer2] operation is performed on the primary database connection and the Contract.create! operation is performed on the secondary database connection.

It is up to the developer to wrap this around a double transaction block to ensure that the two operations are performed within a single transaction.

After

Thanks to this PR that updates how through_reflection distributes transactions across database connections, we can now perform the same operation without the need for a double transaction block.

This was achieved via a very simple update to the ThroughAssociation module. The transaction method opens up the second block with the connection of the through_reflection instead of the source_reflection.