Multi-Tenant Architecture Deep Dive

In this second part of our SaaS tutorial series, we’ll implement the heart of any SaaS application: multi-tenant architecture. You’ll learn how to build systems that serve multiple customers while keeping their data completely isolated and secure.

What You’ll Learn

Three approaches to multi-tenancy and when to use each
Implementing row-level security with PostgreSQL
Building tenant-aware middleware
Dynamic tenant provisioning
Performance considerations for each approach

Multi-Tenancy Strategies Comparison

Let’s start by understanding the three main approaches:

Strategy	Use Case	Pros	Cons
Shared Database, Shared Schema	B2C apps, millions of users	Cost-effective, easy maintenance	Complex security, noisy neighbor
Shared Database, Separate Schema	B2B apps, hundreds of tenants	Good isolation, moderate cost	Schema management complexity
Database per Tenant	Enterprise, strict compliance	Complete isolation, best performance	Highest cost, complex operations

Implementing Shared Database with Row-Level Security

This is the most common approach for SaaS applications. Let’s implement it step by step.

Step 1: Create the Base Model

import { Model, ModelOptions, QueryContext, QueryBuilder } from 'objection';
import { getCurrentTenant } from '../context/tenant-context';

export interface ITenantAware {
  tenantId: string;
}

export class BaseMultiTenantModel extends Model implements ITenantAware {
  tenantId!: string;

  static get modelOptions(): ModelOptions {
    return {
      // Automatically filter all queries by tenant
      defaultScope(builder: QueryBuilder<any>) {
        const tenantId = getCurrentTenant()?.id;
        if (tenantId) {
          builder.where('tenant_id', tenantId);
        }
      }
    };
  }

  async $beforeInsert(queryContext: QueryContext) {
    await super.$beforeInsert(queryContext);

    // Automatically set tenant_id on insert
    const tenantId = getCurrentTenant()?.id;
    if (!this.tenantId && tenantId) {
      this.tenantId = tenantId;
    }

    if (!this.tenantId) {
      throw new Error('Tenant context is required for insert operations');
    }
  }

  async $beforeUpdate(opt: ModelOptions, queryContext: QueryContext) {
    await super.$beforeUpdate(opt, queryContext);

    // Prevent tenant_id from being changed
    delete (this as any).tenantId;
  }

  // Override query builder to ensure tenant filtering
  static query(trx?: any) {
    const query = super.query(trx);

    // Add tenant filter if context exists
    const tenantId = getCurrentTenant()?.id;
    if (tenantId) {
      query.where('tenant_id', tenantId);
    }

    return query;
  }

  // Method to bypass tenant filtering (use with caution!)
  static unscopedQuery(trx?: any) {
    return super.query(trx);
  }
}

Step 2: Tenant Context Management

import { AsyncLocalStorage } from 'async_hooks';

export interface TenantContext {
  id: string;
  slug: string;
  name: string;
  subscription: {
    tier: 'free' | 'starter' | 'pro' | 'enterprise';
    expiresAt: Date | null;
  };
  limits: {
    maxUsers: number;
    maxProjects: number;
    maxStorage: number;
    maxApiCallsPerMonth: number;
  };
}

// Create async local storage for tenant context
const tenantContextStorage = new AsyncLocalStorage<TenantContext>();

export function runWithTenant<T>(
  tenant: TenantContext,
  callback: () => T | Promise<T>
): T | Promise<T> {
  return tenantContextStorage.run(tenant, callback);
}

export function getCurrentTenant(): TenantContext | undefined {
  return tenantContextStorage.getStore();
}

export function requireTenant(): TenantContext {
  const tenant = getCurrentTenant();
  if (!tenant) {
    throw new Error('Tenant context is required but not set');
  }
  return tenant;
}

Step 3: Tenant Middleware

import { Injectable, NestMiddleware, BadRequestException, ForbiddenException } from '@nestjs/common';
import { Request, Response, NextFunction } from 'express';
import { TenantService } from '../modules/tenant/tenant.service';
import { CacheService } from '../modules/cache/cache.service';
import { runWithTenant, TenantContext } from '@saas/database';

export interface TenantRequest extends Request {
  tenant?: TenantContext;
}

