---
title: "@octabits-io/postgres"
summary: "The shared SQL executor seam the Postgres-native building blocks take — query(text, params) → { rows }, satisfied as-is by pg, PGlite and (through postgresJsExecutor) postgres.js."
install: "pnpm add @octabits-io/postgres"
availability: "Available from November 2026"
version: "0.8.1"
license: "MIT"
related:
  - name: "@octabits-io/result"
    url: "https://octabits.io/platform/result.md"
canonical_url: "https://octabits.io/platform/postgres"
---

# `@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`.

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

```ts
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`](https://octabits.io/platform/queue.md) (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`](https://octabits.io/platform/server.md) and `…/idempotency/postgres` — the `Idempotency-Key` header is an HTTP concern |
| `@octabits-io/postgres/backfill` | [`@octabits-io/backfill`](https://octabits.io/platform/backfill.md), 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`](https://octabits.io/platform/rls.md), 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`](https://octabits.io/platform/queue.md).
- **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.

```ts
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:

```ts
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:

```ts
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`](https://octabits.io/platform/rls.md) 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.

```ts
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.