Write SQL migration scripts inside your schema package's slot folders. SchemaQuench executes them in order and tracks what's already run so scripts never repeat.
By the SchemaSmith Team · Last reviewed
SchemaQuench remembers what it has already run, so you never have to worry about a migration script executing twice.
A migration script is a plain .sql file that lives inside one of the script-folder slots in your schema package. SchemaSmith doesn't parse or transform the contents — whatever SQL you write, SchemaQuench executes against the target database when that slot runs. This is where hand-written SQL earns its place in an otherwise declarative workflow: one-time data fixes, backfills, grants, archive operations, anything the declarative table definitions can't express on their own.
Every platform supports migration scripts the same way: same slot semantics, same tracking mechanism, same file-naming rules. Only the SQL dialect inside each script is platform-specific — you write SQL Server T-SQL in a SQL Server package, PostgreSQL in a PostgreSQL package, MySQL in a MySQL package.
Migration scripts are part of the schema package format. For details on product- and template-level configuration, see schema packages. For how SchemaQuench executes the full deployment lifecycle, see SchemaQuench.
Migration scripts live in one of four slots, ordered by when they run inside the deployment lifecycle. Each slot's scripts run in alphabetical order, and each script's completion is recorded so it won't run again on subsequent quenches.
One-time migration scripts that run after initial object creation and new table creation, but before table modifications. Use for data preparation that must happen before columns are altered or dropped.
Migration scripts that need the table structure to exist but must run before foreign key constraints are enforced. Typical use: populating a new NOT NULL column before FKs block the data load.
Migration scripts that depend on the final table and key structure but must run before triggers are deployed.
Final migration scripts. Run after all database objects and data are deployed.
Migration scripts are tracked in the SchemaSmith.CompletedMigrationScripts table inside your target database. On each quench run, SchemaQuench checks which scripts in each slot have already been recorded. Scripts that appear in the tracking table are skipped. Scripts that don't appear are executed, and on success a tracking entry is inserted.
| Column | Description |
|---|---|
ProductName | The product name from Product.json. |
QuenchSlot | The slot the script belongs to. |
ScriptPath | The relative path of the script within the template. |
QuenchDate | Timestamp when the script was executed. |
Scripts with [ALWAYS] in the filename (before the .sql extension) run on every quench regardless of tracking:
001_SeedReferenceData [ALWAYS].sql
002_RefreshPermissions [ALWAYS].sql[ALWAYS] scripts are never recorded in the tracking table.
To force a tracked script to run again, either delete the corresponding row from SchemaSmith.CompletedMigrationScripts in the target database, or rename the script file (tracking is by path, so a renamed script is treated as new).
Tracking is recorded per target database — and per schema for schema templates. The same migration script runs once per database, and once per tenant schema, each tracked independently: a completed run (or a skip) in one database or schema never affects another. A product fanned out across dozens of tenant schemas records each tenant's migrations under its own (template, schema) scope — for example (TenantWorkspace, tenant_acme) — so re-running the deployment re-checks every iteration on its own, and no tenant re-runs a migration another tenant already applied.
A migration script that raises the runtime skip sentinel (SCHEMASMITH: SHOULD NOT APPLY) is recorded as completed just like one that ran normally — it won't be retried on the next deployment. The decision is per database and per schema, so a database with different state re-evaluates the script independently. See Runtime sentinel skip for how a script decides at runtime that it shouldn't apply.
When SchemaQuench processes a slot, it compares the tracking entries against the scripts currently in the package and removes entries for scripts that no longer exist — governed by PruneObsoleteMigrationTracking (default true). When Target filters (Target:Templates, Target:Databases, or Target:Schemas) are active, pruning is restricted to the executed (template, schema) scope: it never deletes tracking rows for databases or schemas you excluded from the run, so a partial deployment can't erase the record of migrations applied elsewhere. Pruning is skipped entirely when TrackRunOnceMigrations is false.
Migration scripts within each slot execute in alphabetical order by filename. Use numeric prefixes to control execution order:
001_CreateStagingTable.sql
002_MigrateData.sql
003_DropStagingTable.sqlScripts within a slot run in alphabetical order by filename. Use 001_, 002_, 003_ prefixes so a future name change doesn't silently re-sort the execution sequence.
Reserve the [ALWAYS] suffix for scripts that are safe to rerun every deployment — seed-data refreshes, permissions grants, lookup-table upserts. Never use it for DDL or one-time migrations; those must be tracked so they run exactly once.
Even tracked scripts should guard their own work. Check for the target state before making changes (IF NOT EXISTS / IF EXISTS) so a partial-failure rerun or manual re-execution doesn't double-apply the change.
Tracking is keyed on the script's relative path. If you need a tracked script to run again on all environments, rename it (for example, bump the numeric prefix to 002b_). The rename registers as a new script on every target database.
Write a one-time migration script in the Before slot, watch SchemaSmith track it, and see it skip on the next quench.
Try hand-rolled scripts