Course 2 · Module 5 — Data delivery: ship the rows too

🧪

Hands-on lab

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

Demos/Learn/level2-module-05 →

Your schema’s in source control and deploys itself. But your currency codes, your status lookups, your country list — that reference data is still living in a pile of hand-written INSERT scripts someone runs by memory after the deploy. Miss one and prod’s missing a row; run one twice and you’ve got duplicates or a primary-key error. The schema is versioned and the data isn’t, so the two drift apart — the exact thing you came here to kill, just moved down a layer. Let’s fix that. Let’s make the data ship with the schema.

The data side of the forge

Course 1 gave you two tools: SchemaQuench deploys your schema, SchemaTongs casts an existing schema into files. Data delivery is the third leg — shipping the rows that belong with the schema, not just the structure.

You declare it right on the table. A DataDelivery block points at a content file — a .tabledata JSON array of rows — and tells SchemaQuench how to reconcile them:

"DataDelivery": {
  "ContentFile": "data/dbo.IsoCurrency.tabledata",
  "MergeType": "Insert/Update",
  "MatchColumns": "Code"
}

// data/dbo.IsoCurrency.tabledata
[
{"Code":"USD","CurrencyName":"US Dollar","IsoNumber":840},
{"Code":"EUR","CurrencyName":"Euro","IsoNumber":978},
{"Code":"GBP","CurrencyName":"Pound Sterling","IsoNumber":826},
{"Code":"JPY","CurrencyName":"Yen","IsoNumber":392},
{"Code":"CAD","CurrencyName":"Canadian Dollar","IsoNumber":124}
]

The data/ folder sits at the template root, next to Tables/. MatchColumns is the key SchemaSmith uses to tell “already there” from “new” — here, the currency Code. That’s the whole declaration. The rows now live in the package, in source control, right beside the table they belong to.

Deploy, and the rows ride in

The lab ships an IsoCurrency lookup table with that delivery block. One quench creates the table and delivers its rows:

schemaquench --ConfigFile:deploy.settings.json

[localhost,11433].[learn]         Adding new table [dbo].[IsoCurrency]
[localhost,11433].[learn]   Delivering table data
[localhost,11433].[learn]     Delivering dbo.IsoCurrency
[localhost,11433].[learn] Successfully Quenched

Let’s prove it. Count the rows:

SELECT COUNT(*) FROM dbo.IsoCurrency;   -- 5

Five rows, delivered by the same run that built the table. No separate post-deploy script, no hand-typed INSERTs. And run it again — the count stays five. SchemaSmith delivers data the same way it deploys schema: it MERGEs your declared rows against what’s there, so a row that already matches is a no-op. Idempotent by design.

MergeType: how the rows reconcile

MergeType is the contract for what delivery does to the target:

• Insert — add rows that are missing, leave everything else alone. Seed data you set once and let the app own afterward.
• Insert/Update — add missing rows and update ones whose values changed. The right default for reference data you maintain in the package.
• Insert/Update/Delete — full sync: add, update, and delete target rows that aren’t in your content file. The package becomes the complete source of truth for that table.

Watch the update half work. Edit one row in the content file — change JPY from Yen to Japanese Yen — and re-deploy:

SELECT Code, CurrencyName FROM dbo.IsoCurrency WHERE Code = 'JPY';
-- JPY   Japanese Yen

Still five rows — the JPY row was updated, not duplicated, because MatchColumns matched it on Code. That’s Insert/Update: the four unchanged rows no-op, the one changed row updates. Your reference data is now declarative — edit the file, quench, done.

Where content files come from: DataTongs

You won’t hand-type these files for a real table. DataTongs is the third tool, and it’s the data twin of SchemaTongs: where SchemaTongs casts a schema out of a live database, DataTongs casts data out. Point it at a source table and it writes the .tabledata for you:

datatongs --ConfigFile:tongs.settings.json

Casting data for: dbo.IsoCurrency
  Writing contents to : ./cast\dbo.IsoCurrency.tabledata
  Writing merge script to : ./cast\Populate dbo.IsoCurrency.sql

It produces two things, and the difference matters:

• The .tabledata content file — the rows. This is what you drop into your package’s data/ folder for DataDelivery to ship. SchemaQuench reads this and builds its own MERGE at deploy time.
• A standalone Populate ….sql MERGE script — not used by data delivery. It’s there for when you want to run the merge by hand, hand it to a DBA, drop it into a script folder as an override, or use the data outside SchemaQuench entirely. Data delivery doesn’t touch it; SchemaQuench generates its own merge from the content file.

So the loop closes: DataTongs casts your reference data into a content file, DataDelivery ships it, SchemaQuench MERGEs it in on every deploy. Schema and data, versioned together, deployed together.

Per-engine notes

The declaration is identical on all three engines; only the merge idiom underneath differs:

	SQL Server	PostgreSQL	MySQL
New-table wording	Adding new table [dbo].[IsoCurrency]	Create new table public.isocurrency	Create table `IsoCurrency`
Merge idiom	MERGE + OPENJSON	MERGE + jsonb_to_recordset	INSERT … ON DUPLICATE KEY UPDATE (+ DELETE for full sync)
Identifier case	bracketed, mixed-case	folded to lowercase	backticked

MergeType, MatchColumns, and the .tabledata shape are the same everywhere — SchemaSmith emits the right merge for each engine. PostgreSQL and SQL Server have a native MERGE; MySQL doesn’t, so SchemaSmith uses INSERT … ON DUPLICATE KEY UPDATE and a companion DELETE when you ask for full sync. Same contract, engine-native underneath.

Check yourself: Your schema is versioned and deploys automatically. What ships your lookup and reference rows along with it?

DataTongs — data delivery. You attach a DataDelivery block to the table pointing at a .tabledata content file (which DataTongs can cast out of an existing source), and SchemaQuench MERGEs those rows into the target on every deploy, idempotently, per the MergeType you choose.

---

Think of it as the difference between forging a part and stamping the maker’s marks into it. The schema is the part; the reference data is the set of marks every copy has to carry. You don’t punch them in by hand on each one — you set the die once, and every quench presses the same true rows into place. Edit the die, quench again, and only what changed moves. Schema and data, cast from one source, hardened together.

Want the deeper detail? The DataTongs reference documents every extraction option and the per-engine merge syntax, and the DataDelivery section of the schema-packages reference covers MergeFilter, FK-ordered delivery, and the rest. Run the lab on all three engines to feel the deliver-edit-redeliver loop in your own hands.

Got reference data you’re trying to get under control — a lookup table that drifts between environments, a seed set you copy by hand? Email me at forgebarrett@schemasmith.com — I read every one.

Next up: you’ve shipped schema, fanned it out, gated it, parameterized it, and now delivered its data. The last stop in Course 2 is teaching the tools to read your metadata — custom properties — so the model carries your own intent.

Until then, may your schema and its data ship as one, and every lookup hold the value you meant.

— Forge

Mark this lesson complete