Migration Scripts for MySQL

Overview

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

Product-level migration scripts run in one of two slots: Before or After the product is updated.

These scripts would typically be used to add user accounts or scheduled events, manage permissions, or validate server configurations.

Tip

By default, product-level scripts run in the context of the mysql database.

Tip

Product-level scripts always run on every execution. There is no table for tracking past usage.

Template-Level Migration Scripts

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:

Before Scripts

Run after KindleTheForge and baseline validation, before table structure changes.

Transform data before columns are altered or dropped
Insert seed rows for new NOT NULL columns
Any logic requiring the old table structure

After Scripts

Run after all table changes, triggers, data delivery, and table data scripts.

Post-deployment validation queries
Grant permissions on new objects
Cleanup depending on final schema state

The [ALWAYS] Suffix

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

Execution Tracking

SchemaQuench creates a SchemaSmith_CompletedMigrationScripts table in each target database during KindleTheForge. Before executing a non-[ALWAYS] script, it checks this table. If a matching row exists, the script is skipped.

Column	Type	Description
`Id`	INT AUTO_INCREMENT	Primary key
`ProductName`	VARCHAR(100)	Product name from Product.json
`QuenchSlot`	VARCHAR(50)	Slot the script ran in
`ScriptPath`	VARCHAR(500)	Relative path from the template root
`CompletedAt`	DATETIME	Timestamp of last execution

Forcing Re-execution

Delete the tracking row to force a script to run again:

DELETE FROM SchemaSmith_CompletedMigrationScripts
WHERE ProductName = 'MyProduct'
  AND ScriptPath = 'Before Scripts/001 - One time migration.sql';

Automatic Cleanup

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.

Batch Splitting

MySQL scripts are split into batches using the DELIMITER command. This is required because stored procedures, functions, triggers, and events use semicolons internally, so a custom delimiter separates the object body from surrounding statements.

DELIMITER $$

CREATE PROCEDURE MyProcedure()
BEGIN
    SELECT 1;
END$$

DELIMITER ;

INSERT INTO MyTable (col1) VALUES ('done');

This is split into two batches: the CREATE PROCEDURE statement and the INSERT statement. The DELIMITER lines themselves are removed before execution. Scripts without DELIMITER commands are treated as a single batch.

Idempotency

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.

Use CREATE TABLE IF NOT EXISTS instead of bare CREATE TABLE
Use DROP PROCEDURE IF EXISTS before CREATE PROCEDURE
Use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE for reference data
Check INFORMATION_SCHEMA.COLUMNS before adding columns

[ALWAYS] scripts must be fully idempotent. They run on every quench by design, so they must produce the correct result regardless of how many times they have previously executed.

RunScriptsTwice

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.

Which scripts are affected?

[ALWAYS] migration scripts in the Before, BetweenTablesAndKeys, AfterTablesScripts, and After slots execute a second time
Object scripts in the Objects, AfterTablesObjects, and TableData slots run each batch twice to help resolve circular dependencies

Normal (non-[ALWAYS]) migration scripts are not affected by this setting. They still execute only once per quench, governed by the tracking table.

Configuration

{
  "SmithySettings": {
    "RunScriptsTwice": true
  }
}

Default: false. Can also be set via environment variable: SmithySettings_RunScriptsTwice.

Migration Scripts - MySQL (Enterprise)