Use other database connection and execute query

In our app, we need to switch to read replica database and read from it for some read-only APIs.

We decided to use the around_action filter for that:

1. Switch DB to read_replica before the action
2. Yield
3. Switching back to master.

We decided to use establish_connection for switching, which did the job but later we noticed that it's not thread-safe i.e it causes our other threads to face "#<ActiveRecord::ConnectionNotEstablished: No connection pool with 'primary' found.>" issue. So this solution would have worked in the case of single-threaded servers.

Later we tried to create a new connection pool, as below which is thread-safe:

  databases = Rails.configuration.database_configuration
  resolver = ActiveRecord::ConnectionAdapters::ConnectionSpecification::Resolver.new(databases)
  spec = resolver.spec(:read_replica)
  pool = ActiveRecord::ConnectionAdapters::ConnectionPool.new(spec)
  pool.with_connection { |conn|
    execute SQL query here.
  }

The only problem with the above approach is, we can only execute queries using execute method like conn.execute(sql_query) any AR ORM query we execute inside this with_connection block run on the original DB and not read_replica.

Seems like ActiveRecord do have its default connection and it's using it when we run AR ORM queries.

Not sure how can we execute the AR ORM query inside the with_connection block as User.where(id: 1..10).

Please note:

1. I am aware that we can do this natively in rails 6, need to skip that for now.
2. I am also aware of the Octopus gem, again need to skip on that.

Appreciate any help, Thanks.