Use "before/after" slots for exceptional changes without breaking state-based automation.
While SchemaQuench can automate much of the updating process, there will always be edge cases that it cannot support in an automated fashion. Changing a column to non-nullable without a default is an example. You need a way to update any null values before changing the schema.
Migration scripts provide a way to make those types of changes both before and after the schema changes are applied. This allows domain-specific logic to control aspects of the update that cannot be automated without intimate knowledge of your system.
Product-level migration scripts run in one of two slots: Before or
After the product is updated.
These scripts would typically be used to manage roles, permissions, extensions, or validate server configurations.
By default, product-level scripts run in the context of the postgres database.
Product-level scripts always run on every execution. There is no table for tracking past usage.
Template-level migration scripts run in the context of each target database.
Scripts live in folders mapped to quench slots via the
script folder configuration.
The default folders are Before Scripts and After Scripts.
Scripts execute in alphabetical order by full file path. Prefix filenames with a date or sequence number to control execution order:
Run after object scripts and query token resolution, before table structure changes.
Run after all table changes, triggers, data delivery, and table data scripts.
By default, a migration script runs once and is recorded in the
"SchemaSmith"."CompletedMigrationScripts" tracking table. Append
[ALWAYS] to the filename (before .sql) to make it
run on every quench. [ALWAYS] scripts are never recorded in the tracking table.
GrantPermissions[ALWAYS].sql
RefreshMaterializedView[ALWAYS].sql
SchemaQuench creates a "SchemaSmith"."CompletedMigrationScripts" table in
each target database during the kindling step. Before executing a non-[ALWAYS]
script, it checks this table. If a matching row exists, the script is skipped.
| Column | Type | Description |
|---|---|---|
ScriptPath | VARCHAR(800) | Relative path from the template root |
ProductName | VARCHAR(100) | Product name from Product.json |
QuenchSlot | VARCHAR(30) | Slot the script ran in |
QuenchDate | TIMESTAMP | Timestamp of last execution |
Delete the tracking row to force a script to run again:
DELETE FROM "SchemaSmith"."CompletedMigrationScripts"
WHERE "ScriptPath" = 'Before Scripts/001_Backfill.sql'
AND "ProductName" = 'MyProduct';When a migration script file is removed from the schema package, SchemaQuench automatically deletes its row from the tracking table during the next quench. This prevents stale entries from accumulating.
Even though the tracking table prevents accidental re-execution, write migration scripts defensively. Database restores, manual re-runs, and checkpoint recovery can all cause a script to execute against data that was already transformed.
CREATE ... IF NOT EXISTS before CREATE statementsDROP ... IF EXISTS before DROP statementsINSERT ... ON CONFLICT DO NOTHING or ON CONFLICT DO UPDATE instead of plain INSERT
The RunScriptsTwice configuration option (set in appsettings.json)
causes eligible scripts to execute a second time within the same quench run. This is designed
for CI pipelines in non-production environments to surface idempotency issues early, before
they reach production.
Before, BetweenTablesAndKeys,
AfterTablesScripts, and After slots execute a second timeObjects, AfterTablesObjects,
and TableData slots run each batch twice to help resolve circular dependencies{
"SmithySettings": {
"RunScriptsTwice": true
}
}
Default: false. Can also be set via environment variable:
SmithySettings_RunScriptsTwice.