Drizzle

Scaffold with the CLICopy Anchor Link

In a linked project directory (capydb link or capydb create):

capydb init drizzle

This writes three files, wired to the live database:

• drizzle.config.ts - drizzle-kit config pointing DDL at DATABASE_DIRECT_URL
• src/db/schema.ts - a Drizzle schema generated from your cell's current schema
• src/db/index.ts - a db client built with createDb() from @capydb/drizzle

Then install the dependencies (the command is printed, matched to your lockfile) and pull the env vars:

pnpm add @capydb/drizzle drizzle-orm@rc postgres && pnpm add -D drizzle-kit@rc
capydb env pull   # writes DATABASE_URL (pooled) and DATABASE_DIRECT_URL (direct)

--dir scaffolds into another directory, --schema moves the schema path, and --force overwrites existing files.

EnvironmentCopy Anchor Link

DATABASE_URL="postgres://user:password@host:6432/db?sslmode=verify-full"
DATABASE_DIRECT_URL="postgres://user:password@host:5432/db?sslmode=verify-full"

Two URLs, one database: :6432 is the transaction-mode pooled endpoint for application traffic, :5432 the direct connection for migrations and DDL. Connection pooling explains why the split exists.

Runtime client with @capydb/drizzleCopy Anchor Link

@capydb/drizzle is a thin, typed layer over drizzle-orm/postgres-js + postgres that bakes in the connection rules a CapyDB cell expects:

import { createDb } from '@capydb/drizzle'
import { users } from './schema'

export const db = createDb()

// anywhere in your app
const rows = await db.select().from(users)

Drizzle v1 dropped the driver-level schema option: tables are imported and used directly. For the relational query API (db.query.*), build relations with defineRelations and pass them in:

import { defineRelations } from 'drizzle-orm'
import * as schema from './schema'

const relations = defineRelations(schema, (r) => ({
  users: { posts: r.many.posts() },
  posts: { author: r.one.users({ from: schema.posts.userId, to: schema.users.id }) },
}))

export const db = createDb({ relations })

createDb() resolves the connection string from options.connectionString, then CAPYDB_DATABASE_URL, then DATABASE_URL, and throws a descriptive error at startup if none is set. When the URL is pooled (:6432, or pooled: true), the client defaults to { prepare: false, max: 1 } - the two settings transaction pooling requires; direct URLs default to { max: 10 }. Your own client options override any default:

const db = createDb({ client: { max: 2, idle_timeout: 20 } })

Create the client once at module scope so warm serverless invocations reuse it. The tiny per-instance pool is deliberate: the pooled endpoint's job is to multiplex many small client pools onto a few real server connections, and a large per-instance max just exhausts pooler slots.

For programmatic migrations there is createDirectDb() (resolution order: options.connectionString, CAPYDB_DATABASE_DIRECT_URL, DATABASE_DIRECT_URL):

import { createDirectDb } from '@capydb/drizzle'
import { migrate } from 'drizzle-orm/postgres-js/migrator'

const db = createDirectDb()
await migrate(db, { migrationsFolder: './drizzle' })
await db.$client.end()

createDirectDb() rejects the pooled :6432 endpoint (and pooled: true) before opening a client. Prepared-statement settings do not make migrations safe through transaction pooling.

Paused cells: mid-session disconnects and warm-upsCopy Anchor Link

With scale-to-zero, a cell pauses after a quiet spell and resumes on the next connection. A new connection to a paused cell is held while it resumes, so it only sees a slower connect. What needs handling is a connection that was already open when the cell paused - a long-lived server between requests, or a serverless instance frozen between invocations. On that connection postgres.js fails twice before it replaces it:

1. the statement in flight fails with CONNECTION_CLOSED,
2. the next statement receives the server's 57P01 (terminating connection due to administrator command),
3. the one after that reconnects cleanly.

CapyDB's idle sweep does not pause a cell while a statement or transaction is open on it, so in practice this hits idle connections, and the statement that fails is the first one after the pause.

retryOnPause() absorbs exactly those failures and rethrows anything else untouched on the first attempt. Wrap work that is safe to run twice - a read, or a whole transaction (one cut off by the pause was rolled back by the server):

import { retryOnPause, withAuthContext } from '@capydb/drizzle'

const rows = await retryOnPause(() => db.select().from(users))

const todos = await retryOnPause(() =>
  withAuthContext(db, { userId }, (tx) => tx.select().from(schema.todos)),
)

Wrap the whole db.transaction(...) call, never a statement inside it - the transaction's connection is gone, so an inner retry can only fail again. Do not wrap a single write outside a transaction: if the connection closed after the server committed, the retry would apply it twice. Nothing in the package retries implicitly, because only you know which work is idempotent.

