I have a rails app with a Postgres db and 4 mysql dbs - how can I wrap queries to all dbs in transactions and not just the primary (Postgres) db?

Question

Basically, I have a rails app that has one Postgres database as the primary and reads and writes to both it and 4 independent mysql databases. Transactional tests work just fine when testing behavior that only affects the Postgres database, but actions that affect the mysql databases are not wrapped in a transaction.

I can't, for the life of me, figure out how to piggyback on the transaction that's being used for the Postgres database when running my tests.

ActiveRecord models that connect to the Postgres database all inherit from ApplicationRecord which looks like this:

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

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

And models that interact with the mysql databases inherit from a MysqlRecord that looks like this:

class MysqlRecord < ActiveRecord::Base
  self.abstract_class = true
end

with each mysql database having an additional namespace and base record that the individual models inherit from like this:

class Alpha::Base < MysqlRecord
  self.abstract_class = true
  connects_to database: { writing: :alpha, reading: :alpha }
end

class Beta::Base < MysqlRecord
  self.abstract_class = true
  connects_to database: { writing: :beta, reading: :beta }
end

class Alpha::SomeModel < Alpha::Base
  # ... model logic in here
end

class Beta::OtherModel < Beta::Base
  # ... etc.
end

How can I start transactions on the Mysql databases and set savepoints/call rollback whenever the same is done for the Postgres database? I'm totally okay with a sledgehammer approach that does it for all databases on every test, even if individual mysql databases aren't involved in some of them, I just really want to wrap all the database operations in transactions in the tests.

Some additional notes: this is a rails app that started out on Rails 4 in 2016 and is now on Rails 7.1.3.4 and is using RSpec 3.13 - I originally wrote all of the code that managed the connections to the secondary mysql databases (with a fair amount of duct tape and chewing gum holding things together) and was thrilled to delete all that code to use the built-in multidb support now integrated in rails, and am hoping to add new tests that are somewhat performant as I add additional new features to the codebase.

Even just a hint as to where in the Rails source I can poke around to see where the built in support for this stuff is would be helpful, as I can't seem to find where it goes down.

Answer 1

My problem was ultimately caused by the storage engine for the MySQL tables not supporting transactions. I inherited this (20 year old) project and the underlying MySQL databases were using the MyISAM storage engine, which does not support transactions. In the end, I solved my problem by running the following migration on all of the mysql databases, and Rails' built-in transaction support in tests then worked as expected:

    Alpha::Base.connection.tables.each do |table|
      execute "ALTER TABLE #{table} ENGINE=InnoDB"
    end
    
    # and again for each base class