Tenant Database Modes

In Shared Database Mode, all tenants live inside a single database. Each row has a tenant-id column and the platform filters every query to the current tenant's rows. It's the simplest, cheapest and most operations-friendly multi-tenant pattern.

How It Works

Every table that holds tenant data has a tenant_id column. The platform's data access layer automatically attaches WHERE tenant_id = ? to every query. The current tenant ID is set when the user signs in.

Pros

• Single backup, single restore.
• Single schema migration covers all tenants.
• Lowest infrastructure cost — one DB connection pool serves everyone.
• Cross-tenant analytics are easy (one query, no joins across DBs).

Cons

• Single point of failure: If one tenant fills the database with a runaway dataset, everyone slows down.
• Compliance: Some industries (healthcare, banking) require physical data separation.
• Risk of leakage: A single forgotten WHERE clause in custom code could expose another tenant's data.
• Per-tenant backups are awkward — restoring just Tenant B requires a row-level extract from a full backup.

Worked Examples

• SaaS with hundreds of small tenants: Shared DB is ideal — cost per tenant stays low; one schema; uniform feature releases.
• Internal multi-business-unit platform: Different teams as tenants; cross-team reporting is a common need; shared DB makes that trivial.
• Pre-launch / MVP: Start in shared mode while validating market. Migrate selected high-value tenants to separate later.

Tip: Audit every custom SQL query for an explicit tenant_id filter before deploying. The platform's ORM does this automatically, but raw SQL bypasses it. Use the “query audit” command-line tool to scan custom code.

In Separate Database Mode, each tenant gets its own physical database. The application chooses which database to connect to based on the current tenant. This is the multi-tenant pattern most appropriate for high-security or regulated industries.

How It Works

The platform maintains a small directory database (tenant lookups, billing, sign-in) plus one application database per tenant. On every request, after authentication the platform switches to the appropriate tenant DB and serves the rest of the request from there.

Pros

• Strong isolation: A bug or misconfiguration cannot leak data between tenants. The DB connection is fundamentally different.
• Per-tenant backups are trivial — back up the tenant's DB.
• Per-tenant restore is a simple drop-and-load — no row-level surgery.
• Customisation: If a tenant needs a custom column, you can add it to just their schema.
• Compliance: Often required by HIPAA, banking, government contracts.

Cons

• Higher infrastructure cost — many DB instances or schemas.
• Schema migration overhead — every migration runs N times, increasing the window for incidents.
• Cross-tenant analytics are harder — requires a separate data warehouse / ETL.
• Connection-pool tuning matters more — too many tenants exhaust DB connections.

Worked Examples

• Healthcare SaaS: Each hospital is a separate DB so a HIPAA audit can prove physical separation.
• Government tenants: Each agency requires a dedicated DB with its own backup chain.
• Enterprise on-prem: Customer chooses to host their own DB; the application connects to it remotely.
• High-revenue tenants: A “premium tier” gets a separate DB for performance isolation; lower-tier tenants share a DB.

Note: Hybrid deployments are supported — some tenants on shared, some on separate. The mode is per-tenant, so you can graduate a tenant from shared to separate without affecting the rest.

You can change a tenant's mode without losing data, but it's not instant. This article walks through migrating a tenant between Shared and Separate database modes in either direction.

Shared → Separate

1. Schedule: Pick a low-traffic window for the tenant. Set the tenant to “read-only” in the directory.
2. Provision: Create a new database (e.g. tenant_123_db) and run schema migrations.
3. Snapshot: Use the platform's tenant-export tool to extract every row tagged with this tenant's ID.
4. Load: Import into the new DB. Validate row counts per table.
5. Switch: Update the tenant's directory entry to point at the new DB. Existing sessions are terminated.
6. Verify: Log in as a test user, run smoke tests against the new DB.
7. Cleanup: After 24-48 hours of stability, delete the now-orphaned rows from the shared DB.

Separate → Shared

1. Schedule: Low-traffic window. Tenant goes read-only.
2. Export: Dump the tenant's DB.
3. Import: Insert into shared DB, attaching the tenant ID to every row.
4. Switch: Update directory; sessions terminated.
5. Verify: Smoke test as above.
6. Cleanup: Drop the separate DB once you're confident.

Worked Examples

• Tenant graduation: A customer outgrew the shared pool and signed up for premium hosting — migrate them to a dedicated DB during their off-hours.
• Tenant consolidation: Two small tenants on separate DBs cost more to maintain than they're worth — fold them back into shared.
• Acquisition: Acquired company arrives with their own DB; copy into your shared infrastructure to onboard.

Warning: Never skip the row-count verification step. A silent partial copy is the worst failure mode and is hard to detect later. Compare per-table counts and total record counts before declaring success.