SaaS Multitenant Architecture (Database per Tenant)

SaaS Multi-tenancy: Separate Database per Tenant

Maximum isolation: each client gets their own database. Data physically separated — cross-tenant leak impossible. More complex in management, more expensive in infrastructure, required for some compliance.

When to Choose

Database-per-tenant works if:

• Compliance requires physical isolation (HIPAA, financial data)
• Clients require data export/deletion capability
• Different tenants have different schemas or versions
• Need independent DB-level backups

Doesn't work if:

• Thousands of small tenants (connection overhead)
• Need cross-tenant analytics
• Limited budget

Connection Pool Management

// lib/db/tenant-manager.ts
import { PrismaClient } from '@prisma/client';

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

export async function getTenantDb(tenantId: string): Promise<PrismaClient> {
  if (clientPool.has(tenantId)) {
    return clientPool.get(tenantId)!;
  }

  const tenant = await masterDb.tenant.findUniqueOrThrow({
    where: { id: tenantId },
    select: { databaseUrl: true }
  });

  const client = new PrismaClient({
    datasources: {
      db: { url: tenant.databaseUrl }
    },
  });

  clientPool.set(tenantId, client);

  setTimeout(() => {
    clientPool.get(tenantId)?.$disconnect();
    clientPool.delete(tenantId);
  }, 30 * 60 * 1000); // 30 minutes

  return client;
}

Database Provisioning on Onboarding

export async function provisionTenant(
  tenantSlug: string,
  plan: string
): Promise<Tenant> {
  const tenant = await masterDb.tenant.create({
    data: {
      slug: tenantSlug,
      plan,
      status: 'PROVISIONING',
    }
  });

  try {
    const dbName = `tenant_${tenantSlug.replace(/-/g, '_')}`;
    const dbUser = `user_${tenant.id.substring(0, 8)}`;
    const dbPassword = generateSecurePassword();

    const adminPool = new Pool({ connectionString: process.env.POSTGRES_ADMIN_URL });

    await adminPool.query(`CREATE DATABASE "${dbName}"`);
    await adminPool.query(`CREATE USER "${dbUser}" WITH PASSWORD '${dbPassword}'`);
    await adminPool.query(`GRANT ALL PRIVILEGES ON DATABASE "${dbName}" TO "${dbUser}"`);

    const databaseUrl = `postgresql://${dbUser}:${dbPassword}@${process.env.DB_HOST}/${dbName}`;

    const { execSync } = await import('child_process');
    execSync(`DATABASE_URL="${databaseUrl}" npx prisma migrate deploy`, {
      env: { ...process.env, DATABASE_URL: databaseUrl }
    });

    await masterDb.tenant.update({
      where: { id: tenant.id },
      data: {
        databaseUrl,
        databaseName: dbName,
        status: 'ACTIVE',
      }
    });

    return tenant;
  } catch (error) {
    await masterDb.tenant.update({
      where: { id: tenant.id },
      data: { status: 'FAILED' }
    });
    throw error;
  }
}

Migrations: Roll Out to All Tenants

async function migrateAllTenants() {
  const tenants = await masterDb.tenant.findMany({
    where: { status: 'ACTIVE' },
    select: { id: true, slug: true, databaseUrl: true }
  });

  const results = { success: [] as string[], failed: [] as string[] };

  for (let i = 0; i < tenants.length; i += 10) {
    const batch = tenants.slice(i, i + 10);

    await Promise.allSettled(
      batch.map(async (tenant) => {
        try {
          execSync(`npx prisma migrate deploy`, {
            env: { ...process.env, DATABASE_URL: tenant.databaseUrl },
            stdio: 'pipe',
          });
          results.success.push(tenant.slug);
        } catch (error) {
          results.failed.push(tenant.slug);
          console.error(`Failed to migrate ${tenant.slug}:`, error);
        }
      })
    );
  }

  console.log(`Success: ${results.success.length}, Failed: ${results.failed.length}`);
}

Per-Tenant Backups

#!/bin/bash
TENANT_ID=$1
DB_URL=$(psql $MASTER_DB_URL -t -c "SELECT database_url FROM tenants WHERE id='$TENANT_ID'")

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="backup_${TENANT_ID}_${TIMESTAMP}.dump"

pg_dump "$DB_URL" -Fc -f "$BACKUP_FILE"

aws s3 cp "$BACKUP_FILE" \
  "s3://my-backups/tenants/${TENANT_ID}/${BACKUP_FILE}" \
  --server-side-encryption aws:kms

rm "$BACKUP_FILE"

Development of database-per-tenant architecture with auto-provisioning — 5–10 working days.