[BUG]: drizzle-kit generate derives idx from journal alone — prefix collision + silent snapshot overwrite when journal is stale vs on-disk SQL

[philipobermeyer]

Verified not previously filed: searched closed + open issues for migration prefix collision, drizzle-kit generate idx, journal stale snapshot overwrite, two migrations same prefix, snapshot overwritten, lastEntryInJournal — no matches.

drizzle-orm version: ^0.45.2
drizzle-kit version: ^0.31.10
Other packages: N/A
Runtime: Node 22, postgres dialect (Neon HTTP driver)

---

Describe the bug

drizzle-kit generate derives the next migration index from the local journal alone, without cross-checking on-disk drizzle/*.sql. When the journal is stale relative to on-disk SQL (a common branch-divergence scenario — partial rebase, manual journal repair, pulled migration files without journal sync), the next generate:

1. Produces a new SQL file with a prefix that collides with an existing on-disk migration
2. Silently overwrites the existing drizzle/meta/<prefix>_snapshot.json in place

Silent at author time. Detection is manual (ls drizzle/ + git diff drizzle/meta/); recovery is multi-step (rename the new file + restore the overwritten snapshot from git + edit _journal.json to repair the id/prevId chain).

Source location

In node_modules/drizzle-kit/bin.cjs (verified at ~L32926 in the 0.31.x release; line number may move in subsequent releases — the shape is what matters):

const lastEntryInJournal = journal.entries[journal.entries.length - 1];
const idx = typeof lastEntryInJournal === "undefined" ? 0 : lastEntryInJournal.idx + 1;
const { prefix, tag } = prepareMigrationMetadata(idx, prefixMode, name);
// prefix = idx.toFixed(0).padStart(4, "0")
fs.writeFileSync(`${metaFolderPath}/${prefix}_snapshot.json`, ...);  // overwrites without check
fs.writeFileSync(`${outFolder}/${tag}.sql`, sql);                     // collides without check

Two filesystem writes, neither preceded by an existence check against on-disk state.

Minimum reproduction

mkdir drizzle-repro && cd drizzle-repro
npm init -y
npm install drizzle-orm@^0.45.2 drizzle-kit@^0.31.10 pg

cat > schema.ts <<'EOF'
import { pgTable, serial, text } from "drizzle-orm/pg-core";
export const a = pgTable("a", {
  id: serial("id").primaryKey(),
  name: text("name"),
});
EOF

cat > drizzle.config.ts <<'EOF'
import type { Config } from "drizzle-kit";
export default {
  schema: "./schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
} satisfies Config;
EOF

npx drizzle-kit generate --name initial    # creates 0000_*.sql + journal entry idx=0

# Edit schema to add `email: text("email")` column
npx drizzle-kit generate --name add_email  # creates 0001_*.sql + journal entry idx=1

# Simulate "journal stale vs on-disk" — analogue of partial rebase / manual repair
node -e '
const fs = require("fs");
const j = require("./drizzle/meta/_journal.json");
j.entries.pop();
fs.writeFileSync("./drizzle/meta/_journal.json", JSON.stringify(j, null, 2));
'

# State now: journal max idx = 0; on-disk max prefix = 1
ls drizzle/                          # → 0000_*.sql, 0001_*.sql both present
cat drizzle/meta/_journal.json       # → only idx=0 entry

# Edit schema to add `bio: text("bio")` column
npx drizzle-kit generate --name add_bio

# OBSERVED:
#   drizzle-kit emits 0001_<random>.sql — colliding prefix with existing 0001_add_email.sql
#   drizzle/meta/0001_snapshot.json silently OVERWRITTEN with new schema state
git diff drizzle/meta/0001_snapshot.json    # → snapshot was rewritten in place

Expected behavior

drizzle-kit should either:

1. Refuse to run with a clear error:
   Journal stale: on-disk has migration 0001_* but journal max idx is 0000. Sync your journal (e.g., \git pull --rebase origin master`) or run `drizzle-kit check`.`
2. OR derive idx from max(journalMaxIdx, onDiskMaxPrefix) + 1 so collisions can't occur even when the journal is stale.

Option 1 is more diagnostic (surfaces the underlying state problem to the user). Option 2 is the strictly-safer narrow change.

Suggested fix sketch

const onDiskMaxPrefix = fs.readdirSync(outFolder)
  .filter(f => /^\d{4}_/.test(f))
  .map(f => parseInt(f.slice(0, 4), 10))
  .reduce((m, n) => Math.max(m, n), -1);

const journalMaxIdx = journal.entries.length === 0
  ? -1
  : journal.entries[journal.entries.length - 1].idx;

if (onDiskMaxPrefix > journalMaxIdx) {
  throw new Error(
    `Journal stale: on-disk has migration ${pad4(onDiskMaxPrefix)}_* but journal max idx is ${pad4(journalMaxIdx)}. ` +
    `Sync your journal (e.g., \`git pull --rebase origin master\`) or run \`drizzle-kit check\`.`
  );
}

const idx = journalMaxIdx + 1;

Common triggers

Branch-divergence scenarios that cause the journal to drift from on-disk SQL:

Scenario	Mechanism
Branch opened pre-merge, partial rebase resolved with --ours strategy on _journal.json	Journal misses upstream entries while SQL files merge cleanly
Manual _journal.json edit during a prior repair-in-progress left idx < on-disk max prefix	Operator removed an entry to retry generation
Two parallel branches each run generate before either merges	Both produce idx N+1 — collision surfaces at second branch's PR

The first two are local-author windows that drizzle-kit would close if it cross-checked on-disk SQL. The third is a cross-PR window that drizzle-kit check at CI time can catch.

Workaround for anyone hitting this in the meantime

A wrapper script around drizzle-kit generate:

• Pre-condition checks: journal-on-disk consistency (journalMaxIdx >= fileMaxPrefix); snapshot-chain integrity via id/prevId walk; clean working tree
• Post-condition checks: exactly one new SQL + snapshot file; no in-place snapshot overwrite (content hash compare against pre-snapshot); new journal entry's idx matches new SQL file's prefix
• Revert on detection: restores pre-condition drizzle/ state if any post-condition fails

Closes the local-author window. Every downstream consumer would need their own wrapper — hence filing for an upstream fix.

Happy to provide additional repros or test against a candidate fix.