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.

By the SchemaSmith Team · Last reviewed

Overview Product.json Template.json Schema Templates 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.

Templates come in two flavors, and both work through the same execution pipeline. A regular template fans out across databases: DatabaseIdentificationScript returns one row per database, and SchemaQuench runs the full template against each returned database. A schema template fans out across schemas inside a single database: SchemaIdentificationScript returns one row per schema, and SchemaQuench runs the full template against each returned schema with the active schema name available as {{SchemaName}} everywhere it's needed. One declaration, many iterations, one quench.

An iteration is one matched database for a regular template, or one matched schema inside a database for a schema template. See Schema Templates below for the full mode-switch mechanics, and the Multi-Tenant Deployments page for both patterns end to end.

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 — and, on a schema template, matching schemas via the schema identification script
  7. Quenches each iteration 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 floor. Optional; omit to deploy against any version. SchemaQuench detects the version of every resolved target before any deployment work begins — if any target is below the floor, the entire run aborts with no side effects, and the diagnostic names each below-floor target with its detected version. SQL Server accepts a major version number (16) or a release year (2022, 2019, 2017, 2016); PostgreSQL accepts a major number (15, 16, 17); MySQL accepts major.minor (8.0, 8.4). An unparseable value is a configuration error that aborts at startup; if a target's version cannot be determined, that is also a hard error. See Validation and Version Stamps.
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.
BranchNameFile string "{{repo_path}}/.git/HEAD" No Path to the file SchemaSmith reads to derive the {{BranchName}} automatic token. Default points at Git's HEAD. Use any VCS that exposes the current branch as a single-line file; the only requirement is that the file exists and contains the branch identifier somewhere on its first line.
BeforeBranchNameMask string "ref: refs/heads/" No Prefix to strip from the line read out of BranchNameFile. Default matches Git's ref: refs/heads/<branch> format. Set to "" for VCSs whose branch file already contains the bare branch name.
AfterBranchNameMask string "" No Suffix to strip after the prefix is removed. Default empty. Set when your VCS appends extra text after the branch name.
Extensions any null No Reserved. Product.json does not currently use Extensions for custom properties.

Drop-Control Flags

SchemaQuench's drop-control flags are product-tier settings too — declare any of them in Product.json to govern the whole product. All seven below are bool, optional, and default to true. They resolve across an environment → product → template cascade (all but DropTablesRemovedFromProduct add a per-table tier) where an explicit false at a higher tier is sticky for all lower tiers. DropUnknownIndexes (in the table above) participates in the same cascade with a default of false. See Drop Control for full cascade behavior.

Flag What it gates
DropTablesRemovedFromProduct Tables that exist in the target, aren't defined in any table JSON in the package, and were previously managed by this product.
DropColumnsRemovedFromProduct Column-drop-by-absence: columns present in the database but absent from the table JSON.
DropForeignKeysRemovedFromProduct Foreign-key-drop-by-absence. Only by-absence removal is gated — a modified foreign key (same name, changed definition) is still dropped and recreated.
DropCheckConstraintsRemovedFromProduct Table-level check-constraint-drop-by-absence. Column-level CheckExpression reconciliation is not governed by this flag.
DropExcludeConstraintsRemovedFromProduct EXCLUDE-constraint-drop-by-absence. PostgreSQL only — no effect on SQL Server or MySQL.
DropStatisticsRemovedFromProduct Statistics-drop-by-absence for user-created statistics objects; auto-created statistics are never touched. SQL Server and PostgreSQL.
DropIndexesRemovedFromProduct Product-owned indexes removed from the table JSON — distinct from DropUnknownIndexes, which targets out-of-band indexes never managed by SchemaSmith. Primary keys are never dropped.

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.
IdentificationDatabase string — No Re-targets which database the DatabaseIdentificationScript runs against. Empty (the default) uses the platform init database. Point it at a control-plane registry database to enumerate a roster from a registry table. Token-resolvable — see roster from a control-plane registry.
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. 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.
RequireAtLeastOneTarget bool true No When true, deployment fails if discovery returns no targets — zero matching databases for a regular template, or zero matching (database, schema) pairs for a schema template. Catches misconfigured identification scripts that silently skip an entire template. Replaces the prior Required field (renamed in v2.1).
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.
SchemaIdentificationScript string — No SQL Server / PostgreSQL: query returning one column, N rows; each row is a schema name to iterate over — presence activates schema-template mode (see Schema Templates). MySQL: has no in-database schema axis (a schema is a database), so schema templates don't apply; the field is instead accepted as a deprecated backward-compat alias for DatabaseIdentificationScript — on load its value migrates into that field (only when it is empty) and a deprecation warning advises renaming. Use DatabaseIdentificationScript directly on MySQL.
CreateSchemaIfMissing bool false No Schema templates only. When true, the engine creates any discovered schema that doesn't yet exist before running that iteration. See Schema Templates.
AllowParallel bool true No Schema templates only. When false, iterations of this template run serially even when the global thread pool has capacity. See Schema Templates.
ContinueOnSchemaFailure bool true No Schema templates only. When false, the first failing iteration aborts all subsequent iterations for this template. See Schema Templates.
ContinueOnDatabaseFailure bool true No Regular templates only. When false, the first failing database iteration aborts all subsequent database iterations for this template. Ignored on schema templates — failure isolation there is governed by ContinueOnSchemaFailure.