@Injectable()
export class TenantMiddleware implements NestMiddleware {
  constructor(
    private tenantService: TenantService,
    private cacheService: CacheService
  ) {}

  async use(req: TenantRequest, res: Response, next: NextFunction) {
    try {
      // Extract tenant identifier
      const tenantId = this.extractTenantId(req);

      if (!tenantId) {
        throw new BadRequestException('Tenant identification required');
      }

      // Get tenant with caching
      const tenant = await this.getTenantWithCache(tenantId);

      if (!tenant) {
        throw new BadRequestException('Invalid tenant');
      }

      // Check tenant status
      if (tenant.status !== 'ACTIVE') {
        throw new ForbiddenException(`Tenant account is ${tenant.status.toLowerCase()}`);
      }

      // Check subscription expiry
      if (tenant.subscriptionExpiresAt && new Date() > tenant.subscriptionExpiresAt) {
        throw new ForbiddenException('Subscription has expired');
      }

      // Build tenant context
      const context: TenantContext = {
        id: tenant.id,
        slug: tenant.slug,
        name: tenant.name,
        subscription: {
          tier: tenant.subscriptionTier,
          expiresAt: tenant.subscriptionExpiresAt
        },
        limits: this.calculateLimits(tenant.subscriptionTier)
      };

      // Attach to request
      req.tenant = context;

      // Run the rest of the request within tenant context
      runWithTenant(context, () => {
        next();
      });

    } catch (error) {
      next(error);
    }
  }

  private extractTenantId(req: Request): string | null {
    // Multiple strategies for tenant identification

    // 1. Subdomain (e.g., acme.app.com)
    const hostname = req.hostname;
    const subdomain = hostname.split('.')[0];
    if (subdomain && subdomain !== 'www' && subdomain !== 'app') {
      return subdomain;
    }

    // 2. Custom header (for API access)
    const headerTenantId = req.headers['x-tenant-id'] as string;
    if (headerTenantId) {
      return headerTenantId;
    }

    // 3. JWT claim
    const user = (req as any).user;
    if (user?.tenantId) {
      return user.tenantId;
    }

    // 4. Path parameter (e.g., /api/tenants/:tenantId/...)
    const pathMatch = req.path.match(/^\/api\/tenants\/([^\/]+)/);
    if (pathMatch) {
      return pathMatch[1];
    }

    // 5. Query parameter (last resort)
    const queryTenantId = req.query.tenantId as string;
    if (queryTenantId) {
      return queryTenantId;
    }

    return null;
  }

  private async getTenantWithCache(identifier: string) {
    const cacheKey = `tenant:${identifier}`;

    // Try cache first
    const cached = await this.cacheService.get(cacheKey);
    if (cached) {
      return cached;
    }

    // Load from database
    const tenant = await this.tenantService.findByIdentifier(identifier);

    if (tenant) {
      // Cache for 5 minutes
      await this.cacheService.set(cacheKey, tenant, 300);
    }

    return tenant;
  }

  private calculateLimits(tier: string) {
    const limits = {
      free: {
        maxUsers: 5,
        maxProjects: 3,
        maxStorage: 1_000_000_000, // 1GB
        maxApiCallsPerMonth: 10_000
      },
      starter: {
        maxUsers: 20,
        maxProjects: 10,
        maxStorage: 10_000_000_000, // 10GB
        maxApiCallsPerMonth: 100_000
      },
      pro: {
        maxUsers: 100,
        maxProjects: 50,
        maxStorage: 100_000_000_000, // 100GB
        maxApiCallsPerMonth: 1_000_000
      },
      enterprise: {
        maxUsers: Infinity,
        maxProjects: Infinity,
        maxStorage: Infinity,
        maxApiCallsPerMonth: Infinity
      }
    };

    return limits[tier] || limits.free;
  }
}

Step 4: Apply Middleware

import { Module, MiddlewareConsumer, RequestMethod } from '@nestjs/common';
import { TenantMiddleware } from './middleware/tenant.middleware';

