SchemaSmith Documentation

Products & Templates

Product.json defines your schema package. Template.json targets databases within it. Together they control what gets deployed, where, and in what order.

Overview Product.json Template.json Custom Script Folders Quench Slots Platform Differences
Products and Templates Configuration

The Product.json file sits at the root of the schema package and is the top-level configuration — the starting point for every deployment.

What Are Products and Templates?

A schema package contains two types of JSON configuration files that govern how SchemaQuench discovers, validates, and deploys your database schema:

  • Product.json sits at the package root. It names the product, defines the order in which templates are processed, declares package-wide script tokens, and optionally runs product-level scripts before and after all template work.
  • Template.json lives inside each Templates/<TemplateName>/ subfolder. It controls everything for one logical group of databases: which databases to target, what script folders to process, and template-specific token overrides.

How SchemaQuench Uses Them

  1. Loads Product.json from the package root
  2. Validates that Platform matches the running tool
  3. Runs the ValidationScript against the target server
  4. Resolves all script tokens (file tokens, configuration overrides)
  5. Iterates through TemplateOrder, loading each Template.json in sequence
  6. For each template, identifies matching databases by running the database identification script
  7. Quenches each identified database through the full slot execution pipeline

File Locations

<package-root>/
  Product.json
  Templates/
    <TemplateName>/
      Template.json
      Tables/
      Before Scripts/
      Schemas/
      Functions/
      Views/
      Procedures/
      Triggers/
      Table Data/
      After Scripts/
      ...

Product.json Fields

The following fields are shared across all platforms.

Property Type Default Required Description
Name string — Yes Product name. Automatically added as a {{ProductName}} script token. Used for migration script tracking and version stamping.
Platform string — Yes Target platform. Valid values: "SqlServer", "PostgreSQL", "MySQL". Determines which platform adapter handles deployment, extraction, and the default folder set.
ValidationScript string — Yes SQL expression evaluated before quench begins. Must return a truthy value or the quench aborts. Supports token replacement.
TemplateOrder string[] [] No Ordered list of template directory names. Templates are quenched in this order.
ScriptTokens object {} No Key-value pairs for {{TokenName}} replacement in scripts and SQL properties. See Script token mechanics.
BaselineValidationScript string — No SQL expression evaluated after server validation but before template processing.
VersionStampScript string — No SQL executed once after all templates complete successfully. Typically records the release version on the server.
DropUnknownIndexes bool false No When true, the table quench drops indexes on managed tables that aren't defined in the table JSON.
MinimumVersion string — No Minimum target server version. Currently metadata only — displayed in tooling and available for future version-adaptive features. Use ValidationScript for runtime version enforcement.
CheckConstraintStyle string "ColumnLevel" No Controls how SchemaTongs writes check constraints during extraction: "ColumnLevel" (inline CheckExpression on the column) or "TableLevel" (named constraints in the CheckConstraints array).
ScriptFolders array [] No Optional product-level folder definitions. Used to add custom folder paths or assign secondary-server filtering. See Custom Script Folders.
Extensions any null No Reserved. Product.json does not currently use Extensions for custom properties.

Annotated Example

{
  "Name": "MyProduct",
  "Platform": "SqlServer",
  "ValidationScript": "SELECT CAST(CASE WHEN EXISTS(SELECT * FROM master.sys.databases WHERE [Name] = '{{AppDb}}') THEN 1 ELSE 0 END AS BIT)",
  "TemplateOrder": ["Shared", "AppDatabase"],
  "ScriptTokens": {
    "AppDb": "MyApp_Production",
    "ReleaseVersion": "3.2.1"
  },
  "VersionStampScript": "UPDATE dbo.SchemaVersion SET Version = '{{ReleaseVersion}}'"
}
{
  "Name": "MyProduct",
  "Platform": "PostgreSQL",
  "ValidationScript": "SELECT EXISTS(SELECT * FROM pg_database WHERE datname = '{{AppDb}}')",
  "TemplateOrder": ["Shared", "AppDatabase"],
  "ScriptTokens": {
    "AppDb": "myapp_production",
    "ReleaseVersion": "3.2.1"
  },
  "VersionStampScript": "UPDATE schema_version SET version = '{{ReleaseVersion}}'"
}
{
  "Name": "MyProduct",
  "Platform": "MySQL",
  "ValidationScript": "SELECT EXISTS(SELECT * FROM information_schema.schemata WHERE SCHEMA_NAME = '{{AppDb}}')",
  "TemplateOrder": ["AppDatabase", "AuditDatabase"],
  "ScriptTokens": {
    "AppDb": "my_app",
    "ReleaseVersion": "3.2.1"
  },
  "VersionStampScript": "INSERT INTO `{{AppDb}}`.`schema_version` (`version`, `applied_at`) VALUES ('{{ReleaseVersion}}', NOW())"
}

