drizzle-kit generated a DROP TABLE for a table that was still in production. drizzle-kit wasn't the problem.

I asked drizzle-kit for a migration that added two tables. The SQL it generated also contained a DROP TABLE ... CASCADE for a table that was still sitting in my production database, and an ADD COLUMN for a column that production already had.

Neither change had anything to do with the feature I was building. If I had applied the file as it came out, the first statement would have deleted a live table along with anything that depended on it. The second would have failed with "column already exists" and taken the rest of the migration with it, or left it half-applied, depending on how I ran it.

drizzle-kit did nothing wrong. It did exactly what it is designed to do. The problem was that I had three different ideas of "the current schema" in my project and assumed they were the same thing. This post explains how they drifted apart, why the diff came out the way it did, and the checks I now run before any generated migration goes near production.

The setup

The app is Next.js with Drizzle ORM on Postgres (Neon). The schema lives in one TypeScript file, lib/db/schema.ts. Migrations are generated with drizzle-kit generate into drizzle/migrations/, and drizzle-kit keeps a JSON snapshot of the schema next to each migration in drizzle/migrations/meta/.

I don't let tooling apply migrations to production. I don't use drizzle-kit push, and the header of my migration files literally says not to use drizzle-kit migrate either. Production changes are applied by hand, inside a transaction, after I've read the SQL. That rule is the only reason this story ends with a cleanup instead of a restore.

There is one more habit in the background that matters. For small additive changes I sometimes skipped the generator and wrote a one-off script that runs a single idempotent DDL statement. That habit is half of the bug.

How the drift happened

The baseline migration (0000) was generated at the end of July. At that point the schema had a waitlist_entries table for a pre-launch email list, and the reviews table did not have a suppressed_count column yet.

The next day I made two schema changes in one commit, and neither went through drizzle-kit generate.

Change 1: I deleted the waitlist feature. We had decided to launch directly instead of collecting a waitlist, so I removed the route, the form, the email-sending module, and the table definition from schema.ts:

 // ウェイトリスト登録（プレローンチ用リード収集）
-export const waitlistEntries = pgTable(
-  "waitlist_entries",
-  {
-    id: uuid("id").defaultRandom().primaryKey(),
-    email: text("email").notNull().unique(),
-    source: text("source"), // UTM等の登録元。任意
-    createdAt: timestamp("created_at").defaultNow().notNull(),
-  },
-  (table) => ({
-    emailIdx: uniqueIndex("waitlist_entries_email_idx").on(table.email),
-  })
-);

(The leftover Japanese comment above it says "waitlist signups (pre-launch lead collection)". It outlived the code it described, which is a small preview of the whole problem.)

I deleted the definition. I did not drop the table. In my head the feature was gone. In production the table was still there, empty and unreferenced.

Change 2: I added a column with a one-off script. In the same commit, reviews gained a column:

     commentsCount: integer("comments_count").default(0),
+    suppressedCount: integer("suppressed_count").default(0),
     tokensUsed: integer("tokens_used").default(0),

Instead of generating a migration, I added it to production with a small script that runs one statement:

await sql`
  ALTER TABLE reviews
    ADD COLUMN IF NOT EXISTS suppressed_count integer DEFAULT 0
`;

It's idempotent, it checks information_schema afterwards, and it worked. From production's point of view, everything was correct.

So now:

• schema.ts had no waitlist table and did have suppressed_count.
• Production still had the waitlist table and also had suppressed_count.
• The latest drizzle snapshot (0000_snapshot.json) still had the waitlist table and did not have suppressed_count.

Nothing complained, because nothing compares those three. The app only reads schema.ts. Production only knows what has been executed against it. The snapshot only changes when you run generate.

More than a month later: drizzle-kit generate

In early September I built a referral feature that needed two new tables, referral_codes and referrals. I added them to schema.ts and ran npm run db:generate.

The generated file contained the two CREATE TABLE statements I expected, and two statements I didn't:

DROP TABLE "waitlist_entries" CASCADE;
ALTER TABLE "reviews" ADD COLUMN "suppressed_count" ...;

(I edited the generated file before committing it, so these are the two lines as I recorded them in my notes at the time, not a byte-for-byte copy of the original output.)

My first reaction was that drizzle-kit had somehow connected to the wrong database. It hadn't connected to any database.

