Multi-tenancy patterns: a practical guide for developers

Multi-tenancy patterns: a practical guide for developers

Building a SaaS platform means serving multiple customers from a single codebase, but isolating their data is one of the trickiest architectural decisions you'll make. Choose poorly and you'll either leak data between tenants, or burn through your infrastructure budget maintaining separate databases for each customer. This guide walks through three proven isolation models and shows you how to pick the right one for your constraints.

Why isolation models matter

Multi-tenancy isn't just about cost—it's about security, compliance, and operational sanity. Your isolation model determines:

• How easily you can filter queries to prevent data leakage
• How much database overhead each tenant creates
• Whether migrations break single tenants or the entire platform
• How you handle regulatory requirements like data residency

There's no universal winner. The model that works for a freemium product with thousands of small tenants looks nothing like the one for an enterprise SaaS with a handful of high-value customers.

Model 1: Shared database with tenant column

This is the lean startup approach. Every table gets a tenant_id column. One database serves all customers. You filter on tenant_id in every query.

Trade-offs: Cheapest operational cost, smallest infrastructure footprint. But you're one missed filter away from a data breach, and your schema changes affect all tenants at once.

Here's what a typical setup looks like:

// User repository with tenant isolation
class UserRepository {
  private db: Database;

  constructor(db: Database, private tenantId: string) {
    this.db = db;
  }

  async findAll() {
    // Filter on tenant_id—this must never be optional
    return this.db.query(
      'SELECT * FROM users WHERE tenant_id = $1',
      [this.tenantId]
    );
  }

  async create(email: string, name: string) {
    return this.db.query(
      'INSERT INTO users (tenant_id, email, name) VALUES ($1, $2, $3)',
      [this.tenantId, email, name]
    );
  }
}

// Middleware that extracts tenant from request
function extractTenantMiddleware(req, res, next) {
  const tenantId = req.headers['x-tenant-id'] || req.user?.tenantId;
  if (!tenantId) {
    return res.status(400).json({ error: 'Missing tenant context' });
  }
  req.tenantId = tenantId;
  next();
}

The critical mistake here is assuming developers will always remember the tenant_id filter. You prevent this with:

• Database-level row security policies (PostgreSQL RLS)
• Repository patterns that bake tenant context into the constructor
• Linting rules that flag raw queries without tenant filtering

Use this model when: you're pre-product-market fit, you have hundreds or thousands of small customers, and your schema is stable. It's also the cheapest to run.

Model 2: Schema per tenant

Each tenant gets their own PostgreSQL schema within a shared database cluster. You keep one connection pool but switch schemas per request.

Trade-offs: Much stronger isolation than the shared-table approach. Schema migrations can target individual tenants. But your connection pool scales with tenant count, and switching schemas adds latency.

Here's how you'd structure this:

// Database connection that switches schema per tenant
class TenantAwareConnection {
  private pool: Pool;

  constructor(connectionString: string) {
    this.pool = new Pool({ connectionString });
  }

  async query(tenantId: string, sql: string, values?: any[]) {
    const client = await this.pool.connect();
    try {
      // Switch to tenant schema
      await client.query(`SET search_path TO tenant_${tenantId}`);
      return await client.query(sql, values);
    } finally {
      client.release();
    }
  }
}

// Now repositories don't need explicit tenant filtering
class UserRepository {
  constructor(
    private db: TenantAwareConnection,
    private tenantId: string
  ) {}

  async findAll() {
    // Query runs in the tenant's schema automatically
    return this.db.query(
      this.tenantId,
      'SELECT * FROM users'
    );
  }
}

One real-world scenario: you're running compliance-heavy software where audit trails must be completely isolated per customer. Schema separation means you can archive a tenant's entire schema without touching others.

The operational cost climbs as you scale. Ten thousand tenants means ten thousand schemas in one database cluster. Connection overhead becomes significant. Most teams using this model cap it at a few hundred tenants before moving to dedicated databases for large customers.

Model 3: Database per tenant

Each tenant (especially enterprise ones) gets their own PostgreSQL instance. Maximum isolation, maximum operational complexity.

Trade-offs: Bulletproof isolation, regulatory compliance, independent backups, dedicated performance. But you're now managing many databases, migrations become distributed operations, and costs explode.

You'd typically implement a registry that maps tenants to connection strings:

// Tenant registry that maps tenant IDs to database URLs
class TenantRegistry {
  private cache = new Map();

  async getConnectionString(tenantId: string): Promise {
    if (this.cache.has(tenantId)) {
      return this.cache.get(tenantId)!;
    }

    // Query your tenant metadata service
    const tenant = await fetch(
      `https://metadata-service/tenants/${tenantId}`
    ).then(r => r.json());

    this.cache.set(tenantId, tenant.databaseUrl);
    return tenant.databaseUrl;
  }

