# Type Coercion: JS ↔ PostgreSQL

# Type Coercion: JS ↔ PostgreSQL

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

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

- [src/dal/type-parsers.ts](src/dal/type-parsers.ts)
- [src/meta/column-pg-io.ts](src/meta/column-pg-io.ts)
- [src/meta/entity-ts-to-pg.ts](src/meta/entity-ts-to-pg.ts)

</details>



The `@primebrick/dal-pg` library provides a transparent bridge between JavaScript's dynamic type system and PostgreSQL's strict schema. This page details how data is transformed during the "outbound" trip (JS values to SQL parameters) and the "inbound" trip (database wire values to JS entities), as well as the specialized type parsers registered on the `pg` driver to handle numeric precision and large integers.

## Overview of the Coercion Pipeline

Data transformation occurs at two distinct boundaries:
1.  **Global Type Parsers**: Configured once per process via `pg.types.setTypeParser`. These handle the raw bytes coming off the wire for specific OIDs (Object Identifiers) like `INT8` and `NUMERIC` [src/dal/type-parsers.ts:1-11]().
2.  **Metadata-Driven Mapping**: Performed by `jsValueToPgParam` and `pgValueToJsValue`. These functions use entity metadata (such as `@Column` decorators) to decide how to format values for the driver or hydrate them for the application [src/meta/column-pg-io.ts:1-7]().

### Data Flow Diagram

The following diagram illustrates the transformation path for different data types as they move between the Application Layer and the Database.

**Title: Type Coercion Data Flow**
```mermaid
graph TD
    subgraph "Application Space (JS/TS)"
        JS_BigInt["BigInt"]
        JS_Date["Date Object"]
        JS_Obj["Object / Array"]
        JS_Num["Number (Safe)"]
    end

    subgraph "DAL Transformation (column-pg-io.ts)"
        J2P["jsValueToPgParam()"]
        P2J["pgValueToJsValue()"]
    end

    subgraph "Driver Layer (node-pg + type-parsers.ts)"
        TP["ensureTypeParsers()"]
        INT8_P["INT8 Parser"]
        NUM_P["NUMERIC Parser"]
    end

    subgraph "PostgreSQL Space"
        PG_BINT["BIGINT (INT8)"]
        PG_TZ["TIMESTAMPTZ"]
        PG_DATE["DATE (YYYY-MM-DD)"]
        PG_JSONB["JSONB"]
        PG_NUM["NUMERIC"]
    end

    JS_BigInt --> J2P --> PG_BINT
    PG_BINT --> INT8_P --> JS_BigInt

    JS_Date -- "Default" --> J2P --> PG_TZ
    JS_Date -- "pgType: 'date'" --> J2P --> PG_DATE
    
    PG_TZ --> P2J --> JS_Date
    PG_DATE --> P2J --> JS_Date

    JS_Obj -- "JSON.stringify" --> J2P --> PG_JSONB
    PG_JSONB --> JS_Obj

    PG_NUM --> NUM_P -- "If > MAX_SAFE_INTEGER" --> JS_Str["String"]
    PG_NUM --> NUM_P -- "If Safe" --> JS_Num
```
Sources: [src/dal/type-parsers.ts:21-35](), [src/meta/column-pg-io.ts:43-73]()

## Global Type Parsers

To ensure data integrity for large numbers, the DAL registers custom parsers during initialization. This is handled by `ensureTypeParsers()`, which is idempotent to prevent multiple registrations in multi-database or HMR (Hot Module Replacement) scenarios [src/dal/type-parsers.ts:15-22]().

### BigInt (INT8)
By default, `node-pg` returns `bigint` columns as strings because JavaScript `Number` cannot safely represent the full 64-bit range. The DAL overrides this to return native JS `BigInt` objects [src/dal/type-parsers.ts:24-25]().

### Numeric Precision
The `NUMERIC` parser implements logic to prevent precision loss while maintaining developer ergonomics:
-   If the value contains a decimal point, it is returned as a `number` [src/dal/type-parsers.ts:29-31]().
-   If the value is an integer but exceeds `Number.MAX_SAFE_INTEGER` (2^53 - 1), it is returned as a `string` to avoid silent rounding [src/dal/type-parsers.ts:30]().
-   Otherwise, it is returned as a `number` [src/dal/type-parsers.ts:31]().