Why the diff looks like that

This was the part I had wrong in my head for months: drizzle-kit generate does not diff your schema against your database. It diffs your schema against its own last snapshot.

The flow is:

1. Read schema.ts and build a schema object.
2. Read the most recent snapshot JSON in meta/.
3. Emit SQL for every difference between the two.
4. Write a new snapshot equal to the current schema.ts.

My drizzle.config.ts has a dbCredentials block with the production-shaped URL, which made it easy to believe that generate looks at the database. It doesn't. Those credentials are for commands that actually talk to a database (push, migrate, pull, studio).

Run the drift through that flow:

	Last snapshot (0000)	schema.ts now	Generated SQL
waitlist_entries	exists	gone	DROP TABLE ... CASCADE
reviews.suppressed_count	missing	exists	ADD COLUMN
referral_codes, referrals	missing	exist	CREATE TABLE x2

Every line is correct relative to the snapshot. The snapshot was the stale party, and it was stale because I had changed the schema twice without telling the generator.

I can confirm this from the repository now. grep over the snapshot files shows waitlist_entries present in 0000_snapshot.json and absent from 0001 onward, and suppressed_count absent from 0000 and present from 0001 onward. The snapshot quietly absorbed both changes at the moment I ran generate for an unrelated feature.

What each statement would have done

The DROP. In my case the damage would have been close to zero. Before deciding anything, I checked production: the table existed, and SELECT COUNT(*) on it returned an empty result set. But "it happened to be empty" is luck, not a safety property. Put any other "dead" table in that position (an audit log you stopped writing to, a table a cron job still reads, a table a teammate is about to wire up) and the same generate run produces the same line. CASCADE widens it further: it also drops whatever depends on the table, such as foreign keys from other tables and views.

The ADD COLUMN. This one would have failed loudly, since production already had the column, and Postgres rejects a plain ADD COLUMN for an existing column. Loud is better than silent, but it still matters where it fails. If the file is applied as a single transaction, the whole migration rolls back and you retry. If it's applied statement by statement (some SQL consoles autocommit each statement), you end up with whatever ran before the failure applied and whatever came after it not. That's a schema nobody designed.

My one-off script had used ADD COLUMN IF NOT EXISTS, which is idempotent. The generator emits a plain ADD COLUMN, because as far as the snapshot knows the column has never existed. The idempotency lived in the script, not in the migration history.

What I actually did

1. Checked production before touching anything. I queried information_schema.tables and information_schema.columns to confirm what really existed. The table was there, and the column was there.
2. Removed both unrelated statements from the generated file. The migration was narrowed to the referral tables only. A migration should do what its name says and nothing else.
3. Cleaned up the dead table as a separate, deliberate step. After confirming it was empty, and with explicit sign-off, I dropped waitlist_entries by hand. It was its own decision instead of a side effect of a referral feature.
4. Applied the migration by hand, in a transaction. The committed file is now wrapped in BEGIN; ... COMMIT; with a header comment explaining why. A later edit to the same file made the transaction even more important: it replaces an index with a unique version (DROP INDEX IF EXISTS followed by CREATE UNIQUE INDEX). In an autocommit console, if the create fails after the drop succeeds, you're left with no index at all, which breaks every ON CONFLICT that relied on it.

Because the snapshot had already been rewritten to match schema.ts, the next generate (for later changes) produced clean diffs. The drift was absorbed into history. Repository and production agreed again, but only because a human reconciled them.

Why the side door existed in the first place

It's worth being honest about why I was making schema changes outside the generator at all, because the reason wasn't laziness. It was friction, and friction is what pushes everyone toward side doors.

The header comment of my one-off column script explains it: I didn't use drizzle-kit push because push can stop and ask interactive questions (for example, whether it's allowed to truncate a table), and a command that waits for a keypress in a non-interactive context just hangs. So for a single additive column against production, a tiny script with ADD COLUMN IF NOT EXISTS, a dry-run mode by default, an explicit --apply flag, and an information_schema check afterwards felt safer than the official tool. For that one change, it was.

