Multi-Tenant in PostgreSQL with NestJS and Prisma

Every SaaS application faces the same question: how to store data for multiple clients in a single database? The answer affects security, performance, operational complexity, and how you scale. This guide covers the three main approaches with practical implementations in PostgreSQL, NestJS, and Prisma ORM.

The Three Approaches

1. Shared Tables (Pool Model)

All tenants share the same tables with a tenant_id column.

flowchart TD
    subgraph Database ["Shared Database (saas_db)"]
        subgraph Tables ["Shared Tables (Filter by tenant_id)"]
            Users["Table: users (id, tenant_id, name, email)"]
            Orders["Table: orders (id, tenant_id, user_id, amount)"]
        end
    end
    TenantAcme["Client / Tenant: acme"] -->|WHERE tenant_id = 'acme'| Users
    TenantGlobex["Client / Tenant: globex"] -->|WHERE tenant_id = 'globex'| Users

2. Separate Schemas (Bridge Model)

Each tenant gets its own PostgreSQL schema inside a shared database.

flowchart TD
    subgraph DB ["Database: saas_db"]
        subgraph SchemaAcme ["Schema: acme"]
            UsersA["users"]
            OrdersA["orders"]
        end
        subgraph SchemaGlobex ["Schema: globex"]
            UsersG["users"]
            OrdersG["orders"]
        end
    end
    TenantAcme["Tenant: acme"] -->|search_path = acme| SchemaAcme
    TenantGlobex["Tenant: globex"] -->|search_path = globex| SchemaGlobex

3. Separate Databases (Silo Model)

Each tenant gets its own PostgreSQL database.

flowchart TD
    subgraph Server ["PostgreSQL Instance"]
        subgraph DBAcme ["Database: tenant_acme"]
            UsersA["users"]
            OrdersA["orders"]
        end
        subgraph DBGlobex ["Database: tenant_globex"]
            UsersG["users"]
            OrdersG["orders"]
        end
    end
    TenantAcme["Tenant: acme"] --> DBAcme
    TenantGlobex["Tenant: globex"] --> DBGlobex

Approach 1: Shared Tables

DDL and RLS Implementation in PostgreSQL

-- Create tables with tenant_id
CREATE TABLE tenants (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL REFERENCES tenants(id),
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT now(),
    UNIQUE(tenant_id, id),
    UNIQUE(tenant_id, email) -- Unique email per tenant
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL REFERENCES tenants(id),
    user_id INTEGER NOT NULL,
    amount NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT now(),
    FOREIGN KEY (tenant_id, user_id) REFERENCES users(tenant_id, id)
);

-- Create indexes including tenant_id
CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE INDEX idx_users_tenant_email ON users(tenant_id, email);
CREATE INDEX idx_orders_tenant ON orders(tenant_id);
CREATE INDEX idx_orders_tenant_user ON orders(tenant_id, user_id);

-- Enable Row Level Security (RLS)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create application role and RLS policies
CREATE ROLE app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

CREATE POLICY tenant_isolation_users ON users
    USING (tenant_id = current_setting('app.current_tenant'))
    WITH CHECK (tenant_id = current_setting('app.current_tenant'));

CREATE POLICY tenant_isolation_orders ON orders
    USING (tenant_id = current_setting('app.current_tenant'))
    WITH CHECK (tenant_id = current_setting('app.current_tenant'));

Integration in NestJS with Prisma and Row Level Security (RLS)

To apply RLS in Prisma, it is necessary to execute SET LOCAL app.current_tenant before executing the queries within the context of the current request. In NestJS we can achieve this through AsyncLocalStorage and NestJS Interceptors/Middleware, using Prisma extensions ($extends).

1. Tenant Context with AsyncLocalStorage (`tenant.context.ts`)

import { AsyncLocalStorage } from 'async_hooks';

export const tenantStorage = new AsyncLocalStorage<string>();

2. NestJS Middleware (`tenant.middleware.ts`)

import { Injectable, NestMiddleware, BadRequestException } from '@nestjs/common';
import { Request, Response, NextFunction } from 'express';
import { tenantStorage } from './tenant.context';