Annotated Example

Regular template

{
  "Name": "AppDatabase",
  "DatabaseIdentificationScript": "SELECT [name] FROM master.sys.databases WHERE [name] = '{{AppDb}}'",
  "VersionStampScript": "EXEC dbo.RecordDeployment '{{ReleaseVersion}}'",
  "RequireAtLeastOneTarget": true
}

Schema template

{
  "Name": "TenantWorkspace",
  "DatabaseIdentificationScript": "SELECT [name] FROM master.sys.databases WHERE [name] = '{{TenantCRMDb}}'",
  "SchemaIdentificationScript": "SELECT [Name] FROM dbo.Tenants WHERE [Status] = N'Active' ORDER BY [Name]",
  "RequireAtLeastOneTarget": false
}

The presence of SchemaIdentificationScript makes this a schema template — the full template runs once per returned schema. See Schema Templates below.

Regular template

{
  "Name": "AppDatabase",
  "DatabaseIdentificationScript": "SELECT datname FROM pg_database WHERE datname = '{{AppDb}}'",
  "VersionStampScript": "INSERT INTO deploy_log (version, deployed_at) VALUES ('{{ReleaseVersion}}', NOW())",
  "RequireAtLeastOneTarget": true
}

Schema template

{
  "Name": "TenantWorkspace",
  "DatabaseIdentificationScript": "SELECT datname FROM pg_database WHERE datname = '{{TenantCRMDb}}'",
  "SchemaIdentificationScript": "SELECT name FROM public.tenants WHERE status = 'Active' ORDER BY name",
  "RequireAtLeastOneTarget": false
}

The presence of SchemaIdentificationScript makes this a schema template — the full template runs once per returned schema. See Schema Templates below.

Regular template

{
  "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'",
  "RequireAtLeastOneTarget": true
}

Schema templates don't apply on MySQL — a schema is a database, so MySQL fans out across tenants with DatabaseIdentificationScript instead (database-per-tenant). See Schema Templates below.

Schema Templates

Schema templates fan a single declarative template out across multiple schemas inside one database. You write the template once — tables, procedures, views, migration scripts — and SchemaQuench runs it once per schema returned by the SchemaIdentificationScript query, injecting the active schema name as the {{SchemaName}} token at every step. The most common use is multi-tenant SaaS where each tenant owns their own schema, but any pattern that needs the same object shape replicated across schemas works the same way. For a full narrative walkthrough, see Multi-Tenant Deployments.

Schema templates are supported on SQL Server and PostgreSQL only. MySQL uses a database-per-tenant model instead — there is no sub-database schema namespace to fan out across. On MySQL the SchemaIdentificationScript field is still accepted, but only as a deprecated backward-compat alias for DatabaseIdentificationScript (MySQL conflates schema and database): its value migrates into DatabaseIdentificationScript when that field is empty, a deprecation warning advises renaming, and no schema fan-out occurs. New MySQL packages should use DatabaseIdentificationScript directly.

Discovery query

SchemaIdentificationScript is the mode switch. When this field is present and non-empty on a Template.json, the template becomes a schema template. The value is a SQL query that returns one column and any number of rows; each row is a schema name, and SchemaQuench runs the full template once per returned row.

