Rails Multi-Databases and Tenancy: How You Can Do It Today

Portuguese version here: https://tech-pills.github.io/2025-09-18-Rails-Multi-Databases-e-Tenancy-como-fazemos-isso-em-2025/

Hey everyone! Hope you’re doing well.

So, you’re building an application and you’ve reached that moment when you think: "Damn, how do I keep Company Two Brothers’ data completely separated from The Cousins’ data?". Maybe you’re dealing with medical records, financial data, or just passing by to see where I’m going with this.

As an application grows in popularity and usage, you will need to scale it to support your new users and their data. One way your application may need to be scaled is on the database level. Rails supports using multiple databases so you don’t have to store your data all in one place.

The example application is HealthCare Management, an application for a Healthcare Practice Management System that addresses critical points regarding data isolation and access.
I’ll show how each "clinic/Practice" has its own isolated databases, because sometimes the best way to keep data separated is literally to keep them separated.

I created a minimal structure to exemplify how we’ll work with more than one database and what problems we’ll solve (and which ones we’ll create) following this approach.
Here’s a representation of what we have so far:

A Practice refers to the organization and management of healthcare services, encompassing various ownership models, such as private practice, group practice, etc., and it will provide the isolation we need in the steps ahead.

The authentication layer uses the integrated User model from Rails 8, which connects to a Staff model that contains Practice-specific information, such as roles, license numbers, and departmental assignments.

The Appointment serves as the central scheduling entity, linking a Staff member (like healthcare providers/professionals) to a Patient.
The Patient contains sensitive information and is where we want to better work on this data isolation.

Multiple Databases

Much of what will be done here follows Active Record Multiple Databases and it’s important to emphasize that I won’t detail the initial implementation of the project.

So what we’ll do is add another database specifically to handle the Patients, you can check the code here

We don’t make any changes to our primary database, but we add a new configuration for what we want, and notice that we can also configure a different connection for this new database, which can be interesting if we need to granularize access.

  patients:
    <<: *default
    database: storage/production_patients.sqlite3 # Database name
    username: patients_root # User and password for connection
    password: <%= ENV['PATIENTS_ROOT_PASSWORD'] %>
    migrations_paths: db/patients_migrate # Path for migrations

It’s also necessary to modify all the Models that will connect to the new database. In our case, only the Patient at the moment.
We need to make the connection between it and a new abstraction of ApplicationRecord

# app/models/patients_record.rb
class PatientsRecord < ApplicationRecord
  self.abstract_class = true

  connects_to database: { writing: :patients }
end

# app/models/patient.rb
class Patient < PatientsRecord
...

It’s important to connect to your database in a single model and then inherit from that model for the tables rather than connecting multiple individual models to the same database. Database clients have a limit for the number of open connections, and if you do this, you will multiply the number of connections since Rails uses the model name for the connection specification name.

At this point, we can run rails db:create and the new database will be created. It’s necessary to modify all environments so that everything works correctly, including the test environment; otherwise, the new reference to Patient < PatientsRecord won't work.

You must have noticed that the new tables we’re going to create will reference the folder we configured with migrations_paths: db/patients_migrate.
Obviously, if this migration happens with data that already exists in production, it will be necessary to dump and load it into this new database instance. In our case, it would be possible to move the necessary migrations to the new folder, but it’s certainly better to generate a new one to maintain this history, and after a successful migration, we can create new ones, deleting what’s necessary from our primary database.

I’ll create the migration with the same fields, but this time we can include the flag with the target database:
rails g migration CreatePatients ... --database patients
I’ll manually adjust some validations we already had in the other migration to maintain consistency.
So now we can execute them and check if the tests are passing…

bin/rails aborted!
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table: main.practices: (ActiveRecord::StatementInvalid)

We’re trying to use a foreign key in a database that doesn’t exist in the table to make this sharing possible, so how do we proceed?
In our new migration, it will be necessary to just remove the foreign_key: true.
After that, the Appointments that also have a relationship with it need to be updated, so let’s create a migration for this:

class RemoveForeignKeyFromAppointmentsPatient < ActiveRecord::Migration[8.1]
  def change
    remove_foreign_key :appointments, :patients
  end
