Stop expression churn on re-deploy

🧪

Hands-on lab

Run this module yourself — the `before/` source, the three-engine sandbox, and the worked solution to diff against.

Demos/Learn/course12-module-05 →

You deploy a package. You deploy it again, unchanged. Exit 0 both times. You’d bet the second run did nothing.

It didn’t always. A check constraint got dropped and re-added. A computed column got dropped and re-added. A filtered index, the same. Nothing in the log said why, because as far as the tool could tell, something had changed.

On a PERSISTED or STORED column that’s a table rewrite. Every row. Every deploy. And “re-run and nothing changes” is the promise you built your pipeline on.

The engine never hands your text back

Every engine rewrites an expression when it stores it. You write RetentionDays <= 365; SQL Server keeps ([RetentionDays]<=(365)). You write starts_with(plan_code, 'P'); PostgreSQL keeps starts_with((plan_code)::text, 'P'::text). MySQL adds backticks and charset introducers to a generated column.

So compare what you wrote against what the catalog gives back, and they never match. Not after trimming whitespace, not after stripping parens — PostgreSQL added casts, MySQL added a charset introducer. The rewrite is a fixed point, and no normaliser catches every one. A text comparison was always going to lose that fight.

Ask what was applied, not what was written

From SchemaSmith 2.7.0, the tool stops asking the text. It kindles a table into every target, SchemaSmith.ExpressionMap (SchemaSmith_ExpressionMap on MySQL and MariaDB), with one row per expression: what your package authored, what the engine stored after applying it, and the engine version — plus, on SQL Server, the compatibility level — in force at the time. Let’s look at it:

../lab-sql.sh sqlserver declared_m5 "SELECT ObjectName, Slot, AuthoredText, CanonicalText, CompatLevel FROM SchemaSmith.ExpressionMap"
# → CK_Subscription_Retention   expression  RetentionDays <= 365   ([RetentionDays]<=(365))   160
# → CreatedAt                   default     SYSUTCDATETIME()       (sysutcdatetime())         160
# → IsActive                    default     1                      ((1))                      160
# → RetentionWeeks              computed    RetentionDays / 7      ([RetentionDays]/(7))      160
# → IX_Subscription_ActivePlan  filter      IsActive = 1           [IsActive]=(1)             160

Two columns that will never read the same, and that’s fine now. On the next deploy SchemaSmith asks one question per object: does the live text still match what I recorded for this declaration? It trusts neither the package text nor the catalog text; it checks both against the record.

What moved	What SchemaSmith does
Nothing	Leaves it alone, however differently the two texts read
Your declaration	Applies it and records the new pair
The live object, edited by hand	Puts your declaration back
The engine version or compatibility level	Re-baselines the record; no object is changed

Empty the map: verified, not rebuilt

The map is safe to empty. An empty map is also where any database starts that SchemaSmith didn’t build — one another tool made, or one deployed before 2.7.0. There’s no record to check against, and the text comparison would rebuild every expression. So SchemaSmith checks first:

../lab-sql.sh sqlserver declared_m5 "DELETE FROM SchemaSmith.ExpressionMap"
cd sqlserver && schemaquench --ConfigFile:deploy.settings.json --report:./norecord ; cd ..

Verified 3 expression(s) on a scratch copy of their table: the declared text renders to exactly what the database holds, so they are recorded rather than re-applied.

Nothing in the package changed, and nothing in the database did either. SchemaSmith applied your declared text to a scratch copy of the table, read back what the engine stored, and found the same text the live table holds. The computed column, the filtered index and the check were recorded as they stand. No PERSISTED column dropped, no rows rewritten, and the map is full again for the next deploy.

Quiet isn’t blind

Going quiet on churn would be dangerous if it also went blind to drift. So the lab tries to fool it.

Change the check by hand, the way someone patches production at 2am, and leave the package alone. The live text no longer matches the record, so the declaration goes back:

Dropping check constraint [dbo].[Subscription].CK_Subscription_Retention
Adding check constraint [dbo].[Subscription].CK_Subscription_Retention

Edit the Expression in dbo.Subscription.json to RetentionDays <= 730 and re-quench, and you get the same two lines. Applied, recorded, and the next deploy is quiet again. An edit you make is always an edit.

Then the one that would’ve hurt most. SQL Server freezes an expression’s stored text at the compatibility level it was created under, so re-applying every expression-bearing object after a compat change would be a far bigger event than the churn it prevents. Drop declared_m5 to 150 and deploy:

Re-baselined 5 recorded expression(s): they were recorded under a different SQL Server version or compatibility
level, and their stored canonical text is now refreshed for version 16.0.4260.1 at compatibility level 150. No
object was changed.

It re-reads the live text, updates its record, and tells you so — with a count and the version. An engine upgrade shows up in the first deploy after it, instead of as a mystery.

The report is the evidence

Don’t take a skimmed log as proof. Deploy twice with --report: and read the summary it writes:

schemaquench --ConfigFile:deploy.settings.json                     # exit 0 -- builds the table
schemaquench --ConfigFile:deploy.settings.json --report:./rerun    # exit 0

## Object Changes
- Created: tables=0, columns=0, indexes=0, constraints=0, foreignKeys=0, procedures=0, views=0, functions=0
- Modified: tables=0, columns=0
- Dropped: tables=0, indexes=0, constraints=0, foreignKeys=0

Zeros, on every engine in the lab. That’s the claim, and that’s the receipt.

The type names get rewritten too

Expressions aren’t the only thing the engine hands back in its own words. The lab’s columns are spelled INTEGER, BOOL, CHARACTER VARYING(20) and TIMESTAMP WITH TIME ZONE, and those aren’t accidents. Each is a synonym, and the catalog reports the base name: int, tinyint(1), varchar(20), timestamptz.

Compare the two as text and you get the same fight as above. Before 2.7.0 every one of those columns was altered on every deploy, with the package and the database both correct. And it hit exactly the folks who did it by the book: the SQL standard spells it TIMESTAMP WITH TIME ZONE, pg_dump writes it that way, and most ORMs generate it.

2.8 finishes the family. The lab has you add one more column per engine, spelled the textbook way:

{ "Name": "Notes",      "DataType": "CHAR VARYING(100)", "Nullable": true }             // SQL Server
{ "Name": "renewed_at", "DataType": "TIMESTAMP WITHOUT TIME ZONE", "Nullable": true }   // PostgreSQL
{ "Name": "Score",      "DataType": "DOUBLE PRECISION", "Nullable": true }              // MySQL, MariaDB

Deploy once to add it, deploy again with a report, and it’s Modified: tables=0, columns=0. You write the type your way. The engine stores it its way. SchemaSmith compares what each one means.

A note on nullability

None of the lab’s computed or generated columns declares Nullable, and that’s deliberate. On those columns an omitted Nullable leaves nullability to the engine — which makes it nullable — so SchemaSmith never narrows it. Declare "Nullable": false when you genuinely want NOT NULL (SQL Server on a PERSISTED column, PostgreSQL and MySQL on any generated column; MariaDB can’t declare it at all). Ask for NOT NULL where existing rows make the expression NULL and the deploy fails, which is the right answer.

Check yourself: After a maintenance window, your SQL Server's compatibility level moved from 160 to 150. The next deploy of an unchanged package prints a line starting 'Re-baselined 5 recorded expression(s)' and the report shows all zeros. Did anything in the database change, and why didn't SchemaSmith rebuild those objects?

No objects changed — the line itself ends with “No object was changed,” and the zeros in the report confirm it. SQL Server freezes an expression’s stored text at the compatibility level it was created under, and every record was made under the old level — so a different rendering from here on could mean drift, or could just mean the new level. Rather than re-apply every expression-bearing object to find out, SchemaSmith re-reads the live text and refreshes its record in SchemaSmith.ExpressionMap for the new version and level.

Contrast that with a hand edit: there the engine and compat level are unchanged but the live text no longer matches the record, so the declaration is put back. The recorded version and level are what let SchemaSmith tell “the engine moved” apart from “someone moved the object.”

---

A declarative tool lives or dies on the re-run. If the second quench strikes the same metal the first one did, you stop trusting the zeros, and a deploy log you don’t trust is one you stop reading. So SchemaSmith stopped arguing with the engine about spelling and started remembering what it did: an unchanged declaration is left alone, a changed one is applied, a hand-edited object is put back, and an engine upgrade is recorded rather than replayed.

No text games. No phantom rebuilds. Just a record of the work.

Got a deploy that still rebuilds something on every run? Email me at forgebarrett@schemasmith.com — I’d like to see the object. More’s coming from the forge.

Until then, may your second quench leave the metal exactly as the first one set it.

— Forge

Mark this lesson complete