Script Token Mechanics

Script tokens are how one schema package becomes ten environments. Define a token once, reference it everywhere, override it per environment without touching a script file. The simple form is just two curly braces and a name — but tokens go far beyond that. They can pull in file contents, execute server-side queries to generate values at deployment time, embed entire table schemas as JSON, and surface custom metadata you've attached through Extensions. One feature, many superpowers, all in the free toolset.

Tokens are replaced in every place SchemaSmith processes script content, both in JSON expression fields and in .sql files.

Product-level JSON properties

• BaselineValidationScript
• ValidationScript
• VersionStampScript

Template-level JSON properties

• BaselineValidationScript
• DatabaseIdentificationScript
• VersionStampScript

Table-component expression fields

Inside table JSON files:

• CheckExpression on columns
• Default on columns
• Expression on table-level check constraints
• FilterExpression on indexes (where supported)
• ShouldApplyExpression on tables, columns, indexes, foreign keys, check constraints, indexed views, materialized views, and other supported components

SQL script files

In every product and template script folder:

The exact set of default folders depends on the product's Platform. Anything you add via custom ScriptFolders participates in token resolution exactly the same way as the defaults.

Define tokens in Template.json under ScriptTokens. Template tokens override product tokens with the same key for the duration of that template's execution, and they can also introduce new tokens that only exist inside the template.

Say your Product.json defines a product-wide default for MainDB:

{
  "Name": "SaasProduct",
  "TemplateOrder": ["Registry", "Reporting"],
  "ScriptTokens": {
    "MainDB": "ProductionMain"
  }
}

Then one of its templates overrides MainDB and adds a new SchemaOwner token that the product never declared:

{
  "Name": "Reporting",
  "ScriptTokens": {
    "MainDB": "ReportingAlias",
    "SchemaOwner": "rpt"
  }
}

Inside the Reporting template, {{MainDB}} resolves to ReportingAlias instead of the product-scope ProductionMain. The new {{SchemaOwner}} token is available only inside this template; other templates in the same product don't see it.

Here is where tokens go from "find and replace" to "deployment-time content engine." A token's value in ScriptTokens can start with a special tag that tells SchemaSmith to resolve it at deployment time, by reading a file, by querying the live database, or by embedding a serialized object's JSON. The token name in your scripts stays a plain {{TokenName}}; the magic happens in how the value is computed before substitution.

The advanced tags:

All resolution happens automatically. Your SQL scripts just see {{TokenName}} and the right value lands there at deployment time.

Example — embed a file's contents

You have a long stored-procedure body or a static reference dataset stored in a .sql file outside your script folders, and you want to drop its contents into a migration script verbatim:

{
  "Name": "DataMigration",
  "ScriptTokens": {
    "ReferenceData": "<*File*>resources/reference-data.sql"
  }
}

In any script in this template:

-- The next line will be replaced with the
-- entire contents of resources/reference-data.sql
{{ReferenceData}}

Example — embed binary content as hex

For test images, certificate blobs, signing keys; anything you need to inject as binary into a column at deployment time:

{
  "ScriptTokens": {
    "DefaultLogo": "<*BinaryFile*>resources/logo.png"
  }
}

INSERT dbo.BrandAssets(Name, Image) VALUES('Default', {{DefaultLogo}});

The token resolves to the platform-appropriate binary literal form automatically, chosen from the product's Platform:

• SQL Server: 0x89504E47... (VARBINARY literal)
• MySQL: 0x89504E47... (BLOB literal)
• PostgreSQL: E'\\x89504E47...'::bytea (BYTEA literal with escape-string and explicit cast)

The same <*BinaryFile*> token works across all three engines with no per-environment editing. The resolver reads the file once and emits the correct literal form for the target; your SQL script just sees {{DefaultLogo}} land as whatever that engine will accept.

Example — query the target server for a value

Your deployment script needs a value from the target database itself: a tenant ID, a feature flag, the next batch number, the result of a row count check. Resolve it at deployment time, before any of your scripts run, against the actual server you're deploying to.

• SQL Server
• PostgreSQL
• MySQL

{
  "ScriptTokens": {
    "ActiveTenants": "<*Query*>SELECT TenantId FROM dbo.Tenants WHERE Active = 1"
  }
}

-- After resolution, {{ActiveTenants}} contains one tenant ID per line
EXEC dbo.ProvisionAuditTables @TenantIdList = '{{ActiveTenants}}';

{
  "ScriptTokens": {
    "ActiveTenants": "<*Query*>SELECT tenant_id FROM public.tenants WHERE active = true"
  }
}

-- After resolution, {{ActiveTenants}} contains one tenant ID per line
CALL public.provision_audit_tables('{{ActiveTenants}}');

