Let the engine adapt

🧪

Hands-on lab

Author a modern feature per engine — a PostgreSQL 14 expression statistic, a MySQL 8.0.16 CHECK, MariaDB descending and invisible indexes — deploy them to floor-version targets that can't take them, and read the downgrade manifest. Then flip one setting to fail and watch the same package refuse.

Demos/Learn/course10-module-01 →

You’ve got a package that declares a feature the newest engine loves. Now it has to land on the tenant three versions back — the one that’s never heard of that feature. Do you fork the package? Keep a second copy? Strip the feature out and hope nobody on the new engine misses it?

None of the above.

Hey folks. I’m Forge Barrett, master of the Content Forge here at SchemaSmith.

Last module we stood up the mixed fleet and got two numbers straight — the floor you declare and the version the engine reports. Today we put that reported version to work. You author the modern thing once, deploy the same package to every tier, and SchemaSmith reads each target and emits exactly what that target can take. Where the old engine can’t take a feature at all, you get one setting that decides what happens: degrade and write it down, or refuse outright.

This is answer number one from the arc — the engine adapts for you. Let’s kindle the forge and watch it work.

The one knob

Everything in this module hangs off a single setting in your Target block:

"Target": {
  "UnsupportedFeaturePolicy": "warn"
}

Two values, and only two:

• warn — the default. SchemaSmith emits the object without the unsupported aspect, and records one downgraded row per affected object. The deploy succeeds; you get a receipt.
• fail — SchemaSmith aborts the run with a message naming the feature and the version it needs, before it deploys a silently-weakened schema.

That’s the whole decision. The degrade itself is automatic — it’s driven by the version the target reports, checked against the floor each feature was introduced at. The policy only chooses what to do about it: take the degraded form and log it, or stop the line.

One more thing before the demos, because it’s the load-bearing idea of the whole course: this only touches the DDL SchemaSmith itself generates. It reads your declared table, sees an expression statistic the target can’t build, and adapts its own emitted SQL. It will never reach into a script you wrote and rewrite it. That’s Module 2’s job, and the boundary between the two is the point.

PostgreSQL — the degrade you can flip

Here’s the feature. A statistic on an expression — CREATE STATISTICS over ("Id" / 1) — which PostgreSQL didn’t support until version 14:

"Statistics": [
  { "Name": "ST_metric_expr", "StatisticsColumns": "(\"Id\" / 1)" }
]

The parentheses are the tell — that’s what marks it an expression statistic rather than a plain multi-column one. Our floor target is PostgreSQL 12, on port 15433. Twelve is below fourteen, so this can’t be built there.

Deploy it with the default warn policy:

cd postgres
schemaquench --ConfigFile:quench.settings.warn.json --LogPath:"$PWD/logs"

The metric table lands fine. The statistic can’t — so SchemaSmith skips it and writes a downgraded row. Watch the tail of the run and the deployment summary:

[localhost].[learn]   Quenching indexes and constraints
[localhost].[learn]       Add Missing Statistics
[localhost].[learn] Successfully Quenched

# The receipt — the "Unsupported Feature Downgrades" section of SchemaQuench - Summary.md:
## Unsupported Feature Downgrades
- 1 feature(s) declared but unsupported at the target version — emitted without the unsupported aspect:
  - expression statistics (PG14): public.metric.ST_metric_expr

That summary section is your receipt. Nothing was hidden — the run tells you exactly what it relaxed and why. Deploy the same package to PostgreSQL 14 or newer and the statistic gets built for real. Same JSON, target decides.

Now change your mind. You’d rather not deploy a schema that’s quietly missing a statistic — you want to know before anything changes. Flip the knob:

schemaquench --ConfigFile:quench.settings.fail.json --LogPath:"$PWD/logs"

Same package, same server. This time it refuses:

[localhost].[learn] FAILED to quench:
P0001: Expression statistics require PostgreSQL 14 (detected major 12); statistic(s): public.metric.ST_metric_expr
[localhost].[learn] *** FAILED [Template:Main] ***
One or more database quenches FAILED

