Module 4 — Bring an existing database under management

So far you’ve built a database up from a file. But that’s not where most of us start. You inherit a database. It’s been running for years, nobody wrote down its shape, and the only “source of truth” is the live server. Source control? None. Where do you even begin? The old way: open the database, eyeball every table, and hand-write a CREATE TABLE for each one — and hope you didn’t miss a foreign key somewhere.

Let’s not. SchemaSmith reads the database for you and writes the files itself. That’s casting — the reverse of a quench — and the tool for it is SchemaTongs.

A database nobody modeled

The lab ships a seed.sql that stands up a small music-catalog database — a chinook database with seven related tables: artists, albums, tracks, genres, and the rest, wired together with foreign keys. Plain SQL. No SchemaSmith anywhere. It’s exactly the situation you walk into: a real database, on a real server, that nobody’s ever put in a repo.

Run the seed (the lab README has the one-liner per engine), and you’ve got your “legacy” database. Now let’s bring it under management.

Point the tongs at it

SchemaTongs reads its marching orders from a settings file — where to connect, and where to write the package. The lab’s tongs.settings.json points the Source at the chinook database and sets the output path:

{
  "Source": {
    "Server": "localhost,11433",
    "Database": "chinook",
    "Platform": "SqlServer",
    "...": "user, password, port per engine"
  },
  "Product": { "Path": "./Extracted", "Name": "Chinook" },
  "Template": { "Name": "Chinook" },
  "ShouldCast": { "Tables": true }
}

Then cast:

schematongs --ConfigFile:tongs.settings.json

  Cast Json for dbo.Album
  Cast Json for dbo.Artist
  Cast Json for dbo.Track
  ... (and the rest)

=== Casting Summary ===
  Tables:     7 extracted, 0 errors
Casting Completed Successfully

Seven tables, zero errors, and a whole schema package sitting in ./Extracted that you never typed:

Extracted/
  Product.json
  Templates/Chinook/Tables/dbo.Album.json
  Templates/Chinook/Tables/dbo.Artist.json
  Templates/Chinook/Tables/dbo.Track.json
  ... (one file per table)

Every flag controlling what SchemaTongs casts — views, procedures, functions, and more — is in the SchemaTongs reference.

What it caught

Open the extracted Album table and look at what SchemaTongs worked out on its own:

"ForeignKeys": [
  {
    "Name": "[FK_Album_Artist]",
    "Columns": "[ArtistId]",
    "RelatedTable": "[Artist]",
    "RelatedColumns": "[ArtistId]"
  }
]

It didn’t just list columns. It found the primary key, the index on ArtistId, and the foreign key tying Album back to Artist — the relationships that are the easiest thing to miss when you transcribe a schema by hand. SchemaTongs read them straight out of the live catalog and wrote them down for you. (PostgreSQL and MySQL produce the same package shape with their own type names and identifier quoting — the lab has all three.)

And now you own it. That Extracted package is the same kind of package you built by hand in Module 2 — which means you manage it the same way. Edit a table, run WhatIf, quench. The database that lived only on a server is now a thing in version control, right next to your app code. That’s the whole arc: a database nobody modeled, cast into files in one command.

Check yourself: You have a database nobody has under source control. Which tool gets you started, and what does it produce?

SchemaTongs — it casts the existing database into a declarative schema package (tables, keys, indexes, and more) that you can then manage and deploy with SchemaQuench.

A quench shapes a database from your files. A cast does the reverse — it reads a database and hands you the files. Tables, keys, indexes, all of it, pulled out of a live server and dropped into a package you control. That’s how a legacy database stops being a mystery and starts being source.

Staring down a gnarly old database you need to bring under management? Email me at forgebarrett@schemasmith.com — I read every one.

Next up: Module 5 — Where to go next. We’ll pull the whole first course together and point you at what SchemaSmith can do once you’re past the basics.

Until then, may your tongs grip true and every database give up its shape clean.

— Forge

Mark this lesson complete