Multi-Tenant Data Isolation Strategies
Schema-per-tenant, shared schema with tenant_id, and dynamic connections—trade-offs for CRM and SaaS backends.
Multi-tenant backends power CRMs, campaign tools, and B2B SaaS. The hard part is not adding a tenant_id column—it is guaranteeing one tenant never sees another’s data under load, bugs, or bad queries.
Strategy 1: Shared database, tenant_id column
Pros: Simple ops, one migration path, easy reporting across tenants.
Cons: Every query must filter by tenant_id. One missing WHERE is a data leak.
Mitigation: Row-level security in Postgres, middleware that injects tenant context, integration tests per tenant.
Strategy 2: Schema per tenant
Pros: Strong isolation, easier per-tenant backup/restore.
Cons: Migration fan-out, connection pool size, operational complexity at 100+ tenants.
Use when: Compliance or enterprise contracts require hard boundaries.
Strategy 3: Database per tenant
Pros: Maximum isolation.
Cons: Expensive and heavy to operate at scale. Rare except for largest accounts.
Dynamic connections (Node.js)
Some systems resolve tenant from JWT or subdomain, then open the correct MySQL/Postgres connection from a pool map. Cache pool references; cap max tenants per instance.
Testing isolation
- Automated tests that user A cannot read user B’s resource IDs.
- Periodic audit queries:
SELECT tenant_id, COUNT(*) ... HAVING ...anomalies.
Takeaway
I have built shared-schema with strict middleware and dynamic MySQL per tenant in CRM workloads. Choose based on tenant count, compliance, and ops capacity—not copy-paste from a blog (including this one) without sizing your constraints.