# And the exit code CI will see:
$ echo $?
2

That’s the knob. warn deploys and logs; fail stops the line. One setting, two postures, your call.

MySQL — a CHECK the floor can’t keep

Same mechanism, different feature. MySQL 5.7 parses a CHECK constraint and then silently ignores it — it wasn’t really enforced until 8.0.16. So SchemaSmith treats it as unsupported at the 5.7 floor: it can neither build it nor read it back, which means leaving it in would make every deploy think the check is still missing.

Here’s the declared check on the Ledger table:

"CheckConstraints": [
  { "Name": "CK_Ledger_Amount", "Expression": "`Amount` >= 0" }
]

Deploy to MySQL 5.7 on port 13316:

cd mysql
schemaquench --ConfigFile:quench.settings.json --LogPath:"$PWD/logs"

The table deploys; the check degrades and gets logged:

[localhost].[learn] Successfully Quenched

# SchemaQuench - Summary.md:
## Unsupported Feature Downgrades
- 1 feature(s) declared but unsupported at the target version — emitted without the unsupported aspect:
  - CHECK constraint (MySQL 8.0.16): Ledger.CK_Ledger_Amount

Want the refusal instead? You don’t need a second settings file — flip the policy for one run with an environment override:

SmithySettings_Target__UnsupportedFeaturePolicy=fail schemaquench --ConfigFile:quench.settings.json --LogPath:"$PWD/logs"

Same story as PostgreSQL: it aborts naming the feature and the version — MySQL 8.0.16 — instead of deploying the table without its guard.

MariaDB — two downgrades in one deploy

MariaDB 10.2 is our oldest MySQL-family floor, and the Ledger package declares two things it’s below. A descending index key part (MariaDB stored those ascending until 10.8) and an invisible index (the IGNORED keyword didn’t arrive until 10.6):

"Indexes": [
  { "Name": "IX_Ledger_Amount_Desc", "IndexColumns": "`Amount` DESC" },
  { "Name": "IX_Ledger_Region_Hidden", "IndexColumns": "`Region`", "Visible": false }
]

Deploy to MariaDB 10.2 on port 13317:

cd mariadb
schemaquench --ConfigFile:quench.settings.json --LogPath:"$PWD/logs"

Both indexes still get created — the descending one stored ascending (which the engine does silently anyway), the hidden one created visible. And you get two downgraded rows, one per feature:

[localhost].[learn] Successfully Quenched

# SchemaQuench - Summary.md:
## Unsupported Feature Downgrades
- 2 feature(s) declared but unsupported at the target version — emitted without the unsupported aspect:
  - INDEX (invisible, MySQL 8.0 / MariaDB 10.6): Ledger.IX_Ledger_Region_Hidden
  - INDEX (descending key part, MySQL 8.0 / MariaDB 10.8): Ledger.IX_Ledger_Amount_Desc

The index still functions — it just can’t carry the ordering or the visibility hint on this engine. And the summary names both, so nothing slips by unmarked.

SQL Server — the honest contrast

Now the twist, and it’s the one that catches nearly everyone. SQL Server plays this differently, and if you assume it works like the others you’ll get it exactly backwards.

We deploy a would-degrade feature — dynamic data masking, a SQL Server 2016 feature — to learn_2008, a database sitting at compatibility level 100:

"Columns": [
  { "Name": "Email", "DataType": "NVARCHAR(256)", "Nullable": true, "DataMaskFunction": "email()" }
]

cd sqlserver
schemaquench --ConfigFile:quench.settings.json --LogPath:"$PWD/logs"

On PostgreSQL, MySQL, and MariaDB an old target meant a degrade. Here? Nothing degrades. The mask is applied at full fidelity, and there’s no Unsupported Feature Downgrades section at all:

[localhost].[learn_2008] Successfully Quenched

# SchemaQuench - Summary.md has NO "Unsupported Feature Downgrades" section — nothing degraded.