Template.json Fields

Property Type Default Required Description
Name string — Yes Template name. Must match the containing directory name. Automatically added as a {{TemplateName}} script token.
DatabaseIdentificationScript string — Yes SQL query that returns one or more database names. SchemaQuench reads the first column of each row. Supports token replacement.
VersionStampScript string — No SQL executed per database after that database's quench completes successfully.
UpdateFillFactor bool true No When true, the table quench updates index fill factors to match the JSON definitions. OR'd with table-level and index-level UpdateFillFactor settings.
IndexOnlyTableQuenches bool false No When true, the table quench only manages indexes, statistics, XML/full-text indexes, exclude constraints. Skips table creation, column changes, and foreign key management. Tables that don't exist are silently skipped.
BaselineValidationScript string — No SQL validation executed per database before quenching that database.
Required bool true No When true, deployment fails if DatabaseIdentificationScript returns no databases. Catches misconfigured identification scripts that silently skip an entire template.
SkipIfReadOnly bool false No When true, databases that are read-only are silently skipped instead of failing the quench. Enables Availability Group secondary handling on SQL Server and replica handling on other platforms.
ScriptFolders array [] No Optional list of TemplateFolder definitions. When empty, the platform's default folder set is used. When non-empty, this array fully replaces the defaults — so include every folder you want active. See Custom Script Folders.
ScriptTokens object {} No Key-value pairs that override matching product-level tokens for this template. Template tokens take precedence over product tokens with the same key.

Annotated Example

{
  "Name": "AppDatabase",
  "DatabaseIdentificationScript": "SELECT [name] FROM master.sys.databases WHERE [name] = '{{AppDb}}'",
  "VersionStampScript": "EXEC dbo.RecordDeployment '{{ReleaseVersion}}'",
  "Required": true
}
{
  "Name": "AppDatabase",
  "DatabaseIdentificationScript": "SELECT datname FROM pg_database WHERE datname = '{{AppDb}}'",
  "VersionStampScript": "INSERT INTO deploy_log (version, deployed_at) VALUES ('{{ReleaseVersion}}', NOW())",
  "Required": true
}
{
  "Name": "AppDatabase",
  "DatabaseIdentificationScript": "SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME = '{{AppDb}}'",
  "VersionStampScript": "UPDATE `{{AppDb}}`.`schema_version` SET `last_applied` = NOW() WHERE `template` = 'AppDatabase'",
  "Required": true
}

Custom Script Folders

By default, every template uses the platform's standard folder layout (see Default Folders below). When you need to add a folder that isn't in the defaults, rename a folder, or change which slot a folder runs in, you declare your own ScriptFolders array on Template.json.

The array is an explicit replacement, not a merge. The moment you provide ScriptFolders with at least one entry, the defaults are skipped entirely — so you should include every folder you want loaded.

TemplateFolder properties

Property Type Required Description
FolderPath string Yes Relative path under the template directory. Forward or back slashes both work.
QuenchSlot string Yes Which execution slot the folder runs in. See Quench Slot Reference below for valid values.
ObjectType string No When the folder contains programmable objects (functions, views, procedures, triggers, etc.), tag it with the corresponding object type so SchemaQuench can route it through the dependency-retry loop correctly.

Example — adding a custom folder for an extra migration step