generate has the same trait. When I ran the referral migration, I first tried it from an automated, non-TTY session, and it stopped at an interactive prompt it couldn't get an answer to. It had to be run from a real terminal. That's a reasonable design, since some schema changes are genuinely ambiguous (is this a rename or a drop-and-create?) and a tool shouldn't guess. But it means the "proper" path costs more than the side door, and every time the proper path costs more, some changes will skip it.

The fix isn't to ban side doors. Sometimes a hand-written, idempotent statement really is the right way to change production. The fix is to make sure every side door ends with a step that updates the front door's records: after the script runs, run generate so the snapshot catches up, and commit the result as "already applied". The script changes the database. The generate run changes the history. You need both, or the history starts lying.

The check I already had, and why it wasn't enough

I already had a read-only preflight script that runs before any DDL. It opens a READ ONLY transaction, lists the tables in public, and compares them with what schema.ts defines. Here's the core of it (comments translated):

// Query inside a READ ONLY transaction.
// Even if code below ever tries to write, the database refuses it.
const { currentDatabase, tables } = await sql.begin(async (tx) => {
  await tx`SET TRANSACTION READ ONLY`;

  const [{ current_database }] = await tx<{ current_database: string }[]>`
    SELECT current_database()
  `;
  const rows = await tx<{ table_name: string }[]>`
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
    ORDER BY table_name
  `;

  return { currentDatabase: current_database, tables: rows.map((r) => r.table_name) };
});

and later:

const warnings: string[] = [];
const unexpected = tables.filter((t) => !ALL_EXPECTED.includes(t));
if (unexpected.length > 0) {
  warnings.push(`table exists that schema.ts does not define: ${unexpected.join(", ")}`);
}

That warning is exactly the signal for the waitlist half of this bug: a table in production that the code doesn't define. It would have fired. But a warning in a JSON report is something you have to go and read, and the preflight answers a different question ("am I connected to the right database?"). It checks tables, not columns, so it would never have caught the suppressed_count half. And it compares production with schema.ts, not with the snapshot. The snapshot is the third party that actually drives generate, and nothing was looking at it.

What I do now

None of this needs special tooling. It's mostly about treating a generated migration as a proposal and treating the snapshot as state that can go stale.

1. Read every generated statement and trace it back to your change. For each line, ask "which edit in this branch caused this?" If the answer is "none", stop. An unexplained DROP or ALTER means the snapshot and reality have diverged somewhere, and the fix is to find out where, not to delete the line and move on.

2. Treat DROP and CASCADE in a generated file as a hard stop. Any destructive statement gets checked against the live database (does it exist, does it have rows, what depends on it) before it's allowed to stay. If it stays, it should be its own reviewed change, not a side effect of something else.

3. Don't change the schema outside the generator, or if you do, close the loop. One-off scripts are fine and sometimes the right call for production. But if a column goes in by script, run generate immediately afterwards so the snapshot learns about it, and commit that migration marked as already applied. Otherwise the generator will "discover" the change later, inside somebody else's feature branch.

4. Removing a table from schema.ts is a migration, not a cleanup. Deleting the definition is the start of removing a table, not the end. Either drop it in the same change, through a reviewed migration, or leave the definition in place with a comment until you're ready to drop it.

5. Wrap hand-applied migrations in a transaction. Partial application is the failure mode that turns an ordinary error into an unplanned schema.

6. Check production against schema.ts periodically, not just before migrations. Tables and columns. An information_schema query is enough:

SELECT table_name, column_name, data_type, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

Compare that list with your schema file. Anything that exists on only one side is drift that your next generate will try to "fix" for you.

The general lesson

Most migration tools that generate SQL from a declarative schema work this way. They have to compare against something, and if that something is a file in your repo rather than the live database, it can only be as accurate as the history you fed it. Every schema change made by hand, by script, from a SQL console, or by deleting code without dropping the table it described creates a gap between the snapshot and reality. The generator closes that gap the only way it can: by writing SQL that makes the database match the file, whether you meant to ask for that or not.

The dangerous part isn't the generator. It's the assumption that "the schema" is one thing. In my project it was three: what the code declares, what the tool's snapshot records, and what the database actually contains. They agree only as long as every change goes through the same door. The day one change uses a side door, the next person to use the front door gets a migration that "fixes" it.

So the rule I follow now is short: a generated migration is a diff against history, not against production. Read it like a diff from a teammate who has been away for a month, because in effect that's what it is.