Daily Schema Workflows on SQL Server

The Promotions table needs changes. Marketing wants a description field, the discount column needs more precision, and you need an index on the active flag for a dashboard query. All three edits happen in the same JSON file — you shape the table right where it lives.

Add a column

Insert a new entry in the Columns array:

{
  "Name": "[Description]",
  "DataType": "NVARCHAR(500)",
  "Nullable": true
}

Change a data type

Find the DiscountPercent column and edit its DataType:

"DataType": "DECIMAL(7,4)"

Add an index

Add a new entry in the Indexes array:

{
  "Name": "[IX_Promotions_IsActive]",
  "IndexColumns": "[IsActive]",
  "FilterExpression": "[IsActive] = 1"
}

Preview with WhatIf

Preview the changes before touching the database. Run SchemaQuench in WhatIf mode:

SmithySettings_WhatIfONLY=true SchemaQuench

SchemaQuench generates the SQL it would execute — an ALTER TABLE ... ADD for the new column, an ALTER TABLE ... ALTER COLUMN for the data type change, and a CREATE INDEX for the new filtered index — and logs it all without applying anything. Read the generated SQL, confirm it looks right, then run SchemaQuench normally to quench the changes into your database. Three changes to one file. One preview. One command. No scripts to write, number, or maintain.

Some products need to create their target database from scratch. CI pipelines spin up fresh containers. Docker Compose environments start from nothing. New developers clone the repo and need a working database in one command. The Initialize template pattern handles all of these. Three pieces work together. Here's how the Northwind demo product sets it up on SQL Server.

1. The Initialize template identifies itself out of the deployment. In Templates/Initialize/Template.json:

   {
     "Name": "Initialize",
     "DatabaseIdentificationScript": "SELECT [Name] FROM master.sys.databases WHERE [Name] = 'master' AND NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE [Name] = '{{NorthwindDb}}')"
   }

   The DatabaseIdentificationScript is the key. It returns a result only when the target database doesn't yet exist — it matches master (a database that always exists on the server) but only when NorthwindDb is missing. On the first run, this template activates and creates the database. On every subsequent run, the script returns no rows, SchemaQuench skips the template entirely, and deployment proceeds straight to the main template.

2. A migration script creates the database idempotently. In Templates/Initialize/Before Scripts/Create Northwind [ALWAYS].sql:

   IF NOT EXISTS (
       SELECT 1 FROM master.sys.databases
       WHERE [Name] = '{{NorthwindDb}}'
   )
   BEGIN
       CREATE DATABASE [{{NorthwindDb}}]
   END

   The [ALWAYS] marker tells SchemaQuench to run this script every time the Initialize template is active — no version tracking needed. The IF NOT EXISTS guard makes the script safe to re-run, though in practice it only executes once because the template self-selects out after the database exists.

3. Product.json defines the template order.

   {
     "Name": "Northwind",
     "Platform": "SqlServer",
     "ValidationScript": "SELECT CAST(1 AS BIT)",
     "TemplateOrder": [
       "Initialize",
       "Northwind"
     ],
     "ScriptTokens": {
       "NorthwindDb": "Northwind"
     }
   }

   TemplateOrder ensures Initialize runs first. If the database doesn't exist, Initialize creates it, then the Northwind template deploys the full schema. If the database already exists, Initialize is skipped and Northwind deploys any pending changes. One docker compose up bootstraps everything from an empty server. Subsequent runs skip Initialize automatically and apply only schema changes. Fresh environment or existing environment, same command, same result.