Rails 7: switch to different databases

i am trying to connect to different databases in my script but i am getting ActiveRecord::ConnectionNotEstablished: No connection pool for 'ActiveRecord::Base' found for the 'main' shard.

relevant code from database.yml is below

test:
  primary:
    adapter: postgresql
    database: test
    database: "<%= ENV['DATABASE_NAME'] %>"
    username: "<%= ENV['DATABASE_USERNAME'] %>"
    password: "<%= ENV['DATABASE_PASSWORD'] %>"
    host: "<%= ENV['DATABASE_HOST'] %>"
  main:
    adapter: postgresql
    username: postgres
    database: "<%= ENV['API_MAIN_DATABASE_NAME'] %>"
    password: "<%= ENV['API_DATABASE_PASSWORD'] %>"
    host: "<%= ENV['API_DATABASE_HOST'] %>"
    database_tasks: false
  prediction:
    adapter: postgresql
    username: postgres
    database: "<%= ENV['API_PREDICTION_DATABASE_NAME'] %>"
    password: "<%= ENV['API_DATABASE_PASSWORD'] %>"
    host: "<%= ENV['API_DATABASE_HOST'] %>"
    database_tasks: false
  onshore:
    adapter: postgresql
    username: postgres
    database: "<%= ENV['API_US_ONSHORE_DATABASE_NAME'] %>"
    password: "<%= ENV['API_DATABASE_PASSWORD'] %>"
    host: "<%= ENV['API_DATABASE_HOST'] %>"
    database_tasks: false

i have 3 models connecting to main, prediction & onshore databases as below

class ExternalRecord < ApplicationRecord
  self.abstract_class = true
  connects_to shards: { writing: :primary, reading: :main }
end

class ExternalRecordPrediction < ApplicationRecord
  self.abstract_class = true
  connects_to shards: { writing: :primary, reading: :prediction }
end

class ExternalRecordOnshore < ApplicationRecord
  self.abstract_class = true
  connects_to shards: { writing: :primary, reading: :onshore }
end

I am trying to do some processing by connecting to these three different database in a loop like below

     ActiveRecord::Base.connected_to(role: :reading, shard: :main) do
     results = ActiveRecord::Base.connection.execute(query_here)
     #process results

But i get an error ActiveRecord::ConnectionNotEstablished: No connection pool for 'ActiveRecord::Base' found for the 'main' shard.

How do i switch to different databases in the script.

I am using Rails 7

I was previously using Rails 5 and using ar-octopus to achieve this.

Solution

ActiveRecord::Base shares a connection with your ApplicationRecord class or some other class where primary_abstract_class is set. You have to set up connects_to there.

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class # <= there can only be one in your app

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

# if you are in a console make sure ApplicationRecord class is loaded
ApplicationRecord

ActiveRecord::Base.connected_to(role: :reading) do
  # ActiveRecord::Base.current_role                     # => :reading
  # ActiveRecord::Base.current_shard                    # => :default
  #   :default is configuration named 'primary' or the first entry if 'primary' is not found
  # ActiveRecord::Base.connection.execute("INSERT ...") # => ActiveRecord::ReadOnlyError
  
  ActiveRecord::Base.connection.execute("SELECT ...")   # ok
end

With shards

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  connects_to shards: {
    one: { writing: :primary, reading: :main },
    # two: ...
  }
end

# if you are in a console make sure ApplicationRecord class is loaded
ApplicationRecord

ActiveRecord::Base.connected_to(role: :reading, shard: :one) do
  # ActiveRecord::Base.current_role                     # => :reading
  # ActiveRecord::Base.current_shard                    # => :one
  # ActiveRecord::Base.connection.execute("INSERT ...") # => ActiveRecord::ReadOnlyError
  
  ActiveRecord::Base.connection.execute("SELECT ...")   # ok
end

Reference: rails v7.0.2.3 ruby v3.1.1

You can also just force a connection. But I don't know how safe this is, I wouldn't use it in the main app.

ActiveRecord::Base.establish_connection(:prediction)
# do it here
ActiveRecord::Base.establish_connection # back to default just in case

https://api.rubyonrails.org/classes/ActiveRecord/ConnectionHandling.html#method-i-establish_connection