Building Scalable Multi-Tenant SaaS Backends with Node.js, TypeScript & PostgreSQL

1. Isolation Models: Row-Level Security vs Schema per Tenant

When evaluating multi-tenant database designs, three major patterns emerge: Shared Database / Shared Schema (with tenant_id), Shared Database / Separate Schema, and Separate Database per Tenant.

• Shared Schema + RLS: High resource efficiency, lower maintenance cost, supported natively by PostgreSQL.
• Schema per Tenant: Harder data boundary separation, easier tenant backup & data deletion compliance.
• Database per Tenant: Maximum isolation, suitable for strict compliance enterprise tiers (HIPAA/SOC2), higher cloud cost.

2. Implementing Row-Level Security in TypeScript

PostgreSQL Row-Level Security (RLS) lets you enforce access controls directly at the database engine layer, preventing accidental data leaks from application bug oversights.

-- Enable RLS on organization data table
ALTER TABLE workspaces ENABLE ROW LEVEL SECURITY;

-- Create policy enforcing tenant scope
CREATE POLICY tenant_isolation_policy ON workspaces
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id'));

3. Managing Dynamic Connection Pools in Node.js

When serving thousands of active tenants, initializing separate connection pools per schema exhausts PostgreSQL memory. A dynamic pool manager wraps connection requests with session variables.

import { Pool, PoolClient } from 'pg';

export async function executeTenantQuery<T>(
  tenantId: string,
  queryText: string,
  params: any[] = []
): Promise<T[]> {
  const client: PoolClient = await pool.connect();
  try {
    await client.query("SELECT set_config('app.current_tenant_id', $1, true)", [tenantId]);
    const res = await client.query(queryText, params);
    return res.rows;
  } finally {
    client.release();
  }
}