end

So at this point, our tests pass again, and we have each database taking care of a view:

Primary Database (primary.sqlite3):
├── practices  ←──┐
├── staffs        │ (Foreign keys still work with the same DB)
├── appointments ←┘
└── users

Patients Data Database (patients.sqlite3):
└── patients ← (No foreign key usage for Appointments)

We can still use ActiveRecord association normally, but we won’t have the constraints that a Foreign Key guarantees at the database level, and we still maintain the record of this key in each table, but it only works to maintain this record of each id value. So, like everything in life, we lose Referential Integrity, and orphaned data prevention will have to be managed by the application layer, in addition to indexes.

Practice.first.patients # Works normally
Patient.first.practice  # Works normally

Hungry reader

It may happen that we need a table or a specific database to have high read demand, or we want to maintain a read-only access profile for more sensitive data, so it’s possible to create a replica of it.

For our example, we’re using SQLite and the Rails default is that WAL mode is activated. With this, we have multiple readers but always only one write process.
I don’t want to go into much detail about SQLite itself; the Rails ecosystem as a whole has suggested it as the default, but if you have doubts, this reading is quite nice What you need to know about SQLite.

We then need to add a new configuration for our databases, remembering to also update it in the development environment; otherwise, all reads will hit a non-existent table. You can check the code here

# config/database.yml
  patients_replica:
    <<: *default
    # database: storage/production_patient_replica.sqlite3 # Another file
    database: storage/production_replica.sqlite3 # Same file as the main database
    # Actually, since we're using SQLite, we don't deal with user creation for connection
    # username: patients_readonly
    # password: <%= ENV['ROOT_READONLY_PASSWORD'] %>
    replica: true # This shows Rails that it's a read-only replica

Notice that we can configure this replica to point exactly to the same database as the main one (write), and in this case, the biggest benefit would be the Automatic read/write switching and restrict only read permissions for a specific user or to another database. And with this, we gain even more isolation. However, somehow we need to write or execute regular updates in the other database/file, depending on the project’s needs, but today I won’t cover this part.

We also need to add some configuration to guide the application to where we’ll direct writes and reads:

# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
  # Read/write split configuration without replica
  connects_to database: { writing: :primary, reading: :primary }

# app/models/patients_record.rb
class PatientsRecord < ApplicationRecord
  # Read/write split configuration with replica
  connects_to database: { writing: :patients, reading: :patients_replica }

We need to activate automatic read/write switching by running rails g active_record:multi_db and uncommenting the lines:

# config/initializers/multi_db.rb
Rails.application.configure do
  config.active_record.database_selector = { delay: 2.seconds }
  config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
  config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session
end

Remember that when we execute a command like rails db:create, it will create the primary database and the patients, but we’ll need to manually create users who will have read-only access in production (for development, I didn’t add the need for other users just to make it easier).

Friendly neighborhood

A tenant can be an individual user, but more frequently, it’s a group of users (like a customer organization) who share common access and privileges within the application instance. Each tenant’s data is isolated from and invisible to other tenants sharing the application instance, ensuring data security and privacy for all tenants.

The Rails ecosystem is just growing, and basecamp has been creating more and more interesting tools to drive this. One of them is ActiveRecord::Tenanted.
We do have some gems out there that already performed this role, like acts_as_tenant and apartment, but they don’t cover all the cases that ActiveRecord::Tenanted wants to embrace (which, for now, only works with the sqlite3 adapter).

I want to emphasize that the talk by Mike Dalessio – Multi-Tenant Rails: Everybody Gets a Database! explains very well everything they propose with the gem.

ActiveRecord::Tenanted extends the Rails framework to allow an application to have multiple tenant-specific databases. It provides data isolation by logically separating each tenant’s data, providing security mechanisms to help ensure safe use of Active Record and modifying the behavior of many parts of Rails, such as fragment caching, Active Job, Action Cable, Active Storage, Global ID, and database tasks. By providing an integrated framework support for tenancy, Active Record Tenanted ensures that developers can write most of their code as if they were in a single-tenant application, without compromising tenant data privacy and security.

