Handle special-case changes before and after automated updates with clear ordering rules.
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.
Migration scripts are SQL files placed in the MigrationScripts/Before and
MigrationScripts/After folders within a template. Scripts execute in
alphabetical order by filename. Prefix filenames with a date or sequence number to
control execution order.
Run after Kindling, before object scripts and table structure changes.
Run after all table changes and TableData 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 | DATETIME | Timestamp of last execution |
Delete the tracking row to force a script to run again:
DELETE FROM SchemaSmith.CompletedMigrationScripts
WHERE ScriptPath = 'MigrationScripts/Before/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.
Migration scripts are split into batches by GO statements before execution.
The GO keyword must appear on its own line (leading and trailing whitespace
is permitted) and is case-insensitive.
-- Batch 1
CREATE TABLE dbo.Staging (Id INT PRIMARY KEY, Value NVARCHAR(100));
GO
-- Batch 2
INSERT INTO dbo.Staging (Id, Value) VALUES (1, 'Initial');
GOEach batch executes as a separate command. If a batch fails, remaining batches in that script are skipped and the quench halts with an error.
The batch splitter is aware of SQL strings, comments, and bracket-delimited identifiers.
A GO inside a string literal ('... GO ...'), block comment
(/* GO */), line comment (-- GO), or bracketed identifier
([GO]) is not treated as a batch separator.
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.
IF NOT EXISTS checks before CREATE statementsIF EXISTS checks before DROP statementsMERGE or IF NOT EXISTS before INSERT statements