@wtfalch/db

App-owned PostgreSQL connections with parameterized SQL and optional native Drizzle access. Node.js 22/24 and PostgreSQL 16 are the initial validation targets.

import { createDatabase } from '@wtfalch/db/postgres';

const connection = createDatabase({
  url: () => process.env.DATABASE_URL,
  applicationName: 'my-app',
  max: 5, // choose from your replica/worker connection budget
});
const db = connection.database;
const rows = await db.query<{ id: string }>(
  'select id from items where owner_id = $1',
  [authenticatedUser.id],
);
await db.transaction(async (tx) => {
  await tx.query('insert into items(id) values ($1)', ['example']);
  await tx.transaction(async (savepoint) => {
    await savepoint.query('select 1');
  });
});
await connection.close({ timeoutMs: 5000 });

The app supplies authorization, schema, migrations, credentials and pool budgets. SQL generics assert an expected row shape; they do not validate database results. A query returns rows, including an empty array for commands without RETURNING. Use unique aliases for result columns; duplicate names fail instead of overwriting.

Connection options

url is a PostgreSQL URL or a lazy resolver returning one. It is captured on successful initialization, with no owner-credential fallback. applicationName defaults to wtfalch-db, max to 10, and connectTimeoutMs to 10,000. idleTimeoutMs is optional. Configure each app's budget explicitly.

statementTimeoutMs sets PostgreSQL's own statement_timeout for every connection this owner opens: a shared cutoff so one runaway query cannot hold a connection indefinitely against apps sharing Postgres capacity. It has no default — unset, a statement runs until it finishes, errors, or the connection closes. A cancelled statement fails with SQLSTATE 57014.

query(text, values, options) takes an optional third argument for a single call: signal (an AbortSignal) and/or timeoutMs each cancel that one query in flight — a real PostgreSQL cancel request, not a client-side abandon — on top of any statementTimeoutMs, whichever fires first:

const controller = new AbortController();
setTimeout(() => controller.abort(), 500);
await db.query('select pg_sleep(10)', [], { signal: controller.signal });
// or, equivalently for a single call:
await db.query('select pg_sleep(10)', [], { timeoutMs: 500 });

For authenticated remote TLS, use tls: 'verify-full' and supply ca as PEM text (or an array of PEM certificates) when the provider uses a private CA. Supplying ca with any other tls value, including the default, is a configuration error. require encrypts without verifying the server certificate; prefer permits plaintext fallback. disable is intended for controlled local setups. When tls is omitted, the driver's URL/environment TLS configuration applies. A successful connection alone is not proof of the intended TLS policy.

Use a standard single-host URL, with the database in its path and connection credentials in its authority. Multihost URLs and libpq-style ?host= Unix-socket URLs are not supported. URL options follow Postgres.js semantics, not libpq.

Optional Drizzle