The documentation is still under construction, so let’s learn while we dig a bit.
Keep in mind that if the model you’d like to orchestrate this already exists, or even if the creation of new Tenants is manual, we’ll need to have a migration strategy, but today we won’t take care of that.

n general (even for other ecosystem gems), when we create a new Tenant, another database for each instance will be initialized. That’s fine so far, but I had some challenges because I wanted to use the Practice registration to orchestrate the creation of Tenants, thinking that, for our application, whenever a new clinic adopted our system, we would just create this new Practice for the magic to happen, but not only that, I also wanted to maintain the isolation of Patients to follow the approach we started. But for now, this adaptation costs a bit, and I’ll explain the whys.

I had to keep some records in a centralized "global" database for Practice and also Session so that it’s possible for the same user to work in different places using the same account.

ActiveRecord::Base
├── GlobalRecord (global database read/write)
│    ├── Practice (manages Tenants, creates/destroys new Tenant databases)
│    └── Session (Authentication Sessions)
├── ApplicationRecord (tenanted database :primary)
│    ├── User
│    ├── Staff
│    └── Appointment
└── PatientsRecord (tenanted database :patients)
     └── Patient

So, after installing the gem, it’s good to emphasize that we’re on version 0.3.0 at the moment.
We have to adjust our configuration file for the databases. You can follow the changes here

// config/database.yml
development:
  global: # New global database that will handle Practice and Session
    <<: *default
    database: storage/development_global.sqlite3
  primary: # Database that handles User, Staff, and Appointment
    <<: *default
    tenanted: true # Notice that this database will be a Tenant
    database: storage/development/%{tenant}.sqlite3
    # Location for storage plus the name used by the Tenant
  patients: # Database that continues to handle only Patient
    <<: *default
    tenanted: true
    database: storage/development/patients_%{tenant}.sqlite3
    migrations_paths: db/patients_migrate
  patients_replica: # Patient replica also as a Tenant
    <<: *default
    tenanted: true
    replica: true
    database: storage/development/patients_%{tenant}.sqlite3

And we also need to update each Record:

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class
  tenanted :primary # Removing the old `connects_to database`

class GlobalRecord < ActiveRecord::Base
  # Write/read connection with the new Global database
  connects_to database: { writing: :global, reading: :global }

class PatientsRecord < ActiveRecord::Base
  tenanted :patients # Removing the old `connects_to database`

The gem integrates with Rails’ default tasks but also provides some new ones, like:

- `db:migrate:tenant:all` - Migrates all existing tenants
- `db:migrate:tenant ARTENANT=name` - Migrates a specific tenant
- `db:migrate:tenant` - Migrate default development tenant
- `db:tenant` - Set current tenant as ARTENANT, otherwise default environment
- `db:reset:tenant` - Delete and recreate all tenant databases from their schemas for the current environment

To authenticate a user, we need to know which Practice to define which Tenant is currently active. The default Tenant resolver of ActiveRecord::Tenanted uses the subdomain:

# config/initializers/active_record_tenanted.rb
Rails.application.configure do
  # Each model inherited from ApplicationRecord is Tenanted
  config.active_record_tenanted.connection_class = "ApplicationRecord"
  # Request subdomain is used to resolve the Tenant
  config.active_record_tenanted.tenant_resolver = ->(request) { request.subdomain }
end

At this moment, after countless alternative solutions, I realized that at least for now, it’s not possible to maintain multiple databases as tenanted, our primary, and the patients.
The gem was designed for a single connection class, so I had several problems trying to change connections without falling into the Shard Swapping Conflicts problem, both to override some middleware behaviors and define the current_tenant of PatientsRecord at different times (session creation or route calls, which was also just escaping what the gem provides us for now).

To run migrations for the patients database, it would be necessary to create a different generator than what the gem provides us, since it also only works with the configured class.
Besides that, also in the attempt to generate some seeds for local tests with multiple Practices, they always pointed to ApplicationRecord even when I forced the Patients context with something like PatientsRecord.with_tenant(tenant_name)

I created a discussion Multi-Database Support Limitations in the gem repository. Let’s follow the next steps.