@Injectable()
export class TenantMiddleware implements NestMiddleware {
    use(req: Request, res: Response, next: NextFunction) {
        const tenantId = req.headers['x-tenant-id'] as string;

        if (!tenantId) {
            throw new BadRequestException('The X-Tenant-ID header is required.');
        }

        tenantStorage.run(tenantId, () => {
            next();
        });
    }
}

3. Extended Prisma Service with RLS (`prisma.service.ts`)

import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';
import { tenantStorage } from './tenant.context';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
    async onModuleInit() {
        await this.$connect();
    }

    // Prisma Client dynamically configured based on the tenant context
    get tenantClient() {
        const tenantId = tenantStorage.getStore();

        if (!tenantId) {
            throw new Error('No tenant context has been defined for this request.');
        }

        return this.$extends({
            query: {
            $allModels: {
                async $allOperations({ args, query }) {
                // We execute in a transaction using set_config
                const [, result] = await PrismaService.prototype.$transaction.call(this, [
                    this.$executeRaw`SELECT set_config('app.current_tenant', ${tenantId}, TRUE)`,
                    query(args),
                ]);
                return result;
                },
            },
            },
        });
    }
}

4. Usage in a NestJS Service (`users.service.ts`)

import { Injectable } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Injectable()
export class UsersService {
    constructor(private readonly prisma: PrismaService) {}

    async findAllUsers() {
        // The query is automatically filtered thanks to RLS in PostgreSQL
        return this.prisma.tenantClient.user.findMany();
    }

    async createUser(data: { name: string; email: string }) {
        const tenantId = tenantStorage.getStore();

        return this.prisma.tenantClient.user.create({
            data: {
                ...data,
                tenantId, // Must match the variable set in RLS
            },
        });
    }
}

Pros and Cons

Pros:

Simple deployment and maintenance.
Ease of making cross-tenant queries and analytics.
Efficient connection pooling.
No schema migration complexity per tenant.

Cons:

Risk of noisy neighbor (one tenant’s queries affect the others).
RLS overhead in each query.
Harder to offer specific customizations per tenant.
Single point of failure.

Approach 2: Separate Schemas

Implementation in PostgreSQL

