Course 4 · Recipe 5 — Scripts that write scripts

You want an object that’s derived from a table — a snapshot table that mirrors it, an audit shadow, a history copy. So you hand-write it: declare a second table with the same columns, and now you’ve got two definitions to keep in lockstep. Add a column to the source and forget the copy, and they drift. The copy isn’t really its own thing — it’s a function of the source. So let the schema compute it.

The lever: read the model, emit the DDL

Back in Course 2 you read the whole model as JSON to build a catalog — rows that describe your schema. This recipe goes the other way: it reads the model and emits schema — CREATE TABLE, ALTER TABLE, INSERT — generated from the columns it finds.

A token holds one table’s model as JSON:

"ScriptTokens": { "ProductTable": "<*SpecificTable*>dbo.Product" }

A deploy script walks that JSON and builds DDL from the column list. Here’s the SQL Server version, in full — shred the model’s columns into a [Name] DataType NULL, … string, then run a CREATE TABLE built from it:

DECLARE @json NVARCHAR(MAX) = N'{{ProductTable}}';

-- shred the model's column list straight out of the JSON
DECLARE @colDefs NVARCHAR(MAX);
SELECT @colDefs = STRING_AGG('[' + [Name] + '] ' + [DataType] + ' NULL', ', ')
  FROM OPENJSON(@json, '$.Columns')
       WITH ([Name] NVARCHAR(128) '$.Name', [DataType] NVARCHAR(128) '$.DataType');

-- build the mirror table from exactly what we shredded
EXEC('CREATE TABLE dbo.ProductSnapshot (SnapshotAt DATETIME2(7) DEFAULT SYSUTCDATETIME(), ' + @colDefs + ')');

The shred is the one line that changes per engine — the JSON function differs, the pattern doesn’t:

Engine	Shred the columns with
SQL Server	`OPENJSON(@json, '$.Columns') WITH ([Name] … '$.Name', [DataType] … '$.DataType')` + `STRING_AGG`
PostgreSQL	`jsonb_array_elements(v_json->'Columns')` + `string_agg(format('%I %s', col, dtype), ', ')`
MySQL	`JSON_TABLE(@json, '$.Columns[*]' COLUMNS(col … PATH '$.Name', dtype … PATH '$.DataType'))` + `GROUP_CONCAT`

The script generates the mirror table, adds any new columns the model has gained (an ALTER TABLE … ADD built the same way), and snapshots the current rows — all from the declared columns, never hand-typed. The full script for each engine is in the lab’s After Scripts/ folder.

The recipe: the mirror follows the model

Deploy, and ProductSnapshot exists with Product’s columns. Put rows in Product, re-quench, and they’re captured. Now add Sku to Product’s JSON and re-quench:

ProductSnapshot columns → SnapshotAt, ProductId, Name, Price, Sku

You never touched ProductSnapshot’s definition — it doesn’t have one. It’s generated from Product on every deploy, so the moment Product grows a column, the snapshot does too, and the next snapshot includes it. Same behavior on SQL Server, PostgreSQL, and MySQL; only the JSON-shredding and dynamic-execution dialect differ.

The aha: derived objects shouldn’t be hand-kept

This is the difference between describing your schema and generating from it. The catalog in Course 2 recorded facts about the model. Here the model drives the creation of another object — and because that object is computed from the one declared source of truth, it can’t drift. You stop maintaining a parallel definition and start maintaining one schema, with the rest derived. Combine it with custom properties — gate which tables get a mirror on an Extensions tag — and one generator script can maintain shadow tables for an entire schema. The Script Tokens reference covers {{TableSchema}} (the whole template) and <*SpecificTable*> (one table); your Extensions ride inside both.

Check yourself: How can a deploy-time script generate the right DDL for every table without you hand-writing it per table?

It reads the declared model as JSON — {{TableSchema}} for the whole template or <*SpecificTable*> for one table — walks the columns, and emits DDL from them. Because it’s computed from the one declared source of truth, the generated object can never drift: change the schema, re-quench, and the generated DDL follows.

Think of a jig that builds another jig. You cut the master once — the table you actually care about — and a second tool takes its measurements and shapes itself to match, every time you fire the forge. You don’t measure twice, you don’t keep two jigs trued to each other by hand. The master changes, and the tool that reads it re-shapes on the next heat. The schema is the master; the generator reads it and forges the rest.

Got an object you’re keeping in sync with a table by hand — a history table, an audit shadow, a reporting mirror? Email me at forgebarrett@schemasmith.com — I read every one.

Next up: Course 4 · Recipe 6 — Surviving a destructive rebuild, where the recyclebin hooks carry a table’s data safely through a drop and recreate.

Until then, may the work you declare write the rest for you, and nothing you forge ever drift from its source.

— Forge

Mark this lesson complete