Is there a better method than this to switch database per request by subdomain?

rubyonrails-talk
1

We previous was on Rails 5 and we have many clients who are on their own subdomain. The clients have their own database. We have the client list on an array in the ActiveRecord model. In the ApplicationController we used before_action to set the database for each client. I am giving the relevant snippets here:

before_action :set_database_for_subdomain

def set_database_for_subdomain
    DatabaseManager::SetDatabaseForSubdomain.call(request.subdomain.to_s)
  end

def call
      cache_key = "db_name_for_subdomain_#{subdomain}"

      db_name = Rails.cache.fetch(cache_key, expires_in: 1.hours) do
        if ApplicationRecord::SUBDOMAINS.include?(subdomain&.gsub('www.', ''))
          case Rails.env
          when 'development', 'staging' then "dev_#{subdomain}"
          when 'production' then "prod_#{subdomain}"
          end
        else
          case Rails.env
          when 'development', 'staging' then 'test_database'
          when 'production' then 'default'
          end
        end
      end

      current_db = ActiveRecord::Base.connection_config[:database] rescue nil

      ActiveRecord::Base.establish_connection(website_connection(db_name)) if db_name && current_db != db_name
    end

    def website_connection(subdomain)
      subdomain = subdomain.gsub('www.', '')
      default_connection.dup.update(:database => subdomain)
    end

    def default_connection
      @default_config ||=
        ActiveRecord::Base
          .connection
          .instance_variable_get("@config")
          .dup
    end
  end

so if our client_a sends request it will get hooked on the before_action and find client_a on SUBDOMAINS array in active record model and then connect to prod_client_a, if next request is from client_b it will set prod_client_b as database…. I learned that calling establish_connection on each request is a bad practice.

Now that we have successfully upgraded from Rails 5 to Rails 7.2, we want to know if there’s a better way to do this which doesn’t exhaust the connection pool. We sometime lose connection pool when it’s running as a docker.

Any help is appreciated. Thanks.

2

I don’t think using establish_connection is thread-safe as it’s modifying the state of the connection pool correct? This may result in a request talking to the wrong database I believe when you have concurrent requests.

Some of the answer depends on your setup. What database server are you using? Are the databases co-located on the same server? For example if the answer to those questions are MySQL and all on the same server then use <database> at the start of the request is the most efficient approach as the apartment gem is doing. But if the answer is something else then there might be another strategy that is better. For example, in PostgreSQL you likely want a single DB but with multiple schemas.

In general, you probably want to use something like the apartment gem because it has already found a good strategy for all the options. Plus it handles other things like running migrations on each DB, etc. Also, there are things that happen outside of requests. For example, if you kick off a background job you want that job to use the right DB. The apartment gem has an addition to support that.

I believe the apartment gem is most robust solution. Just make sure you use the RoR fork as it’s the one maintained. You can checkout the Ruby Toolbox for other options. I will note that even though the apartment gem is fairly robust it’s still not a 100% solution. There are still some edge cases that it doesn’t handle. This discussion is a pretty good summary of them and some of them can be subtle. In the end, Rails just doesn’t support this sort of multi-tenancy well so although many people do use it, it will be something of a struggle.

There is good news on the horizon though. 37Signals has been recently working on their own extension called activerecord-tenanted. Since it was recently developed it’s able to lean more heavily on Rails’ existing sharding functionality as provided by connect_to. This simplifies the impl as it’s largely just doing one shard per customer. Right now it only supports SQLite but it’s something I would keep an eye out for in the future. Since it’s being developed by 37Signals I wouldn’t be surprised if they use their influence on Rails to at least adjust things so less monkey-patching is done to provide this sort of functionality.