Architecting for N-Tenants: Solving Database Connection Exhaustion in Node.js

300 tenants × 10 pool connections ≈ 3,000 open sockets

# The errors that woke me up at 2 AM:
Error: EMFILE: too many open files
MongoServerSelectionError: connection pool exhausted

// Shared collection: ONE bug can leak data
const products = await Product.find({ tenantId: req.tenantId });
// What if a developer forgets tenantId filter? 💀 LEAKS ALL DATA

// Database-per-tenant: Impossible to leak
const Product = req.db.model('Product');
const products = await Product.find({ category: 'electronics' });
// Only queries THIS tenant's database

// ❌ WRONG: Race condition
if (!this.connections.has(tenantId)) {
  const conn = await createConnection(uri); // 50 calls happen here!
  this.connections.set(tenantId, conn);
}

// ✅ CORRECT: Promise caching
if (!this.promises.has(tenantId)) {
  const promise = createConnection(uri);    // Promise created
  this.promises.set(tenantId, promise);     // Cached IMMEDIATELY
}
return await this.promises.get(tenantId);   // All requests share it

class TenantConnectionManager {
  constructor() {
    // Active connections: tenantId -> { conn, lastUsedAt }
    this.connections = new Map();
    
    // In-flight promises: tenantId -> Promise<connection>
    // This is the thundering herd protection
    this.connectionPromises = new Map();
    
    // Configuration from environment
    this.config = {
      poolSize: parseInt(process.env.DB_POOL_SIZE) || 10,
      minPoolSize: parseInt(process.env.DB_MIN_POOL_SIZE) || 2,
      ttlMs: parseInt(process.env.DB_CONN_TTL_MS) || 30 * 60 * 1000,
      cleanupIntervalMs: parseInt(process.env.DB_CLEANUP_INTERVAL_MS) || 60000,
    };
    
    // Start the background eviction loop
    this.startCleanupLoop();
  }

  async getConnection(tenantId) {
    // 1. Check for existing healthy connection
    const existing = this.connections.get(tenantId);
    if (existing?.conn?.readyState === 1) {
      existing.lastUsedAt = Date.now();  // Touch for LRU
      return existing.conn;
    }

    // 2. Check for in-flight connection (thundering herd protection)
    if (this.connectionPromises.has(tenantId)) {
      return this.connectionPromises.get(tenantId);
    }

    // 3. Create new connection, cache the Promise immediately
    const connectionPromise = this.createConnection(tenantId);
    this.connectionPromises.set(tenantId, connectionPromise);

    try {
      const conn = await connectionPromise;
      this.connections.set(tenantId, {
        conn,
        lastUsedAt: Date.now()
      });
      return conn;
    } finally {
      // Always clean up the promise cache
      this.connectionPromises.delete(tenantId);
    }
  }
}

startCleanupLoop() {
  setInterval(() => {
    const now = Date.now();
    
    for (const [tenantId, entry] of this.connections.entries()) {
      const idleTime = now - entry.lastUsedAt;
      
      if (idleTime > this.config.ttlMs) {
        // Close the connection gracefully
        entry.conn.close().catch(err => {
          console.error(`Error closing connection for ${tenantId}:`, err);
        });
        
        this.connections.delete(tenantId);
        console.log(`[Evicted] ${tenantId} after ${Math.round(idleTime/1000)}s idle`);
      }
    }
  }, this.config.cleanupIntervalMs);
}

// middleware/tenantMiddleware.js
export const tenantMiddleware = async (req, res, next) => {
  const tenantId = req.headers['x-tenant-id'];

  if (!tenantId) {
    return res.status(400).json({
      success: false,
      error: 'Tenant ID is required',
      code: 'MISSING_TENANT_ID',
    });
  }

  try {
    // Get or create connection for this tenant
    const connection = await connectDB(tenantId);

    // Register Mongoose models on this connection (idempotent)
    registerAllModels(connection);

    // Attach connection to request for use in route handlers
    req.db = connection;
    req.tenantId = tenantId;

    next();
  } catch (err) {
    if (err.name === 'MongoServerSelectionError') {
      return res.status(503).json({
        success: false,
        error: 'Database temporarily unavailable',
        code: 'DB_UNAVAILABLE',
      });
    }
    res.status(500).json({
      success: false,
      error: 'Failed to connect to tenant database',
    });
  }
};

