0Pricing
NestJS Enterprise Backend APIs · Lesson

Schema-per-Tenant Database Connections

Switch database schemas or connections dynamically based on the resolved tenant.

Schema-per-Tenant Database Connections is a free NestJS Enterprise Backend APIs lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the NestJS Enterprise Backend APIs learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Schema-per-Tenant: The Big Picture

In a multi-tenant API, every tenant's data must stay isolated. The schema-per-tenant model keeps one physical database but gives each tenant its own PostgreSQL schema (e.g. tenant_acme, tenant_globex). Tables have identical structures across schemas.

  • Pool of tables (shared schema): one set of tables, isolation by a tenant_id column. Simple, but leaks are one missed WHERE clause away.
  • Schema-per-tenant: stronger isolation, easy per-tenant backup, but you must switch the active schema per request.
  • Database-per-tenant: maximum isolation, heaviest operational cost.

This lesson focuses on dynamically routing each request to the correct schema or connection once the tenant has been resolved.

Resolving the Tenant per Request

Before you can switch schemas, you need the tenant. Resolution typically comes from a subdomain, a header, or a JWT claim. A lightweight middleware extracts it and attaches it to the request so downstream providers can read it.

Keep resolution dumb and cheap here; validation of whether the tenant exists happens when you build the connection.

import { Injectable, NestMiddleware } from '@nestjs/common';
import { Request, Response, NextFunction } from 'express';

@Injectable()
export class TenantMiddleware implements NestMiddleware {
  use(req: Request, _res: Response, next: NextFunction) {
    // Prefer an explicit header; fall back to subdomain.
    const headerTenant = req.headers['x-tenant-id'] as string | undefined;
    const host = req.headers.host ?? '';
    const subdomain = host.split('.')[0];

    const tenantId = headerTenant ?? subdomain;
    (req as any).tenantId = tenantId;
    next();
  }
}

Why REQUEST Scope Is the Natural Fit

The active schema changes on every request. NestJS providers are singletons by default, so a singleton cannot safely hold a per-request schema. The clean answer is a request-scoped provider that receives the current REQUEST.

  • Scope.REQUEST creates a fresh provider instance per incoming request.
  • Any provider that injects a request-scoped provider becomes request-scoped too (it bubbles up the chain).
  • Trade-off: instantiation per request has overhead, so keep request-scoped providers thin and cache the heavy bits (connections) outside.

Switching Schema with SET search_path

The simplest schema switch on a single Postgres connection is SET search_path. It tells Postgres which schema to resolve unqualified table names against for the rest of that session.

Critical caveat: with a connection pool, a connection may be handed to another tenant's request next. You must scope the switch to the work and reset it, or use a transaction-local variant.

import { DataSource } from 'typeorm';

export async function withTenantSchema<T>(
  dataSource: DataSource,
  schema: string,
  work: () => Promise<T>,
): Promise<T> {
  const runner = dataSource.createQueryRunner();
  await runner.connect();
  try {
    // SET LOCAL is transaction-scoped and auto-resets on commit/rollback.
    await runner.startTransaction();
    await runner.query('SET LOCAL search_path TO $1', [schema]);
    const result = await work();
    await runner.commitTransaction();
    return result;
  } catch (err) {
    await runner.rollbackTransaction();
    throw err;
  } finally {
    await runner.release();
  }
}

Sanitizing the Schema Name

Schema names are identifiers, not values. You cannot safely parameterize an identifier in SET search_path the way you parameterize data. A malicious or malformed tenant id could become SQL injection.

Always validate the resolved schema against a strict allow-list pattern (and ideally against a registry of known tenants) before interpolating it.

const SCHEMA_PATTERN = /^[a-z][a-z0-9_]{1,62}$/;

export function tenantSchema(tenantId: string): string {
  const candidate = `tenant_${tenantId.toLowerCase()}`;
  if (!SCHEMA_PATTERN.test(candidate)) {
    throw new Error(`Invalid tenant schema: ${candidate}`);
  }
  return candidate;
}

console.log(tenantSchema('Acme'));      // tenant_acme
try {
  tenantSchema('acme; DROP SCHEMA x');  // throws
} catch (e) {
  console.log((e as Error).message);
}

Per-Tenant Connections Instead of search_path

An alternative to mutating search_path on a shared pool is to keep a dedicated DataSource (and pool) per tenant schema. Each DataSource is configured once with its schema and reused.

  • Pro: no per-request schema mutation, no pool cross-contamination risk.
  • Con: connection count multiplies by active tenants — you must cap pool sizes and evict idle tenants.

This is where a connection manager that lazily builds and caches DataSources shines.

A Tenant Connection Manager

The manager owns the lifecycle: build a DataSource the first time a tenant is seen, cache it, and reuse it afterwards. It is a singleton — only the lookup is per-request, the heavy connections are shared safely because each is bound to its own schema.

Note the cache key is the schema, and concurrent first-hits must not build twice (store the promise, not just the resolved value).

import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';

@Injectable()
export class TenantConnectionManager {
  private readonly pools = new Map<string, Promise<DataSource>>();

  get(schema: string): Promise<DataSource> {
    let pool = this.pools.get(schema);
    if (!pool) {
      pool = this.build(schema);
      this.pools.set(schema, pool); // cache the promise to dedupe races
    }
    return pool;
  }

  private async build(schema: string): Promise<DataSource> {
    const ds = new DataSource({
      type: 'postgres',
      url: process.env.DATABASE_URL,
      schema,
      entities: [__dirname + '/**/*.entity.{ts,js}'],
      poolSize: 5,
    });
    await ds.initialize();
    return ds;
  }
}

