Connection Pooling & Performance

Prorm manages database connections through a configurable pool so that repeated queries reuse warm connections instead of paying the cost of a fresh handshake every time. This guide covers how to size and tune the pool, retry transient failures, apply connection and query timeouts, watch pool health through events and metrics, and squeeze more performance out of individual queries. Every option shown here maps directly to the PoolOptions, RetryOptions, and QueryOptions types exposed by the library.

Two pools: the driver's and `prorm.pool`

[!IMPORTANT]
The connections your queries run on are pooled by the driver (pg.Pool
for PostgreSQL, mysql2's pool for MySQL, mssql's for SQL Server). The
top-level pool option sizes that driver pool, and a size in
dialectOptions wins over it. The same pool option also builds a separate
DatabaseConnectionPool, exposed as prorm.pool, which tracks placeholder
connections for events and stats; queries do not check connections out of
it. Before 4.9.2 the top-level pool did not reach the driver at all, so
pool.max alone changed nothing.

This was checked by constructing each dialect and reading back the driver
config it was given:

Options	Driver pool	`prorm.pool.max`
postgres, `pool: { max: 50 }`, `dialectOptions: { max: 25 }`	`max: 25`	50
postgres, `pool: { max: 50 }` only	`max: 50`	50
mysql, `pool: { max: 50 }`, `dialectOptions: { pool: { max: 15 } }`	`connectionLimit: 15`	50
mysql, `pool: { max: 50 }` only	`connectionLimit: 50`	50

On SQLite three concurrent prorm.query() calls left prorm.getPoolStats()
at acquired: 0: there is one connection and nothing is borrowed from the
tracking pool.

Size the driver's pool per dialect:

import { Prorm } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'postgres',
  host: process.env.DB_HOST,
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    max: 20,                        // pg.Pool size (dialect default 20)
    idleTimeoutMillis: 10_000,      // close a connection idle this long (default 30000)
    connectionTimeoutMillis: 5_000, // wait this long for a connection (default 2000)
    statementTimeout: 30_000,       // server-side statement_timeout per connection
  },
});

import { Prorm } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'mysql',
  host: process.env.DB_HOST,
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    pool: { max: 20 },      // becomes mysql2's connectionLimit (default 10)
    connectTimeout: 5_000,  // TCP + handshake
    idleTimeout: 60_000,    // close an idle connection after this long
  },
});

import { Prorm } from 'ts-prorm-orm';

// One file, one connection: there is no pool to size. Concurrent root
// transactions are queued so each runs BEGIN … COMMIT on its own.
const prorm = new Prorm({ dialect: 'sqlite', storage: './app.sqlite' });

Enabling the pool

Pass a pool object to the Prorm constructor. Once configured, Prorm creates a DatabaseConnectionPool, initializes it, and exposes it as prorm.pool, with the events and stats described below. Size the driver's pool alongside it, as above.

import { Prorm } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'postgres',
  host: 'localhost',
  port: 5432,
  database: 'app',
  username: 'app',
  password: 'secret',
  pool: {
    max: 20,   // never open more than 20 connections
    min: 5,    // keep at least 5 idle connections warm
    acquire: 30000, // wait up to 30s for a free connection
    idle: 10000,    // evict connections idle for 10s (down to `min`)
  },
});

If you omit pool, prorm.pool is null and connections are handled directly by the dialect driver.

The lifecycle of one query

This is the shape of every pool here: the driver's, and the one createPool()
builds. The path to watch is queue → timeout. Those errors say the pool is
exhausted, not that the database is slow — and they look identical from the
outside unless you check whether the database is actually busy.

Pool configuration

The pool key accepts PoolOptions. All fields are optional and fall back to the defaults below.

import { Prorm } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'mysql',
  pool: {
    max: 20,                // Maximum connections in the pool (default 10)
    min: 2,                 // Minimum idle connections to maintain (default 0)
    acquire: 60000,         // Max ms to wait for a connection (default 60000)
    idle: 30000,            // Max ms a connection may sit idle (default 30000)
    evict: 1000,            // How often (ms) to sweep for idle connections (default 1000)
    handleDisconnects: true,   // Automatically handle dropped connections
    enableEvents: true,        // Emit pool:* events (default true)
    logPoolOperations: false,  // Log every acquire/release/destroy to console
    onConnect: (conn) => {     // Called when a connection is acquired
      // e.g. set a session variable
    },
    onDisconnect: (conn) => {  // Called when a connection is released
      // e.g. reset session state
    },
  },
});

What min/max/idle/acquire actually do

max caps concurrency. When every connection is busy, further requests queue.

