Copy page

Developer Guide

Architecture

@primebrick/dal-pg is a leaf dependency with three layers: a Dal gateway that owns the connection pool, a Repository that turns entity metadata into parameterized SQL, and a Query DSL that lets callers compose filters, sorts, joins, and projections in a type-safe way. This page explains how the layers fit together and the design decisions behind them.

High-level architecture

The Dal gateway is the recommended entry point. It owns the pg.Pool, registers type parsers once per process, and sets session defaults on every connection. The Repository is the low-level engine — it accepts any Queryable (pool or pooled client), which is how it participates in transactions via dal.transaction(txRepo => …) — the tx-scoped Repository is handed to the callback; consumers never touch the raw PoolClient.

Module structure

The library is organized into focused modules under src/:

Module	Responsibility	Public exports
meta/	Entity decorators + metadata storage	@Entity, @Column, @Key, @Unique, @AuditableField, @DeletableField, @CloneField, @AuditTrail, @AuditTrailEntity, syncImplicitEntityColumns, getEntityPersistenceMeta
query/	Query DSL + SQL builder + streaming	field, Filter, Sort, Join, Project, buildSelectQuery, createStream
repository/	The Repository class (finders, writes, bulk, clone)	Repository
dal/	The Dal gateway + type parsers	Dal, getDal, resetDal, DalConfig, WithClientOptions
types/	Public option types + entity interfaces	FindOptions, WriteOptions, AuditableWriteOptions, BulkOptions, AuditPort, LoggerPort, IAuditableEntity, IDeletableEntity, IClonableEntity, IExposableEntity
errors/	Framework-agnostic error classes + stable codes	DalError, NotFoundError, MultipleRowsError, UnknownColumnError, ValidationError, MissingVersionError, RecordVanishedError, OptimisticLockError, DalErrorCodes
audit/	Audit helpers + the generic AuditLogEntity	AuditLogEntity, buildAuditableJoins, buildAuditTrailJoins, calculateDelta, calculateDeltaWithForcedFields

Entity metadata system

Entity metadata is stored in a module-private WeakMap<Function, ClassEntityMeta> keyed by the class constructor. Each decorator mutates the metadata for its class. There is no global registry, no schema file, and no code generation — the metadata is built at class-declaration time and read at query time.

syncImplicitEntityColumns

When a class is loaded, every own enumerable property of new ctor() is treated as a column by convention — you only need @Column() to override sqlName, pgType, nullable, length, precision, scale, defaultSql, or castInJoin. syncImplicitEntityColumns(ctor) walks the discovered property keys and registers every one that decorators didn't already claim — columns are NOT NULL by default (@Key()/@Unique() pin it too; a nullable column must declare @Column({ nullable: true }) or a decorator that implies it). This is why a property declared without any decorator still becomes a column. Storage types resolve statically from the TS annotation in the schema pipeline — never from design:type or the property name (see Column types).

Query builder pipeline

The Repository turns a finder call into SQL in four stages:

1. Resolve FieldRefs — each field(Entity, "prop") is mapped to its qualified <schema>.<table>.<column> name using entity metadata.
2. Build the SQL string — buildSelectQuery emits SELECT ... FROM ... [JOIN ...] [WHERE ...] [ORDER BY ...] with $1, $2, ... placeholders for every operand. Identifiers are quoted via quoteIdent to prevent SQL injection.
3. Execute parameterized — pool.query(sql, params) sends the SQL and the params separately to PostgreSQL. No string interpolation of values.
4. Hydrate rows — each result row is coerced from PG representation to JS representation via pgValueToJsValue per column (e.g. INT8→bigint via the registered type parser, timestamptz→Date).

Type coercion pipeline

PG types and JS types are not 1:1. The Dal gateway registers type parsers once per process so that node-postgres returns the right JS type for each PG type:

PG type	JS type	Mechanism
int8 / bigint	bigint	pg.types.setTypeParser(INT8_OID, v => BigInt(v))
numeric / decimal	number (or string if too large)	pg.types.setTypeParser(NUMERIC_OID, ...)
timestamptz / timestamp	Date	native node-postgres default
jsonb / json	parsed object	native node-postgres default
uuid / text / varchar	string	native node-postgres default

For writes, jsValueToPgParam does the reverse: a Date is bound as an ISO string for timestamptz (or YYYY-MM-DD when the column is ISODate-marked), a bigint is bound as a string for INT8, a Decimal serializes to its plain numeric string, a Vector/number[] to a [1,2,3] literal. Runtime meta cannot see annotations, so the coercion path is driven by explicit jsType markers, semantic decorator pins, and value shape — never design:type (see Column types).

Bulk operation strategies

Bulk operations (addMany, updateMany, deleteMany, restoreMany) handle large row counts without hitting PostgreSQL's 65535-parameter limit per statement — and without per-row round-trips.

