`@octabits-io/postgres`

The seam the Postgres-native building blocks share: SqlExecutor — query(text, params) resolving to { rows }, satisfied as-is by a pg Pool or Client and a PGlite instance, and by postgres.js through postgresJsExecutor. And the Postgres error classification: a thrown driver error — bare, or wrapped by an ORM — turned into an OctDatabaseError value, with isUniqueViolation for the question most stores ask. And isoTimestamp, the timestamptz column that reads back as ISO-8601 in UTC, and drizzleExecutor, a Drizzle database or transaction as the seam (./drizzle). No driver import, no HTTP framework, and Drizzle only behind ./drizzle.

npm i @octabits-io/postgres            # nothing else — bring the executor you already have

import postgres from 'postgres';
import { postgresJsExecutor } from '@octabits-io/postgres';

const db = postgresJsExecutor(postgres(process.env.DATABASE_URL));   // or a pg Pool / a PGlite, as-is

Subpath	Deps	Contents
`.`	none	`SqlExecutor`, the one seam every store shares, and `postgresJsExecutor` for postgres.js; the error classification — `withDbErrorHandling`, `handleTransactionError`, `TransactionRollbackError`, `extractPgError`, `isUniqueViolation`, `PG_ERROR_CODE_MAP`, `OctDatabaseError`, `PostgresErrorCode`, `ExtractedPgError`; `isoInstant`, a `timestamptz` value as ISO-8601 in UTC
`./drizzle`	`drizzle-orm` (optional peer)	`isoTimestamp`, the `timestamptz` column that reads back as ISO-8601 in UTC — what every platform column set declares; `isoInstant`; `drizzleExecutor`, a Drizzle database or transaction as a `SqlExecutor`

The whole package type-checks with no Node typings and imports no driver. A boundary lint keeps it that way.

Moved on 2026-09-13

The dead-letter audit sink waited here until the queue had an offer package of its own. It has one now, so the sink lives beside the queue it audits:

Was	Is
`@octabits-io/postgres/job-audit-store`	`@octabits-io/queue` (the record, the `JobAuditStore` contract and its scoping rules, the in-memory store) and `@octabits-io/queue/postgres` (`createPostgresJobAuditStore`, `jobAuditDdl`)

./job-audit-store re-exported every name it had for one minor and was removed at the next one, together with this package’s dependency on @octabits-io/queue. Import the queue package directly.

Moved on 2026-09-10

A package is a capability a developer asks for by name; a subpath is what they already run. Three modules here were cut the other way — by dependency — and have gone to the packages whose capability they are. Each old subpath stayed as a named re-export shim for one minor and was removed on 2026-09-12: install the package and import it directly.

Was	Is
`@octabits-io/postgres/idempotency`	`@octabits-io/server/idempotency` and `…/idempotency/postgres` — the `Idempotency-Key` header is an HTTP concern
`@octabits-io/postgres/backfill`	`@octabits-io/backfill`, the marker stores on `…/backfill/postgres` and `…/backfill/drizzle` — a contract, a plain-SQL store and a Drizzle adapter is a capability, not someone else’s subpath
`@octabits-io/postgres/rls`	`@octabits-io/rls`, with the Prisma runtime on `@octabits-io/rls/prisma`

When not to use it

Another database. The seam is shaped for Postgres drivers ($1 placeholders); a MySQL or SQLite client needs its own adapter, and none of the stores that take this seam speak another dialect.
A queue or its audit trail. That is @octabits-io/queue.
An ORM seam. Each capability ships its Drizzle adapter on its own ./drizzle subpath; this package’s ./drizzle is one column type and the executor bridge, not a store.

A Drizzle transaction as the seam

Every plain-SQL store writes on the executor it is handed. On a Drizzle host, drizzleExecutor(tx) is the transaction you are in, as that executor: the store’s statement runs on the transaction’s connection and commits or rolls back with your own writes.

import { drizzleExecutor } from '@octabits-io/postgres/drizzle';

await db.transaction(async (tx) => {
  await tx.update(order).set({ status: 'shipped' }).where(eq(order.id, id));
  await outbox.append(envelope, drizzleExecutor(tx));
});

$1-style placeholders become bound parameters, one per placeholder, an array included; the result is { rows } whatever the driver returns.

Timestamps that read back as ISO-8601

Drizzle’s timestamp(name, { withTimezone: true, mode: 'string' }) hands a timestamptz back in Postgres’s text form, in the session’s time zone (2026-10-09 09:00:00+02) — which luxon’s fromISO and a browser’s Date refuse. isoTimestamp is the same column read back as ISO-8601 in UTC (2026-10-09T07:00:00.000Z); the type in the database does not change, so swapping one for the other generates no migration:

import { sql } from 'drizzle-orm';
import { pgTable, uuid } from 'drizzle-orm/pg-core';
import { isoTimestamp } from '@octabits-io/postgres/drizzle';