Exposing the Tenant DataSource as a Provider

Now wire a request-scoped factory provider that reads the tenant from REQUEST, computes its schema, and asks the manager for the right DataSource. Services inject this token instead of a fixed connection.

Because the factory injects REQUEST, the provider is request-scoped — but the manager it calls is a singleton, so connection reuse is preserved.

import { Scope, Provider } from '@nestjs/common';
import { REQUEST } from '@nestjs/core';
import { Request } from 'express';
import { DataSource } from 'typeorm';

export const TENANT_DATA_SOURCE = 'TENANT_DATA_SOURCE';

export const tenantDataSourceProvider: Provider = {
  provide: TENANT_DATA_SOURCE,
  scope: Scope.REQUEST,
  inject: [REQUEST, TenantConnectionManager],
  useFactory: (req: Request, manager: TenantConnectionManager): Promise<DataSource> => {
    const tenantId = (req as any).tenantId as string | undefined;
    if (!tenantId) {
      throw new Error('No tenant resolved for this request');
    }
    const schema = tenantSchema(tenantId);
    return manager.get(schema);
  },
};

Using the Tenant DataSource in a Service

A request-scoped service injects the resolved DataSource by token. Every query it runs already targets the correct schema — there is no tenant_id filter and no manual schema switch in business code.

The factory returns a Promise<DataSource>, so await it (or have the factory await before returning) before opening repositories.

import { Inject, Injectable, Scope } from '@nestjs/common';
import { DataSource } from 'typeorm';
import { Invoice } from './invoice.entity';

@Injectable({ scope: Scope.REQUEST })
export class InvoiceService {
  constructor(
    @Inject(TENANT_DATA_SOURCE) private readonly dataSource: DataSource,
  ) {}

  findAll(): Promise<Invoice[]> {
    // Already bound to tenant_<x> schema — no tenant filter needed.
    return this.dataSource.getRepository(Invoice).find();
  }
}

Dynamic Modules for Configurable Tenancy

Reusable tenancy logic belongs in a dynamic module so apps can configure resolution strategy, schema prefix, and pool size via forRoot/forRootAsync. The module exports the manager and the request-scoped DataSource provider.

This is the multi-tenancy + dynamic-module pairing: configuration is static (set once at boot), while the resolved connection is dynamic (per request).

import { DynamicModule, Module } from '@nestjs/common';

export interface TenancyOptions {
  schemaPrefix: string;
  poolSize: number;
}

@Module({})
export class TenancyModule {
  static forRoot(options: TenancyOptions): DynamicModule {
    return {
      module: TenancyModule,
      global: true,
      providers: [
        { provide: 'TENANCY_OPTIONS', useValue: options },
        TenantConnectionManager,
        tenantDataSourceProvider,
      ],
      exports: [TenantConnectionManager, TENANT_DATA_SOURCE],
    };
  }
}

Lifecycle, Eviction, and Migrations

Per-tenant pools are a resource leak waiting to happen. Manage them deliberately:

  • Cap concurrency: small poolSize per tenant; many tenants × big pools exhausts Postgres max_connections.
  • Evict idle tenants: track last-used time and destroy() DataSources that go cold (an LRU keeps memory bounded).
  • Shut down cleanly: implement OnModuleDestroy to close every cached DataSource.
  • Migrations: a new tenant means CREATE SCHEMA + run migrations against it before first use; loop migrations across all tenant schemas on deploy.

Treat schema provisioning as an explicit onboarding step, never an accident of the first query.

Quick Check: Avoiding Cross-Tenant Leaks

You switch schemas using SET search_path on connections borrowed from a shared TypeORM pool. Occasionally tenant A sees tenant B's rows. What is the most likely root cause and the correct fix?

Recap: Dynamic Schema Routing

You learned to route each request to the right tenant schema:

  • Resolve the tenant early (header/subdomain/JWT) in middleware and attach it to the request.
  • Switch schemas either via transaction-scoped SET LOCAL search_path on a shared pool, or via a dedicated DataSource per tenant cached in a singleton manager.
  • Wire a request-scoped factory provider that reads REQUEST, validates the schema name, and returns the correct DataSource — services stay tenant-agnostic.
  • Configure the whole thing through a dynamic module (forRoot), keeping config static while the connection is dynamic.
  • Operate safely: validate identifiers, cap and evict pools, close on shutdown, and provision/migrate schemas as an explicit onboarding step.

Frequently asked questions

Is the “Schema-per-Tenant Database Connections” lesson free?

Yes — the full text of “Schema-per-Tenant Database Connections” is free to read here on the web, and the NestJS Enterprise Backend APIs course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the NestJS Enterprise Backend APIs course, upgrade to CoddyKit PRO.

What will I learn in “Schema-per-Tenant Database Connections”?

Switch database schemas or connections dynamically based on the resolved tenant. You practise NestJS Enterprise Backend APIs with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start NestJS Enterprise Backend APIs?

No prior experience is required. NestJS Enterprise Backend APIs on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Schema-per-Tenant Database Connections” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this NestJS Enterprise Backend APIs lesson?

Yes. Every NestJS Enterprise Backend APIs lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Tenant Resolution via Middleware and AsyncLocalStorage
  2. Schema-per-Tenant Database Connections
  3. Building Configurable Dynamic Modules
  4. Request-Scoped Providers and Their Trade-offs
← Back to NestJS Enterprise Backend APIs