@Module({
  // ... other configuration
})
export class AppModule {
  configure(consumer: MiddlewareConsumer) {
    consumer
      .apply(TenantMiddleware)
      .exclude(
        { path: 'auth/register', method: RequestMethod.POST },
        { path: 'auth/login', method: RequestMethod.POST },
        { path: 'health', method: RequestMethod.GET }
      )
      .forRoutes('*');
  }
}

Implementing Schema-Based Multi-Tenancy

For B2B applications with moderate isolation requirements, schema-based multi-tenancy provides a good balance.

Step 1: Schema Manager Service

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

@Injectable()
export class SchemaManagerService {
  constructor(
    @InjectConnection() private connection: Connection,
    private prisma: PrismaService
  ) {}

  async createTenantSchema(tenantId: string): Promise<void> {
    const schemaName = this.getSchemaName(tenantId);

    try {
      // Start transaction
      await this.connection.transaction(async manager => {
        // Create schema
        await manager.query(
          `CREATE SCHEMA IF NOT EXISTS "${schemaName}"`
        );

        // Grant permissions
        await manager.query(
          `GRANT ALL ON SCHEMA "${schemaName}" TO ${process.env.DB_USER}`
        );

        // Create tables by running migrations
        await this.createTablesInSchema(schemaName, manager);

        // Set up row-level security
        await this.setupRowLevelSecurity(schemaName, manager);

        // Create initial indexes
        await this.createIndexes(schemaName, manager);
      });

      console.log(`✅ Schema ${schemaName} created successfully`);
    } catch (error) {
      console.error(`❌ Failed to create schema ${schemaName}:`, error);
      throw error;
    }
  }

  private async createTablesInSchema(schemaName: string, manager: any) {
    // Set search path
    await manager.query(`SET search_path TO "${schemaName}"`);

    // Create users table
    await manager.query(`
      CREATE TABLE IF NOT EXISTS users (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        email VARCHAR(255) UNIQUE NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        name VARCHAR(255),
        role VARCHAR(50) DEFAULT 'member',
        status VARCHAR(20) DEFAULT 'active',
        last_login_at TIMESTAMP,
        settings JSONB DEFAULT '{}',
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW()
      )
    `);

    // Create projects table
    await manager.query(`
      CREATE TABLE IF NOT EXISTS projects (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name VARCHAR(255) NOT NULL,
        description TEXT,
        owner_id UUID REFERENCES users(id) ON DELETE SET NULL,
        status VARCHAR(20) DEFAULT 'active',
        settings JSONB DEFAULT '{}',
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW()
      )
    `);

    // Create audit_logs table
    await manager.query(`
      CREATE TABLE IF NOT EXISTS audit_logs (
        id BIGSERIAL PRIMARY KEY,
        user_id UUID REFERENCES users(id) ON DELETE SET NULL,
        action VARCHAR(100) NOT NULL,
        entity_type VARCHAR(50),
        entity_id VARCHAR(255),
        old_values JSONB,
        new_values JSONB,
        ip_address INET,
        user_agent TEXT,
        created_at TIMESTAMP DEFAULT NOW()
      )
    `);

    // Reset search path
    await manager.query(`SET search_path TO public`);
  }

  private async setupRowLevelSecurity(schemaName: string, manager: any) {
    await manager.query(`
      -- Enable RLS on all tables
      ALTER TABLE "${schemaName}".users ENABLE ROW LEVEL SECURITY;
      ALTER TABLE "${schemaName}".projects ENABLE ROW LEVEL SECURITY;
      ALTER TABLE "${schemaName}".audit_logs ENABLE ROW LEVEL SECURITY;

      -- Create policies
      CREATE POLICY tenant_isolation_policy ON "${schemaName}".users
        USING (current_setting('app.current_tenant_id')::uuid = '${this.getTenantIdFromSchema(schemaName)}'::uuid);

      CREATE POLICY tenant_isolation_policy ON "${schemaName}".projects
        USING (current_setting('app.current_tenant_id')::uuid = '${this.getTenantIdFromSchema(schemaName)}'::uuid);

      CREATE POLICY tenant_isolation_policy ON "${schemaName}".audit_logs
        USING (current_setting('app.current_tenant_id')::uuid = '${this.getTenantIdFromSchema(schemaName)}'::uuid);
    `);
  }

