SaaS Multitenant Architecture (Schema per Tenant)

SaaS Multi-tenancy: Schema per Tenant

Schema-per-tenant is a compromise between shared DB and database-per-tenant. All tenants in one PostgreSQL database, but each in its own schema (namespace). Good isolation with less overhead.

Concept

PostgreSQL database:
  schema: public         → shared tables (tenants, plans)
  schema: tenant_acme    → Acme client data
  schema: tenant_globex  → Globex client data
  schema: tenant_initech → Initech client data

PostgreSQL allows up to ~10,000 schemas in one database.

Creating Schema on Registration

// lib/tenant-provisioning.ts
export async function createTenantSchema(tenantSlug: string): Promise<string> {
  const schemaName = `tenant_${tenantSlug.replace(/-/g, '_')}`;

  await adminDb.$transaction(async (tx) => {
    await tx.$executeRawUnsafe(`CREATE SCHEMA "${schemaName}"`);

    await tx.$executeRawUnsafe(`
      SET search_path TO "${schemaName}";

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

      CREATE TABLE team_members (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id TEXT NOT NULL,
        role TEXT NOT NULL DEFAULT 'member',
        joined_at TIMESTAMPTZ DEFAULT NOW()
      );

      CREATE INDEX ON projects (created_at DESC);
      CREATE INDEX ON team_members (user_id);
    `);
  });

  return schemaName;
}

Prisma: Dynamic Schema

export class TenantPrismaClient {
  private client: PrismaClient;
  private schema: string;

  constructor(schema: string) {
    this.schema = schema;
    this.client = new PrismaClient();

    this.client.$use(async (params, next) => {
      await this.client.$executeRawUnsafe(
        `SET search_path TO "${this.schema}", public`
      );
      return next(params);
    });
  }

  get db() { return this.client; }

  async disconnect() {
    await this.client.$disconnect();
  }
}

const clients = new Map<string, TenantPrismaClient>();

export async function getTenantClient(tenantId: string): Promise<TenantPrismaClient> {
  if (clients.has(tenantId)) {
    return clients.get(tenantId)!;
  }

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

  const client = new TenantPrismaClient(tenant.schemaName);
  clients.set(tenantId, client);
  return client;
}

Alternative: Kysely with Dynamic Schema

import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';

function createTenantDb(schemaName: string) {
  const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
  });

  pool.on('connect', (client) => {
    client.query(`SET search_path TO "${schemaName}", public`);
  });

  return new Kysely({
    dialect: new PostgresDialect({ pool }),
  });
}

const tenantDb = createTenantDb('tenant_acme');

const projects = await tenantDb
  .selectFrom('projects')
  .selectAll()
  .orderBy('created_at', 'desc')
  .execute();

Migrations on All Schemas

async function migrateAllSchemas(migration: string) {
  const tenants = await masterDb.tenant.findMany({
    select: { schemaName: true, slug: true }
  });

  for (const tenant of tenants) {
    console.log(`Migrating ${tenant.slug}...`);
    try {
      await adminDb.$executeRawUnsafe(`
        SET search_path TO "${tenant.schemaName}";
        ${migration}
      `);
    } catch (error) {
      console.error(`Failed: ${tenant.slug}`, error);
    }
  }
}

migrateAllSchemas(`
  ALTER TABLE projects ADD COLUMN IF NOT EXISTS archived_at TIMESTAMPTZ;
  CREATE INDEX IF NOT EXISTS projects_archived_at ON projects (archived_at);
`);

Cross-tenant Queries (Analytics)

SELECT
  t.slug as tenant,
  COUNT(p.id) as project_count
FROM public.tenants t
CROSS JOIN LATERAL (
  SELECT id FROM tenant_acme.projects
  UNION ALL
  SELECT id FROM tenant_globex.projects
) p(id)
GROUP BY t.slug;

Schema-per-tenant architecture with migration tool — 4–7 working days.