0Pricing
NestJS Enterprise Backend APIs · Урок

Подключения к базам данных по схеме арендатора

Динамически переключайте схемы или подключения к базе данных в зависимости от определённого арендатора.

«Подключения к базам данных по схеме арендатора» — бесплатный урок NestJS Enterprise Backend APIs на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения NestJS Enterprise Backend APIs, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс NestJS Enterprise Backend APIs содержит 4 уроков всего.

Части этого урока еще не переведены и отображаются на английском.

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.

Часто задаваемые вопросы

Урок «Подключения к базам данных по схеме арендатора» бесплатный?

Да — полный текст урока «Подключения к базам данных по схеме арендатора» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс NestJS Enterprise Backend APIs, подпишись на CoddyKit PRO. Курс NestJS Enterprise Backend APIs содержит 4 уроков всего.

Чему я научусь в уроке «Подключения к базам данных по схеме арендатора»?

Динамически переключайте схемы или подключения к базе данных в зависимости от определённого арендатора. Ты практикуешь NestJS Enterprise Backend APIs с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать NestJS Enterprise Backend APIs?

Предыдущий опыт не требуется. NestJS Enterprise Backend APIs на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «Подключения к базам данных по схеме арендатора»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке NestJS Enterprise Backend APIs?

Да. Каждый урок NestJS Enterprise Backend APIs включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Определение арендатора через промежуточное ПО и AsyncLocalStorage
  2. Подключения к базам данных по схеме арендатора
  3. Создание настраиваемых динамических модулей
  4. Провайдеры с областью действия запроса и их компромиссы
← Назад к NestJS Enterprise Backend APIs