`@doable/db`¶

PostgreSQL connection + typed query helpers. Lives at packages/db/.

Install (within the monorepo)¶

Already wired up via pnpm workspaces — you don't npm install it.

import { sql } from "@doable/db";

Connection¶

A single postgres-js instance is created on first import, configured from DATABASE_URL and DATABASE_POOL_SIZE. Re-using one instance per process is mandatory — don't construct your own.

import postgres from "postgres";
import { sql } from "@doable/db";   // tagged template

const rows = await sql`SELECT id, email FROM users WHERE id = ${userId}`;

Parameters are passed safely as bind variables — never concatenate user input.

Query helpers¶

Each domain has a file under src/queries/:

import { getProjectById, listWorkspaceProjects } from "@doable/db";

const project = await getProjectById(id);
const items   = await listWorkspaceProjects(workspaceId, { limit: 20 });

Helpers return strongly typed objects — see types-core.ts and types-ai.ts.

Migrations¶

Numbered SQL files in migrations/. Run with:

pnpm db:migrate

The runner (services/api/src/db/migrate.ts) is idempotent and tracks applied migrations in a _migrations table.

To add a new migration:

Create packages/db/migrations/0NN_my_change.sql with a unique number greater than the current highest.
Use IF NOT EXISTS and IF EXISTS so re-runs are safe.
Run pnpm db:migrate.
Commit both the migration and any new query helpers.

Direct SQL vs helpers¶

Use a helper when one exists. Drop down to raw sql\…`` when:

The query is one-off (e.g. an admin script).
You need a complex JOIN that doesn't generalize.
You're inside a route handler that already has narrow scope.

@doable/db¶