  invalidateCache(tenantId: string) {
    this.cache.delete(tenantId);
  }
}

// Connection pool manager
class MultiDatabaseConnectionManager {
  private pools = new Map();

  async query(tenantId: string, sql: string, values?: any[]) {
    const connectionString = 
      await this.registry.getConnectionString(tenantId);

    if (!this.pools.has(tenantId)) {
      this.pools.set(
        tenantId,
        new Pool({ connectionString })
      );
    }

    const pool = this.pools.get(tenantId)!;
    return pool.query(sql, values);
  }
}

Use this for: enterprise SaaS where you have 5-50 high-value customers willing to pay for dedicated infrastructure, or where regulatory requirements mandate complete data isolation.

The hybrid approach: start small, scale selectively

Most mature SaaS platforms end up here. Run 95% of your customer base on a shared database with tenant columns. When a large enterprise customer signs on, spin up a dedicated database and route their traffic there.

This requires a routing layer that knows which isolation model each tenant uses:

// Routing logic that chooses the right database
class DataAccessLayer {
  async query(tenantId: string, sql: string, values?: any[]) {
    const tenant = await this.tenantRegistry.get(tenantId);

    switch (tenant.isolationModel) {
      case 'shared':
        // Add tenant_id filter automatically
        const modifiedSql = this.injectTenantFilter(sql, 'tenant_id');
        return this.sharedDb.query(modifiedSql, [tenantId, ...values]);

      case 'schema':
        return this.schemaDb.query(tenantId, sql, values);

      case 'dedicated':
        return this.dedicatedDb.query(tenantId, sql, values);

      default:
        throw new Error(`Unknown isolation model: ${tenant.isolationModel}`);
    }
  }

  private injectTenantFilter(sql: string, column: string): string {
    // Append WHERE or AND clause for tenant filtering
    if (sql.toLowerCase().includes('where')) {
      return sql + ` AND ${column} = $${/* count params */}`;
    }
    return sql + ` WHERE ${column} = $1`;
  }
}

Tenant context: resolve once, pass everywhere

Whatever isolation model you pick, you need a clean way to thread tenant context through your application without passing tenantId as a parameter to every function.

Most frameworks support async-local storage for this:

// Express middleware approach
import { AsyncLocalStorage } from 'async_hooks';

const tenantStorage = new AsyncLocalStorage();

app.use((req, res, next) => {
  const tenantId = extractTenantId(req);
  tenantStorage.run(tenantId, () => next());
});

// Deep in your business logic, retrieve the tenant without parameters
class OrderService {
  async createOrder(items: OrderItem[]) {
    const tenantId = tenantStorage.getStore();
    // Now use tenantId in your database queries
    return this.db.query(
      'INSERT INTO orders (tenant_id, ...) VALUES ($1, ...)',
      [tenantId, ...]
    );
  }
}

This pattern works in NestJS (dependency injection + scope), Next.js (headers() for server components), and most other frameworks. The key is resolving the tenant once at the request boundary and making it available throughout the request lifecycle.

Common mistakes

• Forgetting the tenant filter in a reporting query. You query an analytics table without filtering by tenant and accidentally expose aggregated data across customers. Use read replicas or views that enforce tenant isolation automatically.
• Mixing isolation models without a clear routing layer. If some of your code assumes shared-database queries and other code assumes schema separation, you'll leak data. Centralize all data access through one abstraction.
• Not testing tenant isolation. Write integration tests that verify queries from one tenant never return another tenant's data. Use parameterized test cases for each isolation model.
• Scaling to dedicated databases too early. The operational cost of managing 10 databases is real. Don't give every customer a dedicated instance until they're paying enough to justify it.
• Assuming schema switches are free. Switching PostgreSQL schemas per request has measurable latency. Profile before you commit to this model at scale.

Key takeaways

• Shared database with tenant columns is the right starting point: minimal cost, easy to deploy, but requires discipline around filtering.
• Schema per tenant adds isolation and allows independent migrations, but connection pooling becomes a bottleneck as you scale.
• Database per tenant is the safest isolation model but only makes economic sense for a handful of high-value customers.
• Most successful SaaS platforms use a hybrid model: shared infrastructure for the majority, dedicated for enterprise tenants.
• Extract and resolve tenant context at the request boundary and pass it through your entire stack via async-local storage or dependency injection, not function parameters.
• Test tenant isolation explicitly; a data leak is far more expensive than any infrastructure optimization.