database-development

# Database Development ## Commands ```bash 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) ```bash # 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**. ```bash # 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. ```typescript // 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`: ```typescript 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