# Overview

# Overview

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

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

- [.devin/skills/dal-usage/SKILL.md](.devin/skills/dal-usage/SKILL.md)
- [.github/workflows/ci.yml](.github/workflows/ci.yml)
- [README.md](README.md)
- [package.json](package.json)
- [src/index.ts](src/index.ts)

</details>



The `@primebrick/dal-pg` package is a type-driven PostgreSQL Data Access Layer (DAL) designed for the Primebrick v3 ecosystem [package.json:2-4](). It provides a metadata-based approach to database interactions, leveraging TypeScript decorators to map plain classes to database entities without the need for manual DTO transformations or hand-written SQL for standard operations [README.md:7-9]().

## What is @primebrick/dal-pg?

It is a shared library that offers a type-safe `Repository` and a centralized `Dal` gateway. The system is designed around the philosophy of **snake_case everywhere**, ensuring that database columns, TypeScript properties, and JSON responses remain consistent throughout the stack [README.md:42-42]().

Key capabilities include:
- **Decorator-based Entities**: Use `@Entity`, `@Column`, `@Key`, and audit decorators to define your schema in code [src/index.ts:18-31]().
- **Automated SQL Generation**: The `Repository` reads entity metadata at runtime to generate parameterized SQL for CRUD and bulk operations [README.md:7-9]().
- **Performance-Oriented Bulk Ops**: Utilizes a `TEMP TABLE` strategy for high-volume updates and upserts, ensuring atomicity and safety at scale [README.md:46-46]().
- **Resilient Connection Management**: The `Dal` gateway enforces best-practice pool defaults like `statement_timeout` to prevent connection starvation in high-traffic environments [README.md:51-51]().

## System Context Diagram

The following diagram illustrates how `@primebrick/dal-pg` sits between your application logic and the PostgreSQL database, bridging the gap between class definitions and SQL execution.

**Entity-to-Database Mapping Flow**
```mermaid
graph TD
    subgraph "Natural Language Space"
        User["User Entity"]
        Save["Save Operation"]
        Batch["Batch Update"]
    end

    subgraph "Code Entity Space"
        E["@Entity('user_account')"]
        R["Repository.add()"]
        D["Dal.upsertMany()"]
        Q["buildSelectQuery()"]
        P["pg.Pool"]
    end

    User --> E
    Save --> R
    Batch --> D
    R --> Q
    D --> Q
    Q --> P
    E -.-> |Metadata| R
```
Sources: [src/index.ts:18-31](), [src/index.ts:79-83](), [src/index.ts:89-89](), [src/index.ts:140-145]()

## Key Design Principles

The architecture is governed by several strict principles to ensure predictability and performance:

1.  **Implicit Hydration**: All write operations use `RETURNING *`, allowing the database to return the full row (including database-generated defaults or triggers) which is then hydrated directly into the entity instance [README.md:43-43]().
2.  **Safety Defaults**: Finders throw a `NotFoundError` by default if no record is found, and soft-deleted records are automatically excluded from results unless explicitly requested [README.md:44-45]().
3.  **Anti-Throttling**: A default `statement_timeout` (typically 30s) is applied to all sessions to ensure slow queries do not exhaust the connection pool [README.md:51-51]().
4.  **Port-Based Audit**: Audit logging is handled via an `AuditPort`, allowing for fire-and-forget audit trails that don't block the primary database transaction [src/index.ts:112-116](), [README.md:27-27]().

**Data Flow Architecture**
```mermaid
graph LR
    subgraph "Application"
        App["TS Logic"]
    end

    subgraph "DAL Gateway (Dal class)"
        DalSingleton["getDal()"]
        Parser["INT8_OID Parser"]
    end

    subgraph "Repository Layer"
        Repo["Repository"]
        DSL["Query DSL"]
    end

    subgraph "Database"
        PG[("PostgreSQL")]
    end

    App --> DalSingleton
    DalSingleton --> Repo
    Repo --> DSL
    DSL --> PG
    PG --> |"RETURNING *"| Repo
    Repo --> |"Entity Instance"| App
```
Sources: [src/dal/dal.ts:1-10](), [src/repository/repository.ts:1-10](), [src/query/dsl.ts:1-10](), [README.md:48-48]()

## Navigation

To explore the system in depth, refer to the following child pages:

### [Getting Started](#1.1)
Learn how to install the package via `pnpm`, configure your environment variables (like `DATABASE_URL`), and initialize the `Dal` singleton using `getDal()`. This guide covers the essential setup required to run your first query.

### [Key Design Decisions](#1.2)
A detailed breakdown of the architectural choices that define the DAL. This includes the rationale behind the `TEMP TABLE` strategy for bulk operations, the "snake_case everywhere" rule, and how the system handles soft-deletes and audit stamping.

## Core Components Reference

| Component | Role | Source |
| :--- | :--- | :--- |
| `Dal` | Process-wide singleton managing the `pg.Pool` and type parsers. | [src/dal/dal.ts:140-145]() |
| `Repository` | The primary engine for CRUD, finders, and bulk operations. | [src/repository/repository.ts:89-89]() |
| `Entity` | Decorator used to define the mapping between a TS class and a DB table. | [src/meta/entity-meta.ts:19-19]() |
| `Query DSL` | Functional tools (`Filter`, `Sort`, `Join`) for building type-safe queries. | [src/query/dsl.ts:60-75]() |
| `DalError` | Base class for framework-agnostic errors (e.g., `NotFoundError`). | [src/errors/errors.ts:93-98]() |

Sources: [src/index.ts:1-146](), [README.md:27-38]()

---