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 module checks the claim the only way that counts. Extract, rebuild, compare, re-extract, on all four engines. Every row below is a hole that used to be there:

What extraction used to do	What it cost
PostgreSQL `bit(8)` extracted as bare `bit` (closed in 2.7.0)	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` (2.7.0)	a silent widening
MySQL/MariaDB indexes, foreign keys and checks extracted in query-plan order (2.7.0)	the same table came out differently on two runs
Every engine: the first re-extraction into an existing package (2.7.0)	rewrote every table file with empty keys it never had
SQL Server column collations extracted as `""` (2.7.2)	a redeploy moved every column with its own collation to the database default
SQL Server `XML(DOCUMENT …)` extracted as `CONTENT` (2.7.2)	the column quietly started accepting fragments
SQL Server alias type `ShortChar` extracted as `SHORTCHAR(10)` (2.8)	the package couldn’t deploy at all

The database you inherited

The lab builds it by hand, the way yours was. SQL Server gets an account table that leans on three things the database carries beside the table:

CREATE TYPE dbo.ShortChar FROM varchar(10) NULL;
CREATE XML SCHEMA COLLECTION dbo.InvoiceSchema AS N'<xs:schema …>';

CREATE TABLE dbo.account (
  account_id  int NOT NULL PRIMARY KEY,
  login_name  varchar(40) COLLATE Latin1_General_CS_AS NOT NULL,
  region_code dbo.ShortChar,
  invoice     xml(DOCUMENT dbo.InvoiceSchema) NULL
);

The database is case-insensitive. login_name isn’t, on purpose: 'Alice' and 'alice' are two different logins. 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 sqlserver postgres mysql mariadb; do (cd $e && schematongs --ConfigFile:SchemaTongs.settings.json); done

What a SQL Server column carries

Open SQL Server’s extracted/Templates/Main/Tables/dbo.account.json:

{ "Name": "[login_name]",  "DataType": "VARCHAR(40)", "Collation": "Latin1_General_CS_AS", ... },
{ "Name": "[region_code]", "DataType": "ShortChar", "Nullable": true, ... },
{ "Name": "[invoice]",     "DataType": "XML(DOCUMENT [dbo].[InvoiceSchema])", "Nullable": true, ... }

Three columns, three things that aren’t really about the column. The collation is a rule about comparing text. The alias type and the schema collection are separate objects, and they come along as scripts: DataTypes/dbo.ShortChar.sql and XMLSchemaCollections/dbo.InvoiceSchema.sql.

Each of these used to slip. Collations came out as "", so a rebuild handed every column the database default. Two logins that differed only in case became the same login, and nothing errored. DOCUMENT came out as CONTENT, so the column started accepting fragments. And an alias type whose name happens to end in CHAR got mistaken for its base type, extracted as SHORTCHAR(10), and SQL Server refused to build it at all. That last one is the honest kind of wrong, at least. It fails loud.

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 sqlserver declared_m6 "DROP TABLE dbo.account; DROP TYPE dbo.ShortChar; DROP XML SCHEMA COLLECTION dbo.InvoiceSchema"
../lab-sql.sh postgres declared_m6 "DROP TABLE public.device"
for e in mysql mariadb; do ../lab-sql.sh $e declared_m6 "DROP TABLE order_line; DROP TABLE customer"; done
for e in sqlserver postgres mysql mariadb; do (cd $e && schemaquench --ConfigFile:deploy.settings.json); done

Ask SQL Server what it built:

../lab-sql.sh sqlserver declared_m6 "SELECT c.name, TYPE_NAME(c.user_type_id), c.collation_name, c.is_xml_document FROM sys.columns c WHERE c.object_id = OBJECT_ID('dbo.account') ORDER BY c.column_id"
# → account_id int NULL 0
# → login_name varchar Latin1_General_CS_AS 0
# → region_code ShortChar SQL_Latin1_General_CP1_CI_AS 0
# → invoice xml NULL 1

Case-sensitive collation, back where it was. The alias type, by name. A document-only XML column. Now ask PostgreSQL what it built:

../lab-sql.sh postgres declared_m6 "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)
# → mask|bit varying(16)
# → label|text

bit(8), not bit(1). And the columns sit in the order the inherited table had them, because extraction wrote them in physical order. Deploy once more with --report:./rerun and rerun.md reports zero changes on all four 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 sqlserver postgres mysql mariadb; do (cd $e && cp -r extracted before && schematongs --ConfigFile:SchemaTongs.settings.json && diff -r before extracted && echo "$e: identical"); done
# → sqlserver: identical
# → 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 module in this course 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