Course 2 · Module 3 — Conditional deployment: the right thing in the right place

You’ve got one package and you want to keep it that way — but the targets aren’t identical. That analytics index is worth it in production and a waste in dev. That folder of seed data belongs in dev and must never touch prod. That one-time backfill should run when there’s legacy data to move and quietly stand down when there isn’t. The wrong answer is a package per environment — that’s the drift you came here to kill. The right answer is one package that adapts per target. SchemaSmith gives you three ways to gate it, at three different sizes. Let’s walk all three.

Three granularities

Conditional deployment isn’t one feature; it’s a family, sized to what you’re gating:

An object — a single column, index, foreign key, check constraint, or whole table — gated with a ShouldApplyExpression.
A whole folder of scripts — gated with a ShouldApplyExpression on its ScriptFolders entry.
An individual script that can only decide once it’s running — gated by raising a runtime sentinel.

The lab ships one product, OrderService, with an Orders table, a TestData folder, and a backfill migration — and deploys it two ways: dev.settings.json and prod.settings.json, which differ only by overriding the {{Environment}} token (the script-token trick from Module 4). Watch each gate make a different call per environment.

Tier 1: gate one object

Any component can carry a ShouldApplyExpression — a boolean SQL predicate, evaluated per target. True, it deploys; false, it’s skipped. The lab puts a region-analytics index on Orders that’s only worth building in production:

{ "Name": "IX_Orders_Region", "IndexColumns": "Region",
  "ShouldApplyExpression": "'{{Environment}}' = 'Production'" }

Deploy to dev and the index isn’t there; deploy to prod and it is:

# dev:  (no IX_Orders_Region)
# prod: Creating index [dbo].[Orders].[IX_Orders_Region]

One index definition, gated by a predicate. The same hook gates a column that only exists on newer engines (SERVERPROPERTY/@@version), a constraint that only applies to one edition, or a whole table. It’s a boolean predicate — a = b, EXISTS(...) — not a SELECT.

Tier 2: gate a whole folder

Sometimes it’s not one object — it’s an entire folder of scripts that belongs in some environments and not others. Test data is the classic case: you want it seeded in dev and absolutely not in prod. Gate the folder itself, in Template.json:

"ScriptFolders": [
  { "FolderPath": "After Scripts", "QuenchSlot": "After" },
  { "FolderPath": "TestData", "QuenchSlot": "After",
    "ShouldApplyExpression": "SELECT CASE WHEN '{{Environment}}' = 'Development' THEN 1 ELSE 0 END" }
]

The TestData folder (which seeds three sample orders) is gated to Development. Deploy to dev and it runs; deploy to prod and the whole folder is skipped — logged, not silently dropped:

# dev:  (TestData runs — Orders gets 3 sample rows)
# prod: Skipping folder 'TestData' — ShouldApplyExpression evaluated false

Two things to know. The folder gate is a scalar SELECT (the object-level one was a bare predicate — different shapes for different layers). And once you declare a ScriptFolders array, it replaces the default folder set entirely — so the lab lists After Scripts explicitly too, otherwise the backfill migration in it wouldn’t load. (A gated-off folder’s run-once migrations aren’t pruned, either — flip the gate back on later and they’re still tracked.)

Tier 3: let the script decide

Some decisions can’t be written as an expression in the package at all — they depend on what the script finds when it runs. Is there actually legacy data to back-fill? Has a prior step already run? For those, the script decides at runtime and raises the sentinel — the exact message SCHEMASMITH: SHOULD NOT APPLY — and SchemaQuench treats it as an intentional skip, not a failure:

-- SQL Server: a one-time backfill that stands down on a fresh install
IF OBJECT_ID('dbo.LegacyOrders', 'U') IS NULL
BEGIN
    RAISERROR('SCHEMASMITH: SHOULD NOT APPLY', 16, 1);
    RETURN;
END;
-- ...real backfill would run here when LegacyOrders exists

Deploy it and the run succeeds with the script logged as skipped:

Skipped (ShouldNotApply): ...After Scripts\Backfill Legacy Regions.sql

Because it’s a run-once migration, that skip is recorded as completed — it won’t be retried. Two cautions worth their weight: on SQL Server the RAISERROR must be severity ≥ 11 (16 is conventional) or SchemaQuench never sees it, and put a RETURN after it so the batch doesn’t fall through into the work you meant to skip.

Which gate, when

The three aren’t competing — they nest, and the rule of thumb is simple:

One object, decision is a SQL expression → object ShouldApplyExpression.
A whole folder of scripts → folder ShouldApplyExpression.
The decision needs the script to look around first (row counts, prior-run artifacts, branching) → the runtime sentinel.

Reach for the smallest one that fits. Most gating is a one-line predicate on an object; the folder gate is for whole slices of a package; the sentinel is the escape hatch for “I can’t know until I’m running.”

Per-engine notes

All three tiers work on all three engines; the shapes differ in the expected ways:

	SQL Server	PostgreSQL	MySQL
Object predicate	`'{{Environment}}' = 'Production'`	same	same
Folder gate (scalar SELECT)	`SELECT CASE WHEN … THEN 1 ELSE 0 END`	same	same
Sentinel raise	`RAISERROR('SCHEMASMITH: SHOULD NOT APPLY', 16, 1)` (sev ≥ 11)	`RAISE EXCEPTION 'SCHEMASMITH: SHOULD NOT APPLY'`	`SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'SCHEMASMITH: SHOULD NOT APPLY'`
Runtime branching	inline in the batch	a `DO $$ … $$` block	inside a short stored routine (MySQL can’t branch in a plain batch)

The mechanism is identical; only the dialect’s way of writing a predicate, a scalar, and a conditional raise changes.

Check yourself: You need to (a) skip one analytics index on dev, (b) keep a whole TestData folder out of production, and (c) skip a backfill migration only when there's no legacy data to move. Which conditional-deployment tier fits each?

(a) Object-level ShouldApplyExpression — a boolean predicate on the index, gating that one object per target. (b) Folder-level ShouldApplyExpression — on the TestData folder’s ScriptFolders entry, so the whole folder is skipped where it returns false. (c) The runtime sentinel — the backfill script checks for the legacy table and raises SCHEMASMITH: SHOULD NOT APPLY when there’s nothing to do, because that decision can only be made while the script runs.

Think of it as three sizes of damper on the forge. You can hold back a single spark, bank a whole section of the fire, or let the smith feel the heat and decide in the moment not to strike. One package, shaped to every target it lands on — the analytics index where it earns its keep, the test data only where it belongs, the backfill only when there’s something to move. No package-per-environment, no drift; just the right thing in the right place.

Want the full mechanics — match rules, slot coverage, the folder-gate pruning guarantee? The ShouldApplyExpression and Conditional Deployment section of the SchemaQuench reference and the Power Workflows guide cover all three tiers. Run the lab on all three engines to watch one package make different calls in dev and prod.

Got a target that needs to differ from the rest — an edition gap, an environment rule, a migration that shouldn’t always run? Email me at forgebarrett@schemasmith.com — I read every one.

Next up: Course 2 · Module 4 — Script tokens, where that {{Environment}} value you’ve been overriding gets the full treatment — one package, every environment, values resolved per target.

Until then, may your gates open for the right targets and hold fast against the wrong ones.

— Forge

Mark this lesson complete