{
  "ScriptTokens": {
    "ActiveTenants": "<*Query*>SELECT `TenantId` FROM `Tenants` WHERE `Active` = 1"
  }
}

-- After resolution, {{ActiveTenants}} contains one tenant ID per line
SET @TenantIdList = '{{ActiveTenants}}';
CALL ProvisionAuditTables(@TenantIdList);

Example — query body in a file

Long queries stay readable when they live in their own files. The token value points at the file; the file contents become the query.

{
  "ScriptTokens": {
    "DriftReport": "<*QueryFile*>queries/drift-report.sql"
  }
}

Example — embed one table's JSON

When you only need one table's metadata in a script, not the whole template, the specific-table tag is the surgical option. The token value names one table; the resolved content is that table's full JSON, ready to hand to a stored procedure that introspects columns, indexes, or custom metadata.

• SQL Server
• PostgreSQL
• MySQL

{
  "ScriptTokens": {
    "OrdersTable": "<*SpecificTable*>dbo.Orders"
  }
}

DECLARE @TableJson NVARCHAR(MAX) = '{{OrdersTable}}';
EXEC dbo.GenerateAuditTriggerForTable @TableJson;

{
  "ScriptTokens": {
    "OrdersTable": "<*SpecificTable*>public.orders"
  }
}

DO $$
DECLARE
  v_table_json TEXT := '{{OrdersTable}}';
BEGIN
  CALL public.generate_audit_trigger_for_table(v_table_json);
END $$;

{
  "ScriptTokens": {
    "OrdersTable": "<*SpecificTable*>Orders"
  }
}

SET @TableJson = '{{OrdersTable}}';
CALL GenerateAuditTriggerForTable(@TableJson);

The same pattern works for indexed views (<*SpecificIndexedView*>, SQL Server) and materialized views (<*SpecificMaterializedView*>, PostgreSQL).

Resolution order for advanced tags

1. File tags (<*File*>, <*BinaryFile*>, <*QueryFile*>) are resolved first, against the product directory.
2. Specific-object tags (<*SpecificTable*>, <*SpecificIndexedView*>, <*SpecificMaterializedView*>) are resolved against the loaded template.
3. Query tags (<*Query*>, <*QueryFile*> after its file is loaded) are deferred until just before each script runs and execute against the open deployment connection.
4. After all resolution, the remaining {{TokenName}} substitutions are performed in your script content.

If a file is missing, a query fails, or a specific table can't be found, the deployment stops with a clear error; you find out at the start, not after a half-applied database. Safe by default.

Override product token values using environment variables. The naming pattern is:

SmithySettings_ScriptTokens__TokenName=Value

Note the prefix SmithySettings_ (single underscore) and the double underscore __ before the token name. This follows the .NET configuration environment variable convention.

• Windows
• macOS / Linux

rem Windows (cmd)
set SmithySettings_ScriptTokens__RegistryDb=Registry_CI
set SmithySettings_ScriptTokens__MigrationVersion=1.0.1-ci.42

rem Windows (PowerShell)
$env:SmithySettings_ScriptTokens__RegistryDb = "Registry_CI"
$env:SmithySettings_ScriptTokens__MigrationVersion = "1.0.1-ci.42"

export SmithySettings_ScriptTokens__RegistryDb="Registry_CI"
export SmithySettings_ScriptTokens__MigrationVersion="1.0.1-ci.42"

Like config-level overrides, environment variable overrides only apply to tokens that already exist in Product.json. See Configuration for the full settings-and-environment layering.

Four scenarios that tie the mechanics together. Each shows the same token shape across SQL Server, PostgreSQL, and MySQL; the SQL dialect on each tab reflects what that engine expects.

Multi-environment deployment

A SaaS product uses tokens to manage database names and version stamps across development, staging, and production. Product.json defines the baseline:

{
  "Name": "SaasProduct",
  "TemplateOrder": ["Registry", "Client"],
  "ScriptTokens": {
    "RegistryDb": "Registry",
    "MigrationVersion": "3.2.0"
  }
}

A migration script announces the deployment target using the resolved tokens. The token shape is identical across platforms; only the logging statement differs.

• SQL Server
• PostgreSQL
• MySQL

PRINT 'Deploying {{ProductName}} '
    + '{{MigrationVersion}} against '
    + '{{RegistryDb}}';

DO $$ BEGIN
  RAISE NOTICE 'Deploying % % against %',
    '{{ProductName}}',
    '{{MigrationVersion}}',
    '{{RegistryDb}}';
END $$;

SELECT CONCAT(
  'Deploying {{ProductName}} ',
  '{{MigrationVersion}} ',
  'against {{RegistryDb}}'
) AS deployment_banner;

For staging, drop a ScriptTokens.RegistryDb override into SchemaQuench.settings.json and ship the same package. For CI, do the same via SmithySettings_ScriptTokens__RegistryDb. The schema package is unchanged across all three environments; only the resolved token values differ.

