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!