The default budget is 3 attempts (100 ms backoff, doubling, at most 2 s) - the minimum that covers both failures. When it runs out, retryOnPause() throws CellPausedError, with the last pause error as its cause. Tune it with { attempts, baseDelayMs, maxDelayMs, signal }.

Cron jobs, CI steps and migrations that are the first thing to touch a paused cell can warm it up explicitly instead of discovering it halfway through a migration:

import { createDirectDb, waitForWake } from '@capydb/drizzle'
import { migrate } from 'drizzle-orm/postgres-js/migrator'

const db = createDirectDb()
await waitForWake(db.$client, { signal: AbortSignal.timeout(60_000) })
await migrate(db, { migrationsFolder: './drizzle' })
await db.$client.end()

waitForWake() retries a select 1 (10 attempts, 250 ms doubling to at most 5 s - about 30 s in total) and throws CellPausedError if the cell never answers. Real failures - bad credentials, a genuine SQL error - are rethrown immediately. A cron job that runs every few minutes keeps the cell awake as a side effect; that is a property of the schedule, not something to rely on. For a cell that must never pause, turn scale-to-zero off with capydb projects always-on on. The classifier both helpers use, isCellWakingError(err), is exported for building your own policy.

Prefer plain Drizzle without the helper? The manual equivalent is:

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'

const client = postgres(process.env.DATABASE_URL!, { prepare: false, max: 1 })
export const db = drizzle(client)

prepare: false matters on the pooled URL - transaction pooling does not support postgres.js's named prepared statements.

Row-level security contextCopy Anchor Link

If your database enforces RLS with the vanilla session-setting convention (what capydb migrate rls emits when converting Supabase policies), set the per-request context with withAuthContext - it opens a transaction, applies the context transaction-locally, and runs your callback inside it:

import { withAuthContext } from '@capydb/drizzle'

const todos = await withAuthContext(db, { userId: session.userId }, (tx) =>
  tx.select().from(schema.todos)
)

Transaction-local is the only pooler-safe shape: through the :6432 endpoint a session-level SET could leak one user's identity into another request's connection. Query through the tx handle inside the callback - db queries run outside the context. For databases converted with --mode supabase-compat, withSupabaseJwtClaims(db, claims, callback) sets request.jwt.claims for the shim instead.

Calling Postgres functionsCopy Anchor Link

callFunction() is the Drizzle equivalent of supabase.rpc(name, args): it calls a function with named arguments and returns the rows it produces.

import { callFunction, withAuthContext } from '@capydb/drizzle'

const feed = await withAuthContext(db, { userId }, (tx) =>
  callFunction<{ id: string; title: string }>(tx, 'get_feed', {
    p_limit: 20,
    p_filters: { tags: ['postgres'] }, // jsonb parameter
  }),
)

The statement is select * from get_feed(p_limit => $1, p_filters => $2) - named notation, as PostgREST used, so existing functions keep their parameter names and argument order does not matter. Names are quoted as identifiers and values are bound parameters.

• undefined leaves the argument out, so the parameter's DEFAULT applies; a parameter without a default then fails loudly (function ... does not exist). Pass null for SQL NULL.
• Plain objects are JSON-encoded for json/jsonb parameters. Arrays are sent as Postgres arrays - for a JSON array argument, pass JSON.stringify(value).
• A table or set-returning function yields its rows; a scalar function yields one row with one column named after the function ([{ add: 42 }]).
• { schema: 'api' } qualifies the name; by default it resolves through the role's search_path.
• The row type is a claim about the function, not checked at runtime - the same contract as supabase.rpc<T>().

Call it with the tx handle inside withAuthContext: a function called through db runs outside the RLS context.

Migrations with drizzle-kitCopy Anchor Link

Point drizzle-kit at the direct URL - migration tools rely on advisory locks and session state that the transaction pooler cannot provide. capydb init drizzle writes exactly this:

import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/db/schema.ts',
  out: './drizzle',
  // drizzle-kit v1 manages ALL schemas by default; keep push/pull scoped to
  // the schemas you own so extension-created schemas (e.g. cron from pg_cron)
  // are never offered for DROP.
  schemaFilter: ['public'],
  // pg_stat_statements (the slow-query view) lives in public on older cells and
  // is extension-owned; without this, drizzle-kit tries to DROP it on every
  // push and fails.
  tablesFilter: ['!pg_stat_statements'],
  dbCredentials: {
    url: process.env.DATABASE_DIRECT_URL ?? process.env.DATABASE_URL!,
  },
})

Moving from push to migrationsCopy Anchor Link

