Multi-Database Support Limitations

[miguelmarcondesf]

Hello there!
I'm doing some studies and experiments with multi-database setups, and I stumbled upon some limitations and conflicts when trying to use multiple tenanted connection classes, which I think are expected for now.

Use Case

Healthcare management system requiring:

• ApplicationRecord: Users, Staff, Appointments (main business logic)
• PatientsRecord: Sensitive patient data (separate database for HIPAA compliance)
• Both databases need tenant isolation per medical practice

My database config:

// config/database.yml
development:
  global: # storing Practice to handle new Tenant creations and Session
    <<: *default
    database: storage/development_global.sqlite3
  primary: 
    <<: *default
    tenanted: true 
    database: storage/development/%{tenant}.sqlite3
  patients: 
    <<: *default
    tenanted: true
    database: storage/development/patients_%{tenant}.sqlite3
    migrations_paths: db/patients_migrate
  patients_replica:
    <<: *default
    tenanted: true
    replica: true
    database: storage/development/patients_%{tenant}.sqlite3

What I Tried

Manual Tenant Synchronization

Being called on active_record_tenanted.tenant_resolver

  class MultiTenantResolver
    def self.call(request)
      tenant = request.subdomain
      if tenant
        PatientsRecord.current_tenant = tenant
        return tenant
      end
    end
  end

Expected result: cannot swap sharp while shard swapping is prohibited

Custom Middleware

  class MultiTenantMiddleware
    def call(env)
      tenant = extract_tenant(env)
      ApplicationRecord.current_tenant = tenant
      PatientsRecord.current_tenant = tenant
    end
  end

Expect Result: Same shard swapping prohibition

Connection Override

  class PatientsRecord < ActiveRecord::Base
    def self.connection
      if ApplicationRecord.current_tenant && !current_tenant
        with_tenant(ApplicationRecord.current_tenant) { super }
      end
    end
  end

expected Result: Circular dependencies and shard swapping errors

Rails connects_to + activerecord-tenanted

  class PatientsRecord < ApplicationRecord
    tenanted :patients
    connects_to database: { writing: :patients }
  end

Expected Result: Conflicts between gems tenant switching and native multi-database

Other problems

Migration Management

• Only runs migrations for configured connection class
• Secondary databases (like db/patients_migrate/) ignored

Final Workaround

For now, to continue digging the gem, I just migrated things from the second tenanted database to the primary one

Suggested Improvements

Multi-Connection Support

Helping with handling the context switching and correctly finding migrations

  Rails.application.configure do
    config.active_record_tenanted.connection_classes = {
      "ApplicationRecord" => :primary,
      "PatientsRecord" => :patients
    }
    config.active_record_tenanted.tenant_resolver = ->(request) { request.subdomain }
  end

Thank you in advance and let me know if it makes sense

[murny]

Not to distract from your issue, but I am quite interested in this as I think using Solid gems like SolidCache also suffers from a similar issue.

As when using SolidCache you may be using multiple database under the with_tenant block. So when it connects to the cache DB inside the with_tenant it ends up with a similar error:

cannot swap `shard` while shard swapping is prohibited.

[flavorjones]

Yes, I mention this in GUIDE.md, it's a limitation in Rails' horizontal sharding that I'd like to fix:

• upstream
  • feature: make shard swap prohibition database-specific
    • which would work around Solid Cache config wonkiness caused by Always manage connections when database is specified rails/solid_cache#219

[flavorjones]

In the meantime you can work around it by updating your solid cache config to look something like this:

# config/cache.yml
default_connection: &default_connection
  connects_to:
    database:
      writing: :cache

default: &default
  <<: *default_connection
  <<: *default_options

development: *default
test: *default_options
beta: *default
staging: *default
production: *default

[murny]

Awesome, will give this a try! Thanks @flavorjones

[flavorjones]

FWIW I landed a patch in Rails to avoid the "cannot swap shard" error: rails/rails#55927

[flavorjones]

@miguelmarcondesf Thanks for asking this question!

multiple tenanted connection classes

This is not something I've attempted to do myself. The database connections should mostly work, but the Rails integrations controlled by setting config.active_record_tenanted.connection_class are definitely not going to work.

I'm deeply curious about this use case -- generally speaking, what are the shear zones?

For example, I can imagine that Action Cable integration would be affined with the ApplicationRecord in your app -- the user who is logged in.

But I have no idea what Active Storage should do here, since obviously there are going to be some blobs associated with the user (e.g., avatars) and some blobs associated with the patient (e.g., x-rays). Have you thought through how you want that to behave?

