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:
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:
Prefer a schema column or a Drizzle helper such as count() that already
owns the correct decoder.
Use .mapWith(column) when the expression has exactly the same PostgreSQL
runtime representation as that column.
Use .mapWith(decoder) for a dedicated runtime contract. Shared decoders
live in turbo/apps/api/src/lib/db-structured-result.ts.
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:
Use a schema column directly when its decoder, nullability, alias, and
encoder are already correct.
Use an exact helper exported by the installed Drizzle version.
Use a complete schema-aware read or write builder when it preserves the
whole statement contract.
Replace independently supported leaves with typed helpers inside an
otherwise irreducible PostgreSQL expression or statement.
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:
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)
