# @primebrick/dal-pg


`@primebrick/dal-pg` is a shared Data Access Layer library for Primebrick v3.
It provides a type-driven, metadata-based `Repository` for PostgreSQL. Entities
are plain TypeScript classes decorated with `@Entity`, `@Column`, `@Key`,
`@Unique`, `@AuditableField`, `@DeletableField`, and `@CloneField`. The
`Repository` reads entity metadata at runtime to generate parameterized SQL.

## What it gives you

- **Type-safe entities** — plain TS classes + decorators, no schema files to keep in sync.
- **Metadata-driven SQL** — `Repository` reads entity metadata at runtime and emits parameterized SQL (`RETURNING *` on every write).
- **Query DSL** — composable `field()`, `Filter`, `Sort`, `Join`, `Project` expressions, type-checked at compile time.
- **Bulk operations** — `addMany`, `upsertMany`, `updateMany` using a TEMP TABLE strategy (atomic, SQL-injection safe).
- **Soft delete** — opt in via `@DeletableField`; finders exclude soft-deleted rows by default.
- **Audit trail** — `@AuditTrail()` + `AuditPort` produce field-level deltas fire-and-forget.
- **Optimistic locking** — automatic for auditable entities; stable `ERR01`/`ERR02`/`ERR03` codes.
- **Clone** — `Repository.clone()` copies a record by UUID, resetting audit/unique fields.
- **Streaming** — `findAll({ stream: true })` returns an `AsyncIterable` backed by a pg cursor.
- **Dal gateway** — `getDal()` singleton owns the pool, registers type parsers (`INT8`→`bigint`, `NUMERIC`→`number`), and sets `search_path`/`statement_timeout`/`application_name` on every connection.
- **Framework-agnostic errors** — `DalError` carries a stable `code` string; consumers map it to HTTP/NATS at their own boundary.

## Architecture at a glance

<Mermaid chart={`flowchart LR
  subgraph Your app
    A[Entity class<br/>+ decorators] --> R[Repository]
    Q[Query DSL<br/>field/Filter/Sort/Join/Project] --> R
    R --> QB[Query builder<br/>parameterized SQL]
  end
  subgraph Dal gateway
    D[getDal singleton] --> P[(pg.Pool)]
    P -->|onConnect| S["search_path<br/>statement_timeout<br/>application_name"]
    TP[Type parsers<br/>INT8 -> bigint<br/>NUMERIC -> number"]
  end
  QB --> P
  P --> PG[(PostgreSQL)]
  S --> PG
  TP --> PG
`} />

The **Dal gateway** owns the connection pool and is the recommended entry point.
The **Repository** is the low-level engine — it accepts any `Queryable` (pool or
pooled client), which is how it participates in transactions via `dal.withClient()`.

## Hello World

A minimal end-to-end example: define an entity, bootstrap the Dal gateway, and
perform one insert + one read.

```typescript
import "reflect-metadata";
import {
  Entity, Column, Key, Unique,
  AuditableField, AuditableFieldType,
  getDal,
} from "@primebrick/dal-pg";

// 1. Define an entity — plain TS class + decorators.
@Entity("customers")
export class CustomerEntity {
  @Key() @Column({ pgType: "bigint" }) id!: bigint;
  @Unique() @Column({ pgType: "uuid" }) uuid!: string;
  @Column({ pgType: "text" }) name!: string;

  @AuditableField(AuditableFieldType.CREATED_AT) @Column({ pgType: "timestamptz" }) created_at!: Date;
  @AuditableField(AuditableFieldType.CREATED_BY) @Column({ pgType: "text" }) created_by!: string;
  @AuditableField(AuditableFieldType.UPDATED_AT) @Column({ pgType: "timestamptz" }) updated_at!: Date;
  @AuditableField(AuditableFieldType.UPDATED_BY) @Column({ pgType: "text" }) updated_by!: string;
  @AuditableField(AuditableFieldType.VERSION) @Column({ pgType: "integer" }) version!: number;
}

// 2. Bootstrap the Dal gateway once at process startup.
const dal = getDal({
  connectionString: process.env.DATABASE_URL!,
  schema: "myapp",
  statementTimeoutMs: 30_000,
});

// 3. Insert — RETURNING * gives you the full hydrated row.
const created = await dal.add(CustomerEntity, {
  name: "Alice",
}, { actor: "system" });
// created.uuid, created.id, created.version === 1, created.created_at, ...

// 4. Read by UUID — throws NotFoundError by default if no row matches.
const found = await dal.findByUUID(CustomerEntity, created.uuid);
console.log(found.name); // "Alice"

// 5. Graceful shutdown on SIGTERM.
process.on("SIGTERM", async () => { await dal.close(); process.exit(0); });
```

## Where to go next

| If you want to… | Read |
|------------------|------|
| Get a service running end-to-end | [Getting started](./getting-started) |
| Understand the layers and design decisions | [Architecture](./architecture) |
| Define entities with all decorators | [Entities & decorators](./entities) |
| Build type-safe queries | [Query DSL](./query-dsl) |
| Use the Repository API (finders, writes, bulk, streaming) | [Repository](./repository) |
| Manage the pool, transactions, timeouts, shutdown | [Connections & transactions](./connections) |
| Track field-level changes | [Audit trail](./audit-trail) |
| Prevent lost updates | [Optimistic locking](./optimistic-lock) |
| Copy records by UUID | [Clone](./clone) |
| See what changed per release | [Changelog](./changelog) |
| Look up a specific symbol | [API reference](./api-reference) |

## Next steps

- [Getting started](./getting-started) — install, configure, and run your first query.
- [Architecture](./architecture) — how the layers fit together.