Daily Schema Workflows on MySQL

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": "VARCHAR(500)",
  "Nullable": true
}

Change a data type

Find the discount_percent column and edit its DataType:

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

Add an index

Add a new entry in the Indexes array:

{
  "Name": "ix_promotions_is_active",
  "IndexColumns": "is_active"
}

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 COLUMN for the new column, an ALTER TABLE ... MODIFY COLUMN for the data type change, and a CREATE INDEX for the new 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 MySQL.

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

   {
     "Name": "Initialize",
     "DatabaseIdentificationScript": "SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME = 'TestMain' AND NOT EXISTS (SELECT * FROM information_schema.schemata WHERE SCHEMA_NAME = '{{NorthwindDb}}')"
   }

   The DatabaseIdentificationScript is the key. In MySQL, databases live in information_schema.schemata. The script returns a result only when the target database doesn't yet exist — it matches TestMain (the bootstrap database this demo connects to) 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 Database [ALWAYS].sql:

   CREATE DATABASE IF NOT EXISTS `{{NorthwindDb}}`
     CHARACTER SET utf8mb4
     COLLATE utf8mb4_0900_ai_ci;

   The [ALWAYS] marker tells SchemaQuench to run this script every time the Initialize template is active — no version tracking needed. CREATE DATABASE IF NOT EXISTS makes the script safe to re-run, though in practice it only executes once because the template self-selects out after the database exists. The explicit charset and collation pin the new database to modern Unicode defaults.

3. Product.json defines the template order.

   {
     "Name": "Northwind",
     "Platform": "MySQL",
     "ValidationScript": "SELECT EXISTS(SELECT * FROM information_schema.schemata WHERE SCHEMA_NAME IN ('{{NorthwindDb}}', 'TestMain'))",
     "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.