Repair Connection Pool Exhaustion & Recovery

I have analyzed the transaction middleware snippet. The issue is a missing finally block in the custom db-transaction decorator, which fails to release connections when an error occurs during the commit phase.

Connection Pool Diagnosis

Proposed Repair

// middleware/transaction.ts
import { Request, Response, NextFunction } from 'express';
import { DataSource } from 'typeorm';

export const transactionMiddleware = (dataSource: DataSource) => {
  return async (req: Request, res: Response, next: NextFunction) => {
    const queryRunner = dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      (req as any).transactionManager = queryRunner.manager;
      await next();
      if (queryRunner.isTransactionActive) {
        await queryRunner.commitTransaction();
      }
    } catch (err) {
      if (queryRunner.isTransactionActive) {
        await queryRunner.rollbackTransaction();
      }
      throw err;
    } finally {
      // FIX: Ensure connection returns to pool regardless of outcome
      if (!queryRunner.isReleased) {
        await queryRunner.release();
      }
    }
  };
};

Next steps

1. Apply the finally block to the transactionMiddleware to ensure queryRunner.release() is always called.
2. Monitor the pg_stat_activity view to verify that active connections scale down during idle periods.
3. Add a unit test using a mock QueryRunner to verify release is called even when next() throws an exception.