  private async createIndexes(schemaName: string, manager: any) {
    await manager.query(`
      -- Users indexes
      CREATE INDEX idx_${schemaName}_users_email ON "${schemaName}".users(email);
      CREATE INDEX idx_${schemaName}_users_status ON "${schemaName}".users(status) WHERE status = 'active';

      -- Projects indexes
      CREATE INDEX idx_${schemaName}_projects_owner ON "${schemaName}".projects(owner_id);
      CREATE INDEX idx_${schemaName}_projects_status ON "${schemaName}".projects(status);

      -- Audit logs indexes
      CREATE INDEX idx_${schemaName}_audit_logs_user ON "${schemaName}".audit_logs(user_id);
      CREATE INDEX idx_${schemaName}_audit_logs_action ON "${schemaName}".audit_logs(action);
      CREATE INDEX idx_${schemaName}_audit_logs_created ON "${schemaName}".audit_logs(created_at DESC);
    `);
  }

  async deleteTenantSchema(tenantId: string): Promise<void> {
    const schemaName = this.getSchemaName(tenantId);

    // Safety check - require confirmation
    const confirmation = `DELETE_SCHEMA_${schemaName}`;
    if (process.env.CONFIRM_SCHEMA_DELETE !== confirmation) {
      throw new Error('Schema deletion requires confirmation');
    }

    await this.connection.query(
      `DROP SCHEMA IF EXISTS "${schemaName}" CASCADE`
    );
  }

  async setSchemaSearchPath(tenantId: string): Promise<void> {
    const schemaName = this.getSchemaName(tenantId);
    await this.connection.query(`SET search_path TO "${schemaName}", public`);
  }

  private getSchemaName(tenantId: string): string {
    // Convert UUID to valid schema name
    return `tenant_${tenantId.replace(/-/g, '_')}`;
  }

  private getTenantIdFromSchema(schemaName: string): string {
    // Convert schema name back to UUID
    return schemaName.replace('tenant_', '').replace(/_/g, '-');
  }
}

Step 2: Dynamic Connection Management

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

@Injectable()
export class TenantConnectionService {
  private connections: Map<string, DataSource> = new Map();

  async getConnection(tenantId: string): Promise<DataSource> {
    // Check if connection exists
    if (this.connections.has(tenantId)) {
      const connection = this.connections.get(tenantId)!;
      if (connection.isInitialized) {
        return connection;
      }
    }

    // Create new connection
    const connection = await this.createConnection(tenantId);
    this.connections.set(tenantId, connection);

    return connection;
  }

  private async createConnection(tenantId: string): Promise<DataSource> {
    const schemaName = `tenant_${tenantId.replace(/-/g, '_')}`;

    const options: DataSourceOptions = {
      type: 'postgres',
      host: process.env.DB_HOST,
      port: parseInt(process.env.DB_PORT || '5432'),
      username: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      schema: schemaName,
      entities: [__dirname + '/../**/*.entity{.ts,.js}'],
      synchronize: false, // Never use in production
      logging: process.env.NODE_ENV === 'development',
      pool: {
        max: 5, // Limit connections per tenant
        min: 1,
        idleTimeoutMillis: 30000
      }
    };

    const dataSource = new DataSource(options);
    await dataSource.initialize();

    return dataSource;
  }

  async closeConnection(tenantId: string): Promise<void> {
    const connection = this.connections.get(tenantId);
    if (connection?.isInitialized) {
      await connection.destroy();
      this.connections.delete(tenantId);
    }
  }

  async closeAllConnections(): Promise<void> {
    const promises = Array.from(this.connections.values()).map(conn =>
      conn.isInitialized ? conn.destroy() : Promise.resolve()
    );
    await Promise.all(promises);
    this.connections.clear();
  }
}

Implementing Database-Per-Tenant

For enterprise clients requiring complete isolation:

Step 1: Database Provisioning Service

import { Injectable } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import * as AWS from 'aws-sdk';
import { DatabaseConfig } from './interfaces/database-config.interface';

@Injectable()
export class DatabaseProvisionerService {
  private rds: AWS.RDS;
  private secretsManager: AWS.SecretsManager;

