Declare it, don't script it

You edited the script. The deploy said it succeeded. The database didn’t change. Nothing failed, nothing warned, and the object in production is still the one somebody created two years ago.

That’s what a guarded CREATE does. IF NOT EXISTS, then create. It runs once, does its job, and from then on it skips itself every time, edits and all. It isn’t a declaration. It’s an instruction with an expiry date, and the expiry is silent.

Your tables have never worked that way. You declare a table, change the file, and the next quench makes the database match. This course takes that same deal to everything else you’ve been scripting.

What declaring buys you

A declared object gets the treatment a table gets:

It’s compared. Every deploy reads what’s there and diffs it against the file, so an edit lands instead of being skipped.
It converges. The database ends up matching the declaration, whoever changed it in between.
It drops by absence. Delete its file and the next deploy removes it, so taking an object out is a reviewed change like any other. Module 2 shows a hand-made event left alone while a declared one goes.
It’s stopped or reported by name when it can’t change safely. A change that would break what’s already stored is refused, or reported and left undone, with a message that names the object, instead of quietly going through.

None of that is possible for a script. A script can only be run or not run.

The modules

Each module stands on its own, with its own lab and its own database. Work them in order or go straight to the object you’re scripting today.

Module 1 · Declared, not scripted — stop scripting PostgreSQL domain types, enum types and sequences behind a guarded CREATE, and declare them so every edit actually lands.
Module 2 · Declared scheduled events — the same move for MySQL and MariaDB events, including removing one by deleting its file.
Module 3 · Index-only templates — manage indexes on a vendor or replicated table without touching its columns.
Module 4 · Subfolder organization — keep the folder structure you gave your procedures across every re-extraction.
Module 5 · Expression change detection — prove a re-deploy of an unchanged package changes nothing, on the check constraints and computed columns where that used to be false.
Module 6 · Trust the round trip — extract a database, rebuild it from the package, and re-extract, so you know the package gives back exactly what you had.
Module 7 · Search you declare — full-text indexes declared like any other index on MySQL, MariaDB and SQL Server, and what a server without Full-Text Search does with one.
Module 8 · History the database keeps — SQL Server temporal tables and MariaDB system versioning and application-time periods, as declarations.
Module 9 · History you can’t rewrite — SQL Server ledger tables: what a deploy changes in place, what it refuses, and what a ledger table keeps forever.
Module 10 · Indexes beyond the B-tree — SQL Server spatial, selective XML and ordered columnstore indexes, and PostgreSQL GiST and GIN, declared instead of kept in a script.
Module 11 · The security layer, declared — row-level security, Always Encrypted keys, signed procedures and trigger state, extracted and kept intact by every deploy.

Modules 4 and 6 point the other way, from the database back to the package. Declaring only pays off if extraction gives you back a package you can trust, folders and all.

Where the deep reference lives

Each module links the reference page behind it. The end-user guide’s Defining your schema is the map of what you can declare, and the SchemaQuench reference covers what each engine version supports.

Check yourself: A migration script creates a scheduled event with CREATE EVENT IF NOT EXISTS. Six months later you change its schedule in the script and deploy. What happens, and what would have happened if the event were declared?

The guarded script skips itself because the event already exists, so the new schedule never reaches the database, and the deploy still reports success. A declared event is compared on every deploy, so the changed schedule is found and applied, and deleting the file removes the event on the next deploy.

Think of it this way. A guarded script is a mold you poured once and then locked in a cupboard. Every deploy walks past it, sees the cupboard’s shut, and moves on, no matter how many times you redraw the pattern on the door. A declaration is the pattern on the bench. Every quench presses the metal back to it. Change the pattern and the next piece comes out the new shape. So stop locking molds away. Lay them on the bench. Declare, compare, converge.

Got an object your team still scripts behind a guard, and you’re not sure SchemaSmith can declare it? Email me at forgebarrett@schemasmith.com — I read every one.

Next up: Module 1 · Declared, not scripted, where you watch a guarded CREATE DOMAIN swallow an edit and then make the same change land.

Until then, may every object you own have a file, and every file mean exactly what it says.

— Forge

Mark this lesson complete