database-development

Database Development

Commands

cd turbo/packages/db

pnpm db:generate   # Generate migration from schema changes
pnpm db:migrate    # Run pending migrations
pnpm db:studio     # Open Drizzle Studio UI

Migration Workflows

Auto-Generated (simple changes)

# 1. Edit schema in src/schema/
# 2. Generate migration (auto-updates _journal.json and snapshot)
pnpm db:generate
# 3. Run locally
pnpm db:migrate

Custom SQL (renames, complex ALTER, data transforms)

Use drizzle-kit generate --custom to create an empty migration file managed by Drizzle. This auto-updates _journal.json and snapshot — never edit these manually.

# 1. Generate empty migration file
pnpm drizzle-kit generate --custom --name=rename_foo_to_bar
# 2. Write SQL in the generated file
# 3. Update schema file to match
# 4. Run locally
pnpm db:migrate

Data Migration Scripts (Clerk API)

When a data migration requires external API calls (e.g., reading from Clerk), it cannot be done in a SQL migration. These scripts live in:

turbo/packages/db/scripts/migrations/NNN-description/
├── backfill.ts   # (or sync.ts) — the migration script
└── README.md     # Usage, prerequisites, verification steps

Pure data transforms that only touch the database should use regular SQL migrations instead.

Convention

• Numbered sequentially: 001-, 002-, etc. — never reuse numbers
• Permanent: these scripts are historical records and MUST NOT be deleted, even after the migration is complete and the referenced tables/schemas no longer exist
• Default dry-run: use parseArgs with --migrate flag; default mode is dry-run
• Self-contained: each directory has its own README with usage instructions
• Excluded from CI: completed scripts that reference deleted schemas are excluded from tsconfig.json and eslint.config.js to avoid build errors

Database Result Boundaries

Drizzle's sql<T>, SQL<T>, generic .as<T>(), execute<Row>, and TypeScript assertions only change compile-time types. They do not validate or decode PostgreSQL driver values. Never use them to declare a database result contract.

Structured Selections

For every raw expression selected by select, selectDistinct, selectDistinctOn, returning, or relational-query extras, choose the first applicable runtime boundary:

1. Prefer a schema column or a Drizzle helper such as count() that already owns the correct decoder.
2. Use .mapWith(column) when the expression has exactly the same PostgreSQL runtime representation as that column.
3. Use .mapWith(decoder) for a dedicated runtime contract. Shared decoders live in turbo/apps/api/src/lib/db-structured-result.ts.
4. If the expression can return SQL NULL, wrap the column or decoder with nullableDriverValueDecoder(...). Drizzle preserves null and applies the wrapped decoder only to non-null values.

// Correct: LOWER(text) has the same driver representation as the text column.
const normalizedEmail = sql`LOWER(${users.email})`
  .mapWith(users.email)
  .as("normalized_email");

// Correct: the explicit decoder owns the runtime number contract.
const total = sql`COUNT(*)::int`.mapWith(pgIntegerDecoder).as("total");

// Correct: nullable SQL result with the column's non-null decoder.
const latestName = sql`MAX(${users.name})`
  .mapWith(nullableDriverValueDecoder(users.name))
  .as("latest_name");

// Incorrect: these only restate a TypeScript type.
const unsafeEmail = sql<string>`LOWER(${users.email})`;
const unsafeAlias = sql`LOWER(${users.email})`.as<string>("email");

Apply .mapWith(...) before .as("alias"); aliasing names a SQL field but does not add or replace its decoder. A PostgreSQL cast such as ::int changes the server-side value representation, while .mapWith(...) defines the client-side runtime decoder. A TypeScript assertion changes neither one.

Use only statically inspectable decoder provenance in .mapWith(...): a real schema column, a reviewed decoder from db-structured-result.ts, or a decoder constructed through its Zod, enum, or nullable factories. Immutable local const alias chains may preserve that provenance. Built-in coercers such as Number and String, inline callbacks, assertions, mutable aliases, and opaque values declared as DriverValueDecoder do not establish a reviewed runtime contract and are rejected by lint.

The same rule applies to db.query.<table>.findMany(...) and findFirst(...), including callback-form extras and extras nested below with. Keep relational configs inline or in inspectable local variables so lint can follow every selected extra. Call select, selectDistinct, selectDistinctOn, returning, findMany, and findFirst directly; do not alias, destructure, or bind these methods, and do not spread their invocation arguments, because those forms hide the result boundary from static enforcement.

