Drizzle ORM: Typed Schema and Migration Review

1) Problem before tooling

A typed TypeScript schema does not prove that the deployed database has the same shape. Even when drizzle-kit generate emits SQL, three questions remain open: what is the declared source of truth, what operational/data risk exists in the generated change, and did the change actually apply to a clean database with the expected observable state? This lesson owns one bounded change: add a normalized, unique handle to customer_profile. The required artifact is Drizzle Typed Schema & Migration Review Record v1. It does not prove production safety and it does not attempt to teach all of Drizzle.

2) Route orientation

DAT-005 is the hard prerequisite: treat migration as a schema/data compatibility contract. Here that reasoning is placed inside a Drizzle workflow. The worked route is codebase-first because the typed schema is explicitly declared as the source of schema intent. Drizzle also documents database-first workflows. The route is a project governance decision; the tool does not make that accountability decision for you.

3) Mental model

Keep the decision chain explicit:

Source of Truth → Typed Schema / DB State → Previous Snapshot & Migration History → Generated Diff/SQL → Human Review → Clean Apply → Direct DB Observation → Decision + Residual Risk

generate is not the finish line. SQL and snapshot artifacts are inputs to review. Applying a migration is still not proof of the target invariant. The database must be observed directly. push is also a distinct workflow; in this lesson it does not silently replace the reviewed migration-file path unless the governance decision is recorded.

4) Foundation: declare source of truth

Start the record with an unambiguous statement: in this exercise, the codebase schema is the declared source of intent and database state is the source of observation. Do not collapse those roles. If code changes but no migration is applied, the intent is new while deployed state is old. If the database changes outside the recorded migration path, actual state may diverge from both code and snapshot. Track declared schema, migration history/snapshot, and actual database state separately.

5) Deep explanation: what does generate prove?

Current Drizzle documentation describes drizzle-kit generate as reading schema files, composing the current snapshot, comparing against prior migration snapshots, and saving SQL migration plus a new snapshot from the difference. That behavior is valuable for traceability, but it is not a safety guarantee. Review the generated SQL. Look for destructive operations, rename ambiguity, constraints that depend on existing data, early NOT NULL or uniqueness enforcement, and compatibility effects on old readers/writers.

The fixture keeps two failure classes separate. First, the plausible generated review fixture adds handle NOT NULL to an already-populated table and is expected to fail on the required-column precondition with SQLSTATE 23502; that file is generation evidence to review, not accepted SQL. Second, a separate deliberately naive data-transition probe adds handle as nullable, backfills it from display_name, and then reaches uniqueness. The two Sara rows normalize to the same handle, so the uniqueness step is expected to fail with 23505. The lesson is not that Drizzle is "wrong"; schema intent and generated DDL do not automatically solve existing-data preconditions or collision policy.

6) Bounded worked example

The baseline table is customer_profile(id, email, display_name). The target typed schema introduces handle. The review record contains ten inspection fields:

1. Schema Intent: normalized unique handle.
2. Source of Truth: codebase-first.
3. Prior Snapshot/Migration: baseline history is identified.
4. Generated SQL: record artifact path/hash.
5. Data Risk: collision and null transition.
6. Compatibility: can old readers/writers coexist during the chosen sequence?
7. Clean Apply: result on a disposable database/schema.
8. Database Observation: query the column, constraint and resulting rows.
9. Decision: accept, repair or defer.
10. Residual Risk: the fixture does not model production workload/topology.

7) Failure modes that must be observed

Failure A — required-column precondition. The generated review fixture attempts to add handle NOT NULL to a populated table. On the disposable fixture it should fail with 23502; record this as a generated-SQL/data-transition mismatch, not as a uniqueness collision.

Failure B — duplicate normalization precondition. The separate naive transition probe backfills normalized handles and then enforces uniqueness. Two seed records normalize to the same handle, so the uniqueness step should fail with 23505.

Failure C — schema changed, migration not applied. The target TypeScript schema exists while information_schema.columns still lacks handle. That is drift between declared intent and deployed state.

Failure D — generated SQL accepted unread. "Generate succeeded" is incomplete evidence. The record needs a human destructive/data-risk review and decision.

Failure E — push substituted without governance. push is not categorically wrong. But if the lesson contract selects a reviewed migration-file path, silently replacing it with push changes the audit/review path and must be flagged.

8) Experienced-programmer bridge

If you already know an ORM or migration framework, compress syntax and focus on four separations: type model ≠ deployed schema; generated diff ≠ accepted change; apply success ≠ invariant proof; tool convenience ≠ governance decision. A professional review must label which statements are direct observations and which remain inference.

9) Alternatives and trade-offs

Database-first fits a system where database schema is the primary authority and code is pulled/introspected from it. Codebase-first with migration files fits workflows that want a versioned schema declaration, migration history and explicit SQL review. Codebase-first with push can optimize rapid iteration, but it carries a different review/audit shape. This lesson does not declare one route universally superior. It declares one route for this fixture and records the boundary.

10) Guided and independent practice

For the guided path, inspect the baseline, record source of truth, inspect the target schema, run generate only in the publisher-pinned toolchain, and review SQL/snapshot artifacts before application. Predict the generated required-column failure (23502) separately from the naive normalized-handle uniqueness failure (23505). Run the drift probe before either migration changes the database. Apply only against a disposable PostgreSQL target and observe database state directly.

Independent transfer: a subscription service changes nullable external_id into a constrained identifier while a prior migration folder already exists. Without copying the fixture steps, decide source of truth, the role of the prior snapshot, the SQL risk checklist, clean-apply evidence, and the residual limitation.

11) Verification and evidence

The artifact must retain positive and negative observations. Minimum evidence includes baseline database observation, generated artifact identity, human SQL review, the generated required-column failure with its observed SQLSTATE, the separate normalization-collision failure with its observed SQLSTATE, repair/recovery result, clean apply, direct database observation, a drift probe, push-boundary decision, and one residual limitation. If real PostgreSQL or Drizzle tooling did not run, runtime status remains NOT_RUN; no simulated output is presented as runtime evidence.

12) Assessment and transfer

Passing work can state the bounded contract; distinguish database-first from codebase-first; review generated SQL for data/destructive risk; demonstrate the separation between schema intent and deployed state; diagnose the duplicate failure; and transfer the method to a changed context without decisive scaffolding. "TypeScript compiled" or "generate created a file" is not sufficient evidence.

13) References and freshness

The source boundary is exactly two frozen official vendor sources: DRIZZLE-MIGRATIONS and DRIZZLE-GENERATE. Both have review_due 2026-10-17. On 2026-09-15 the scheduled freshness recheck is not due; a same-source verification was performed without expanding scope. Re-review the TOOL_VOLATILE claims if behavior around generate, snapshots, migration folders, or the documented workflow changes.