Copy page

DAL Library

Key Design Decisions

Key Design Decisions

Relevant source files

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

• .devin/skills/dal-usage/SKILL.md
• README.md
• docs/ai/dal-usage-guide.md
• src/index.ts

This page documents the core architectural choices and philosophical foundations of the @primebrick/dal-pg library. These decisions prioritize performance, safety, and consistency across the Primebrick v3 ecosystem.

Architectural Philosophy

The DAL is designed as a type-driven, metadata-heavy layer that bridges TypeScript classes to PostgreSQL tables without the overhead of a full ORM. It favors explicit configuration via decorators over convention-based magic, while enforcing strict anti-throttling measures to protect the database under high-concurrency workloads.

1. Snake_Case Everywhere

To eliminate the "transformation tax" common in Node.js applications, the DAL enforces snake_case for database columns, TypeScript entity properties, and JSON payloads.

• Implementation: The @Column decorator assumes the property name matches the database column name unless explicitly overridden src/meta/entity-meta.ts:333-338.
• Data Flow: Data fetched from the wire via pg is hydrated directly into entity instances. Since the property names are already in snake_case, no key-remapping logic is required during serialization to JSON README.md:42-42.

2. RETURNING * on All Writes

Every write operation (add, update, upsert, delete, restore) appends a RETURNING * clause to the SQL statement README.md:43-43.

• Benefit: This ensures that the application always has the most up-to-date state of the record, including database-generated defaults (e.g., serial IDs), triggers, and audit timestamps, without requiring a secondary SELECT query docs/ai/dal-usage-guide.md:231-235.
• Hydration: The resulting row is passed through pgValueToJsValue to ensure proper type coercion (e.g., converting timestamptz strings to JS Date objects) src/meta/column-pg-io.ts:108-142.

3. "Throw If Not Found" by Default

Finders (findById, findByUUID, find) default to throwing a NotFoundError if zero rows match the criteria docs/ai/dal-usage-guide.md:143-144.

• Rationale: In most business logic, requesting a specific resource that does not exist is an exceptional state. This reduces "if (null)" checks in consumer code.
• Override: Consumers can opt-out by passing { throwIfNotFound: false } in the options, which causes the method to return null instead docs/ai/dal-usage-guide.md:146-147.

4. Soft-Delete Visibility (EXCLUDED by Default)

The DAL natively supports soft-deletion via the @DeletableField decorator src/meta/entity-meta.ts:251-267.

• Default Behavior: All read operations automatically append a WHERE deleted_at IS NULL clause (or equivalent) unless specified otherwise README.md:45-45.
• Visibility Control: The deletedRecords option allows three modes: "EXCLUDED" (default), "ONLY", and "INCLUDED" docs/ai/dal-usage-guide.md:155-155.

Sources:

• src/meta/entity-meta.ts:333-338
• src/meta/column-pg-io.ts:108-142
• docs/ai/dal-usage-guide.md:143-155
• README.md:42-45

---

High-Volume Write Strategy

For bulk operations, the DAL avoids the overhead of individual INSERT or UPDATE statements by using a temporary table strategy.

TEMP TABLE Strategy for Bulk Ops

When performing updateMany or upsertMany, the DAL follows a specific sequence to ensure atomicity and performance README.md:46-46.

Data Flow: Bulk Update

1. Create: A temporary table is created with ON COMMIT DROP to ensure it is cleaned up automatically after the transaction.
2. Load: Data is inserted into the temporary table using batched INSERT statements (respecting the 65,535 parameter limit) docs/ai/dal-usage-guide.md:315-320.
3. Execute: A single UPDATE ... FROM or INSERT ... SELECT ON CONFLICT statement is executed to merge the data from the temporary table into the target table.

Audit-Aware Upserts

The upsertMany operation is designed to preserve the integrity of audit trails during conflicts README.md:47-47.

• It preserves the original created_at and created_by values.
• It updates the updated_at, updated_by, and increments the version field docs/ai/dal-usage-guide.md:330-335.

Entity Space to Code Entity Space: Bulk Writes

The following diagram maps the high-level bulk strategy to the internal functions that execute it.

System Name	Code Entity	File Path
Bulk Gateway	Repository.updateMany	src/repository/repository.ts
Temp Table Logic	createTempTable	src/repository/repository.ts
Batch Manager	autoBatchSize	src/repository/repository.ts

Diagram: Bulk Operation Lifecycle

Code
sequenceDiagram
    participant C as Consumer
    participant R as Repository
    participant DB as PostgreSQL
    
    C->>R: updateMany(Entity, data[])
    R->>DB: CREATE TEMP TABLE "tmp_..." ON COMMIT DROP
    Note over R,DB: Batching data to stay under 65k params
    R->>DB: INSERT INTO "tmp_..." VALUES (...)
    R->>DB: UPDATE "target" SET ... FROM "tmp_..."
    DB-->>R: RETURNING *
    R-->>C: Entity[]

Sources:

• src/repository/repository.ts:1-500
• docs/ai/dal-usage-guide.md:315-335
• README.md:46-47

---

Connection and Security

Port-Based Audit

Audit logging is decoupled from the main database transaction via a "Port" pattern src/index.ts:112-115.

• Implementation: The AuditPort interface allows consumers to provide their own implementation (e.g., logging to a separate audit database or a message bus) src/types/types.ts:200-210.
• Non-Blocking: Audit writes are typically "fire-and-forget" from the perspective of the DAL, ensuring that audit failures do not roll back business transactions unless explicitly configured to do so docs/ai/dal-usage-guide.md:435-440.

Anti-Throttling: statement_timeout

To prevent slow queries from starving the connection pool, the DAL enforces a mandatory statement_timeout README.md:51-51.

• Default: 30 seconds docs/ai/dal-usage-guide.md:53-53.
• Mechanism: The Dal gateway sets SET statement_timeout = 30000 on every new connection via the onConnect hook src/dal/dal.ts:80-95.
• Overrides: For long-running bulk operations, the withClient method allows a per-call override using SET LOCAL statement_timeout, which automatically resets when the client returns to the pool src/dal/dal.ts:180-200.

No Schema Migrations

The DAL intentionally excludes schema migration tools README.md:9-9.

• Decision: Schema management is considered a separate lifecycle concern (handled by tools like db-migrate or liquibase).
• Boundary: The DAL provides dal.getPool() to allow external migration tools to share the connection configuration, but it does not execute DDL itself outside of TEMP TABLE creation docs/ai/dal-usage-guide.md:120-123.

Diagram: Connection Initialization

Code
graph TD
    subgraph "Dal Gateway [src/dal/dal.ts]"
        G["getDal() Singleton"] --> P["pg.Pool"]
        P --> C["onConnect Hook"]
    end

    subgraph "Session Config"
        C --> S1["SET search_path"]
        C --> S2["SET statement_timeout"]
        C --> S3["SET application_name"]
    end

    subgraph "Type Parsers"
        G --> T1["INT8_OID -> BigInt"]
        G --> T2["NUMERIC_OID -> Number/String"]
    end

Sources:

• src/dal/dal.ts:80-200
• src/types/types.ts:200-210
• docs/ai/dal-usage-guide.md:53-123
• README.md:9-51

---