Keep your folder structure across re-extraction

You organized the procedures folder once. Two hundred files, sorted into Billing/, Identity/, Reporting/ — an afternoon’s work that made the next code review actually reviewable.

Then someone re-extracted, and got back one directory with two hundred files in alphabetical order.

Nobody organizes it a second time. That’s the real cost, and it isn’t the afternoon — it’s that from then on the team quietly stops running extraction at all, because the tool punishes them for having an opinion about their own repository. Which means drift stops getting caught, onboarding an existing database gets scarier, and the one workflow that keeps the package honest falls out of use.

The layout is the input

SchemaTongs doesn’t flatten, and there’s nothing to configure for it. No manifest, no FolderMapping, no setting — just the files on disk:

Templates/Main/Procedures/
  Billing/
    usp_invoice_total.sql
  Identity/
    usp_customer_lookup.sql

Re-extract, and both come back to their own subfolders. The mechanism is worth knowing because it explains the edge cases: before writing anything, SchemaTongs walks the template folder and builds an index of every .sql, .sqlerror, and .json file, mapping each filename to the full path it currently occupies. When it writes an extracted object, it looks the name up and puts it back where it found it.

The check that matters isn’t a file listing — it’s git status showing nothing. A moved file would appear as a delete plus an add, and a re-extraction that produces an empty diff is one you’ll actually keep running.

New objects go to the root, and that is correct

Extract a procedure the package has never seen and it lands in Procedures/ — the root, not in a subfolder.

SchemaTongs has no way to know that usp_refund_batch is a Billing concern, and a tool that guessed would scatter files into directories you didn’t choose. So new work surfaces in one predictable place where you’ll notice it during review. Move it where it belongs; the index is rebuilt from disk on every run, so filing an object is a one-time act and the next extraction remembers.

That’s the whole contract: you decide the structure, once per object, and the tool stops having an opinion.

The same idea, one level down

Folder layout is not the only thing a re-extraction can churn. Column order inside a table file is the other, and it is governed by two Product-level settings.

PreserveExistingOrder (default true) does for a file’s contents what the folder index does for its location: when SchemaTongs overwrites a table file that already exists, every entry still present keeps the position that file gave it. Dropped entries fall out, genuinely new ones are appended. Turn it off and every extraction re-sequences the whole file from scratch — so a one-column change arrives buried in a whole-file reshuffle, and any ordering someone arranged by hand is lost on every refresh.

ObjectOrder decides the sequence only for what there is nothing to preserve — a brand-new file, or newly appended entries. Name (the default) sorts alphabetically and is stable across engines; Physical uses the table’s own column order, so the package reads the way the table does.

One thing to be clear about, because it is the likeliest misreading: PreserveExistingOrder preserves the order in the file. It does not reorder a deployed table’s columns to match your package — that is a rebuild, not a formatting preference, and it is a separate opt-in you met in Course 8. If you have never re-extracted over an existing file, this setting has changed nothing for you.

The case it can’t resolve

Put the same filename in two subfolders and the index has two candidate homes with no way to choose:

Found dbo.usp_invoice_total.sql in multiple subfolders:
  Procedures\Billing, Procedures\Identity - writing to base folder

A warning, not a failure — exit 0. But look at what’s on disk afterward:

Procedures/Billing/dbo.usp_invoice_total.sql       ← stale
Procedures/Identity/dbo.usp_invoice_total.sql      ← stale
Procedures/dbo.usp_invoice_total.sql               ← freshly extracted

Three copies. The warning tells you what happened; it does not clean up. Both stale files are still real scripts in your package, and a deploy will run all three of them. So that line in the log is work to do, not information — delete the duplicates, keep the one you meant, re-extract to confirm you’re back to two files.

Check yourself: A teammate reports that every re-extraction dumps three specific procedures into the root of Procedures/, even though they keep moving them back into subfolders. Nothing errors. What is the most likely cause, and where would you look to confirm it?

Almost certainly those filenames exist in more than one subfolder — so the index finds multiple candidate homes, refuses to choose, and writes to the root every time. Moving the file back fixes nothing, because the duplicate that caused the ambiguity is still sitting in the other folder.

Confirm it in the extraction log: the “Found file in multiple subfolders: … - writing to base folder” warning names both paths. It is exit 0, so a pipeline that only checks return codes will never surface it, and someone re-filing the files by hand each time would never see the message that explains why. Delete the stale copy in whichever folder is wrong, then re-extract — and check whether those duplicates have been deploying twice all along.

Most of this cookbook has been about making the package drive the database. This recipe is the return trip, and its argument is social rather than technical: a tool that reorganizes your repository every time you run it teaches the team not to run it. Extraction earns its place — onboarding a legacy database, catching drift, refreshing after someone patched production at 2am — only if it leaves your structure alone.

So shape the package the way your team reviews code, not the way an extractor happens to emit files. The structure is yours. SchemaTongs just remembers it.

Got an extraction step your team quietly stopped running? Email me at forgebarrett@schemasmith.com — I’d like to know what it did to your tree. More’s coming from the forge.

Until then, may your repository keep the shape you gave it.

— Forge

Mark this lesson complete