Generate schema from metadata

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, MySQL, and MariaDB; only the JSON-shredding and dynamic-execution dialect differ.

The same lever, a view’s turn

<*SpecificTable*> isn’t the only tag that hands you a model. Indexed views on SQL Server and materialized views on PostgreSQL have their own — <*SpecificIndexedView*> and <*SpecificMaterializedView*>. Same move, the token holds the object’s full JSON. But a view’s model isn’t shaped like a table’s: there’s no Columns array to mirror. What you get is the view’s Definition and its declared Indexes. So the script you drive off it reads the index model — and that difference is the whole point, because it plays out differently on each engine.

PostgreSQL — the model picks the refresh mode. A materialized view has to be refreshed to pick up new rows, and REFRESH … CONCURRENTLY — the one that doesn’t lock readers out — only works if the view declares a unique index. So let the model decide:

-- v_json holds the view's model, via <*SpecificMaterializedView*>public.vproductsummary
SELECT EXISTS (
  SELECT 1 FROM jsonb_array_elements(v_json->'Indexes') AS ix
  WHERE (ix->>'Unique')::boolean IS TRUE
) INTO v_concurrent;

IF v_concurrent THEN
  EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I.%I', v_schema, v_name);
ELSE
  EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', v_schema, v_name);
END IF;

Declare a unique index on the view and the refresh goes concurrent — no reader gets locked out. Drop it, and the script falls back to a plain refresh on its own. You never edit the refresh script; the declared index model decides.

SQL Server — read the model for governance. SQL Server keeps indexed views maintained for you, so there’s nothing to rebuild or refresh at deploy time. The model’s still worth reading, though. An indexed view carries its declared Indexes — the unique clustered one that materializes it, plus any nonclustered indexes you add — so read that list and keep a governance inventory in sync with exactly what you declared:

-- @json holds the view's model, via <*SpecificIndexedView*>dbo.vProductSummary
INSERT INTO dbo.IndexedViewInventory (ViewName, IndexName, IsUnique, IsClustered, KeyColumns)
SELECT @view, REPLACE(REPLACE(ix.[Name],'[',''),']',''),
       ISNULL(ix.[Unique],0), ISNULL(ix.[Clustered],0), ISNULL(ix.[IndexColumns],'')
FROM OPENJSON(@json, '$.Indexes')
     WITH ([Name] NVARCHAR(128) '$.Name', [Unique] BIT '$.Unique',
           [Clustered] BIT '$.Clustered', [IndexColumns] NVARCHAR(400) '$.IndexColumns') ix;

Add another index to the view’s declaration, re-quench, and the inventory picks it up. That’s the describe side of the same lever — you don’t always generate DDL from a model; sometimes you just keep an honest record of what’s declared, always in sync with the source of truth.

And MySQL? No indexed or materialized view type, so there’s no view-model tag to read — nothing to introspect, nothing to drift. The snapshot table from earlier still works the same; the view lever just doesn’t apply there.

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