So here I simply removed the database part that referenced patients and the model goes back under the umbrella of class Patient < ApplicationRecord.

After running the migrations, if we try to run the tests, we'll still have several problems that we need to fix. I won't put step-by-step instructions for the code but they are:

Since we moved Practice and Session to the global database, we have to remove the related foreign key constraints and validate the presence of ids in each model:

remove_foreign_key :staffs, :practices if foreign_key_exists?(:staffs, :practices)
remove_foreign_key :sessions, :users if foreign_key_exists?(:sessions, :users)
remove_foreign_key :patients, :practices if foreign_key_exists?(:patients, :practices)
remove_foreign_key :appointments, :practices if foreign_key_exists?(:appointments :practices)

We need to resolve subdomains locally (best solution I found):

# config/environments/development.rb
config.action_dispatch.tld_length = 0

Addition of this slug to facilitate finding Practice, e.g., clinic named "Development Clinic" generates a database with /{env_name}/development-clinic.sqlite3;

Need to add some methods to look for some references in other databases:

# models/practice.rb
def patients
  return Patient.none unless slug.present?
  ApplicationRecord.with_tenant(slug) { Patient.where(practice_id: id) }
end

# models/patient.rb
def practice
  return nil unless practice_id
  @practice ||= Practice.find_by(id: practice_id)
end

When creating a Practice, we have to create a Tenant for the Primary database:

# models/practice.rb
# I had several problems with this setup in test, the best path was to disable
after_create :setup_tenants, unless: -> { Rails.env.test? }
def setup_tenants
# ...
ApplicationRecord.create_tenant(slug)
# ...

I created a seed to facilitate our development tests, and now we can play a bit with what we have:

➜  healthcare_management git:(tenanted) ✗ rails c
Loading development environment (Rails 8.1.0.beta1)
# The migration will already create a default database for dev, which I overwrote the name
Defaulting current tenant to "test-medical-center"

# All tenants we have available
3.4.5 :001 > ApplicationRecord.tenants
 => ["development-clinic", "metro-health-center", "sunset-medical-group", "test-medical-center"]

# Setting the current tenant to one with data
3.4.5 :002 > ApplicationRecord.current_tenant = "development-clinic"
=> "development-clinic"

# Notice that just by changing the tenant, the gem takes care to search from that database
3.4.5 :003 > Patient.all

