Course 5 · Module 5 — Migrating from hand-rolled scripts

No framework at all — just a folder of numbered SQL files and a shell script that runs them in order. 001_create_customer.sql, 002_create_product.sql, on and on. One file guards itself with IF OBJECT_ID(...) IS NULL, the next one doesn’t, because whoever wrote it was in a hurry. A home-grown schema_version table tracks what ran — when someone remembered to add the row. To know what your database actually is, you read every script top to bottom and hope none of them were edited after the fact.

This is the messiest “before” of the course. It’s also the most freeing to leave. Let’s move it to SchemaSmith.

Hand-rolled scripts, concept for concept

Hand-rolled pipeline	SchemaSmith
A pile of numbered `ALTER`/`CREATE` scripts	Declarative table files — the end state, not the pile that built it
Idempotency guards (`IF NOT EXISTS` here, forgotten there)	Idempotent by construction — quench computes the diff every time, so re-runs converge instead of double-applying
Hand-maintained `schema_version` table	No version table — your table definitions are the state
The shell runner that applies files in order	`schemaquench` — no order to maintain; there’s one declared state
”Did `004` ever run on prod?”	No ledger to consult, because there are no steps to have run
Data-fix scripts mixed in with structure	Structure is declarative; true data fixes move to run-once migration scripts (below)

The guards row is the one that changes your life. In a hand-rolled pipeline, you are responsible for making every script safe to re-run — and you’ll miss one. SchemaSmith makes idempotency the engine’s job: quench reads the live shape, compares it to the declared shape, and emits only the difference. Run it once or run it ten times — same result. There’s nothing to guard, because nothing replays.

Extract, and walk away from the pile

You don’t run the scripts. The setup already applied their end state to shop_from_scripts — the four tables and the schema_version table, exactly as the shell runner would have left them.

Point SchemaTongs at the database. The settings file names the four tables you’re keeping:

"ShouldCast": { "ObjectList": "dbo.Customer,dbo.Product,dbo.SalesOrder,dbo.OrderItem" }

cd sqlserver
schematongs --ConfigFile:SchemaTongs.settings.json

=== Casting Summary ===
  Tables:     4 extracted, 0 errors

Four tables. No schema_version.json — you named the tables you wanted, and the home-grown tracker wasn’t on the list. The whole before/ folder of numbered scripts? Irrelevant now. You’re not porting the steps; you’re capturing the result.

Now quench the package back to prove it:

schemaquench --ConfigFile:quench.settings.json

The first run adopts your tables — SchemaSmith stamps them as managed and stands up its own bookkeeping in a separate SchemaSmith schema. Run it again and nothing happens. A clean no-op is the proof: the package is a faithful cast of the live database, and you’re declarative now.

schema_version is still sitting there, untouched — an inert table you can drop whenever you like. The pile of scripts that built this database has nothing left to do.

What about the scripts I can’t reproduce — the data fixes?

Fair question. Some scripts in a hand-rolled pile aren’t structure at all — they’re data: a one-time backfill, a correction to a stuck row, a reference-table reseed. Those don’t disappear into the declarative model, and they shouldn’t.

SchemaSmith has a home for them: run-once migration scripts. Drop a plain .sql file into one of the template’s script slots — say After Scripts/ — and write it exactly as you would have in your pile, no guard needed:

-- Package/Templates/Main/After Scripts/Backfill OrderStatus.sql
UPDATE dbo.SalesOrder SET Status = 'New' WHERE Status IS NULL;

On the next quench, SchemaSmith runs it and records it in its own tracking table (CompletedMigrationScripts) — so it runs exactly once per database and is never replayed. That’s the same job your schema_version table did, with one difference: SchemaSmith keeps the ledger for you, automatically, instead of relying on you to remember the INSERT. No IF NOT EXISTS guard — the tracking, not the script, guarantees once. (Need it to run every time instead — a stats refresh, a re-grant? Tag the filename [ALWAYS].) Structure goes declarative; genuine data fixes become tracked, run-once migrations. Course 2 covers migration scripts in depth.

Per-engine notes

The mechanism is identical on all three engines — whitelist the four tables, extract, quench to a no-op. Only the dialect’s names differ.

	SQL Server	PostgreSQL	MySQL
`ObjectList` form	`dbo.Customer,…`	`public.customer,…` (folded lowercase)	`Customer,…` (schema = database)
Tracker left behind	`schema_version`	`schema_version`	`schema_version`
Extract result	4 tables, no tracker	4 tables, no tracker	4 tables, no tracker

Check yourself: In the hand-rolled pile, some scripts guard against re-running and some don't. How does SchemaSmith handle idempotency?

It doesn’t need per-script guards at all. Quench computes the difference between the declared table files and the live database every run, and emits only what’s missing. Running it again finds no difference and does nothing — a clean no-op. Idempotency is a property of the engine’s diff-and-converge model, not something you hand-write into each script and hope you didn’t forget.

That’s the switch. Five tools — Flyway, Liquibase, EF Core, SSDT, and a hand-rolled pile — and every one of them ends the same way: you stop porting the history and start declaring the state. The metal doesn’t remember which hammer shaped it. Neither should your database.

You’ve switched. Now go deeper — the reference docs cover the full quench config, and Course 2 takes you into fan-out across many databases, conditional deployment, and data delivery. The forge is yours now.

Got a pipeline with a tangle of data-fix scripts you’re not sure how to sort from the structural ones? Email me at forgebarrett@schemasmith.com — I read every one.

Until then, may you never again keep a tally of what you’ve run, and every change you forge hold fast without one.

— Forge

Mark this lesson complete