-- Function to create a new schema per tenant
CREATE OR REPLACE FUNCTION create_tenant_schema(tenant_name TEXT)
RETURNS VOID AS $$
BEGIN
    EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', tenant_name);

    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_name);

    EXECUTE format('
        CREATE TABLE %I.orders (
            id SERIAL PRIMARY KEY,
            user_id INTEGER NOT NULL REFERENCES %I.users(id),
            amount NUMERIC(10,2) NOT NULL,
            created_at TIMESTAMP DEFAULT now()
        )', tenant_name, tenant_name);

    EXECUTE format('CREATE INDEX ON %I.users(email)', tenant_name);
    EXECUTE format('CREATE INDEX ON %I.orders(user_id)', tenant_name);
END;
$$ LANGUAGE plpgsql;

SELECT create_tenant_schema('acme');
SELECT create_tenant_schema('globex');

Integration in NestJS with Prisma (Dynamic Schema Handling)

Prisma allows configuring the ?schema= parameter in the PostgreSQL connection URL. To handle dynamic schemas, we create a Prisma client manager with caching (connection pool per schema).

1. Prisma Client Manager per Schema (`tenant-prisma-factory.service.ts`)

import { Injectable, OnModuleDestroy } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class TenantPrismaFactoryService implements OnModuleDestroy {
    private clients: Map<string, PrismaClient> = new Map();

    getPrismaClientForTenant(tenantSchema: string): PrismaClient {
        if (!this.clients.has(tenantSchema)) {
            const baseUrl = process.env.DATABASE_URL; // e.g., postgresql://user:pass@localhost:5432/saas_db
            const tenantUrl = `${baseUrl}?schema=${tenantSchema}`;

            const client = new PrismaClient({
                datasources: {
                    db: {
                       url: tenantUrl,
                    },
                },
            });

            this.clients.set(tenantSchema, client);
        }

        return this.clients.get(tenantSchema)!;
    }

    async onModuleDestroy() {
        for (const client of this.clients.values()) {
            await client.$disconnect();
        }
    }
}

2. Controller and Service in NestJS (`orders.service.ts`)

import { Injectable, Inject, Scope } from '@nestjs/common';
import { REQUEST } from '@nestjs/core';
import { Request } from 'express';
import { TenantPrismaFactoryService } from './tenant-prisma-factory.service';

@Injectable({ scope: Scope.REQUEST })
export class OrdersService {
    constructor(
        @Inject(REQUEST) private readonly request: Request,
        private readonly prismaFactory: TenantPrismaFactoryService,
    ) {}

    private get prisma() {
        const tenantSchema = this.request.headers['x-tenant-id'] as string;
        return this.prismaFactory.getPrismaClientForTenant(tenantSchema);
    }

    async getOrders() {
        return this.prisma.order.findMany();
    }
}

Migrations with Prisma Across Multiple Schemas

To apply Prisma migrations in each tenant schema, we can create an executable script within the NestJS project:

import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';

async function migrateAllSchemas() {
    const mainPrisma = new PrismaClient();

    // Get all created tenant schemas
    const schemas: { schema_name: string }[] = await mainPrisma.$queryRaw`
        SELECT schema_name
        FROM information_schema.schemata
        WHERE schema_name NOT IN ('public', 'pg_catalog', 'information_schema')
    `;

    for (const { schema_name } of schemas) {
        console.log(`Applying migrations to schema: ${schema_name}`);
        const tenantUrl = `${process.env.DATABASE_URL}?schema=${schema_name}`;

        execSync(`npx prisma migrate deploy`, {
            env: { ...process.env, DATABASE_URL: tenantUrl },
            stdio: 'inherit',
        });
    }

    await mainPrisma.$disconnect();
}

migrateAllSchemas().catch(console.error);

Pros and Cons

Pros:

Good isolation of tenants within a shared database.
Specific customizations per tenant are easier.
Ability to dump and restore individual schemas via logical backups.
Better protection against noisy neighbors than the shared tables approach.

Cons:

Complexity in schema migrations (you must update all schemas).
Connection pooling is more challenging.
Cross-tenant queries require schema prefixes.
Harder to monitor and maintain as the number of tenants grows.

Approach 3: Separate Databases

Implementation

# Create database for each tenant
createdb -O app_user tenant_acme
createdb -O app_user tenant_globex

# Apply schema using Prisma CLI
DATABASE_URL="postgresql://app_user:pass@localhost:5432/tenant_acme" npx prisma migrate deploy
DATABASE_URL="postgresql://app_user:pass@localhost:5432/tenant_globex" npx prisma migrate deploy

Connection Routing in NestJS with Prisma

In NestJS we create a service in charge of managing the connection pools for each independent database.

import { Injectable, OnModuleDestroy } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class MultiDatabaseService implements OnModuleDestroy {
    private tenantClients: Map<string, PrismaClient> = new Map();

    getDatabaseClient(tenantId: string): PrismaClient {
        if (!this.tenantClients.has(tenantId)) {
            const dbHost = process.env.DB_HOST || 'localhost';
            const dbPort = process.env.DB_PORT || '5432';
            const dbUser = process.env.DB_USER || 'postgres';
            const dbPass = process.env.DB_PASS || 'secret';

            const dsn = `postgresql://${dbUser}:${dbPass}@${dbHost}:${dbPort}/tenant_${tenantId}?schema=public`;

            const client = new PrismaClient({
                datasources: {
                    db: { url: dsn },
                },
            });

            this.tenantClients.set(tenantId, client);
        }

        return this.tenantClients.get(tenantId)!;
    }

    async onModuleDestroy() {
        for (const client of this.tenantClients.values()) {
            await client.$disconnect();
        }
    }
}

Pros and Cons

Pros:

The strongest isolation (security and performance).
Easy to backup and restore per tenant.
Ability to place high-value tenants on dedicated hardware.
The simplest mental model.

Cons:

The highest operational complexity.
Connection pooling per database.
Cross-tenant analytics require additional infrastructure.
Higher resource overhead.

Choosing the Right Approach

Decision Matrix

Factor	Shared Tables	Separate Schemas	Separate Databases
Tenant count	1000+	100–1000	10–100
Isolation needs	Low	Medium	High
Customization	None	Some	Total
Ops complexity	Low	Medium	High
Cross-tenant queries	Easy	Medium	Hard
Backup granularity	Database	Schema (logical backups)	Per tenant

Recommendations

Choose Shared Tables when:

You have many small tenants (B2C SaaS).
Tenants have identical schemas.
You need easy cross-tenant analytics.
Operational simplicity is your priority.

Choose Separate Schemas when:

You have a moderate tenant count.
Tenants might need slight customizations.
You need better isolation than shared tables.
Individual tenant restores are required.

Choose Separate Databases when:

Tenants have strict compliance requirements.
You have a few high-value enterprise clients.
Tenants need complete isolation.
Performance guarantees per tenant are required.

Hybrid Approach

Many SaaS applications use a hybrid model:

flowchart TD
    subgraph SharedDB ["Shared Database (Shared Tables + RLS)"]
        SmallCo1["tenant_id: small_co_1"]
        SmallCo2["tenant_id: small_co_2"]
        SmallCo3["tenant_id: small_co_3"]
    end

    subgraph DB_Enterprise1 ["Database: enterprise_acme (Dedicated)"]
        AcmeData["Acme Data"]
    end

    subgraph DB_Enterprise2 ["Database: enterprise_mega (Dedicated)"]
        MegaData["Mega Data"]
    end

Route tenants based on their plan using NestJS and Prisma:

import { Injectable } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

export interface TenantInfo {
    id: string;
    plan: 'free' | 'pro' | 'enterprise';
    customDbUrl?: string;
}

@Injectable()
export class HybridTenantService {
    private dedicatedClients: Map<string, PrismaClient> = new Map();
    private sharedClient: PrismaClient;

    constructor() {
        this.sharedClient = new PrismaClient({
            datasources: { db: { url: process.env.SHARED_DATABASE_URL } },
        });
    }

    getPrismaClient(tenant: TenantInfo): PrismaClient {
        if (tenant.plan === 'enterprise' && tenant.customDbUrl) {
            if (!this.dedicatedClients.has(tenant.id)) {
                const client = new PrismaClient({
                    datasources: { db: { url: tenant.customDbUrl } },
                });
                this.dedicatedClients.set(tenant.id, client);
            }
            return this.dedicatedClients.get(tenant.id)!;
        }

        // For free and pro plans, the shared database is used
        return this.sharedClient;
    }
}

This allows you to optimize costs for small tenants while offering premium isolation for enterprise clients who are willing to pay for it.

Summary

The right multi-tenant architecture depends on your specific needs:

Start with shared tables for simplicity and scale.
Add Row Level Security to enforce tenant isolation.
Consider separate schemas when you need tenant customization.
Use separate databases for high isolation requirements or enterprise tiers.

No matter which approach you choose, ensure your application code consistently enforces tenant boundaries. The simple absence of a WHERE clause or a mishandling of context can expose one tenant’s data to another.

Multi-Tenant in PostgreSQL with NestJS and Prisma

The Three Approaches

1. Shared Tables (Pool Model)

2. Separate Schemas (Bridge Model)

3. Separate Databases (Silo Model)

Approach 1: Shared Tables

DDL and RLS Implementation in PostgreSQL

Integration in NestJS with Prisma and Row Level Security (RLS)

1. Tenant Context with AsyncLocalStorage (tenant.context.ts)

2. NestJS Middleware (tenant.middleware.ts)

3. Extended Prisma Service with RLS (prisma.service.ts)

4. Usage in a NestJS Service (users.service.ts)

Pros and Cons

Approach 2: Separate Schemas

Implementation in PostgreSQL

Integration in NestJS with Prisma (Dynamic Schema Handling)

1. Prisma Client Manager per Schema (tenant-prisma-factory.service.ts)

2. Controller and Service in NestJS (orders.service.ts)

Migrations with Prisma Across Multiple Schemas

Pros and Cons

Approach 3: Separate Databases

Implementation

Connection Routing in NestJS with Prisma

Pros and Cons

Choosing the Right Approach

Decision Matrix

Recommendations

Hybrid Approach

Summary

1. Tenant Context with AsyncLocalStorage (`tenant.context.ts`)

2. NestJS Middleware (`tenant.middleware.ts`)

3. Extended Prisma Service with RLS (`prisma.service.ts`)

4. Usage in a NestJS Service (`users.service.ts`)

1. Prisma Client Manager per Schema (`tenant-prisma-factory.service.ts`)

2. Controller and Service in NestJS (`orders.service.ts`)