Copy page

DAL Library

Timeout Management and withClient

Timeout Management and withClient

Relevant source files

The following files were used as context for generating this wiki page:

• docs/ai/dal-usage-guide.md
• src/dal/dal.ts
• test/dal-timeout.test.ts

Timeout management is a core anti-throttling strategy in @primebrick/dal-pg designed to prevent slow queries from starving the connection pool src/dal/dal.ts:15-18. The library enforces a statement_timeout at the session level by default and provides mechanisms to override this for specific long-running operations or transactions without leaking those settings to subsequent users of the connection src/dal/dal.ts:23-30.

Anti-Throttling Strategy

The DAL implements a multi-layered timeout strategy to ensure high-async REST traffic remains responsive:

1. Global Session Default: Every connection acquired by the Dal instance is initialized with a statement_timeout (default 30s) src/dal/dal.ts:66-69.
2. Fail-Fast Connection Acquisition: The connectionTimeoutMillis (default 5s) ensures that if the pool is exhausted, the application errors quickly rather than queuing requests indefinitely src/dal/dal.ts:71-73.
3. Transaction-Scoped Overrides: For bulk operations, the DAL uses SET LOCAL statement_timeout within a transaction, ensuring the setting automatically reverts on COMMIT or ROLLBACK src/dal/dal.ts:23-26.
4. Client-Scoped Overrides: The withClient pattern allows manual overrides for ad-hoc long queries, with a guaranteed reset to the session default upon releasing the client back to the pool src/dal/dal.ts:27-28.

Timeout Measurement

The statement_timeout measures the full wall-clock time from the moment the command arrives at the server until the server completes the command and transmits all result rows back to the client docs/ai/dal-usage-guide.md:124-126.

Sources: src/dal/dal.ts:15-30, src/dal/dal.ts:66-73, docs/ai/dal-usage-guide.md:124-126

---

The withClient Pattern

The withClient(fn, options) method is the primary tool for manual transaction management and per-call timeout overrides src/dal/dal.ts:251-253.

Implementation and Data Flow

When withClient is called:

1. A PoolClient is acquired from the pg.Pool src/dal/dal.ts:258.
2. If timeoutMs is provided, the DAL executes SET statement_timeout TO <ms> on that specific client src/dal/dal.ts:262-264.
3. The provided function fn(client) is executed src/dal/dal.ts:267.
4. Leakage Prevention: In the finally block, if a custom timeout was set, it is reset to the global config.statementTimeoutMs before the client is released back to the pool src/dal/dal.ts:270-272.

Code Entity Interaction

The following diagram illustrates how withClient manages the lifecycle of a PoolClient and its settings.

Client Lifecycle and Timeout Reset

Code
sequenceDiagram
    participant App as "Consumer Code"
    participant Dal as "Dal Instance"
    participant Pool as "pg.Pool"
    participant Client as "pg.PoolClient"

    App->>Dal: withClient(fn, { timeoutMs: 5000 })
    Dal->>Pool: connect()
    Pool-->>Dal: Client
    Dal->>Client: query("SET statement_timeout TO 5000")
    
    Dal->>App: fn(Client)
    Note over App: Perform manual queries<br/>or Repository(Client) ops
    App-->>Dal: Result / Error
    
    Dal->>Client: query("SET statement_timeout TO 30000")
    Note right of Client: Reset to session default
    Dal->>Client: release()
    Dal-->>App: Final Result

Sources: src/dal/dal.ts:251-280, test/dal-timeout.test.ts:157-179

---

Per-Call Overrides in Bulk Operations

Bulk operations (addMany, upsertMany, updateMany) accept a timeoutMs in their options src/types/types.ts:168-171. Unlike withClient, these methods use SET LOCAL within a transaction block src/dal/dal.ts:23-26.

Transactional Scoping

Because bulk operations are wrapped in a transaction, the DAL issues SET LOCAL statement_timeout. This PostgreSQL command ensures the timeout is only active for the duration of the current transaction. This provides a second layer of safety against timeout leakage, as the database engine itself reverts the setting upon transaction termination src/dal/dal.ts:23-26.

Method	Override Mechanism	Scope
addMany	SET LOCAL statement_timeout	Transaction
upsertMany	SET LOCAL statement_timeout	Transaction
updateMany	SET LOCAL statement_timeout	Transaction
withClient	SET statement_timeout + Manual Reset	Connection Session

Sources: src/dal/dal.ts:23-30, src/types/types.ts:168-171, test/dal-timeout.test.ts:31-35

---

Configuration Defaults

The Dal gateway applies these defaults unless overridden during initialization via DalConfig src/dal/dal.ts:93-99.

Property	Code Constant	Default Value	Description
statementTimeoutMs	DEFAULTS.statementTimeoutMs	30000 (30s)	Max duration for a single SQL command execution.
connectionTimeoutMillis	DEFAULTS.connectionTimeoutMillis	5000 (5s)	Max time to wait for a free connection from the pool.
max	DEFAULTS.max	10	Maximum number of concurrent connections in the pool.

Sources: src/dal/dal.ts:54-84, src/dal/dal.ts:93-99

---

Technical Flow: Connection Initialization

When a new connection is created (the onConnect event), the DAL applies the base session configuration src/dal/dal.ts:147-163.

Entity Mapping: Configuration to SQL Commands

Code
graph TD
    subgraph "DalConfig (JS Space)"
        SC["schema"]
        ST["statementTimeoutMs"]
        AN["applicationName"]
    end

    subgraph "PostgreSQL Session (SQL Space)"
        SP["SET search_path TO ..."]
        STT["SET statement_timeout TO ..."]
        SAN["SET application_name TO ..."]
    end

    SC -->|quoteIdent| SP
    ST -->|numeric value| STT
    AN -->|escaped string| SAN

    subgraph "dal.ts Entities"
        OC["onConnectHandler"]
        DAL["Dal Class"]
    end

    OC -.-> SP
    OC -.-> STT
    OC -.-> SAN
    DAL --> OC

Sources: src/dal/dal.ts:147-164, src/dal/dal.ts:116-125

---