The query runs against each target database identified by DatabaseIdentificationScript. If both are present, the engine computes the full cross-product: every (database, schema) pair runs as an independent iteration. Token replacement applies to the query body before execution, so you can reference script tokens or <*Query*> tokens in the discovery query itself.

The active schema name is available to every part of the iteration as {{SchemaName}} — in table Name and Schema fields, in procedure and view SQL bodies, in migration script filenames, in VersionStampScript, and in user-defined script tokens. See Script token mechanics for availability rules and resolution timing.

Reserved schema names

A small set of platform-built-in schemas can't be used as iteration targets. If your discovery query returns one of these names, the engine fails the iteration with an error naming the offending schema and pointing you at the "shared content lives in a regular template" remediation. The reserved sets are:

  • SQL Server: dbo, sys, INFORMATION_SCHEMA, guest, plus the fixed database roles that double as schemas (db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_backupoperator, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter).
  • PostgreSQL: public, pg_catalog, pg_toast, information_schema, plus any schema matching the pg_temp_* or pg_toast_temp_* wildcards (Postgres uses these for session-scoped temp objects).

Shared content (lookup tables, audit logs, dimension data) belongs in a regular template that runs once per database, not in a schema-template iteration. The reserved-name guard is the engine's way of catching a discovery query that accidentally returns public or dbo instead of a real tenant schema.

Auto-create schemas

When CreateSchemaIfMissing is false (the default), an iteration whose schema doesn't exist fails immediately with a clear error. This is the safer behavior: a typo in your discovery query should not silently create schemas in production. When true, the engine emits CREATE SCHEMA for any schema returned by the discovery query that does not yet exist on the target database.

Set CreateSchemaIfMissing to true when you're running a fully automated onboarding pipeline and the deployment user is trusted to create schemas. Many teams prefer to create schemas via an explicit stored procedure (OnboardTenant) in the Shared template and leave this false.

Privilege requirement

CreateSchemaIfMissing: true requires the deployment user to have CREATE SCHEMA permission on SQL Server or CREATE on the database on PostgreSQL. The default false is intentional fail-fast: if the discovery query returns an unexpected schema name, you want an error, not a new schema.

AllowParallel

When AllowParallel is true (the default), schema iterations can run in parallel up to the MaxThreads limit alongside iterations from other templates and databases. Parallel execution is safe: each iteration touches its own schema namespace and converges independently.

Serial iterations

Set AllowParallel: false to force this template's iterations to run one at a time — useful to cap concurrent load on a resource-constrained target, or when the template's own migration scripts perform DDL that can't run concurrently. Parallel iteration is otherwise the production-realistic default; both TenantCRM demos ship AllowParallel: true.

Failure isolation

When ContinueOnSchemaFailure is true (the default), a single schema iteration's failure does not abort the others — remaining iterations continue and the product run exits non-zero after all iterations have completed or failed. This matches the database-level isolation behavior that most teams already rely on. When false, the first failing schema iteration stops the dispatcher: no new iterations start, in-flight iterations drain, and subsequent templates in TemplateOrder do not run.

Set ContinueOnSchemaFailure to false for deployments where any single-tenant failure is a hard stop — for example, a CI environment where partial deployment is worse than no deployment.

Complete example

This is the TenantWorkspace/Template.json from the SQL Server TenantCRM demo — a real schema template with all four fields declared: 

{
  "Name": "TenantWorkspace",
  "DatabaseIdentificationScript": "SELECT [name] FROM master.sys.databases WHERE [name] = '{{TenantCRMDb}}'",
  "SchemaIdentificationScript": "SELECT [Name] FROM dbo.Tenants WHERE [Status] = N'Active' ORDER BY [Name]",
  "RequireAtLeastOneTarget": false,
  "CreateSchemaIfMissing": false,
  "AllowParallel": true,
  "ContinueOnSchemaFailure": true,
  "VersionStampScript": "PRINT 'TenantCRM TenantWorkspace [{{SchemaName}}] {{ReleaseVersion}}'",
  "ScriptFolders": [
    { "FolderPath": "Before Scripts", "QuenchSlot": "Before" },
    { "FolderPath": "Functions",      "QuenchSlot": "Objects", "ObjectType": "Functions" },
    { "FolderPath": "Views",          "QuenchSlot": "Objects", "ObjectType": "Views" },
    { "FolderPath": "Procedures",     "QuenchSlot": "Objects", "ObjectType": "Procedures" },
    { "FolderPath": "Triggers",       "QuenchSlot": "Objects", "ObjectType": "Triggers" }
  ]
}

