AI agent skill · by @pedronauck

Drizzle Safe Migrations

Plan safe Drizzle backfills, enum/check/default changes, constraint tightening, migration ordering, and rollback. Excludes other ORMs, query optimization, and new-schema design.

Archived 2026-10-23 · Source updated 2026-09-14. Check the original repository for the latest version.

SKILL.md instructions

# Drizzle Safe Migrations

## Overview

Use this skill to run database migrations in a way that is auditable, deployment-safe, and consistent with Drizzle's migration model.

## Core Rules

- Generate schema migrations with the existing project script and package manager; `bun run db:generate` below is an example, not a required script name.
- Never hand-edit generated schema migration files.
- Generate data backfills as custom migrations (`bun run db:generate -- --custom --name <name>`) and edit only that custom SQL file.
- Apply data normalization before tightening constraints.
- Keep one-off data fixes in migration history, not as hidden runtime logic, unless an emergency hotfix requires temporary mitigation.

## Workflow

1. Classify the change:
   - `schema-only`: only column/table/index/default changes.
   - `data+schema`: old rows must be transformed before new constraints/defaults.
2. For `data+schema`, create custom migration first:
   - `bun run db:generate -- --custom --name <descriptive_name>`
   - Add idempotent backfill SQL.
3. Generate schema migration second:
   - `bun run db:generate`
4. Verify migration ordering in `drizzle/meta/_journal.json`:
   - backfill migration index must be lower than constraint-tightening migration index.
5. Verify generated SQL and snapshots:
   - backfill migration contains only intended data change.
   - schema migration contains constraint/default/type changes.
6. Run the owning migration suite against existing-data and fresh-install cases, plus required project gates. Reuse current evidence; unrelated backend suites are not an additional skill gate.
7. Record material rollout risks and recovery steps when applicable:
   - expected data transformations,
   - lock-risk areas,
   - rollback strategy.

## Backfill Requirements

- Use restrictive `WHERE` clauses.
- Prefer idempotent updates (`UPDATE ... WHERE status = 'legacy_value'`).
- Do not mix unrelated DDL/DML in the same migration.
- Keep SQL explicit and minimal.

## Constraint Tightening Pattern

When removing allowed values (enum/check):

1. Backfill existing rows to valid target value.
2. Update default to new value.
3. Tighten check/enum constraint.

For large tables or strict uptime targets, evaluate staged constraint validation supported by the actual database and driver. PostgreSQL `NOT VALID` / `VALIDATE CONSTRAINT` applies only to supported constraint kinds.

## Anti-Patterns

- Hand-editing generated schema migration files.
- Tightening constraints before backfilling existing data.
- Hiding one-time migration logic in app startup code without migration artifacts.
- Running migrations without validating order in the Drizzle journal.

## Reference

- See `references/production-playbook.md` for command templates and review checklists.