# Validates if the database schema exists, its structure, and if it's updated
  SCHEMA [tenant=development-clinic] (1.8ms)  SELECT sqlite_version(*) /*application='HealthcareManagement'*/
  SCHEMA [tenant=development-clinic] (3.5ms)  SELECT name FROM pragma_table_list WHERE schema <> 'temp' AND name NOT IN ('sqlite_sequence', 'sqlite_schema') AND name = 'schema_migrations' AND type IN ('table') /*application='HealthcareManagement'*/
  SCHEMA [tenant=development-clinic] (0.0ms)  SELECT name FROM pragma_table_list WHERE schema <> 'temp' AND name NOT IN ('sqlite_sequence', 'sqlite_schema') AND name = 'ar_internal_metadata' AND type IN ('table') /*application='HealthcareManagement'*/
  ActiveRecord::SchemaMigration Load [tenant=development-clinic] (0.1ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC /*application='HealthcareManagement'*/

# Actually executes the query
  Patient Load [tenant=development-clinic] (0.2ms)  SELECT "patients".* FROM "patients" /* loading for pp */ LIMIT 11 /*application='HealthcareManagement'*/
 =>

#  Data in our Patients in the context of Tenant development-clinic
[#<Patient id: 1, active: true, address: "101 Code Street, Dev City, ST 12345", blood_type: nil, created_at: "2025-09-17 21:29:02.824924000 +0000", date_of_birth: "1993-09-17", email: [FILTERED], emergency_contact_name: "Jamie Developer", emergency_contact_phone: "(555) 111-1112", first_name: "Alex", gender: nil, insurance_policy_number: nil, insurance_provider: nil, last_name: "Developer", phone: "(555) 111-1111", practice_id: 1, updated_at: "2025-09-17 21:29:02.824924000 +0000", tenant="development-clinic">,
 #<Patient id: 2, active: true, address: "102 Debug Ave, Dev City, ST 12345", blood_type: nil, created_at: "2025-09-17 21:29:02.829741000 +0000", date_of_birth: "1996-09-17", email: [FILTERED], emergency_contact_name: "Casey Tester", emergency_contact_phone: "(555) 111-2223", first_name: "Taylor", gender: nil, insurance_policy_number: nil, insurance_provider: nil, last_name: "Tester", phone: "(555) 111-2222", practice_id: 1, updated_at: "2025-09-17 21:29:02.829741000 +0000", tenant="development-clinic">]

# Switching tenant and doing the same search
3.4.5 :004 > ApplicationRecord.current_tenant = "sunset-medical-group"
 => "sunset-medical-group"
3.4.5 :005 > Patient.all
...
 =>
# Since we're using sequential ids it's easy to identify that they repeat in each database, which is expected, but we can identify that the users are not the same and also reference different practice_id
[#<Patient id: 1, active: true, address: "900 Sunset Drive, West Hills, CA 90210", blood_type: nil, created_at: "2025-09-17 21:29:02.866274000 +0000", date_of_birth: "1994-09-17", email: [FILTERED], emergency_contact_name: "Sky Sunset", emergency_contact_phone: "(555) 345-1112", first_name: "Phoenix", gender: nil, insurance_policy_number: nil, insurance_provider: nil, last_name: "Sunset", phone: "(555) 345-1111", practice_id: 3, updated_at: "2025-09-17 21:29:02.866274000 +0000", tenant="sunset-medical-group">,
 #<Patient id: 2, active: true, address: "901 Pacific View, West Hills, CA 90210", blood_type: nil, created_at: "2025-09-17 21:29:02.868483000 +0000", date_of_birth: "1999-09-17", email: [FILTERED], emergency_contact_name: "Bay Coastal", emergency_contact_phone: "(555) 345-2223", first_name: "Ocean", gender: nil, insurance_policy_number: nil, insurance_provider: nil, last_name: "Coastal", phone: "(555) 345-2222", practice_id: 3, updated_at: "2025-09-17 21:29:02.868483000 +0000", tenant="sunset-medical-group">]

# Another important point is that, as already explained, we can't do joins between the global database to search for a Practice from a Patient, for example, so we need the addition of methods to do this search, as in the examples of point 3, which will always result in a second query
3.4.5 :006 > Patient.first.practice.to_sql
  Patient Load [tenant=sunset-medical-group] (0.6ms)  SELECT "patients".* FROM "patients" ORDER BY "patients"."id" ASC LIMIT 1 /*application='HealthcareManagement'*/
  Practice Load (0.3ms)  SELECT "practices".* FROM "practices" WHERE "practices"."id" = 3 LIMIT 1 /*application='HealthcareManagement'*/

And if we want to test directly in the application, as mentioned above, the tenant_resolver uses the domain to switch the context of Tenant.

Ready, and now to illustrate even more how everything turned out:

ActiveRecord::Base
├── GlobalRecord (global database read/write)
│   ├── Practice (manages Tenants, creates/destroys new Tenant databases)
│   └── Session (Authentication Sessions)
└── ApplicationRecord (tenanted database :primary)
    ├── User
    ├── Staff
    ├── Appointment
   └── Patient

And that’s it for today. For the next article, we’ll cover the biggest advantages of ActiveRecord::Tenanted with caching, Action Cable, Action Job, Action Storage, and so on.

References:

• HealthCare Management
• Rails 8 Authentication
• Active Record Multiple Databases
• Active Record Basics
• Active Record Migrations
• Referential Integrity
• SQLite WAL Mode
• Rails 8 Beta – No PaaS Required
• What you need to know about SQLite
• Automatic Role Switching
• ActiveRecord::Tenanted
• Multi-Tenant Rails: Everybody Gets a Database!
• Shard Swapping Conflicts
• IBM Multi tenant
• Resolving subdomains locally

See you on GitHub! 🚀

We want to work with you. Check out our Services page!