Trust the round trip

You inherited a database nobody ever wrote a package for. So you extract it, commit the files, and call that the source of truth.

That’s a claim. It says this package is your database. And most folks never check it, because the extraction ran clean and the files look plausible.

Here’s the thing about a claim you never test: when it’s wrong, it’s wrong quietly. A column comes back one bit wide. A length limit disappears. A diff shows up every time you extract, whether anything changed or not, until the team learns to stop reading diffs. No error. No warning. Just a package that’s a little less true than it looks.

This recipe checks the claim the only way that counts. Extract, rebuild, compare, re-extract. It needs SchemaSmith 2.7.0 or later, because that’s where the four holes below got closed:

What extraction used to do	What it cost
PostgreSQL `bit(8)` extracted as bare `bit`	the package rebuilt the column as `bit(1)` — every value truncated to one bit, with no diff to review
PostgreSQL `bit varying(16)` extracted as unlimited `varbit`	a silent widening
MySQL/MariaDB indexes, foreign keys and checks extracted in query-plan order	the same table came out differently on two runs
Every engine: the first re-extraction into an existing package	rewrote every table file with empty keys it never had

The database you inherited

The lab builds it by hand, the way yours was. PostgreSQL gets a device table with two bit columns:

CREATE TABLE public.device (
  device_id integer NOT NULL PRIMARY KEY,
  flags     bit(8) NOT NULL,
  mask      bit varying(16),
  label     text
);

MySQL and MariaDB get customer and order_line, with the indexes, foreign keys and checks created deliberately out of name order:

  KEY ix_zeta (qty),
  KEY ix_alpha (price),
  KEY ix_mid (customer_id),
  CONSTRAINT fk_zeta FOREIGN KEY (customer_id) REFERENCES customer (customer_id),
  CONSTRAINT fk_alpha FOREIGN KEY (alt_customer_id) REFERENCES customer (customer_id),
  CONSTRAINT ck_zeta CHECK (qty > 0),
  CONSTRAINT ck_alpha CHECK (price >= 0)

Then cast it to disk. One command per engine:

for e in postgres mysql mariadb; do (cd $e && schematongs --ConfigFile:SchemaTongs.settings.json); done

Lengths that survive the trip

Open PostgreSQL’s extracted/Templates/Main/Tables/device.json and find the two bit columns:

{ "Name": "flags", "DataType": "bit(8)", ... },
{ "Name": "mask",  "DataType": "varbit(16)", "Nullable": true, ... }

Both lengths are there. That’s the whole fix, and it’s worth being clear about why the old shape was so dangerous. Bare bit in PostgreSQL doesn’t mean “a bit string of whatever length.” It means bit(1). So a package that said bit was telling the engine to build a one-bit column, and it did. Eight bits of every flag value became one, and because the package agreed with the rebuilt column, there was nothing to diff. The mask went the other way: varbit with no length is unlimited, so a column that capped at sixteen bits quietly stopped capping.

One truncation, one widening, zero noise. That’s the worst kind of wrong.

Rebuild, and let the engine testify

Reading the JSON is a start, but the proof is what the package actually builds. Throw the hand-built tables away and quench from the package:

../lab-sql.sh postgres cookbook_r16 "DROP TABLE public.device"
for e in mysql mariadb; do ../lab-sql.sh $e cookbook_r16 "DROP TABLE order_line; DROP TABLE customer"; done
for e in postgres mysql mariadb; do (cd $e && schemaquench --ConfigFile:deploy.settings.json); done

Now ask PostgreSQL what it built:

../lab-sql.sh postgres cookbook_r16 "SELECT attname, format_type(atttypid, atttypmod) FROM pg_attribute WHERE attrelid = 'public.device'::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum"
# → device_id|integer
# → flags|bit(8)
# → label|text
# → mask|bit varying(16)

bit(8), not bit(1). Deploy once more with --report:./rerun and rerun.md reports zero changes on all three engines. The package describes the database exactly.

Sets have no order worth keeping

Now open MySQL’s extracted/Templates/Main/Tables/order_line.json and read the three lists:

Indexes:           fk_alpha, ix_alpha, ix_mid, ix_zeta, PRIMARY
ForeignKeys:       fk_alpha, fk_zeta
CheckConstraints:  ck_alpha, ck_zeta

Name order, whatever order they were created in, and MariaDB’s file matches MySQL’s line for line. (fk_alpha in the index list is real: MySQL creates an index for a foreign key whose column has none.)

Why name order and not creation order? Because these lists are sets. Nothing about the table depends on which index comes first. Extraction used to emit them in whatever order the catalog query happened to return, and that order could shift between two runs against the same table. Same table, different file, a diff about nothing. When the sequence doesn’t mean anything, the only sequence worth having is one that never changes.

Columns are a different question, and they keep their own rule: Product:ObjectOrder still decides the column sequence.

Re-extract, and expect nothing

Here’s the everyday loop. Someone changes the database, you extract again into the same package, and you commit the diff. That only works if an unchanged database produces no diff:

for e in postgres mysql mariadb; do (cd $e && cp -r extracted before && schematongs --ConfigFile:SchemaTongs.settings.json && diff -r before extracted && echo "$e: identical"); done
# → postgres: identical
# → mysql: identical
# → mariadb: identical

Re-extraction carries forward what only the package knows — a ShouldApplyExpression, a VariantName, a data delivery — from the file it replaces. It used to carry forward keys the file never had, too, as empty strings. So the first re-extraction of every package rewrote every table file with no schema change behind it. Now a real change is the only thing that shows up in the diff.

Check yourself: A teammate extracts a PostgreSQL database, commits the package, and quenches it to a fresh environment. Every deploy reports success, and a re-extraction of the new environment shows no column changes. Months later someone notices a flag column only ever holds 0 or 1. How could every check have passed, and which step of the round trip would have caught it?

The package itself was wrong, and everything downstream agreed with it. If the column came out of extraction as bare bit, PostgreSQL reads that as bit(1). The deploy built exactly what the package said, re-extracting the new environment read back the same bare bit, and so the package, the new database and every diff lined up perfectly. Consistent isn’t the same as correct.

The step that catches it is compare: rebuild from the package, then check the rebuilt types against the database you started from. Querying format_type(atttypid, atttypmod) shows bit(8) on the original and bit(1) on the rebuild. That’s the difference a round trip exists to surface, and it’s why the check has to go back to the source, not just forward from the package.

Every other recipe in this cookbook assumes the package is right. This one asks the question underneath all of them: is the package telling the truth? A cast is only as good as its fidelity to the metal. If it loses an edge, gains one, or comes out a different shape every time you pour it, you can’t forge anything trustworthy from it.

So don’t take the first extraction on faith. Extract, rebuild, compare, re-extract. When all four come back exact, the package really is the source of truth, and a diff means something changed.

Got an extraction that didn’t give back what you had? Email me at forgebarrett@schemasmith.com — I’d like to see what went missing. More’s coming from the forge.

Until then, may your casts come back true to the metal, and every diff you read mean something changed.

— Forge

Mark this lesson complete