push and migrate are two different workflows, and mixing them is the most common way to get stuck.

push records nothing. It diffs your schema against the database and applies the difference - it never writes to the migrations table. So a database built with push has the tables but an empty migration history. The first time you run migrate against it, drizzle-kit starts from migration #1, tries to create objects that already exist, and fails:

error: relation "contacts" already exists

Nothing is broken - the database is fine, the history just never knew about it. You need to baseline: tell drizzle the existing migrations are already applied, so migrate starts from the current state instead of from zero.

# 1. Generate a migration that represents the CURRENT schema.
npx drizzle-kit generate

# 2. Mark it applied without executing it, so migrate starts from here.
#    (drizzle-kit's --init flag does this; otherwise insert the row yourself.)
npx drizzle-kit migrate --init

If your tooling cannot baseline, the escape hatch is to write the migration's SQL idempotently (CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS) and apply it directly, so replaying it against an already-migrated database is a no-op.

Pick one workflow per environment and stay with it: push for local and preview databases where speed matters and the data is disposable, migrate for anything whose history you need to reproduce. capydb doctor warns when a project has scripts for both, because that combination is what leads here.

Useful drizzle-kit v1 additions: drizzle-kit push --explain previews the SQL without executing it, drizzle-kit pull --init bootstraps the migration table against an existing database, and drizzle-kit check detects conflicting migrations across git branches. Migration folders are self-contained in v1 (one folder per migration, no journal.json).

Two things not to manage from drizzle-kit on CapyDB: leave entities.roles off (cells contain platform roles such as the observability reader), and keep extension schemas out of schemaFilter.

npx drizzle-kit migrate   # or push, against the direct URL

Pulling schema from the databaseCopy Anchor Link

capydb generate drizzle renders the cell's live schema as a Drizzle schema file - the same generator capydb init drizzle uses for the initial src/db/schema.ts:

capydb generate drizzle --out src/db/schema.ts
capydb generate drizzle --preview <id>   # from a preview database instead

Use it to bootstrap Drizzle onto an existing database, or to re-sync the schema file after changes made outside drizzle-kit. TypeScript and Zod variants of the same generator are covered in Type generation.

Seeding dataCopy Anchor Link

Drizzle v1 ships drizzle-seed - deterministic, schema-aware fake data that respects your column types and foreign keys. Point it at the direct URL via createDirectDb() and seed preview databases, not production:

pnpm add -D drizzle-seed@rc

// scripts/seed.ts
import { createDirectDb } from '@capydb/drizzle'
import { seed } from 'drizzle-seed'
import * as schema from '../src/db/schema'

const db = createDirectDb()          // resolves DATABASE_DIRECT_URL
await seed(db, schema, { seed: 42 }) // deterministic: same seed, same data
await db.$client.end()

Two good targets for seed runs: a fresh preview database (capydb preview create --mode empty, seed, test, throw away), or local development. Re-runs against a database that already holds seeded rows will hit unique constraints - drizzle-seed's reset helper truncates the seeded tables first when you want a clean slate.

Migrating from NeonCopy Anchor Link

If your codebase uses @neondatabase/serverless with drizzle-orm/neon-http or neon-serverless, the CLI mechanizes the driver swap:

capydb migrate codemod neon           # dry run: report what would change
capydb migrate codemod neon --write   # apply

The codemod rewrites Neon imports and client construction to postgres with the pooler-safe defaults (max: 1, prepare: false), swaps the drizzle adapter to drizzle-orm/postgres-js, updates package.json and drizzle.config.* (preferring DATABASE_DIRECT_URL for DDL), and strips Neon-specific connection parameters from env files. Call sites that need human judgment - Pool usage, db.batch() (rewrite to db.transaction), neonConfig wiring - are reported with file and line, never guessed at. Pass a path argument to scope it to a subdirectory.

Bring the data over first with an import, then point the env vars at CapyDB (capydb env pull).

PitfallsCopy Anchor Link

• drizzle-kit push fails with cannot drop view pg_stat_statements on older cells, where the slow-query extension's view sits in public. The tablesFilter above excludes it; newer cells install the extension in a separate extensions schema, so schemaFilter: ['public'] already hides it. Configs that also list '!pg_stat_statements_info' can drop that entry: no cell exposes that view to your role any more.
• drizzle-kit push/migrate over the pooled URL can hang or fail on DDL; use the direct URL.
• postgres.js without prepare: false throws prepared statement "..." already exists behind the pooler. createDb() sets it for you on pooled URLs.
• Long transactions get cut by the plan's idle-in-transaction timeout (60–120s depending on plan); keep migration batches reasonable.