Copy page

DAL Library

Connection Pool and Session Configuration

Connection Pool and Session Configuration

Relevant source files

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

• src/dal/dal.ts
• src/dal/type-parsers.ts
• test/dal.test.ts

The Dal class serves as the high-level gateway for the @primebrick/dal-pg library. It centralizes pool management, session configuration, and lifecycle control, ensuring that best practices for PostgreSQL performance and reliability are applied consistently across the application.

Pool Initialization and Configuration

The Dal gateway initializes a pg.Pool using a combination of user-provided settings and library defaults. These defaults are tuned to prevent common issues such as connection starvation and "hanging" requests in high-concurrency environments.

Configuration Defaults

The DalConfig interface defines the parameters for the connection pool src/dal/dal.ts:54-84. If optional values are omitted, the following defaults are applied src/dal/dal.ts:93-99:

Parameter	Default	Description
max	10	Maximum number of clients in the pool.
statementTimeoutMs	30000	Global statement_timeout (30s) to prevent slow queries from blocking the pool.
connectionTimeoutMillis	5000	Time to wait for a connection before failing fast (5s).
idleTimeoutMillis	30000	How long an idle connection is kept before being closed.
applicationName	"primebrick-dal"	Identifier for the connection in pg_stat_activity.

Implementation Flow

When a Dal instance is constructed, it merges the provided DalConfig with DEFAULTS src/dal/dal.ts:116-125, registers global type parsers src/dal/dal.ts:128, and configures the underlying pg.Pool src/dal/dal.ts:131-166.

Pool Setup Logic The following diagram illustrates the transition from configuration objects to the active pg.Pool instance.

Title: Dal Initialization and Pool Setup

Code
graph TD
  subgraph "Configuration Space"
    DC["DalConfig"]
    DEF["DEFAULTS"]
  end

  subgraph "Code Entity Space"
    DAL_CTOR["Dal.constructor()"]
    ETP["ensureTypeParsers()"]
    OC["onConnectHandler"]
    POOL["pg.Pool"]
    REPO["Repository"]
  end

  DC --> DAL_CTOR
  DEF --> DAL_CTOR
  DAL_CTOR --> ETP
  DAL_CTOR --> OC
  OC -- "passed to" --> POOL
  POOL -- "injected into" --> REPO
  DAL_CTOR -- "assigns" --> REPO

Sources: src/dal/dal.ts:110-170, src/dal/type-parsers.ts:21-35

Session Configuration via onConnect

The Dal class utilizes the onConnect hook of pg.Pool to execute session-level SQL commands every time a new physical connection is established to the database src/dal/dal.ts:147-163. This ensures that the environment is correctly primed before any application queries are executed.

The onConnectHandler performs the following actions:

1. Search Path: If a schema is provided in DalConfig, it executes SET search_path TO <schema> using quoteIdent for safety src/dal/dal.ts:149-151.
2. Statement Timeout: Sets the session-level statement_timeout to the configured statementTimeoutMs src/dal/dal.ts:152-154.
3. Application Name: Sets application_name for server-side observability, with single-quote escaping to prevent injection src/dal/dal.ts:155-159.

Sources: src/dal/dal.ts:143-164, src/query/query-builder.ts:12-19

Singleton Management with getDal()

To prevent multiple connection pools from being created for the same database (which could lead to exceeding max_connections on the PostgreSQL server), the library provides a singleton factory.

getDal() and resetDal()

The getDal(config) function ensures that only one Dal instance exists per process src/dal/dal.ts:193-211.

• First Call: Initializes the singleton with the provided DalConfig.
• Subsequent Calls: Returns the existing instance.
• Enforcement: If getDal is called with a connectionString that differs from the existing instance, it throws an error to prevent accidental cross-talk src/dal/dal.ts:202-206.

Title: Singleton Factory Data Flow

Code
flowchart TD
  START(["getDal(config)"])
  CHECK_EXIST{"Is 'instance' defined?"}
  CHECK_CONN{"config.connectionString == instance.config.connectionString?"}
  THROW[["Throw Error: Connection String Mismatch"]]
  CREATE["instance = new Dal(config)"]
  RETURN(["Return instance"])

  START --> CHECK_EXIST
  CHECK_EXIST -- "No" --> CREATE
  CHECK_EXIST -- "Yes" --> CHECK_CONN
  CHECK_CONN -- "No" --> THROW
  CHECK_CONN -- "Yes" --> RETURN
  CREATE --> RETURN

Sources: src/dal/dal.ts:184-211

Graceful Shutdown

The close(timeoutMs) method provides a mechanism to drain the connection pool gracefully src/dal/dal.ts:241-274.

Implementation Details

• State Guards: Uses _isClosing and closed flags to prevent re-entrant calls src/dal/dal.ts:242-250.
• Race Condition Handling: It uses Promise.race between the pool.end() call and a timer src/dal/dal.ts:260-268. This ensures that the process can exit even if some connections are "stuck" or the database is unresponsive.
• Timeout: The default timeout is 10,000ms src/dal/dal.ts:241.

Method	Role
dal.close()	Initiates pool drainage and sets closed = true.
dal.isClosed	Getter returning the final state of the pool src/dal/dal.ts:229.
dal.isClosing	Getter returning true only during the pool.end() execution src/dal/dal.ts:234.

Sources: src/dal/dal.ts:241-274, test/dal.test.ts:77-136

Type Parser Registration

The Dal gateway automatically registers global type parsers for the pg module via ensureTypeParsers() src/dal/type-parsers.ts:21-35. This is done once per process to handle specific PostgreSQL types that do not have a 1:1 mapping in standard JavaScript:

1. INT8 (BigInt): PostgreSQL bigint columns are parsed into native JavaScript bigint objects instead of strings src/dal/type-parsers.ts:25.
2. NUMERIC: Parsed into a number if the value is within Number.MAX_SAFE_INTEGER and has no fractional part; otherwise, it is returned as a string to preserve precision src/dal/type-parsers.ts:28-32.

Sources: src/dal/dal.ts:128, src/dal/type-parsers.ts:1-35

---