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.

Just a couple of days ago horizontal sharding was added to Ruby on Rails' master branch on GitHub. Currently, this feature is not officially released but depending on your application's Rails version you might want to consider using Rails master by adding this to your Gemfile:

gem "rails", github: "rails/rails", branch: "master"

With this new feature, you can take advantage of Rails' database connection pool and switch the database based on conditions.

I haven't used this new feature, but it seems pretty straight-forward:

# in your config/database.yml
production:
  primary:
    database: my_database
    # other config: user, password, etc
  primary_tenant_1:
    database: tenant_1_database
    # other config: user, password, etc

# in your controller for example when updating a tenant
ActiveRecord::Base.connected_to(shard: "primary_tenant_#{tenant.database_shard_number}") do
  tenant.save
end

You didn't add much detail about how you determine the tenant number or how authorization is done in your application. But I would try to determine the tenant number as soon as possible in the application_controller in an around_action. Something like this might be a starting point:

around_filter :determine_database_connection

private

def determine_database_connection
  # assuming you have a method to determine the current_tenant and that tenant
  # has a method that returns the number of the shard to use or even the 
  # full shard identifier
  shard = current_tenant.database_shard # returns for example `:primary_tenant_1` 

  ActiveRecord::Base.connected_to(shard: shard) do
    yield
  end
end

From what I understand, (2) should be possible with manual connection switching in Rails 6.