min keeps a floor of warm, idle connections so the first requests after a lull don't pay connection setup cost. The evictor never drops the pool below min.

idle is how long a connection may sit unused before the background evictor closes it (respecting min).

acquire is the ceiling on how long a caller waits in the queue for a free connection. If it expires, the acquire rejects with a ConnectionAcquireTimeoutError ("Acquisition timeout …") — a signal that max is too low or queries are holding connections too long.

evict controls how frequently the evictor timer runs its idle sweep.

Sizing the pool

Pick max based on your database server's connection limit and your app's concurrency, not arbitrarily high — every open connection consumes server resources. Keep the tracking pool's numbers in step with the driver's so its stats and events describe the same limits:

import { Prorm } from 'ts-prorm-orm';

// Low traffic
const low = new Prorm({
  dialect: 'postgres',
  pool: { max: 10, min: 2, acquire: 30000, idle: 30000 },
  dialectOptions: { max: 10, idleTimeoutMillis: 30000 },
});

// Medium traffic
const medium = new Prorm({
  dialect: 'postgres',
  pool: { max: 20, min: 5, acquire: 30000, idle: 10000 },
  dialectOptions: { max: 20, idleTimeoutMillis: 10000 },
});

// High traffic
const high = new Prorm({
  dialect: 'postgres',
  pool: { max: 50, min: 10, acquire: 20000, idle: 10000 },
  dialectOptions: { max: 50, idleTimeoutMillis: 10000 },
});

The number that matters is the total across processes. Every instance of
your app opens its own pool, so the database sees instances × max:

	Value
PostgreSQL `max_connections`	200
reserved for admin, migrations, replication	20
app instances at peak (autoscaled)	8
per-instance `max`	(200 − 20) / 8 = 22

[!WARNING]
Raising max when you add instances is how a deploy ends with too many connections on every request. Recompute the budget whenever the instance
count can grow, or put PgBouncer or a managed proxy between the app and the
database.

Connection retry

Transient failures — a database restart, a momentary network blip, "too many connections" — are worth retrying. Retry is applied by the dialect around each statement, from RetryOptions. A default for every statement goes in the top-level retry option, which PostgreSQL, MySQL and SQL Server read, or in dialectOptions.retry, which wins over it. Before 4.9.2 the top-level retry was not forwarded, and PostgreSQL ignored it:

import { Prorm } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'mysql',
  dialectOptions: {
    retry: {
      max: 3,               // Retry up to 3 times (default 3)
      timeout: 1000,        // Base delay between attempts in ms (default 1000)
      backoff: true,        // Use exponential backoff (default false)
      backoffMultiplier: 2, // Delay grows by this factor each attempt (default 2)
      backoffMax: 10000,    // Cap the backoff delay at 10s (default 10000)
      match: [              // Only retry when the error message matches one of these
        'ECONNREFUSED',
        'ETIMEDOUT',
        'too many connections',
        'Connection terminated',
      ],
    },
  },
});

When match is omitted, the dialect falls back to a built-in list of common transient connection errors (for example ECONNREFUSED, ENOTFOUND, ETIMEDOUT, connection timeout, too many connections). With backoff: true, the wait between attempts is timeout * backoffMultiplier^attempt, capped at backoffMax.

Per-query retry

Every pooled SQL dialect with retry support (PostgreSQL, MySQL, MariaDB,
SQLite, DuckDB, CockroachDB, Redshift) honours retry in the query options.
This is the portable way to opt a statement in:

// Retry this read up to 3 times with default error matching
const rows = await prorm.query('SELECT * FROM users', {
  retry: { max: 3 },
});

// Retry only on specific, known-transient error patterns
await prorm.query('SELECT * FROM reports', {
  retry: { max: 5, match: ['Lock not available', 'too many connections'] },
});

The defaults for match are per dialect: SQLite retries SQLITE_BUSY,
SQLITE_LOCKED and database is locked; MySQL adds Lock wait timeout and
Deadlock to the connection errors.

[!NOTE]
Retry is skipped for a statement that runs inside a transaction. After an
error the transaction is aborted and every later statement on it fails until
ROLLBACK, so re-sending one statement can only produce a second, misleading
error. Retry the whole transaction with
transaction(fn, { retry }).

Timeouts

There are three timeouts worth tuning, at three different layers: getting a
connection, keeping one, and running a statement. Each dialect names them
differently, and all of them go in dialectOptions:

import { Prorm } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'postgres',
  dialectOptions: {
    connectionTimeoutMillis: 5_000, // connect, or wait for a free pooled connection
    idleTimeoutMillis: 60_000,      // close a connection idle this long
    statementTimeout: 15_000,       // server cancels a statement after this
    queryTimeout: 20_000,           // client gives up waiting for a result
  },
});

