ruby-on-railsoctopus

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