Cross-database references with multiple tokens

When templates need to reference sibling schema managed by other templates, product-level tokens keep the references consistent. The token pattern is the same across platforms; what the tokens encode differs because each engine isolates differently. SQL Server and MySQL use separate databases, PostgreSQL uses schemas within one database.

• SQL Server
• PostgreSQL
• MySQL

{
  "Name": "ECommerce",
  "TemplateOrder": ["Catalog", "Orders", "Reporting"],
  "ScriptTokens": {
    "CatalogDb": "ProductCatalog",
    "OrdersDb": "OrderProcessing"
  }
}

CREATE OR ALTER VIEW dbo.SalesSummary AS
SELECT o.OrderDate, p.ProductName, o.Quantity, o.Total
FROM [{{OrdersDb}}].dbo.Orders o
JOIN [{{CatalogDb}}].dbo.Products p ON o.ProductId = p.Id;

{
  "Name": "ECommerce",
  "TemplateOrder": ["Catalog", "Orders", "Reporting"],
  "ScriptTokens": {
    "CatalogSchema": "product_catalog",
    "OrdersSchema": "order_processing"
  }
}

CREATE OR REPLACE VIEW analytics.sales_summary AS
SELECT o.order_date, p.product_name, o.quantity, o.total
FROM {{OrdersSchema}}.orders o
JOIN {{CatalogSchema}}.products p ON o.product_id = p.id;

{
  "Name": "ECommerce",
  "TemplateOrder": ["Catalog", "Orders", "Reporting"],
  "ScriptTokens": {
    "CatalogDb": "product_catalog",
    "OrdersDb": "order_processing"
  }
}

CREATE OR REPLACE VIEW `analytics`.`SalesSummary` AS
SELECT o.`OrderDate`, p.`ProductName`, o.`Quantity`, o.`Total`
FROM `{{OrdersDb}}`.`Orders` o
JOIN `{{CatalogDb}}`.`Products` p ON o.`ProductId` = p.`Id`;

Generating audit DDL from {{TableSchema}}

A migration script that consumes the live table model to drive its own logic. The stored procedure walks the JSON, reads each table's columns and any Extensions.AuditScope you've attached, and emits the right CREATE TRIGGER statements: one declarative source of truth, one runtime that adapts to it.

• SQL Server
• PostgreSQL
• MySQL

DECLARE @TableSchema NVARCHAR(MAX) = '{{TableSchema}}';
EXEC dbo.GenerateAuditTriggers @TableSchema;

DO $$
DECLARE
  v_table_schema TEXT := '{{TableSchema}}';
BEGIN
  CALL public.generate_audit_triggers(v_table_schema);
END $$;

SET @TableSchema = '{{TableSchema}}';
CALL GenerateAuditTriggers(@TableSchema);

Pulling deployment-time data from the target

A Before migration script needs to know which rows on the server are flagged for the new feature. Point the token at a query file using <*QueryFile*> so the SQL stays readable, and SchemaSmith runs that query once against the actual server you're deploying to before substituting the result into every script that references the token.

The token definition is the same shape across platforms; the query body differs because each engine has its own catalog and quoting style.

• SQL Server
• PostgreSQL
• MySQL

{
  "ScriptTokens": {
    "EnabledTargets": "<*QueryFile*>queries/enabled-targets.sql"
  }
}

SELECT TargetName
FROM dbo.FeatureFlags
WHERE FlagName = 'NewBilling'
  AND Enabled = 1;

-- {{EnabledTargets}} resolves to one target per line
PRINT 'Applying billing schema to: {{EnabledTargets}}';

{
  "ScriptTokens": {
    "EnabledTargets": "<*QueryFile*>queries/enabled-targets.sql"
  }
}

SELECT target_name
FROM public.feature_flags
WHERE flag_name = 'NewBilling'
  AND enabled = true;

-- {{EnabledTargets}} resolves to one target per line
DO $$ BEGIN
  RAISE NOTICE 'Applying billing schema to: %', '{{EnabledTargets}}';
END $$;

{
  "ScriptTokens": {
    "EnabledTargets": "<*QueryFile*>queries/enabled-targets.sql"
  }
}

SELECT `TargetName`
FROM `FeatureFlags`
WHERE `FlagName` = 'NewBilling'
  AND `Enabled` = 1;

-- {{EnabledTargets}} resolves to one target per line
SELECT CONCAT('Applying billing schema to: ',
              '{{EnabledTargets}}') AS status;

Across these four scenarios, the token shape stays constant — {{TokenName}} in scripts, ScriptTokens in JSON — while the SQL dialect and the override surface flex per environment. The same schema package ships unchanged to development, staging, and production; the token layer is where each environment's shape gets expressed.