drizzle-kit generate Duplicate ADD COLUMN: Stale Snapshot

#postgres #database #webdev #devops

If a column addition isn't reflected in the drizzle snapshot, the next drizzle-kit generate re-emits an ADD COLUMN without IF NOT EXISTS.

Starting with the conclusion

For a column addition with no new table involved, don't rerun drizzle-kit generate — hand-edit the snapshot (drizzle/meta/*.json) directly to reflect the column definition. Leave it unreflected and the next generate will judge that column "not yet existing," re-emitting an ADD COLUMN with no IF NOT EXISTS. Applying that ALTER to an environment where the column already exists (via a hand-written migration) fails.

The symptom

On 2026-07-03 (#1205), running drizzle-kit generate to add a new table, class_weekly_schedules, produced a migration file that also re-emitted duplicate ADD COLUMN statements for class_visitors.sort_order and student_callouts.sort_order — columns already added by hand-written migrations 0034 and 0035.

-- (manually removed) drizzle-kit also generated ADD COLUMN for class_visitors / student_callouts
-- sort_order here, but those are owned by hand-written 0034/0035 (ADD COLUMN IF NOT EXISTS, already
-- applied in some environments), so they were removed from this file. sort_order is now captured in
-- the snapshot (meta), so the drift that would re-emit it on future generates is resolved.

0034 and 0035 were written with ADD COLUMN IF NOT EXISTS, but the ALTER drizzle-kit generated this time had none. This round was resolved by manually removing the duplicate section from the SQL file and folding sort_order into the snapshot to close the drift.

The next day, 2026-07-04 (#1220), a similar task came up: adding an is_highlight column to class_visitors and student_callouts. This one involved no new table, so following the same procedure (generate → manually remove the duplicate) would produce a migration file containing nothing but the is_highlight ADD COLUMN, plus a journal entry — leaving behind an empty SQL artifact containing only a comment after removal. The cleanup from last time would become this time's entire change.

The cause

drizzle-kit generate compares the schema definition (TypeScript) against the most recent snapshot and emits only the diff as SQL. If the snapshot has no record of a column, it's treated as a "new addition" regardless of whether it already exists in the actual database, and a corresponding ALTER TABLE ... ADD COLUMN gets generated — without IF NOT EXISTS. The hand-written migrations (0034/0035) included IF NOT EXISTS from the start, so they could safely rerun even against an environment where they'd already applied; the generated side makes no such allowance.

The fix

For #1220, instead of rerunning generate, the target snapshot file (drizzle/meta/20260702121657_snapshot.json) was hand-edited directly, adding the is_highlight column definition to both the class_visitors and student_callouts table definitions.

"is_highlight": {
  "name": "is_highlight",
  "type": "boolean",
  "primaryKey": false,
  "notNull": true,
  "default": false
}

The column spec was shaped to match the existing boolean, default-false columns, and inserted right after sort_order in both tables. That the hand edit touches nothing in the journal/SQL lineage is structurally obvious, but whether that hand edit is "consistent with the schema" was confirmed not by eyeballing it but by rerunning drizzle-kit generate. The result: "No schema changes, nothing to migrate" — drizzle's own signal that schema TS and snapshot now match, with no migration file or journal entry generated.

Preventing recurrence

This choice built on the landmine already stepped on the day before in #1205 (generate emitting a duplicate ALTER, manually removed, leaving behind an SQL artifact containing only a comment). Rather than "step on the same landmine again, then fix it," #1220 switched to "align the snapshot alone before stepping on the landmine." What both share is that correctness wasn't verified by eyeballing either — it was handed off to the deterministic zero-diff confirmation that drizzle-kit generate itself returns.

Originally published at tech.rebounder.jp.

DEV Community