Sources: [src/dal/type-parsers.ts:21-35]()

## Outbound Coercion: JS to PostgreSQL

The function `jsValueToPgParam` prepares JavaScript values for use in parameterized queries. It relies on `ColumnPgPersistenceHints` derived from entity metadata [src/meta/column-pg-io.ts:43-45]().

| JS Type | Target PG Type | Transformation Logic |
| :--- | :--- | :--- |
| `Date` | `date` | Converted to `YYYY-MM-DD` string [src/meta/column-pg-io.ts:47-49](). |
| `Date` | `timestamptz` | Passed as-is (driver handles ISO conversion) [src/meta/column-pg-io.ts:50](). |
| `string` | `timestamptz` | If the column name ends in `_at` or is date-like, the string is parsed into a `Date` before sending [src/meta/column-pg-io.ts:52-55](). |
| `Object`/`Array` | `jsonb` / `json` | Serialized via `JSON.stringify()` [src/meta/column-pg-io.ts:57-59](). |

Sources: [src/meta/column-pg-io.ts:43-61]()

## Inbound Coercion: PostgreSQL to JS

When rows are returned from the database, `pgValueToJsValue` hydrates them back into appropriate JavaScript types.

### Date and Timestamp Hydration
The system identifies "logical date columns" based on two criteria:
1.  The TypeScript design type is `Date` [src/meta/column-pg-io.ts:36]().
2.  The SQL column name ends with the suffix `_at` (e.g., `created_at`, `updated_at`) [src/meta/column-pg-io.ts:36]().

If a column is date-like, strings or numbers returned by the driver are coerced into JS `Date` objects [src/meta/column-pg-io.ts:68-71]().

### JSON Hydration
While the driver often parses `JSONB` automatically, the DAL provides `hydrateEntityDateFieldsFromJson` to handle cases where an entity is initialized from a plain JSON object (e.g., from an API request). This ensures that ISO strings in date-like fields are converted to `Date` instances [src/meta/column-pg-io.ts:80-96]().

Sources: [src/meta/column-pg-io.ts:64-73](), [src/meta/column-pg-io.ts:80-96]()

## Type Inference Logic

When explicit types are not provided via `@Column({ pgType: '...' })`, the DAL infers the PostgreSQL type using a hierarchy of heuristics in `inferPgTypeFromEntityColumn` [src/meta/entity-ts-to-pg.ts:137-143]().

**Title: Type Inference Hierarchy**
```mermaid
graph TD
    Start["inferPgTypeFromEntityColumn()"] --> Explicit{"Explicit pgType?"}
    Explicit -- "Yes" --> Modifiers["applyTypeModifiers()"]
    Explicit -- "No" --> DesignType{"TS Design Metadata?"}
    
    DesignType -- "Found" --> MapDesign["inferFromDesignTypeName()"]
    DesignType -- "Not Found" --> Heuristics["inferFromSqlAndPropertyNames()"]
    
    MapDesign --> Modifiers
    Heuristics --> Modifiers
    
    subgraph "Heuristics (Naming)"
        H1["'uuid' -> uuid"]
        H2["'id' + Key -> bigint"]
        H3["'*_at' -> timestamptz"]
        H4["Default -> text"]
    end
    
    Heuristics --- H1
    Heuristics --- H2
    Heuristics --- H3
    Heuristics --- H4
```
Sources: [src/meta/entity-ts-to-pg.ts:60-90](), [src/meta/entity-ts-to-pg.ts:92-118](), [src/meta/entity-ts-to-pg.ts:137-143]()

### Precision and Scale Modifiers
The `applyTypeModifiers` function handles length constraints for strings (`varchar(N)`) and precision/scale for decimals (`numeric(P,S)`). If `precision` is defined but `scale` is not, it defaults to a single-parameter numeric type [src/meta/entity-ts-to-pg.ts:25-58]().

Sources: [src/meta/entity-ts-to-pg.ts:25-58]()

---