Operation	Strategy	Why
ALL *Many	TEMP TABLE strategy on a dedicated transaction client: CREATE TEMP TABLE (match key + written cols + expected_version), batched INSERT into it, then ONE set-based statement against the target	Atomic all-or-nothing, SQL-injection safe, single round-trip per phase
addMany	Temp table → conflict-reporting CTE → single pg_raise (ERR04/ERR05) or INSERT	Whole-batch conflict detail without per-row errors
updateMany / deleteMany / restoreMany	Temp table join target.<match> = tmp.<match> AND target.version = tmp.expected_version; pre-write diagnosis → pg_raise ERR01/ERR03	Per-row optimistic guard inside one statement
upsertMany	DISABLED (commented out) — conflict branch lacked a version guard	Pending guarded-vs-sync-import decision

autoBatchSize(columnCount) returns min(1000, floor(65535 / columnCount)) so each batch stays under the parameter limit. Callers can override with options.batchSize.

Every bulk op returns BulkResult — { received, affected }. Timeouts: options.timeoutMs overrides DalConfig.bulkTimeoutMs (30min default) as the whole-operation wall-clock budget (BulkTimeoutError / ERR06 on expiry), and is also emitted as SET LOCAL statement_timeout inside the transaction (transaction-scoped, no leakage). A statement killed by PG statement_timeout surfaces as 57014 → logical ERR07 at the HTTP boundary.

Audit integration

Audit is port-based and optional. The Repository accepts an AuditPort via AuditableWriteOptions.audit. If no port is injected, audit is silently skipped — microservices that don't need it aren't forced to implement it.

The writeAudit call is fire-and-forget (.catch(logger?.error ?? noop)) — a slow audit writer never blocks the main write. The delta is computed with calculateDeltaWithForcedFields so audit columns (updated_at, updated_by, deleted_at, deleted_by) are force-included even when unchanged, recording who performed the change.

Error handling philosophy

The DAL is framework-agnostic: it never imports HTTP or NATS types. Every error extends the abstract DalError class, which adds a stable code: string property to the standard Error. Consumers map the code to the appropriate boundary response at their own layer.

Code	Class	Meaning	Typical HTTP
NOT_FOUND	NotFoundError	Finder returned 0 rows with throwIfNotFound: true	404
MULTIPLE_ROWS	MultipleRowsError	Single-row finder returned >1 row	500
UNKNOWN_COLUMN	UnknownColumnError	Write received a property not in entity metadata	400
VALIDATION	ValidationError	Empty updates, missing actor, missing match value	400
ERR01	OptimisticLockError (PG-originated)	Version mismatch on guarded write	409
ERR02	MissingVersionError	Auditable write missing version field	400
ERR03	RecordVanishedError	Row hard-deleted between read and write	404
ERR04	pg_raise (PG-originated)	Unique conflict vs live row (add/addMany)	409
ERR05	pg_raise (PG-originated)	Unique conflict vs soft-deleted row(s) only — restore instead	409
ERR06	BulkTimeoutError	Bulk op exceeded wall-clock budget — rolled back	408
ERR07	PG 57014 (mapped at boundary)	statement_timeout killed a statement	500

The ERR + 2 digits convention places the optimistic-lock codes outside PostgreSQL's SQL-standard SQLSTATE classes (00–99), so PostgreSQL accepts them as custom codes without colliding with built-in error classes. See Optimistic locking for the full disambiguation flow.

Design decisions

Decision	Rationale
Metadata via WeakMap, not codegen	No build step, no schema files to keep in sync, decorators work at runtime. Trade-off: requires reflect-metadata.
RETURNING * on every write	The DB returns the full row, hydrated into entity shape. No need for a separate read-after-write.
throwIfNotFound: true by default	The common case is "I expect this row to exist"; opting out (throwIfNotFound: false) is explicit.
deletedRecords: "EXCLUDED" by default	Soft-deleted rows are invisible by default; opting in ("INCLUDED" / "ONLY") is explicit.
TEMP TABLE for every *Many	One dedicated tx + temp table + set-based write: atomic all-or-nothing, injection-safe, per-row version guard via expected_version, single pg_raise error report.
bigint via INT8_OID	Native bigint preserves precision for PKs and counts; number would lose precision above 2^53.
Port-based audit (fire-and-forget)	Microservices that don't need audit aren't forced to implement it; a slow audit writer never blocks the main write.
Framework-agnostic errors	The DAL is a leaf dependency; it must not import HTTP or NATS types. Consumers map code to their boundary.
statement_timeout as the anti-throttling core	A slow query holding a connection starves the pool; the per-session timeout guarantees connection release under high-async REST traffic.

Next steps

• Entities & decorators — every decorator and entity interface in detail.
• Query DSL — field(), Filter, Sort, Join, Project.
• Repository — finders, writes, bulk ops, streaming.
• Connections & transactions — pool config, dal.transaction, timeouts, shutdown.
• Audit trail — the port-based audit system in action.
• Optimistic locking — the ERR01/ERR02/ERR03 flow.