  constructor(private configService: ConfigService) {
    this.rds = new AWS.RDS({
      region: this.configService.get('AWS_REGION')
    });

    this.secretsManager = new AWS.SecretsManager({
      region: this.configService.get('AWS_REGION')
    });
  }

  async provisionTenantDatabase(
    tenantId: string,
    tier: 'starter' | 'pro' | 'enterprise'
  ): Promise<DatabaseConfig> {
    const dbConfig = this.getDatabaseConfigForTier(tier);
    const dbInstanceIdentifier = `saas-${tenantId}`;

    try {
      // Create RDS instance
      const createResult = await this.rds.createDBInstance({
        DBInstanceIdentifier: dbInstanceIdentifier,
        DBInstanceClass: dbConfig.instanceClass,
        Engine: 'postgres',
        EngineVersion: '15.3',
        MasterUsername: 'tenant_admin',
        MasterUserPassword: this.generateSecurePassword(),
        AllocatedStorage: dbConfig.storage,
        StorageType: 'gp3',
        StorageEncrypted: true,
        Iops: dbConfig.iops,
        BackupRetentionPeriod: dbConfig.backupDays,
        PreferredBackupWindow: '03:00-04:00',
        PreferredMaintenanceWindow: 'sun:04:00-sun:05:00',
        MultiAZ: tier === 'enterprise',
        VpcSecurityGroupIds: [this.configService.get('RDS_SECURITY_GROUP_ID')],
        DBSubnetGroupName: this.configService.get('DB_SUBNET_GROUP'),
        EnablePerformanceInsights: tier !== 'starter',
        PerformanceInsightsRetentionPeriod: tier === 'enterprise' ? 731 : 7,
        DeletionProtection: tier === 'enterprise',
        Tags: [
          { Key: 'TenantId', Value: tenantId },
          { Key: 'Tier', Value: tier },
          { Key: 'Environment', Value: this.configService.get('NODE_ENV') },
          { Key: 'ManagedBy', Value: 'SaaS-Platform' }
        ]
      }).promise();

      // Wait for database to be available
      await this.waitForDatabaseAvailable(dbInstanceIdentifier);

      // Get endpoint
      const endpoint = await this.getDatabaseEndpoint(dbInstanceIdentifier);

      // Create database configuration
      const config: DatabaseConfig = {
        host: endpoint.Address!,
        port: endpoint.Port!,
        database: 'tenant_db',
        username: 'tenant_admin',
        password: createResult.DBInstance!.MasterUserPassword!,
        ssl: true
      };

      // Store credentials in Secrets Manager
      await this.storeCredentials(tenantId, config);

      // Initialize database schema
      await this.initializeDatabase(config);

      // Set up automated backups
      await this.configureBackups(dbInstanceIdentifier, tier);

      // Set up monitoring
      await this.configureMonitoring(dbInstanceIdentifier, tenantId);

      return config;

    } catch (error) {
      console.error(`Failed to provision database for tenant ${tenantId}:`, error);
      // Cleanup on failure
      await this.cleanup(dbInstanceIdentifier);
      throw error;
    }
  }

  private getDatabaseConfigForTier(tier: string) {
    const configs = {
      starter: {
        instanceClass: 'db.t3.micro',
        storage: 20,
        iops: 3000,
        backupDays: 7
      },
      pro: {
        instanceClass: 'db.t3.small',
        storage: 100,
        iops: 12000,
        backupDays: 14
      },
      enterprise: {
        instanceClass: 'db.m6i.large',
        storage: 500,
        iops: 16000,
        backupDays: 30
      }
    };

    return configs[tier] || configs.starter;
  }

  private async waitForDatabaseAvailable(
    instanceId: string,
    maxAttempts: number = 60
  ): Promise<void> {
    for (let attempt = 0; attempt < maxAttempts; attempt++) {
      const { DBInstances } = await this.rds.describeDBInstances({
        DBInstanceIdentifier: instanceId
      }).promise();

      const status = DBInstances?.[0]?.DBInstanceStatus;

      if (status === 'available') {
        return;
      }

      if (status === 'failed' || status === 'deleted') {
        throw new Error(`Database creation failed with status: ${status}`);
      }

      // Wait 30 seconds before next check
      await new Promise(resolve => setTimeout(resolve, 30000));
    }

    throw new Error('Database creation timeout');
  }