For set operations, every branch must expose a compatible, concretely mapped output. Drizzle uses the leftmost branch's decoder for returned rows, so the leftmost expression owns the runtime contract; mapping later branches does not repair an unmapped leftmost branch.

PostgreSQL int8 and numeric commonly arrive from pg as strings to avoid precision loss. Use pgInt8ToSafeIntegerDecoder only when the value is required to fit a JavaScript safe integer, and pgInt8ToBigIntDecoder when lossless integer precision is required. Do not coerce arbitrary numeric values with Number unless the domain contract explicitly permits the resulting precision; use a dedicated decoder that preserves or validates the required representation.

Builder-First SQL Construction

For each expression or statement, use the first applicable option that preserves or strengthens its complete database contract:

1. Use a schema column directly when its decoder, nullability, alias, and encoder are already correct.
2. Use an exact helper exported by the installed Drizzle version.
3. Use a complete schema-aware read or write builder when it preserves the whole statement contract.
4. Replace independently supported leaves with typed helpers inside an otherwise irreducible PostgreSQL expression or statement.
5. Use parameterized sql, or the SQL type without a result generic, when no equal installed API exists.

api/prefer-drizzle-apis deliberately reports only exact replacements in conventional, type-correct code. PostgreSQL parser acceptance proves syntax, not semantic equivalence. A capability must resolve real Drizzle symbols, source and column provenance, interpolation roles, installed API support, and every conventional source variant that it claims to cover. Unsupported syntax, indirect or ambiguous flow, types the analyzer cannot prove, and unproven semantics remain outside that diagnostic and may retain parameterized SQL, subject to the interpolation rules below. A clean lint run means that no implemented capability matched; it does not prove that every retained tag is permanently irreducible.

Compose dynamic SQL from tagged sql fragments so interpolated values remain driver parameters. sql.raw(...) bypasses parameter binding and is prohibited in API source except for the local development seed script. Raw SQL used only as a predicate, join condition, ordering or grouping expression, write value, discarded command, or rowCount command result does not produce a structured field and needs no result decoder. If a write query adds .returning({...}), map raw SQL in the returned fields independently of .set({...}). Likewise, raw SQL passed to insert(...).select(...) is the write source rather than a returned field; only a subsequent returning(...) introduces a result-mapping boundary.

Use typed operators such as eq, gt, isNull, isNotNull, not, exists, and notExists instead of an equivalent SQL tag. Use like, notLike, ilike, and notIlike for dynamic pattern leaves, and between / notBetween for exact range leaves. These helpers make the operation explicit and, when supported, preserve the schema relationship between a column and its bound value. Pass value arrays directly to inArray(column, values) or notInArray(column, values); do not rebuild parameter lists with sql.join(...). Use asc(...) and desc(...) for ordering leaves. Use arrayContains(...), arrayContained(...), or arrayOverlaps(...) only when the left operand is statically array-valued and the right operand preserves the intended array encoding or is an explicit SQL wrapper.

Likewise, use count(), count(...), countDistinct(...), avg(...), avgDistinct(...), sum(...), sumDistinct(...), max(...), or min(...) for an exact aggregate leaf. Use the helper directly at a structured selection boundary when its decoder owns an equal-or-stronger result contract. When the leaf remains inside otherwise irreducible SQL, interpolate the helper while keeping an existing outer SQL cast, FILTER, COALESCE, alias, row schema, and .mapWith(...) that owns the selected result. Do not replace a whole aggregate when the helper's decoder would weaken a non-column result. Keep literal predicates as literals when changing them into helper arguments would introduce a parameter and alter planner-visible query shape; a LIKE ... ESCAPE suffix also remains outside the pattern-helper leaf.

This preference also applies to a replaceable leaf inside otherwise irreducible SQL. Interpolate the typed operator in place of that leaf while retaining the outer tag for surrounding CTE, CASE, join, filter, cast, grouping, or statement syntax. Use and(...) and or(...) for a fixed boolean tree only when its direct consumer accepts their SQL | undefined result; do not use them when that result would weaken a required concrete SQL contract. Keep SQL syntax that belongs to an operand inside that operand. For example, write ``gte(even

Content truncated.