Copy page

DAL Library

Type Coercion: JS ↔ PostgreSQL

Type Coercion: JS ↔ PostgreSQL

Relevant source files

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

• src/dal/type-parsers.ts
• src/meta/column-pg-io.ts
• src/meta/entity-ts-to-pg.ts

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

Code
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

Code
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

---