Daily Schema Workflows on PostgreSQL

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": "NUMERIC(7,4)"

Add an index

Add a new entry in the Indexes array:

{
  "Name": "ix_promotions_is_active",
  "IndexColumns": "is_active",
  "FilterExpression": "is_active = true"
}

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 ... ALTER COLUMN ... TYPE 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 PostgreSQL.

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

   {
     "Name": "Initialize",
     "DatabaseIdentificationScript": "SELECT datname FROM pg_database WHERE datistemplate = false AND datname = 'TestMain' AND NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = '{{NorthwindDb}}')"
   }

   The DatabaseIdentificationScript is the key. It 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. In Templates/Initialize/Before Scripts/Create Northwind [ALWAYS].sql:

   CREATE DATABASE "{{NorthwindDb}}";

   The [ALWAYS] marker tells SchemaQuench to run this script every time the Initialize template is active — no version tracking needed. PostgreSQL doesn't support IF NOT EXISTS on CREATE DATABASE, but that's fine: the template self-selects out of the deployment once the database exists, so this script only ever runs on a fresh server.

3. Product.json defines the template order.

   {
     "Name": "Northwind",
     "Platform": "PostgreSQL",
     "ValidationScript": "SELECT EXISTS(SELECT * FROM pg_database WHERE datname 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.