@apco/db

Lightweight Postgres client for apps deployed on APCO Cloud: a thin postgres.js wrapper plus Zod row parsing and file-based migrations. Requires Node.js ≥ 22 (ESM only). Pairs with the managed Postgres feature — on APCO, DATABASE_URL is injected into your container automatically once the database is ready.

npm install @apco/db

Entry point	Exports
@apco/db	createDb, types Db, DbOptions, Sql (re-exported from postgres.js), row helper types
@apco/db/zod	parseRows, parseRow, z (re-exported from zod/v4)
@apco/db/migrate	runMigrations, type MigrationResult

createDb()

import { createDb } from "@apco/db";

const db = createDb(); // reads process.env.DATABASE_URL

Signature:

interface DbOptions {
  /** Postgres connection URL. Defaults to process.env.DATABASE_URL. */
  url?: string;
  /** Maximum number of connections in the pool. Default: 10. */
  max?: number;
  /** Close idle connections after this many seconds. Default: 20. */
  idle_timeout?: number;
}

interface Db {
  /** Tagged-template SQL query via postgres.js. */
  sql: postgres.Sql;
  /** Run `select 1` and throw if the database is unavailable. */
  ping(): Promise<void>;
  /** Resolve and validate that a query returned exactly one row. */
  one(rowsOrPromise, schema?): Promise<Row>;
  /** Resolve and validate that a query returned zero or one row. */
  maybeOne(rowsOrPromise, schema?): Promise<Row | null>;
  /** Resolve all rows, optionally parsing each row with a schema. */
  many(rowsOrPromise, schema?): Promise<Row[]>;
  /** Gracefully end the connection pool. */
  end(): Promise<void>;
}

function createDb(options?: DbOptions): Db;

Behavior:

• Reads DATABASE_URL from the environment when no url is passed.
• Throws immediately when neither is available — the app fails fast on boot instead of at first query.
• end() drains and closes the pool (call it on graceful shutdown).

Tagged-template sql

db.sql is a full postgres.js Sql instance — parameters are always bound, never interpolated:

const rows = await db.sql`SELECT * FROM todos ORDER BY created_at DESC`;

const [todo] = await db.sql`
  INSERT INTO todos (text) VALUES (${text}) RETURNING *
`;

await db.sql`DELETE FROM todos WHERE id = ${id}`;

The Sql type is re-exported for annotations:

import type { Sql } from "@apco/db";

Row helpers

db.one, db.maybeOne, and db.many keep postgres.js as the query layer but make common row-boundary checks explicit:

const todo = await db.one(
  db.sql`SELECT * FROM todos WHERE id = ${id}`
);

const maybeTodo = await db.maybeOne(
  db.sql`SELECT * FROM todos WHERE id = ${id}`
);

const todos = await db.many(
  db.sql`SELECT * FROM todos ORDER BY created_at DESC`
);

Pass any schema-like object with parse(row) to validate rows inline:

import { z } from "@apco/db/zod";

const Todo = z.object({ id: z.number(), text: z.string(), done: z.boolean() });

const todo = await db.one(
  db.sql`SELECT id, text, done FROM todos WHERE id = ${id}`,
  Todo
);

one() throws unless exactly one row is returned. maybeOne() returns null for no row and throws when more than one row is returned. many() returns every row.

Zod row parsing (@apco/db/zod)

Raw rows come back untyped; validate them at the query boundary:

import { parseRows, parseRow, z } from "@apco/db/zod";

const Todo = z.object({ id: z.number(), text: z.string(), done: z.boolean() });

const rows = await db.sql`SELECT id, text, done FROM todos`;
const todos = parseRows(rows, Todo);   // Todo[] — rows first, schema second
const one = parseRow(rows[0], Todo);   // Todo

Signatures:

/** Parse an array of raw SQL rows. Throws ZodError on the first failing row. */
function parseRows<T extends z.ZodType>(rows: unknown[], schema: T): z.infer<T>[];

/**
 * Parse a single row. Throws ZodError if validation fails,
 * or a plain Error if the row is null/undefined.
 */
function parseRow<T extends z.ZodType>(row: unknown, schema: T): z.infer<T>;

z is re-exported from zod/v4, so you don't need a separate zod dependency (or version-matching) in your app.

Migrations (@apco/db/migrate)

By convention, migrations live in .apco/migrations/NNN_name.sql — that directory is packed into deploy uploads, so migrations ship with the app. Apply them either with the CLI (apco db migrate) or at runtime from the app:

import { createDb } from "@apco/db";
import { runMigrations } from "@apco/db/migrate";

const db = createDb();
await runMigrations(db.sql, ".apco/migrations");

Signature:

interface MigrationResult {
  file: string;
  applied: boolean; // false = already applied or dry-run pending
  status?: "applied" | "skipped" | "pending";
  checksum?: string;
}

interface RunMigrationsOptions {
  lock?: boolean;      // default true; session advisory lock via sql.reserve()
  dryRun?: boolean;    // list what would run without applying
  checksum?: boolean;  // default true; detect edited applied migrations
  onProgress?(event: MigrationProgressEvent): void;
}

function runMigrations(
  sql: postgres.Sql,
  dir: string,
  options?: RunMigrationsOptions
): Promise<MigrationResult[]>;

Behavior:

• Runs all *.sql files in dir in filename-sorted order — use zero-padded numeric prefixes (001_todos.sql, 002_users.sql).
• Tracks applied files in an _apco_migrations table (name TEXT PRIMARY KEY, applied_at TIMESTAMPTZ), created on demand — each file runs at most once; re-running is a no-op for already-applied files.
• Each file executes inside a transaction together with its tracking insert: a failing migration rolls back completely and is not recorded.
• Takes a Postgres advisory lock by default so two app boots do not apply runtime migrations concurrently.
• Records SHA-256 checksums for newly applied runtime migrations and fails fast when a previously checksummed file changes locally.
• Use { dryRun: true } to list pending files without applying them or changing the tracking table. If _apco_migrations does not exist yet, every local SQL file is reported as pending.
• apco db migrate uses the same table and filename scheme, so CLI-applied and runtime-applied migrations never double-apply. Current CLI-applied rows are filename-only (checksum is null) and are skipped by runtime checks rather than checksum-validated.

Never edit an applied migration

Tracking is still by filename. Runtime-applied migrations with stored checksums fail fast if edited later; older or CLI-applied filename-only rows are skipped without checksum validation. Add a new numbered file instead of editing any applied file.

Connection notes

• On APCO: a deployment receives only its exact channel's ready DATABASE_URL; managed injection overrides a user-defined value and never falls back to Production. createDb() needs no options.
• Local development: apco dev --db ensures Dev and writes that branch's standing URL to .env.local, so local HMR and apco dev --sync share isolated Dev data.
• Migrations: apply the same files independently with apco db migrate --channel production|preview|dev, or call the same migration runner from release.command; branch creation is empty and promotion never moves data. Promotion does run a configured release command separately against Production, while rollback skips it.
• Pool sizing: dbConnectionLimit is plan-backed and enforced independently per branch. Check it with apco db status --channel <channel> --json and keep max at or below it.