[SOLVED] Multiple databases / replica not working on test environment with Rails 6

Multiple databases / replica not working on test environment with Rails 6

In an effort to move from distribute_reads to the native Rails 6 multiple databases support I came across a weird behavior, I'm still unsure if it's a bug or a configuration problem.

// database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  pool: 10
  reaping_frequency: 10
  timeout: 5000
  username: my_username
  password: my_password
  database: my_database
  schema_search_path: 'public,utils'

development: &development
  primary:
    <<: *default
    database: my_database
    host: 'localhost'
  primary_replica:
    <<: *default
    database: my_database
    host: 'localhost'
    replica: true
  third_replica:
    <<: *default
    database: my_database
    host: 'localhost'
    replica: true

test: &test
  primary:
    <<: *default
    database: 'my_database_test'
    host: 'localhost'
  primary_replica:
    <<: *default
    database: 'my_database_test'
    host:'localhost'
    replica: true
  third_replica:
    <<: *default
    database: 'my_database_test'
    host: 'localhost'
    replica: true

I also changed the ApplicationRecord that connects to all the records of this codebase

# application_record.rb
connects_to shards: {
  default: { writing: :primary, reading: :primary_replica },
  admin: { writing: :primary, reading: :third_replica }
}

Now when I'm in development it acts exactly as it should

> ActiveRecord::Base.connected_to(role: :reading, shard: :default, prevent_writes: true) do
  EventType.count
end
   (72.3ms)  SELECT COUNT(*) FROM "event_types" /*line:(pry):2:in `block in <main>'*/
=> 2

This is the correct number of records. When I run a test and use the same lines, the EventType.count outside of the wrapper will count the correct number as well, but connected_to will result in 0 no matter what record I'm checking, in any model.

> EventType.count
=> 1
> ActiveRecord::Base.connected_to(role: :reading, shard: :default, prevent_writes: true) do
  EventType.count
end
=> 0

The replica in development/test is actually the same database, so it shouldn't be possible to get 0 in my case.

When using the writing role it works though

> ActiveRecord::Base.connected_to(role: :writing, shard: :default) do
  EventType.count
end
=> 1

Be aware the shards: part doesn't seem to matter because it also breaks with a simplified version through database: and no third_replica

Is there something I'm missing about this Rails functionality? Is there something related to tests that aren't compatible with this? I couldn't find any documentation related to this.

Solution

In the end, the problem came from the transaction we have throughout our tests setup

DatabaseCleaner.strategy = :transaction

And it put in evidence it's a much bigger problem throughout our codebase. When having queries wrapped into transactions and using connected_to you basically go out of the said transaction and it creates lots of consistency problems in our specific case. It's purely logical, but adding this into your stack may be incompatible with what was there in the first place.

The conclusion is to be careful when using transactions and dealing with read-replica.