A statement that runs past statementTimeout fails with

canceling statement
due to statement timeout

import { Prorm } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'mysql',
  dialectOptions: {
    connectTimeout: 5_000, // TCP + handshake
    idleTimeout: 60_000,   // close a pooled connection idle this long
  },
});

// A per-statement ceiling is an optimizer hint in the SQL itself:
await prorm.query('SELECT /*+ MAX_EXECUTION_TIME(15000) */ * FROM big_table');

The connect timeout governs the initial TCP/auth handshake.

The idle timeout is the driver-level lifetime of an unused physical connection.

The statement timeout is the only one that stops a slow query; the others only bound waiting.

The top-level connectTimeout / idleTimeout options reach the driver in
its own spelling (connectionTimeoutMillis / idleTimeoutMillis for pg,
connectTimeout / idleTimeout for mysql2, connectionTimeout for
mssql); a value in dialectOptions wins. The per-query timeout option is
sent as node-postgres query_timeout and as the mysql2 timeout, and a
statement that runs past it fails with a TimeoutError carrying .sql:

await prorm.query('SELECT pg_sleep(5)', { timeout: 50 }); // TimeoutError

Before 4.9.2 these options were declared in the types but not read.

Inspecting the pool

Prorm exposes the live pool and a lightweight stats snapshot.

// The pool instance (or null if pooling isn't configured)
const pool = prorm.getPool();

// A quick stats snapshot
const stats = prorm.getPoolStats();
// => { size, available, inUse, pending } | null

if (stats) {
  console.log(`${stats.available}/${stats.size} idle, ${stats.inUse} busy, ${stats.pending} waiting`);
}

The pool object surfaces the same numbers as properties, plus configured limits:

console.log('Pool size:', prorm.pool?.size);
console.log('Available:', prorm.pool?.available);
console.log('In use:', prorm.pool?.used);      // alias for inUse
console.log('Pending:', prorm.pool?.pending);
console.log('Max / Min:', prorm.pool?.max, prorm.pool?.min);
console.log('Destroyed?', prorm.pool?.isDestroyed);

These numbers describe prorm.pool. For the connections your queries actually
use on PostgreSQL, read the driver's pool, which the dialect returns from
getConnection():

import type { Pool } from 'pg';

const driverPool = prorm.getDialectInstance()?.getConnection() as Pool | null;
if (driverPool) {
  console.log({
    total: driverPool.totalCount,     // open connections
    idle: driverPool.idleCount,       // open and free
    waiting: driverPool.waitingCount, // callers queued for one
  });
}

A pending (or waitingCount) that stays above zero means requests are queuing for connections — a strong hint to raise max or to find the queries that hold connections too long.

Pool events

When enableEvents is on (the default), Prorm forwards prorm.pool's lifecycle events on its own emitter under the pool: prefix. They fire when that pool's connections are acquired and released (by your own prorm.pool.acquire() calls, for instance), not for each query, which runs on the driver's pool; for per-query monitoring use the query event in the next section.

prorm.on('pool:acquire', (conn) => {
  // a connection left the pool for a caller
});

prorm.on('pool:release', (conn) => {
  // a connection returned to the pool
});

prorm.on('pool:create', (conn) => { /* new physical connection opened */ });
prorm.on('pool:remove', (conn) => { /* connection removed from the pool */ });
prorm.on('pool:enqueue', () => { /* a request had to wait for a connection */ });
prorm.on('pool:dequeue', () => { /* a waiting request was served */ });

prorm.on('pool:timeout', () => {
  console.warn('A connection acquisition timed out — pool may be undersized');
});

prorm.on('pool:error', (err) => {
  console.error('Pool error:', err);
});

Repeated pool:enqueue and pool:timeout events are the clearest signal that the pool is saturated.

Logging and slow-query detection

Prorm emits a query event after every statement and a slowQuery event whenever a query exceeds slowQueryThreshold. Combined with the pool metrics, these are your main performance hooks.

const prorm = new Prorm({
  dialect: 'postgres',
  logging: (sql, timing) => console.log(`(${timing}ms) ${sql}`),
  logSlowQueries: true,     // enabled by default
  slowQueryThreshold: 200,  // ms; queries over this fire `slowQuery` (default 1000)
});

prorm.on('query', (event) => {
  // { sql, duration, timestamp, type, model?, error? }
  metrics.observe('query_duration_ms', event.duration);
});

