DEV Community

Atlas Whoff
Atlas Whoff

Posted on

Building Multi-Tenant SaaS: Data Isolation Strategies Compared

#saas #database #node #architecture

The Fundamental Multi-Tenancy Question

When you build a SaaS, every customer is a tenant. How do you make sure Acme Corp's data never shows up for Globex Corp?

There are three architectural patterns, each with different tradeoffs.

Strategy 1: Shared Database, Shared Schema

All tenants share the same tables. Every row has a tenant_id.

CREATE TABLE projects (
  id         UUID PRIMARY KEY,
  tenant_id  UUID NOT NULL REFERENCES tenants(id),
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_projects_tenant ON projects(tenant_id);
Enter fullscreen mode Exit fullscreen mode 
// Every query MUST include tenant_id
async function getProjects(tenantId: string) {
  return db.projects.findMany({
    where: { tenantId }, // NEVER forget this
  });
}
Enter fullscreen mode Exit fullscreen mode

The risk: A missing WHERE tenant_id = ? leaks data across tenants.

Mitigation: Middleware that injects tenant context:

// Prisma extension that auto-adds tenantId
const tenantPrisma = (tenantId: string) => 
  prisma.$extends({
    query: {
      $allModels: {
        async findMany({ args, query }) {
          args.where = { ...args.where, tenantId };
          return query(args);
        },
        async findFirst({ args, query }) {
          args.where = { ...args.where, tenantId };
          return query(args);
        },
      },
    },
  });

// Usage: impossible to forget tenantId
const db = tenantPrisma(req.user.tenantId);
const projects = await db.projects.findMany();
Enter fullscreen mode Exit fullscreen mode

Good for: Most SaaS products. Simple, cheap, scales to thousands of tenants.

Strategy 2: Shared Database, Separate Schemas

Each tenant gets their own PostgreSQL schema:

CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.projects (
  id   UUID PRIMARY KEY,
  name TEXT NOT NULL
);
-- No tenant_id needed — isolation by schema
Enter fullscreen mode Exit fullscreen mode 
async function getTenantDb(tenantSlug: string) {
  return prisma.$executeRawUnsafe(`SET search_path = tenant_${tenantSlug}`);
}

async function getProjects(tenantSlug: string) {
  await getTenantDb(tenantSlug);
  // Queries now hit tenant_acme.projects automatically
  return prisma.$queryRaw`SELECT * FROM projects`;
}
Enter fullscreen mode Exit fullscreen mode

Good for: Compliance requirements, customers who want data separation guarantees, regulated industries.

Limitations: Schema migrations must run for every tenant. PostgreSQL has limits (~10k schemas per DB).

Strategy 3: Separate Databases

Each tenant gets their own database:

const tenantConnections = new Map<string, PrismaClient>();

function getTenantClient(tenantId: string): PrismaClient {
  if (!tenantConnections.has(tenantId)) {
    const tenant = await db.tenants.findUnique({ where: { id: tenantId } });
    const client = new PrismaClient({
      datasources: { db: { url: tenant.databaseUrl } },
    });
    tenantConnections.set(tenantId, client);
  }
  return tenantConnections.get(tenantId)!;
}
Enter fullscreen mode Exit fullscreen mode

Good for: Enterprise customers with strict compliance, high-value accounts, maximum isolation.

Expensive: Each database costs money. Connection pooling is complex.

The Hybrid Approach (Most Common) 

Free/Pro tenants → Shared database, shared schema (with tenant_id)
Enterprise tenants → Dedicated database (provisioned on demand)
Enter fullscreen mode Exit fullscreen mode 
async function getDb(userId: string): Promise<PrismaClient> {
  const user = await db.users.findUnique({
    where: { id: userId },
    include: { organization: true },
  });

  if (user.organization.plan === 'enterprise' && user.organization.dedicatedDbUrl) {
    return getDedicatedClient(user.organization.dedicatedDbUrl);
  }

  return getSharedClient(user.organization.id);
}
Enter fullscreen mode Exit fullscreen mode

Row-Level Security (PostgreSQL)

Enforce isolation at the database level:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Application sets tenant context on each connection
SET app.current_tenant = 'tenant-uuid-here';
-- Now all queries on this connection are automatically scoped
Enter fullscreen mode Exit fullscreen mode

Even if application code forgets WHERE tenant_id = ?, the database rejects unauthorized reads.

Checklist for Multi-Tenant Security 

☐ All DB queries include tenant scope (or RLS enforces it)
☐ API endpoints validate tenant ownership before returning data
☐ File uploads use tenant-prefixed paths (s3://bucket/tenant-id/file)
☐ Background jobs include tenant context
☐ Stripe customer IDs mapped to tenants
☐ Search indexes scoped to tenant
☐ Audit logs record tenant_id
Enter fullscreen mode Exit fullscreen mode

The most common security bugs in SaaS apps are tenant isolation failures. A user accidentally sees another company's data—and you're done.

Multi-tenant architecture with Prisma extensions, RLS, and organization management: Whoff Agents AI SaaS Starter Kit.

Top comments (0)