Rails: Switch Connection on Each Request But Keep a Connection Pool

Rails: switch connection on each request but keep a connection pool

Well, i've been digging into this a bit more and managed to get something working.

After reading tenderlove's post about connection management in ActiveRecord, which explains how the class hierarchy gets unnecessarily coupled with the connection management, i understood why doing what i'm trying to do in not as straightforward as one would expect.

What i ended up doing was subclassing ActiveRecord's ConnectionHandler and using that new connection handler at the top of my model hierarchy (some fiddling on the ConnectionHandler code was needed to understand how it works internally; so of course this solution could be very tied to the Rails version i'm using (3.2)). Something like:

# A model class that connects to a different DB depending on the subdomain 
# we're in
class ModelBase < ActiveRecord::Base
  self.abstract_class = true
  self.connection_handler = CustomConnectionHandler.new
end

# ...

class CustomConnectionHandler < ActiveRecord::ConnectionAdapters::ConnectionHandler
  def initialize
    super
    @pools_by_subdomain = {}
  end

  # Override the behaviour of ActiveRecord's ConnectionHandler to return a
  # connection pool for the current domain.
  def retrieve_connection_pool(klass)
    # Get current subdomain somehow (Maybe store it in a class variable on 
    # each request or whatever)
    subdomain = @@subdomain
    @pools_by_subdomain[subdomain] ||= create_pool(subdomain)
  end

  private
  def create_pool(subdomain)
    conf = Rails.configuration.database_configuration[Rails.env].dup
    # The name of the DB for that subdomain...
    conf.update!('database' => "db_#{subdomain}")
    resolver = ActiveRecord::Base::ConnectionSpecification::Resolver.new(conf, nil)
    # Call ConnectionHandler#establish_connection, which receives a key 
    # (in this case the subdomain) for the new connection pool
    establish_connection(subdomain, resolver.spec)
  end
end

This still needs some testing to check if there is in fact a performance gain, but my initial tests running on a local Unicorn server suggest there is.

Would it be possible to have multiple database connection pools in rails to switch between?

As I understand, there are 4 pattern for multi-tenancy app:

1. Dedicated model/Multiple Production Environments

Each instance or database instance entirely host different tenant application and nothing is shared among tenants.

This is 1 instance app and 1 database for 1 tenant. The development would be easy as if you serve 1 tenant only. But will be nightmare for devops if you have, say, 100 tenants.

2. Physical Segregation of Tenants

1 instance app for all tenant but 1 database for 1 tenant. This is what you are searching for. You can use ActiveRecord::Base.establish_connection(config), or using gems, or update to Rails 6 as other suggests. See the answer for (2) below.

3. Isolated schema model/Logical Segregations

In an Isolated Schema, the tenant tables or database components are group under a logical schema or name-space and separated from other tenant schemas, however the schema are hosted in the same database instance.

1 instance app and 1 database for all tenant, like you do with apartment gem.

4. Partially Isolated Component

In this model, components that have common functionalities are shared among tenants while components with unique or unrelated functions are isolated. At the data layer, common data such as data that identify tenants are grouped or kept in single table while tenant specific data are isolated at table or instance layer.

---

As for (1), ActiveRecord::Base.establish_connection(config) not handshaking to db per request if you use it correctly. You can check here and read all the comment here.

As for (2), If you don't want to use establish_connection, you can use gem multiverse (it works for rails 4.2), or other gems. Or, as other suggest, you can update to Rails 6.

Edit: Multiverse gem is using establish_connection. It will append the database.yml, and create a base class so that each subclass shares the same connection/pool. Basically, it reducing our effort to use establish_connection.

As for (3), the answer:

If you don't have so many tenants, and your application is pretty complex, I suggest you use Dedicated Model pattern. So you go for 1 app instance = one specific connection to one specific tenant. You don't have to make your apps more complex by adding multiple database connections.

But if you have many tenants, I suggest you use Physical Segregation of Tenants or Partially Isolated Component depends on your business process.

Either way, you have to update/rewrite your application to comply with the new architecture.

How rails database connection pool works

Purpose:

Database connections are not thread safe; so ActiveRecord uses separate database connection for each thread.

Limiting factor:

Total database connections is limited by the database server you use (e.g Posgres: default is typically 100 or lesser), by your app server's configuration (number of processes/threads available) and Active Record's configuration : Connection Pool defaults to 5 .

Pool size:

Active Record's pool size is for a single process. A thread uses a connection from this pool and releases it automatically afterwards. (unless you spawn a thread yourself, then you'll have to manually release it). If your application is running on multiple processes, you will have 5 database connections for each of them. If your server is hit by 1000 requests concurrently, it will distribute the requests among these connections, when it gets full, rest of the requests wait for their turn.

Read more at:

https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/ConnectionPool.html

Rails 7: switch to different databases

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

• https://guides.rubyonrails.org/active_record_multiple_databases.html#setting-up-your-application
• https://api.rubyonrails.org/classes/ActiveRecord/Inheritance/ClassMethods.html#method-i-primary_abstract_class
• https://api.rubyonrails.org/classes/ActiveRecord/ConnectionHandling.html#method-i-connects_to

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

switching between databases based on session info

This gem was the silver bullet: https://github.com/thiagopradi/octopus

I stored the database in the session and just use the using method before each of my searches.

---

Related Topics