Implementing Multi-Tenant Database Schemas in PostgreSQL

Introduction

Multi-tenancy allows serving multiple customers from a single database. PostgreSQL supports schema-based and shared-table approaches with proper isolation.

Prerequisites

• PostgreSQL >=12
• Understanding of schemas and RLS

Step 1: Schema-Based Multi-Tenancy

Create separate schema per tenant:

-- Create tenant schemas
CREATE SCHEMA tenant_1;
CREATE SCHEMA tenant_2;

-- Create identical table structure in each schema
CREATE TABLE tenant_1.users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE tenant_2.users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

Step 2: Dynamic Schema Switching

import { Pool } from 'pg';

class TenantDatabase {
  private pool: Pool;

  constructor() {
    this.pool = new Pool({
      connectionString: process.env.DATABASE_URL,
    });
  }

  async withTenant<T>(tenantId: string, callback: (client: any) => Promise<T>): Promise<T> {
    const client = await this.pool.connect();
    
    try {
      // Set search path to tenant schema
      await client.query(`SET search_path TO tenant_${tenantId}, public`);
      return await callback(client);
    } finally {
      client.release();
    }
  }

  async getTenantUsers(tenantId: string) {
    return this.withTenant(tenantId, async (client) => {
      const result = await client.query('SELECT * FROM users');
      return result.rows;
    });
  }

  async createTenantUser(tenantId: string, name: string, email: string) {
    return this.withTenant(tenantId, async (client) => {
      const result = await client.query(
        'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
        [name, email]
      );
      return result.rows[0];
    });
  }
}

export const tenantDb = new TenantDatabase();

Step 3: Shared-Table with RLS

Alternative approach using single table with tenant isolation:

-- Create shared table with tenant_id
CREATE TABLE shared_users (
  id SERIAL PRIMARY KEY,
  tenant_id VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  UNIQUE(tenant_id, email)
);

-- Enable RLS
ALTER TABLE shared_users ENABLE ROW LEVEL SECURITY;

-- Create policy for tenant isolation
CREATE POLICY tenant_isolation ON shared_users
  USING (tenant_id = current_setting('app.current_tenant'));

-- Create policy for inserts
CREATE POLICY tenant_insert ON shared_users
  FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant'));

Step 4: RLS Implementation

import { Pool } from 'pg';

class RLSTenantDatabase {
  private pool: Pool;

  constructor() {
    this.pool = new Pool({
      connectionString: process.env.DATABASE_URL,
    });
  }

  async withTenantContext<T>(tenantId: string, callback: (client: any) => Promise<T>): Promise<T> {
    const client = await this.pool.connect();
    
    try {
      // Set tenant context
      await client.query('SET app.current_tenant = $1', [tenantId]);
      return await callback(client);
    } finally {
      client.release();
    }
  }

  async getUsers(tenantId: string) {
    return this.withTenantContext(tenantId, async (client) => {
      const result = await client.query('SELECT id, name, email FROM shared_users');
      return result.rows;
    });
  }

  async createUser(tenantId: string, name: string, email: string) {
    return this.withTenantContext(tenantId, async (client) => {
      const result = await client.query(
        'INSERT INTO shared_users (tenant_id, name, email) VALUES ($1, $2, $3) RETURNING *',
        [tenantId, name, email]
      );
      return result.rows[0];
    });
  }
}

export const rlsDb = new RLSTenantDatabase();

Step 5: Tenant Management

-- Tenant registry table
CREATE TABLE tenants (
  id VARCHAR(50) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  active BOOLEAN DEFAULT true
);

-- Function to create new tenant schema
CREATE OR REPLACE FUNCTION create_tenant_schema(tenant_id VARCHAR(50))
RETURNS void AS $$
BEGIN
  EXECUTE format('CREATE SCHEMA %I', 'tenant_' || tenant_id);
  
  -- Create tables in new schema
  EXECUTE format('
    CREATE TABLE %I.users (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      created_at TIMESTAMP DEFAULT NOW()
    )', 'tenant_' || tenant_id);
    
  -- Add indexes
  EXECUTE format('CREATE INDEX %I ON %I.users (email)', 
    'idx_tenant_' || tenant_id || '_users_email',
    'tenant_' || tenant_id);
END;
$$ LANGUAGE plpgsql;

Step 6: API Integration

import { NextRequest, NextResponse } from 'next/server';
import { tenantDb } from '@/lib/tenant-db';

export async function GET(
  req: NextRequest,
  { params }: { params: { tenantId: string } }
) {
  try {
    const users = await tenantDb.getTenantUsers(params.tenantId);
    return NextResponse.json({ users });
  } catch (error) {
    return NextResponse.json({ error: 'Failed to fetch users' }, { status: 500 });
  }
}

export async function POST(
  req: NextRequest,
  { params }: { params: { tenantId: string } }
) {
  try {
    const { name, email } = await req.json();
    const user = await tenantDb.createTenantUser(params.tenantId, name, email);
    return NextResponse.json({ user }, { status: 201 });
  } catch (error) {
    return NextResponse.json({ error: 'Failed to create user' }, { status: 500 });
  }
}

Summary

PostgreSQL multi-tenancy can be achieved through schema separation for strong isolation or shared tables with RLS for simpler management. Choose based on tenant count, data size, and isolation requirements.