# Connection Pool and Session Configuration

# Connection Pool and Session Configuration

<details>
<summary>Relevant source files</summary>

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

- [src/dal/dal.ts](src/dal/dal.ts)
- [src/dal/type-parsers.ts](src/dal/type-parsers.ts)
- [test/dal.test.ts](test/dal.test.ts)

</details>



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
```mermaid
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
```mermaid
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]()

---