database-development

name	database-development
description	Database migrations and Drizzle ORM guidelines for the vm0 project

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(events.createdAt, sql`${timestamp}::timestamp`) rather than placing the cast after the interpolated gte(...) fragment.

SQL Conversion Equivalence

Before replacing an SQL tag, verify the complete database contract, including:

• statement and round-trip count;
• generated SQL shape, parameter order and typing, driver bindings, and column encoders;
• runtime decoders, nullability, precision, aliases, cardinality, and error behavior;
• transaction, snapshot, and statement-clock boundaries;
• locks, atomicity, concurrency behavior, and races;
• planner-visible literals, index selection, normalized plans, buffers, and memory; and
• material query-construction, planning, and execution cost.

Require exact generated SQL and ordered parameter representations when identical serialization is the intended contract. For a deliberate equivalent-syntax rewrite, compare the generated SQL and parameters, verify integration and database behavior, and inspect plans and resource use when they can materially change. Equal returned rows alone do not establish equivalence.

Installed-version gaps such as database clocks, COALESCE, scalar GREATEST, JSONB operations, EXCLUDED, DELETE ... USING, no-FROM commands, and materialized or data-modifying CTEs are review examples, not permanent exemptions. Reevaluate them after Drizzle upgrades. Moving raw SQL into a project helper is not a builder conversion unless the helper adds a stronger static or runtime contract.

Every remaining SQL-tag interpolation must have one unambiguous static role. Do not interpolate any, unknown, a value that can be undefined, an array or tuple directly, or a union that mixes an ordinary bound value with an SQL wrapper. Narrow optional values before constructing SQL. Use sql.empty() for an intentionally empty fragment, sql.param(...) when an array or other value must be one driver parameter, and sql.join(...) when composing SQL fragments. Keep bound values and SQL wrappers as distinct types across helper boundaries.

Raw Execute Rows

Prefer structured Drizzle selections whenever they can express the query. Direct db.execute(...) is allowed when rows are discarded or only rowCount is consumed. When an irreducible raw query returns rows, use executeRawRows(executor, query, rowSchema) from turbo/apps/api/src/lib/db-raw-rows.ts:

const rowSchema = z.object({
  id: z.string().uuid(),
  size_bytes: pgInt8ToSafeIntegerSchema,
});

const rows = await executeRawRows(
  db,
  sql`SELECT id, size_bytes FROM artifacts`,
  rowSchema,
);

executeRawRows executes without a row generic and parses every returned driver row with the supplied schema; its TypeScript output is inferred from that schema. Use schema transforms such as pgInt8ToSafeIntegerSchema, pgInt8ToBigIntSchema, and pgTimestampWithoutTimezoneToDateSchema when the driver representation differs from the application value. Never replace this boundary with execute<Row>, a typed wrapper around db.execute, or a downstream assertion.

Checklist

Before committing:

• Schema file updated in src/schema/
• Schema exported in src/index.ts (if new table)
• Custom migrations created via drizzle-kit generate --custom (not manually)
• pnpm db:migrate works locally
• pnpm test passes