{
  "Name": "Reporting",
  "DatabaseIdentificationScript": "SELECT [Name] FROM master.sys.databases WHERE [Name] = '{{ReportDB}}'",
  "ScriptFolders": [
    { "FolderPath": "Before Scripts", "QuenchSlot": "Before" },
    { "FolderPath": "Schemas", "QuenchSlot": "Objects", "ObjectType": "Schemas" },
    { "FolderPath": "Functions", "QuenchSlot": "Objects", "ObjectType": "Functions" },
    { "FolderPath": "Views", "QuenchSlot": "Objects", "ObjectType": "Views" },
    { "FolderPath": "Procedures", "QuenchSlot": "Objects", "ObjectType": "Procedures" },
    { "FolderPath": "BetweenTablesAndKeys", "QuenchSlot": "BetweenTablesAndKeys" },
    { "FolderPath": "AfterTables", "QuenchSlot": "AfterTablesScripts" },
    { "FolderPath": "Triggers", "QuenchSlot": "AfterTablesObjects", "ObjectType": "Triggers" },
    { "FolderPath": "Table Data", "QuenchSlot": "TableData" },
    { "FolderPath": "After Scripts", "QuenchSlot": "After" }
  ]
}

In this example, the team kept the standard folders but added two extra slots (BetweenTablesAndKeys, AfterTablesScripts) that aren't part of the default set. They can write migration scripts that run after the table structure exists but before foreign keys, or after the table structure but before triggers, without writing any extra glue.

Why this matters

Custom script folders are how you make the schema package fit your deployment lifecycle, not the other way around. Need a folder called Permissions that runs in the After slot? Done. Need to split your large Procedures directory into Procedures/Public and Procedures/Internal for code review? Done. Need an entirely new slot for your team's idempotent post-deploy data fixes? Drop a folder, point it at After, and you're done.

Custom product-level folders