# Proof the 2016 mask really landed, on a compatibility-level-100 database:
1> SELECT c.name, mc.masking_function
2> FROM sys.masked_columns mc
3> JOIN sys.columns c ON c.object_id = mc.object_id AND c.column_id = mc.column_id
4> WHERE mc.object_id = OBJECT_ID('dbo.Account');

name   masking_function
------ ----------------
Email  email()

Here’s why, and it’s the whole reason this section exists. SQL Server gates feature support on the server binary, not the database’s compatibility level. Our sandbox runs one 2022 binary. learn_2008 is just a database on it turned down to compatibility level 100. Data masking shipped in the 2016 binary — and this binary is 2022 — so the feature is fully available no matter what compatibility level the database wears.

The compatibility level does change one thing: how SchemaSmith encodes its table-model payload on the wire — JSON at compatibility level 130 and above, XML below it. That’s an encoding shift, not a feature loss, and it’s Module 4’s whole topic. It is not the same test as “which features can this target build.”

Sit with that gap for a second, because Module 2 turns it into a footgun. On SQL Server:

• Gate a feature on the server version. Masking, temporal, columnstore — those track the binary.
• Gate syntax on the compatibility level. A 2022 binary hosting a compat-100 database will still parse-error on GENERATE_SERIES — a function gated by compatibility level, not the binary — because that kind of syntax is a compatibility-level test.

Get those two crossed and you’ll swear the tool is broken when it isn’t. We’ll take that apart properly next module.

What just happened

Step back and look at what you didn’t do. You didn’t fork the package. You didn’t keep a legacy copy. You didn’t strip the modern feature out to appease the oldest tier. You wrote the schema you actually want, once, and deployed it to a farm spanning years of engine versions — and every target took what it could, named what it couldn’t, and left you a receipt.

That’s answer one. SchemaSmith adapts the DDL it generates to the fire in front of it. Where a feature has no equivalent on an old target, UnsupportedFeaturePolicy is your one knob: warn to degrade and record, fail to refuse. And the downgrade manifest means “degrade” never means “quietly” — every relaxed feature lands in the deployment summary where you can see it.

Subscribe and stick around. Next module we cross the boundary: the engine adapts its own generated DDL, but the scripts you wrote are yours — so you gate them yourself, with ShouldApplyExpression. That’s where the compatibility-level footgun you just met on SQL Server comes home to roost.

Until then, may the engine take what it can and name what it can’t, and may the one knob in your hand always answer the way you meant.

— Forge

Check yourself: You deploy a package declaring a PostgreSQL 14 expression statistic to a PostgreSQL 12 target with the default UnsupportedFeaturePolicy. What happens to the statistic, and how do you find out?

The table deploys, but the expression statistic can’t be built on 12 (it needs 14), so SchemaSmith skips it and records a downgraded row. Under the default warn policy the run succeeds — and the skipped statistic shows up in the deployment summary’s Unsupported Feature Downgrades section, naming the object and the version it required. Nothing is hidden: “degrade” always comes with a receipt. If you’d rather the run refuse than deploy a schema quietly missing the statistic, set UnsupportedFeaturePolicy to fail and it aborts before changing anything, with a message naming the feature and that it requires PostgreSQL 14.

Check yourself: You deploy a table with a data-masked column — a SQL Server 2016 feature — to a database sitting at compatibility level 100 on a SQL Server 2022 instance. Does the mask degrade like an unsupported feature would on PostgreSQL or MySQL?

No. On SQL Server, feature support gates on the server binary, not the database’s compatibility level. The binary here is 2022, data masking shipped in 2016, so the feature is fully available and the mask is applied at full fidelity — there’s no downgrade row at all. The compatibility-100 setting only changes how SchemaSmith encodes its table-model payload on the wire (JSON at level 130 and above, XML below — Module 4’s topic), not which features the target can build. That split — features track the server version, syntax tracks the compatibility level — is exactly the footgun Module 2 turns on: a modern binary hosting an old-compat database will happily deploy a 2016 feature and still parse-error on GENERATE_SERIES, which needs compatibility level 160.

Mark this lesson complete