SQL-only installs do not need Drizzle. ORM users install a drizzle-orm version in the supported range, >=0.39.3 <1.0.0 — measured across both ends, not just assumed — shared with packages that inspect native transaction identity. The driver dependency is Postgres.js ^3.4.9. withDrizzle checks the installed drizzle-orm version against this range at first use and throws if it is outside it — a caller-satisfied peer range (^0.39.0 still admits 0.39.0 through 0.39.2, below this package's own tested floor) is not proof the installed version was ever measured against this codec.

import { createDatabase } from '@wtfalch/db/postgres';
import { withDrizzle } from '@wtfalch/db/drizzle';
import * as schema from './schema.js';

const connection = createDatabase({ url: () => process.env.DATABASE_URL });
const db = withDrizzle(connection, { schema }); // before first use
await db.transaction(async (tx) => {
  await tx.orm.insert(schema.items).values({ id: 'example' });
  await tx.query('insert into audit_events(item_id) values ($1)', ['example']);
});

SQL and ORM inside this transaction use the same connection. Nested transactions use savepoints. ORM mappings and errors remain native. Ordinary app functions that accept a context provide named operations; there is no generic binding layer. An arbitrary existing driver pool cannot be adopted.

Handle types for package authors

A package that does not know the app's schema cannot name DrizzleDatabase<TSchema> or DrizzleTransaction<TSchema> directly. Accept AnyDrizzleDatabase and AnyDrizzleTransaction from @wtfalch/db/drizzle instead — the same shapes with the schema erased to Record<string, unknown> — and read the query builder, .execute(sql) and .transaction() surface through .orm, exactly as with the parameterised types.

A package that instead holds only the native Drizzle handle — tx.orm itself, not the SDK's wrapped view — faces the same erasure problem. NativeDatabase<TSchema> and NativeTransaction<TSchema> name that native handle's parameterised type; AnyNativeDatabase and AnyNativeTransaction are their schema-erased forms, so a package taking only tx.orm does not have to hand-roll PgDatabase<PgQueryResultHKT, any, any> itself:

import type { AnyNativeTransaction } from '@wtfalch/db/drizzle';

async function audit(native: AnyNativeTransaction) {
  await native.insert(auditEvents).values({ event: 'example' });
}

tx.orm is Drizzle's own native transaction object, not a lookalike, for every drizzle-orm version this package's peer range admits. But instanceof tests class identity by the exact PgTransaction module a package's own import resolved, and that check fails whenever a package's drizzle-orm and this package's drizzle-orm land as two separate installed copies in the same dependency tree — a real, if quiet, monorepo hazard, and not something this package's contract can promise its way around. Use Drizzle's own is() helper instead of bare instanceof: it checks a shared Symbol.for('drizzle:entityKind') tag rather than class identity, so it tolerates multiple installed copies of the same version. packages/db/test/integration.test.ts asserts both directions: instanceof holding in a single-copy install, and failing across two independently installed copies of the same version.

The way back: queryFor

A package that receives only a native transaction handle — tx.orm, typed or erased — has no way back to SDK SQL on that same connection:

import { queryFor } from '@wtfalch/db/drizzle';

await db.transaction(async (tx) => {
  await audit.record(tx.orm); // a package holds only the native handle
  await queryFor(tx.orm)('insert into audit_events(item_id) values ($1)', [
    'example',
  ]);
});

queryFor(handle) returns the same query function tx.query exposes, bound to that transaction's own connection. A nested transaction's native handle resolves to its own savepoint's connection, never the outer one. A handle from a different transaction, or one whose transaction already committed or rolled back, throws — it never falls back to the pool, where a raw statement would commit or fail independently of the transaction it appears to run inside. See packages/db/test/integration.test.ts for the same-connection commit, same-connection rollback, savepoint-rollback and both-throws proofs against real PostgreSQL.

Nesting registers with queryFor the same way whether it goes through the SDK's own tx.transaction(...) or through the native handle directly:

await db.transaction(async (tx) => {
  await tx.orm.transaction(async (nested) => {
    // nested never went through tx.transaction(...); queryFor still finds it.
    await queryFor(nested)('insert into audit_events(item_id) values ($1)', [
      'example',
    ]);
  });
});

A package that only received tx.orm — never the SDK tx — and nests with it needs no bridge of its own to keep queryFor working on what it opens.

Construction and schema attachment do not resolve the URL or open connections. Attach once during setup; repeated attachment with the same schema object returns the same view, while a different schema or attachment after first use is rejected. Packages borrow execution contexts, which have no top-level close method. The app retains the owner. This is lifecycle separation, not a security sandbox over native ORM objects.

SQL values and errors

SQL results decode int2/int4/oid and floats as numbers, int8/numeric as exact strings, booleans as booleans, JSON as JS values and bytea as Buffer. Finite timestamptz becomes Date with millisecond precision; infinity remains text. Date/time/timestamp without timezone and unknown types remain text. Supported built-in arrays preserve nesting and null elements. For exact timestamp cursors, select text; JSON numbers have ordinary JS precision limits. Native ORM mappings may differ, particularly arrays and column timestamp modes.

Bind values separately from SQL. bigint is exact decimal text, valid Date is ISO text, Buffer stays binary and plain objects are JSON encoded. JS arrays mean SQL arrays; explicitly JSON-encode an array intended as JSON. Undefined, invalid Dates and unsafe integer numbers fail. Extension/composite/domain types and nonstandard array lower bounds require explicit casts to supported types.

import { sqlState } from '@wtfalch/db';
if (sqlState(error) === '23505') {
  // Handle a domain-specific unique conflict.
}

The classifier follows cause chains and handles cycles. Underlying driver and ORM errors stay intact, which means they may contain sensitive SQL, parameters or server details: never log whole error objects. The SDK does not retry callbacks or log credentials. Configure application error reporting at the trusted boundary.

Shutdown

Await every statement and nested transaction within its callback. SDK transaction contexts reject later use after completion. close is terminal and idempotent; the first call sets its deadline and repeated calls share its promise. New SDK root work is refused while accepted managed work drains. The deadline terminates the pool and rejects close if work has not finished. Timeouts must be between 0 and 2,147,483,647 milliseconds.

Stop producers and await direct native ORM queries before closing. Native query builders/handles can escape tracking; root native ORM transactions do not receive all SDK lifetime guarantees. Forced shutdown cannot cancel arbitrary JavaScript or establish whether a racing commit succeeded. Use domain reconciliation for an ambiguous result instead of blindly replaying writes.

Health checks

connection.ping() runs a trivial statement and resolves on success, or rejects on failure or once timeoutMs (default 5000) elapses first — a readiness probe for a deploy target such as Coolify, without hand-writing db.query('select 1'):

app.get('/healthz', async (_req, res) => {
  try {
    await connection.ping({ timeoutMs: 2_000 });
    res.sendStatus(200);
  } catch {
    res.sendStatus(503);
  }
});

adaptPostgres owners expose the same ping(), over the caller's own client. A timed-out ping does not cancel the underlying query; it only stops waiting for it.

Migrations

@wtfalch/db/migrate applies pending *.sql files from a directory, one transaction per file, recorded in an _migrations(filename, applied_at) ledger so a re-run applies nothing:

import { runMigrations } from '@wtfalch/db/migrate';

const { applied } = await runMigrations({
  url: process.env.DATABASE_URL,
  migrationsDir: 'drizzle',
});

Files run in filename order (name them so lexical order is application order) over a session-scoped advisory lock (default id 728143) held for the whole run, so two callers migrating the same database concurrently serialize instead of racing. A failure rolls back only that file; earlier files stay committed. Each migration's SQL runs unprepared, over one connection this call opens and closes itself — multiple statements and dollar-quoted DO $$ ... $$ blocks in one file are fine, but no psql meta-command (\i, \gset, ...) is available, since nothing here shells out to psql.

The package also ships a CLI, db-migrate, reading the same DATABASE_URL and MIGRATIONS_DIR (default /app/drizzle) environment variables app-template's scripts/migrate.sh does, waiting up to 60 seconds for the database to become reachable before applying — a drop-in for that script with no psql/libpq install required:

DATABASE_URL=postgres://... MIGRATIONS_DIR=drizzle npx db-migrate

Test harness

@wtfalch/db/testing starts a disposable PostgreSQL in Docker for an integration suite — the same harness this package's own suite runs in. It needs docker on PATH, binds to a random 127.0.0.1 port and is for tests only (the password is a fixed constant):

import { after, before } from 'node:test';
import { startEphemeralPostgres } from '@wtfalch/db/testing';

let pg;
before(async () => {
  pg = await startEphemeralPostgres(); // { image?, database?, readyTimeoutMs? }
  process.env.DATABASE_URL = pg.url;
});
after(() => pg?.stop());

stop() removes the container and is safe to call twice.

See the included provisioning guide for roles, migration ownership, backup/restore checks and consumer adoption requirements. Provisioning, package publication and production cutovers are separate operations.