// WeakSet allows garbage collection of closed connections
const modelRegistry = new WeakSet();

export const registerAllModels = (conn) => {
  if (modelRegistry.has(conn)) return;  // Already registered

  // Register all models
  getProductModel(conn);
  getOrderModel(conn);
  getCustomerModel(conn);
  // ... other models
  
  modelRegistry.add(conn);
};

// Each model is a factory function
export default (conn) => {
  return conn.models.Product || conn.model('Product', productSchema);
};

// Sample response from /monitoring/connections
{
  "activeConnections": 47,
  "pendingConnections": 0,
  "trackedTenants": 47,
  "config": {
    "poolSize": 10,
    "ttlMinutes": 30
  },
  "metrics": {
    "totalConnectionsCreated": 156,
    "totalConnectionsClosed": 109,
    "totalEvictions": 89,
    "connectionErrors": 2
  }
}

// connectionManager.js - Compact Version
const mongoose = require('mongoose');

class ConnectionManager {
  constructor() {
    this.connections = new Map(); // tenantId -> { conn, promise, lastUsedAt }
    this.IDLE_TIMEOUT = 10 * 60 * 1000; // 10 minutes
    this.startEvictionLoop();
  }

  async getConnection(tenantId, uri) {
    const entry = this.connections.get(tenantId);

    // If active connection exists
    if (entry && entry.conn && entry.conn.readyState === 1) {
      entry.lastUsedAt = Date.now();
      return entry.conn;
    }

    // If a connection promise already exists (Thundering Herd protection)
    if (entry && entry.promise) {
      return entry.promise;
    }

    // Create + cache the connection promise
    const connectionPromise = mongoose
      .createConnection(uri, {
        maxPoolSize: 10,
        minPoolSize: 0,
        serverSelectionTimeoutMS: 5000,
      })
      .asPromise()
      .then((conn) => {
        this.connections.set(tenantId, {
          conn,
          promise: null,
          lastUsedAt: Date.now(),
        });
        return conn;
      })
      .catch((err) => {
        this.connections.delete(tenantId);
        throw err;
      });

    // Store the promise immediately
    this.connections.set(tenantId, {
      conn: null,
      promise: connectionPromise,
      lastUsedAt: Date.now(),
    });

    return connectionPromise;
  }

  startEvictionLoop() {
    setInterval(() => {
      const now = Date.now();
      for (const [tenantId, entry] of this.connections.entries()) {
        if (entry.conn && now - entry.lastUsedAt > this.IDLE_TIMEOUT) {
          entry.conn.close();
          this.connections.delete(tenantId);
          console.log(`[DB] Closed idle connection: ${tenantId}`);
        }
      }
    }, 60 * 1000); // Check every minute
  }
}

module.exports = new ConnectionManager();

// middleware/tenantDb.js
const connectionManager = require('../connectionManager');

module.exports = async (req, res, next) => {
  const tenantId = req.headers['x-tenant-id'];
  if (!tenantId) {
    return res.status(400).json({ error: 'Tenant ID required' });
  }

  const dbUri = `${process.env.MONGO_URI}-${tenantId}`;
  req.db = await connectionManager.getConnection(tenantId, dbUri);
  next();
};

store-product/
└── server/
    ├── app.js                          # Express app setup
    ├── config/
    │   ├── TenantConnectionManager.js  # The core connection manager
    │   ├── db.js                       # Thin wrapper for imports
    │   └── redis.js                    # Redis client (separate concern)
    ├── middleware/
    │   ├── tenantMiddleware.js         # Extracts tenant, gets connection
    │   └── errorMiddleware.js          # Error handling
    ├── utils/
    │   └── loadModels.js               # Model registration
    ├── models/
    │   └── product/
    │       ├── product.model.js        # Factory pattern models
    │       └── ...
    ├── controller/
    │   └── product.controller.js       # Uses req.db.models.Product
    └── routes/
        ├── product.route.js
        └── monitoringRoutes.js         # Exposes /monitoring/connections