  private async getDatabaseEndpoint(instanceId: string) {
    const { DBInstances } = await this.rds.describeDBInstances({
      DBInstanceIdentifier: instanceId
    }).promise();

    const endpoint = DBInstances?.[0]?.Endpoint;

    if (!endpoint) {
      throw new Error('Database endpoint not found');
    }

    return endpoint;
  }

  private generateSecurePassword(): string {
    const crypto = require('crypto');
    const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*';
    let password = '';

    for (let i = 0; i < 32; i++) {
      password += chars.charAt(crypto.randomInt(chars.length));
    }

    return password;
  }

  private async storeCredentials(tenantId: string, config: DatabaseConfig) {
    await this.secretsManager.createSecret({
      Name: `saas/tenant/${tenantId}/database`,
      SecretString: JSON.stringify(config),
      Tags: [
        { Key: 'TenantId', Value: tenantId },
        { Key: 'Type', Value: 'database-credentials' }
      ]
    }).promise();
  }

  private async initializeDatabase(config: DatabaseConfig) {
    // Connect to database and run initial migrations
    // This would use your migration tool of choice
    console.log('Initializing database schema...');
    // Implementation depends on your migration strategy
  }

  private async configureBackups(instanceId: string, tier: string) {
    if (tier === 'enterprise') {
      // Configure point-in-time recovery
      await this.rds.modifyDBInstance({
        DBInstanceIdentifier: instanceId,
        BackupRetentionPeriod: 35, // Maximum for PITR
        EnablePerformanceInsights: true,
        PerformanceInsightsRetentionPeriod: 731 // 2 years
      }).promise();
    }
  }

  private async configureMonitoring(instanceId: string, tenantId: string) {
    // Set up CloudWatch alarms
    const cloudWatch = new AWS.CloudWatch();

    // CPU utilization alarm
    await cloudWatch.putMetricAlarm({
      AlarmName: `${instanceId}-high-cpu`,
      ComparisonOperator: 'GreaterThanThreshold',
      EvaluationPeriods: 2,
      MetricName: 'CPUUtilization',
      Namespace: 'AWS/RDS',
      Period: 300,
      Statistic: 'Average',
      Threshold: 80,
      ActionsEnabled: true,
      AlarmActions: [this.configService.get('SNS_ALERT_TOPIC')],
      AlarmDescription: `High CPU utilization for tenant ${tenantId}`,
      Dimensions: [
        {
          Name: 'DBInstanceIdentifier',
          Value: instanceId
        }
      ]
    }).promise();

    // Storage space alarm
    await cloudWatch.putMetricAlarm({
      AlarmName: `${instanceId}-low-storage`,
      ComparisonOperator: 'LessThanThreshold',
      EvaluationPeriods: 1,
      MetricName: 'FreeStorageSpace',
      Namespace: 'AWS/RDS',
      Period: 300,
      Statistic: 'Average',
      Threshold: 2147483648, // 2GB in bytes
      ActionsEnabled: true,
      AlarmActions: [this.configService.get('SNS_ALERT_TOPIC')],
      AlarmDescription: `Low storage space for tenant ${tenantId}`,
      Dimensions: [
        {
          Name: 'DBInstanceIdentifier',
          Value: instanceId
        }
      ]
    }).promise();
  }

  private async cleanup(instanceId: string) {
    try {
      await this.rds.deleteDBInstance({
        DBInstanceIdentifier: instanceId,
        SkipFinalSnapshot: true,
        DeleteAutomatedBackups: true
      }).promise();
    } catch (error) {
      console.error('Cleanup failed:', error);
    }
  }
}

Performance Optimization for Multi-Tenant Systems

Connection Pooling Strategy

import { Injectable } from '@nestjs/common';
import { Pool } from 'pg';

@Injectable()
export class ConnectionPoolService {
  private pools: Map<string, Pool> = new Map();
  private readonly maxPoolsPerTenant = 5;
  private readonly minPoolsPerTenant = 1;