export const note = pgTable('note', {
  id: uuid().primaryKey().defaultRandom(),
  createdAt: isoTimestamp('created_at').default(sql`now()`).notNull(),
  doneAt: isoTimestamp('done_at'),
});

Write an ISO string (or sql`now()` ). Every platform column set — activity, agent-ledger, events, pii, queue, server/idempotency — declares its timestamps with it. isoInstant(value) is the conversion alone, for a value read some other way: it takes the text form with any offset (+02, +05:30, +00:53:28), an ISO string or a Date, cuts microseconds to milliseconds, and hands infinity back as it was.

The executor

Every store takes db: SqlExecutor — query(text, params) resolving to { rows }. A pg Pool or Client and a PGlite instance satisfy it as-is. postgres.js resolves to the rows directly, so postgresJsExecutor(sql) wraps it:

import postgres from 'postgres';
import { postgresJsExecutor } from '@octabits-io/postgres';

const db = postgresJsExecutor(postgres(process.env.DATABASE_URL));

Because the host owns the executor, the host owns the connection semantics: hand a store an executor that first set the request’s row-level-security setting (@octabits-io/rls builds that statement), and every row it reads or writes is that request’s.

The type is three lines of structure, so a package that needs one declares its own copy rather than taking a dependency on this package — that is why @octabits-io/server/idempotency/postgres, @octabits-io/backfill/postgres and @octabits-io/data-lifecycle/postgres each have a SqlExecutor of their own, identical in shape. This root is where the shape and the postgres.js adapter live. The error classification below is different: it is behaviour, not structure, and it is the part of this package worth importing.

Error classification

A Postgres driver throws; a store here returns a Result. The root turns one into the other. Every driver — pg, PGlite, postgres.js — throws an error with code set to the five-character SQLSTATE and, for a constraint violation, the violated constraint’s name — pg and PGlite call the field constraint, postgres.js constraint_name; both are read. An ORM wraps that error (Drizzle puts it on .cause, and a service may wrap Drizzle’s), so extractPgError walks the cause chain — ten levels at most, which also ends a cyclic chain — and returns the first SQLSTATE it finds with its constraint and message.

SQLSTATE	`code`
`23505`	`unique_violation`
`23503`	`foreign_key_violation`
`23502`	`not_null_violation`
`23514`	`check_violation`
`23P01`	`exclusion_violation`
`42501`	`insufficient_privilege` (row-level security refusals included)
`40001`	`serialization_failure`
`40P01`	`deadlock_detected`
`55P03`	`lock_not_available`
`57014`	`query_canceled`
any other SQLSTATE	`unknown` — still an `OctDatabaseError`, the SQLSTATE is in the message

A Node system error is rethrown, not mapped. ECONNREFUSED, ETIMEDOUT and their kind carry a code too, but only a code of the SQLSTATE shape (five uppercase alphanumerics) counts. A refused connection says the database was never reached, not that it refused the statement: that is an infrastructure failure, and turning it into a database_error value would let a caller treat an outage as a conflict. It propagates as the exception it is, to the retry, the health check or the 500. The shape test is stricter than “five uppercase characters”: EPIPE, EPERM and their kin are five letters too, and no SQLSTATE class begins with E, so a leading E is read as the errno namespace.

import { err, ok, type Result } from '@octabits-io/result';
import {
  handleTransactionError,
  isUniqueViolation,
  TransactionRollbackError,
  withDbErrorHandling,
  type OctDatabaseError,
} from '@octabits-io/postgres';

// A Result passes through; a Postgres error becomes err(OctDatabaseError); anything else rethrows.
async function rename(id: string, name: string): Promise<Result<void, OctDatabaseError>> {
  return withDbErrorHandling(async () => {
    await db.query('UPDATE item SET name = $1 WHERE id = $2', [name, id]);
    return ok(undefined);
  });
}

// The error's message keeps the diagnosis an ORM wrapper drops:
//   '[23505 duplicate key value violates unique constraint "item_name_key"] Failed query: …'

// A typed error that must roll a transaction back survives the rollback.
try {
  await db.transaction(async (tx) => {
    const debited = await debit(tx, amount);
    if (!debited.ok) throw new TransactionRollbackError(debited.error);
  });
} catch (error) {
  return handleTransactionError(error);   // the typed error, a database_error, or a rethrow
}

// The one question most stores ask, with or without the constraint's name.
if (isUniqueViolation(error, 'account_email_key')) return err({ key: 'already_registered', message: '…' });

The names and their behaviour were copied from the house stack’s Drizzle error mapper, so code written against it moves over by an import path; isUniqueViolation is this package’s addition.

Stability

Pre-1.0: patches never break, minors say so in the changelog. SqlExecutor is three lines, and it stays three lines.