Product.json can also declare custom folders via its ScriptFolders array (the property name is the same as Template.json's). The shape is similar but uses ProductQuenchSlot (Before or After) and supports a ServerToQuench setting that controls whether the folder runs on the primary, secondaries, or both. ServerToQuench is a SQL Server-only feature; see Platform Differences below.

Quench Slot Reference

TemplateQuenchSlot controls when in the deployment lifecycle a folder's scripts run.

Slot Behavior
Before One-time migration scripts that run after initial object creation and new table creation, but before table modifications. Use for data preparation that must happen before columns are altered or dropped. Sequential, tracked.
Objects Database objects that may have cross-dependencies (schemas, types, catalogs, functions, views, procedures). The retry loop resolves creation order automatically.
BetweenTablesAndKeys Migration scripts that need the table structure to exist but must run before foreign key constraints are enforced. Typical use: populating a new NOT NULL column before FKs block the data load. Sequential, tracked.
AfterTablesScripts Migration scripts that depend on the final table and key structure but must run before triggers are deployed. Sequential, tracked.
AfterTablesObjects Triggers, DDL triggers, rules, and views that depend on the completed table structure. Dependency retry loop.
TableData Data population scripts (MERGE statements, INSERT/UPDATE seeds). Run after triggers are deployed but before foreign key constraints are applied. Dependency retry loop.
After Final migration scripts. Run after all database objects and data are deployed. Sequential, tracked.

ProductQuenchSlot has only two values:

Slot Behavior
Before Product-level scripts that run before any template processing begins. Sequential, untracked (run every deployment).
After Product-level scripts that run after all templates complete. Sequential, untracked.

Execution behaviors

Sequential, untracked — Product-level scripts run in alphabetical order on every deployment. They aren't recorded in any tracking table. Write these scripts to be idempotent.

Sequential, tracked — Template-level migration scripts run in alphabetical order. Each script's completion is recorded in the SchemaSmith.CompletedMigrationScripts table and won't run again on subsequent quenches. Scripts with [ALWAYS] in the filename run every time regardless of tracking.

Dependency retry loop — All scripts in the slot are attempted. Scripts that fail due to unresolved dependencies are retried on the next iteration. The loop continues until all scripts succeed or no progress is made on an iteration.

Default Script Folders

When Template.json does not declare its own ScriptFolders, SchemaSmith fills in a platform-specific default set. Each platform's defaults reflect the object types and lifecycle stages that platform actually supports.

Folder Quench Slot Object Type
Before Scripts/Before—
Schemas/ObjectsSchemas
DataTypes/ObjectsDataTypes
FullTextCatalogs/ObjectsFullTextCatalogs
FullTextStopLists/ObjectsFullTextStopLists
XMLSchemaCollections/ObjectsXMLSchemaCollections
Functions/ObjectsFunctions
Views/ObjectsViews
Procedures/ObjectsProcedures
Triggers/AfterTablesObjectsTriggers
DDLTriggers/AfterTablesObjectsDDLTriggers
Table Data/TableData—
After Scripts/After—

13 default folders

Folder Quench Slot Object Type
Before Scripts/Before—
Schemas/ObjectsSchemas
Domain Types/ObjectsDomainTypes
Enum Types/ObjectsEnumTypes
Composite Types/ObjectsCompositeTypes
Functions/ObjectsFunctions
Trigger Functions/ObjectsTriggerFunctions
Window Functions/ObjectsWindowFunctions
Aggregates/ObjectsAggregates
Procedures/ObjectsProcedures
Sequences/ObjectsSequences
Rules/AfterTablesObjectsRules
Triggers/AfterTablesObjectsTriggers
Views/AfterTablesObjectsViews
Table Data/TableData—
After Scripts/After—

16 default folders

Folder Quench Slot Object Type
Before Scripts/Before—
Events/ObjectsEvents
Functions/ObjectsFunctions
Procedures/ObjectsProcedures
Triggers/AfterTablesObjectsTriggers
Views/AfterTablesObjectsViews
Table Data/TableData—
After Scripts/After—

8 default folders

Legacy fallback (SQL Server only)

If your existing package has MigrationScripts/Before/, MigrationScripts/After/, or a TableData/ folder (no space) on disk, SchemaSmith will use them in place of Before Scripts/, After Scripts/, and Table Data/ respectively. This keeps older packages working without a folder rename.

Tables are always loaded from Tables/ regardless of platform. SQL Server adds Indexed Views/; PostgreSQL adds Materialized Views/. These are not script folders — they hold structured JSON object definitions, not .sql files.

Platform Differences

The Product.json and Template.json shapes are identical across every platform. The table below summarises the engine-specific variations a schema package carries on top of that shared shape.

Difference SQL Server PostgreSQL MySQL
Table wrapper type SqlServerTable PostgreSqlTable MySqlTable
Default table schema dbo public — (database name = schema)
Database identification DatabaseIdentificationScript against master.sys.databases DatabaseIdentificationScript against pg_database DatabaseIdentificationScript against information_schema.schemata
Default folder count 13 16 8
Platform-specific object types DDL Triggers, XML Schema Collections, Full-Text Catalogs / Stop Lists, Indexed Views Domain Types, Enum Types, Composite Types, Trigger Functions, Window Functions, Aggregates, Sequences, Rules, Materialized Views Events
ServerToQuench (Availability Group / replica routing) Supported Not applicable Not applicable
Reference-data merge idiom MERGE statement MERGE statement (PostgreSQL 15+) INSERT ... ON DUPLICATE KEY UPDATE

Secondary servers are SQL Server-only

The ServerToQuench routing field on product folders, and the SecondaryServers settings that back it, exist only for SQL Server Availability Groups. PostgreSQL and MySQL deployments connect to a single target server.

SQL Server: Secondary servers reference »

For the per-platform table-level fields (indexes, constraints, computed columns, full-text, materialized views), see Defining tables. For the reference-data merge configuration itself, see Data delivery.

Template Ordering

TemplateOrder controls both which templates are included and the sequence in which they execute. Templates are processed strictly sequentially: SchemaQuench completes all databases for template N before moving to template N+1.

Order matters when:

  • Template B's identification script queries a database managed by template A
  • Template B's scripts reference cross-database objects that template A defines
  • Product-level Before scripts must complete before any template work begins

A template name in TemplateOrder with no matching folder on disk causes SchemaQuench to abort. A template folder that exists but is not listed in TemplateOrder is silently ignored.

Validation and Version Stamps

Validation Script Chain

Validation scripts form a gate chain. Each must pass before the next phase begins:

  1. Product.ValidationScript — runs once against the target server before any templates
  2. Product.BaselineValidationScript — runs once after server validation, before template processing
  3. Template.BaselineValidationScript — runs per database before quenching that database

Version Stamp Scripts

  • Template.VersionStampScript — runs per database after that database's quench completes
  • Product.VersionStampScript — runs once after all templates finish

Last reviewed May 2026 by the SchemaSmith Team.