RequireAtLeastOneTarget: false here handles a fresh installation where no tenants have been onboarded yet — the schema template finds zero rows, treats it as a no-op, and the product run succeeds so the Initialize and Shared templates still complete. The PostgreSQL TenantCRM demo is identical in structure and ships AllowParallel: true, so tenants deploy concurrently.

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.
ShouldApplyExpression string No Optional SQL predicate evaluated against the target at deploy time (tokens resolved first): true deploys the folder, false skips it (logged), blank always deploys. See Conditional application.

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. Product folders also accept a ShouldApplyExpression, evaluated per server against the admin connection — see Conditional application.

Organizing subfolders

When a package grows beyond a handful of objects, flat script folders become hard to navigate. SchemaQuench discovers scripts recursively — all .sql files in a folder and every subfolder underneath it, sorted alphabetically by full path — so you can group related objects without changing their deployment behavior. 

Procedures/
  Reporting/
    dbo.GetMonthlyRevenue.sql
    dbo.GetQuarterlyReport.sql
  Core/
    dbo.ProcessOrder.sql
    dbo.ValidateCustomer.sql

The alphabetical-by-full-path sort determines execution order, so Core/ scripts run before Reporting/ scripts. Name your folders and files to make the order legible — a prefix convention (01-Core/, 02-Reporting/) makes intent obvious at a glance.

Table and view JSON follows the same rule. Files in Tables/Analytics/Orders.json and Tables/Transactional/Customers.json are both discovered and loaded; the subfolder is purely organizational.

Layout round-trips. When you re-extract a table, SchemaTongs looks up the object's existing file path in its index and writes back to that same location. Your subfolder organization survives the extraction cycle intact.

Duplicate filenames across subfolders

If the same filename appears in more than one subfolder — for example, Reporting/dbo.GetReport.sql and Archive/dbo.GetReport.sql — SchemaTongs logs a warning and writes to the base folder instead of an ambiguous location. Keep object filenames unique across subfolders to avoid this.

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 catalog master.sys.databases pg_database information_schema.schemata
Schema templates Supported Supported Not applicable — MySQL is database-per-tenant; the schema-identification field is accepted only as a deprecated alias (see Schema Templates)
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

Every SchemaQuench run has a pre-flight window before a single table is touched. That window exists to answer one question: is this the right server, in the right state, for this package?

ValidationScript is a required Product-level property. It runs first, against the server's admin connection (the platform's init database: master, postgres, or information_schema). If it returns a falsy value, the deployment aborts before any quench begins. This is your identity check: am I on the database server I think I am? Common patterns: verify an expected database exists on the server, confirm a linked server or infrastructure dependency is in place, or gate on server version or edition to prevent running an incompatible package. Token replacement applies — {{MainDB}}, {{ReleaseVersion}}, any token you define in ScriptTokens — so the validation script isn't hardcoded to one environment.

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

The Anti-Rollback Handshake

BaselineValidationScript answers "is this environment at the state this package expects?" The canonical use is anti-rollback protection. Pair it with VersionStampScript: the stamp records a version identifier after a successful deployment; the baseline checks for that identifier before the next deployment. If someone accidentally runs an older package against an already-upgraded environment, the baseline check aborts before any harm is done. 

{
  "VersionStampScript": "UPDATE dbo.DeploymentInfo SET Version = '{{ReleaseVersion}}'",
  "BaselineValidationScript": "SELECT CAST(CASE WHEN EXISTS(
      SELECT 1 FROM dbo.DeploymentInfo WHERE Version = '{{PreviousVersion}}'
  ) THEN 1 ELSE 0 END AS BIT)"
}

Version Stamp Scripts

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

The Declarative Version Floor

MinimumVersion is the declarative companion to the validation scripts — set it in Product.json and SchemaQuench enforces a hard engine version floor before running anything. Use it for the unconditional floor ("this package requires PostgreSQL 15 or later"); write ValidationScript logic for conditional gates or checks that need to read server state. See the Product.json fields table for accepted version formats and abort behavior.

Hands-on lab

Define your first Product.json and template structure, then orchestrate which templates deploy and in what order.

Start the product lab