prorm.on('slowQuery', (event) => {
  // { sql, duration, threshold, ... }
  console.warn(`Slow query (${event.duration}ms > ${event.threshold}ms): ${event.sql}`);
});

You can override logging per query — for example to silence a noisy statement or to route it to a custom sink:

await prorm.query('SELECT 1', { logging: false });

await prorm.query('SELECT * FROM audit_log', {
  logging: (sql, time) => auditLogger.info({ sql, time }),
});

Per-query performance tips

Reuse the pool, don't rebuild it. Construct one Prorm instance per process. Creating a new instance per request defeats pooling entirely.

Keep connections checked out briefly. The pool serializes access once max is hit; long-running work while holding a connection starves everyone else. Do heavy in-memory processing after releasing.

Use raw when you don't need mapping. Skipping model hydration avoids per-row object construction:

const rows = await prorm.query('SELECT id, name FROM users', { raw: true });

Set a slowQueryThreshold low enough to catch regressions, then let the slowQuery event surface the offenders so you can add indexes or narrow selections.

Match pool.max to real concurrency. An oversized pool can overwhelm the database server; an undersized one queues requests. Watch the driver pool's waitingCount (see Inspecting the pool) under real load to find the right value.

Graceful shutdown

Close the instance on shutdown. prorm.close() disconnects the dialect (ending the driver's pool) and destroys prorm.pool, whose evictor timer would otherwise keep the process alive.

async function shutdown() {
  await prorm.close(); // driver pool ended, prorm.pool destroyed
  process.exit(0);
}

process.on('SIGINT', shutdown);
process.on('SIGTERM', shutdown);

To tear down only the tracking pool, call prorm.pool.destroy(); with no argument it tears down the whole pool, and passing a single connection destroys just that one.

Advanced: the standalone pool

The pool that Prorm builds internally is also usable on its own via createPool. This is useful when you manage raw driver connections yourself but still want pooling, health checks, and validation. It accepts everything in PoolOptions plus factory, healthCheck, validateOnBorrow, and reconnection settings.

import { createPool } from 'ts-prorm-orm';

const pool = createPool({
  max: 10,
  min: 2,
  acquire: 30000,
  idle: 10000,
  factory: async () => driver.connect(), // opens a real connection
  validateOnBorrow: true,
  validationQuery: 'SELECT 1',
  healthCheck: {
    enabled: true,
    interval: 30000,
    timeout: 5000,
    query: 'SELECT 1',
    maxFailures: 3,
  },
});

async function withConnection<T>(fn: (conn: any) => Promise<T>): Promise<T> {
  const conn = await pool.acquire();
  try {
    return await fn(conn);
  } finally {
    pool.release(conn); // always release, even on error
  }
}

Always release connections in a finally block. A connection that is never released stays "in use" forever, and once every connection leaks the pool exhausts and subsequent acquire() calls time out.

Here is that exhaustion, run for real against a pool of two placeholder
connections with a 200 ms acquire:

import { createPool } from 'ts-prorm-orm';

let opened = 0;
const pool = createPool({ max: 2, acquire: 200, factory: async () => ({ id: ++opened }) });
pool.on('release', (c) => console.log('release', c.id));
pool.on('timeout', () => console.log('timeout event'));

const a = await pool.acquire();
const b = await pool.acquire();          // pool is now full
const waiter = pool.acquire();           // queued
pool.release(a);                         // hands connection 1 to the waiter
console.log('waiter got', (await waiter).id);

try {
  await pool.acquire();                  // nothing frees within 200 ms
} catch (err) {
  console.log((err as Error).message);
}
pool.release(b);
await pool.destroy();

release 1
waiter got 1
timeout event
Acquisition timeout after 200ms. Pool size: 2, max: 2
release 2

The timeout rejects with a ConnectionAcquireTimeoutError, a ConnectionError
with code CONNECTION_TIMED_OUT, so isRetryableError() answers true for
it and its timeout property says how long the caller waited. pool.pending
and pool.stats.pending both count the callers waiting in the queue. Before
4.9.2 the timeout was a plain Error, and pool.pending stayed at 0.

Replication — splitting reads and writes across hosts

SQL constants — why session state and pools interact badly

Connection Pooling & Performance

Two pools: the driver's and prorm.pool#

Enabling the pool#

The lifecycle of one query#

Pool configuration#

What min/max/idle/acquire actually do#

Sizing the pool#

Connection retry#

Per-query retry#

Timeouts#

Inspecting the pool#

Pool events#

Logging and slow-query detection#

Per-query performance tips#

Graceful shutdown#

Advanced: the standalone pool#

Related reading#