Is there a way to switch the database connection on the fly?

Issues
1

Hi, we’re building a multi-tenant app where most tenants will reside in the same database. However, we need to support the ability for some tenants to have their own private database.

I saw that rom-rb supports connecting to multiple databases during initialization, but is it possible to have a default connection and then switch the default to a different database at runtime?

The code below obviously doesn’t work but demonstrates the gist of what I’d like to achieve.

class ApplicationController < ActionController::API
  around_action :connect

  def connect
    custom_config = db_config_for(current_user.tenant_id)
    if custom_config
      # somehow switch the db connection to the tenant's private db
      ROM.env.gateways[:default].with_server(custom_config) do
        yield
      end
    else
      # if no config found, just yield and use the default connection
      yield
    end
  end

  def db_config_for(tenant_id)
    # lookup and return config if there is a private db for this tenant...
  end
end

Sequel’s arbitrary servers seems to be close to what I need. Wondering if there’s possibly a way to take advantage of this?

UPDATE:

I may have gotten this working with the following setup, still need to fully test it:

ROM::Rails::Railtie.configure do |config|
  config.gateways[:default] = [
    :sql,
    **Rails.configuration.database,
    # use the sharded threaded connection pool
    pool_class: :sharded_threaded,
    # include arbitrary_servers and server_block extensions
    extensions: [:arbitrary_servers, :server_block]
  ]
end

class ApplicationController < ActionController::API
  around_action :connect

  def connect
    connection = ROM.env.gateways[:default].connection
    # now we can switch to any database on the fly
    connection.with_server(host: 'localhost', database: 'testing') do
      connection.synchronize do
        yield
      end
    end
  end
end

Anyone aware of any pitfalls to doing this with ROM?

2

rom-sql doesn’t do anything with the connections, it’s all handled by Sequel. This means that whatever works with Sequel, should work with rom-sql. We should have a public API for it though so please remember that whatever you’re doing with Sequel directly is “low level”.

3

Got it, appreciate the response! :+1:

1 Like