My recommendation to you is to set config.active_record_tenanted.connection_class to nil and then build what you need based on patterns in this codebase.

[miguelmarcondesf]

It's a project I started to study and experiment with what's coming in 8.1 and the tenanted so I could write a blog post about it.

I started just using multiple databases to isolate patient data (thinking about some more rigorous criteria for this data perhaps) and also to be able to have a read-only replica.
So when I added tenanted, I kept this idea of ​​trying to maintain multiple tenanted databases.

Thanks for the recommendation and attetion, for the next steps I want to take I will try to follow what you said.

[Merovex]

I came here because I'm wondering if I can use ActionText and Active Storage in Global and in a Tenant context. But Gemspec and other bits in the code tells me "nope."

[flavorjones]

The Action Text and Active Storage gems are designed to work with a single database, currently. This gem uses the subtenant_of method to allow them to support multiple shards within a single database.

You're asking for something different than what this gem does: you want Action Text, Active Storage, and Action Mailbox to support multiple databases. Do you know of a way to use those gems in a vanilla multi-database Rails application without resorting to connection trickery today? I don't, but please tell me if I'm missing something.

Assuming I'm correct and there's no way to do that today ... one idea I have is to upstream to Rails the connection pool forwarding trick that's used by subtenant_of. If that feature is in Rails, then it might be possible for the Rails Records gems to someday support multiple databases.

[Merovex]

I don't know how to avoid trickery. I'm not super sophisticated in my understanding of the internals.

[flavorjones]

As it turns out, I have a need to have multiple tenanted databases in the app I'm working on, and so I expect to do a lot of learning shortly about the current constraints of Rails and Active Record Tenanted, and maybe what can be changed to better support this use case.

[flavorjones]

See #223 for the first small step: rake tasks that support multiple tenanted databases.

[flavorjones]

Woop woop! I've got multiple tenanted databases working in my development environment. This looks promising, though I do need to fix Rails' "prohibit shard swapping" feature to allow finer-grained prohibition. Expect to see some pull requests here and in Rails over the next week to make this happen!

[flavorjones]

Rails pull request to relax the shard prohibition to be database-specific: rails/rails#55927

[matthewblott]

I've just come across this thread which seems to address my use. I have one database that is shared, it basically stores user information. When a new user is created I have a provision_tenant method that gets invoked by after_create. provision_tenant basically does the following:

ApplicationRecord.create_tenant(tenant_name)

tenant_name is a string created with the id of User. This is failing with the following:

cannot swap `shard` while shard swapping is prohibited.

From reading everything here it seems what I want to do isn't possible at the moment. Is that the case? Thanks in advance :-)

[miguelmarcondesf]

Hey @matthewblott, actually, it should be possible, the main problem here was trying to set multiple tenanted databases, but for your case, if you have something like:

1. Having a non-tenanted base class

class GlobalRecord < ActiveRecord::Base
  self.abstract_class = true
  connects_to database: { writing: :global, reading: :global }
end

2. Make User inherit from it

class User < GlobalRecord
  after_create :provision_tenant
  
  def provision_tenant
    tenant_name = "user_#{id}"
    ApplicationRecord.create_tenant(tenant_name)
    ...
  end
end

3. Make sure database.yml has both global and tenanted databases

Let me know if it solves for you :)

[matthewblott]

Thanks for the prompt response @miguelmarcondesf unfortunately I already tried that and it didn't work 🙁

[miguelmarcondesf]

Are you trying to create this through a form with an open connection? That way it really won't work because of shard swapping.
The middleware will resolve the request and swap the tenant, so you can either generate those Users from a non-tenanted subdomain or with a background job.

[matthewblott]

Here's the erb file:

<h1>Sign in</h1>

<form action="/sign_in" method="post" id="sign_in_form">
  <input type="hidden" name="authenticity_token" value="<%= form_authenticity_token %>">
</form>

<button type="submit" form="sign_in_form">Submit</button>

That posts to this method:

def create
  user = User.create!

  cookies.permanent.encrypted[:device_token] = {
    value: user.device_token,
    httponly: true,
    same_site: :lax
  }
  redirect_to user_todos_path(user)
end

FWIW here's the database.yml file:

development:
  global:
    adapter: sqlite3
    database: storage/development/primary.sqlite3
  primary:
    adapter: sqlite3
    tenanted: true
    database: storage/development/%{tenant}.sqlite3

There really isn't too much going on.