Drizzle

Scaffold with the CLI

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.

Environment

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/drizzle

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

Warming a paused cell

A normal connection to a paused cell is held while it resumes, so application traffic needs nothing. Batch jobs, cron and CI steps that are the first thing to touch a paused cell can ask for a bounded warm-up 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)          // retries only transient resume errors
await migrate(db, { migrationsFolder: './drizzle' })
await db.$client.end()

waitForWake() retries with exponential backoff and accepts { attempts, baseDelayMs, maxDelayMs, signal }. Real failures - bad credentials, a genuine SQL error - are rethrown immediately rather than retried. The companion isCellWakingError(err) is exported so you can build your own retry policy; the package deliberately does not retry queries for you, because only you know which of your statements are idempotent.

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.

Migrations with drizzle-kit

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 its views on
  // every push and fails.
  tablesFilter: ['!pg_stat_statements', '!pg_stat_statements_info'],
  dbCredentials: {
    url: process.env.DATABASE_DIRECT_URL ?? process.env.DATABASE_URL!,
  },
})

Moving from push to migrations

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 database

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 data

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 Neon

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).

Pitfalls

• drizzle-kit push fails with cannot drop view pg_stat_statements on older cells, where the slow-query extension's views sit in public. The tablesFilter above excludes them; newer cells install the extension in a separate extensions schema, so schemaFilter: ['public'] already hides it.
• 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.