Course 4 · Recipe 6 — Surviving a destructive rebuild

Some deploys make you hold your breath. A table gets removed from the product, or a change forces a drop-and-recreate, and the rows in it are gone the moment the DROP runs. So you do the manual dance: back it up by hand, run the deploy, copy the data back, hope you matched every column. There’s a built-in way to make that drop reversible — and once it’s in place, you stop holding your breath.

The lever: hooks the engine calls instead of dropping

SchemaQuench looks for two procedures by name. If they exist, it uses them:

CustomTableDrop — when a table is removed from the product, the engine routes its drop through this proc instead of running a hard DROP. What “drop” means becomes yours to decide.
CustomTableRestore — when a table is being added, the engine calls this first; if your proc brings the table back, the engine sees it exists and won’t recreate it empty.

Install them once per database — they live in the SchemaSmith schema that kindling creates. The simplest implementation is a soft-drop: rename the table aside instead of dropping it, so its structure and data are untouched, then rename it back on restore.

-- the heart of the drop hook (SQL Server): set the table aside, don't destroy it
EXEC sp_rename @src, @recyclebinName;

The hook is the same idea on every engine — only the rename statement changes:

Engine	Rename-aside (drop hook)
SQL Server	`EXEC sp_rename @src, @recyclebinName;`
PostgreSQL	`EXECUTE format('ALTER TABLE %I.%I RENAME TO %I', p_Schema, p_Table, rb);`
MySQL	`ALTER TABLE \`db`.`tbl` RENAME TO `db`.`__recyclebin__tbl`;`

The restore hook is the mirror — rename the aside copy back to the original name before the engine would recreate it.

The recipe: drop it, get it back

Deploy a package with a Promotion table and put rows in it. Now deploy the package that no longer defines Promotion. Normally that’s a drop and the rows are gone — but the hook fires:

Promotion gone under its own name… __recyclebin__Promotion holds 2 rows

The data wasn’t dropped; it was set aside. Now deploy the package that defines Promotion again. CustomTableRestore renames the aside copy back before the engine would recreate the table:

Promotion → 1 SAVE10, 2 SAVE20

The original rows survived the round-trip — through a full remove-and-re-add — without a single manual backup step. Same behavior on SQL Server, PostgreSQL, and MySQL; only the procedure names and the rename syntax differ.

The aha: drop-and-recreate becomes reversible

This is the safety net under the most dangerous kind of deploy. Normally a destructive change is a one-way door: once the table’s dropped, the data’s a restore-from-backup problem. The hooks turn that door into a soft-drop you can undo — your drop hook decides the table is set aside, not destroyed, and your restore hook brings it back before anything’s lost. You install it once and every removal becomes recoverable. For the operational picture around rollback and recovery, the Database Rollback Strategies guide is the companion read.

Check yourself: How do you keep a table's data from being lost when a schema change forces SchemaSmith to drop and recreate it?

Install the SchemaSmith.CustomTableDrop and SchemaSmith.CustomTableRestore hook procedures — the engine routes the drop through your archive proc, and when the table returns it calls your restore proc and won’t recreate it empty. Your data rides through the rebuild in an aside copy you control.

Every good forge has a quench tank, but it also has a slack tub off to the side — somewhere to set a piece down without losing the work in it. That’s what these hooks are: when a change would scrap a table, you set it aside in the tub instead, and lift it back out whole when it’s wanted again. The fire never takes what you didn’t mean to give it.

And that’s the cookbook. Six recipes, one idea underneath all of them: declare your intent on the object, and let the tools act on it — gating deployment, enforcing policy, reading the live server, carrying assets, generating schema, and now catching what a destructive change would drop. You came in knowing how to declare and quench; you’re leaving knowing how to drive.

Got a destructive deploy you’ve been dreading — a table you can’t afford to lose the data in? Email me at forgebarrett@schemasmith.com — I read every one.

Until then, may your most dangerous deploys come undone clean, and nothing you forge ever be lost to the fire.

— Forge

Mark this lesson complete