  getPool(tenantId: string): Pool {
    if (!this.pools.has(tenantId)) {
      this.createPool(tenantId);
    }

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

  private createPool(tenantId: string) {
    const pool = new Pool({
      host: process.env.DB_HOST,
      port: parseInt(process.env.DB_PORT || '5432'),
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      max: this.calculatePoolSize(tenantId),
      min: this.minPoolsPerTenant,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 2000,
      // Set schema for this tenant
      options: `-c search_path=tenant_${tenantId.replace(/-/g, '_')}`
    });

    // Monitor pool events
    pool.on('error', (err) => {
      console.error(`Pool error for tenant ${tenantId}:`, err);
    });

    pool.on('connect', () => {
      console.log(`New connection for tenant ${tenantId}`);
    });

    this.pools.set(tenantId, pool);
  }

  private calculatePoolSize(tenantId: string): number {
    // Dynamic pool sizing based on tenant tier
    // This would check the tenant's subscription tier
    // and return appropriate pool size
    return this.maxPoolsPerTenant;
  }

  async closePool(tenantId: string) {
    const pool = this.pools.get(tenantId);
    if (pool) {
      await pool.end();
      this.pools.delete(tenantId);
    }
  }

  async closeAllPools() {
    const promises = Array.from(this.pools.values()).map(pool => pool.end());
    await Promise.all(promises);
    this.pools.clear();
  }
}

Testing Multi-Tenant Implementation

Unit Tests

import { Test } from '@nestjs/testing';
import { TenantService } from './tenant.service';
import { runWithTenant, getCurrentTenant } from '@saas/database';

describe('TenantService', () => {
  let service: TenantService;

  beforeEach(async () => {
    const module = await Test.createTestingModule({
      providers: [TenantService],
    }).compile();

    service = module.get<TenantService>(TenantService);
  });

  describe('Tenant Isolation', () => {
    it('should isolate data between tenants', async () => {
      const tenant1 = { id: 'tenant-1', name: 'Tenant 1' };
      const tenant2 = { id: 'tenant-2', name: 'Tenant 2' };

      // Create data for tenant 1
      await runWithTenant(tenant1, async () => {
        await service.createProject({ name: 'Project 1' });
      });

      // Create data for tenant 2
      await runWithTenant(tenant2, async () => {
        await service.createProject({ name: 'Project 2' });
      });

      // Verify tenant 1 can only see their data
      await runWithTenant(tenant1, async () => {
        const projects = await service.getProjects();
        expect(projects).toHaveLength(1);
        expect(projects[0].name).toBe('Project 1');
      });

      // Verify tenant 2 can only see their data
      await runWithTenant(tenant2, async () => {
        const projects = await service.getProjects();
        expect(projects).toHaveLength(1);
        expect(projects[0].name).toBe('Project 2');
      });
    });

    it('should prevent cross-tenant data access', async () => {
      const tenant1 = { id: 'tenant-1', name: 'Tenant 1' };
      const tenant2 = { id: 'tenant-2', name: 'Tenant 2' };

      let projectId: string;

      // Create project in tenant 1
      await runWithTenant(tenant1, async () => {
        const project = await service.createProject({ name: 'Secret Project' });
        projectId = project.id;
      });

      // Try to access from tenant 2
      await runWithTenant(tenant2, async () => {
        const project = await service.getProject(projectId);
        expect(project).toBeNull();
      });
    });
  });
});

Summary

You’ve now mastered three different multi-tenancy approaches:

✅ Row-level security with shared database
✅ Schema isolation for better separation
✅ Database-per-tenant for complete isolation
✅ Tenant context management with AsyncLocalStorage
✅ Dynamic provisioning and connection management

Key Takeaways

Choose the right strategy based on your requirements
Always validate tenant context at the middleware level
Use connection pooling to optimize database resources
Implement proper monitoring for each tenant
Test isolation thoroughly with automated tests

What’s Next?

In Part 3: Database Design and Caching, we’ll explore:

Advanced database optimization techniques
Multi-layer caching strategies
Real-time data synchronization
Database migration strategies

Happy coding! 🚀

Building Scalable SaaS - Part 2: Multi-Tenant Architecture

Prerequisites