Fan one schema package out to every tenant. Whether each tenant has its own database or its own schema in a shared one, you declare the shape once and a single quench deploys it everywhere.
By the SchemaSmith Team · Last reviewed
Every SaaS team hits the moment when the database model forces a choice: one database per tenant, or one schema per tenant inside a shared database.
Each path has real tradeoffs — license cost, operational isolation, backup granularity, deployment blast radius — and neither is universally correct.
What they share is the maintenance burden of keeping every tenant's database or schema synchronized as the product evolves. One table gets a new column. Now you replicate that change across dozens or hundreds of separate containers. A stored procedure gets a bug fix. Now you fan it out everywhere. The manual version of this job is exactly the kind of fragile, high-stakes operation SchemaSmith was designed to eliminate. SchemaSmith handles both patterns the same way it handles everything else: you declare the desired state once, and the tooling fans it out — one quench, every tenant, no manual scripting.
The database-per-tenant pattern gives each tenant their own isolated database. The schema is identical across tenants, but the blast radius of any individual deployment, backup, or restore operation is bounded to one customer. It's the right shape when your licensing model favors it, when tenants require regulatory isolation, or when per-tenant backup and point-in-time recovery are non-negotiable.
SchemaSmith handles this pattern through the DatabaseIdentificationScript returning multiple rows — one per tenant database. The template deploys to every returned database using the same schema package. No separate product definition per tenant. No manually maintained list of databases.
{
"Name": "TenantWorkspace",
"DatabaseIdentificationScript": "SELECT [name] FROM master.sys.databases WHERE [name] LIKE 'tenant_%' AND state_desc = 'ONLINE'"
}Every database that matches gets the full template treatment. Add MaxThreads to the SchemaQuench settings file and the deployments run in parallel up to the configured limit. Adding a tenant means adding a database; the next quench automatically picks it up. The rest of this page is about the other path.
When one database holds all your tenants — each in their own schema — you're working with the schema-per-tenant pattern. The advantage over database-per-tenant is operational density: fewer database instances to manage, no per-tenant licensing cost on most engines, simpler backups at the database level. The tradeoff is that tenants share the same database process and can't be independently backed up or restored at the tenant granularity.
What you need to manage this well is a way to declare the per-tenant shape once and deploy it consistently across every schema. That's exactly what a schema template does.
A schema template is a regular Template.json with one extra field: SchemaIdentificationScript. Where DatabaseIdentificationScript selects target databases, SchemaIdentificationScript selects target schemas inside one database. SchemaQuench runs the full template once per returned row — tables, procedures, views, migrations, data delivery — with the active schema name available as {{SchemaName}} everywhere it runs. One template. Every active schema. One quench.
The TenantCRM demo is the canonical example. It ships with both SQL Server and PostgreSQL builds; the walkthrough below references the SQL Server version, and the PostgreSQL version is structurally identical. Both live in the SchemaSmith demos.
Start with two tenants already onboarded (the onboarding step is covered below). With tenant_acme and tenant_beta in dbo.Tenants, deploy TenantCRM:
schemaquench `
--SchemaPackagePath ./Demos/SqlServer/TenantCRM `
--Target:Server localhost `
--Target:User sa `
--Target:Password <yourpassword>schemaquench \
--SchemaPackagePath ./Demos/SqlServer/TenantCRM \
--Target:Server localhost \
--Target:User sa \
--Target:Password <yourpassword>For PostgreSQL, point --SchemaPackagePath at ./Demos/PostgreSQL/TenantCRM and set --Target:User to postgres. The deployment log shows three templates running in sequence:
Quenching Template: Initialize
[localhost].[TenantCRM] Successfully Quenched
Quenching Template: Shared
[localhost].[TenantCRM] Successfully Quenched
Quenching Template: TenantWorkspace
[localhost].[TenantCRM] [Schema: tenant_acme] Begin Quench
[localhost].[TenantCRM] [Schema: tenant_beta] Begin Quench
[localhost].[TenantCRM] [Schema: tenant_acme] Successfully Quenched
[localhost].[TenantCRM] [Schema: tenant_beta] Successfully Quenched
Completed quench of TenantCRMInitialize runs once and self-selects out after the database exists. Shared runs once, deploying the tenant directory, plans, countries, audit log, and the onboarding procedure. TenantWorkspace runs once per active tenant. With AllowParallel: true (the SQL Server default), the two iterations overlap — note tenant_acme and tenant_beta both begin before either completes.
After the quench, both tenant_acme and tenant_beta have the full workspace schema: Customers, Contacts, Activities, ActivityTypes, AddCustomer, RecordActivity, ActiveCustomers, and the vw_ActiveCustomerCount indexed view. The Migration_001_BackfillCustomerCountryCode Before-slot script ran once per tenant and was recorded in SchemaSmith.CompletedMigrationScripts with the (TenantWorkspace, tenant_acme) and (TenantWorkspace, tenant_beta) keys. A subsequent quench skips those migrations without any configuration change. To run it hands-on, see the TenantCRM demo.
Inside a schema template, {{SchemaName}} resolves to the active tenant schema for the current iteration — use it in script bodies to qualify every object reference that belongs to the tenant: table inserts, procedure calls, view definitions, trigger bodies. Unqualified references work on SQL Server within an individual procedure context, but making the schema explicit is better practice and is required on PostgreSQL (see the callout below).
The AddCustomer procedure from TenantWorkspace/Procedures/AddCustomer.sql shows the pattern:
CREATE OR ALTER PROCEDURE [{{SchemaName}}].[AddCustomer]
@CustomerName NVARCHAR(128),
@Email NVARCHAR(256) = NULL,
@CountryCode CHAR(2) = NULL,
@CustomerID INT = NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [{{SchemaName}}].[Customers] ([CustomerName], [Email], [CountryCode])
VALUES (@CustomerName, @Email, @CountryCode);
SET @CustomerID = SCOPE_IDENTITY();
INSERT INTO [dbo].[GlobalAuditLog] ([TenantName], [EventType], [Detail])
VALUES (N'{{SchemaName}}', N'CustomerAdded',
N'CustomerID=' + CAST(@CustomerID AS NVARCHAR(16)) +
N'; Name=' + @CustomerName);
END;CREATE OR REPLACE PROCEDURE "{{SchemaName}}".add_customer(
p_customer_name VARCHAR(128),
p_email VARCHAR(256) DEFAULT NULL,
p_country_code CHAR(2) DEFAULT NULL,
INOUT p_customer_id INTEGER DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO "{{SchemaName}}".customers (customer_name, email, country_code)
VALUES (p_customer_name, p_email, p_country_code)
RETURNING customer_id INTO p_customer_id;
INSERT INTO public.global_audit_log (tenant_name, event_type, detail)
VALUES ('{{SchemaName}}', 'CustomerAdded',
'customer_id=' || p_customer_id::TEXT || '; name=' || p_customer_name);
END;
$$;[{{SchemaName}}] qualifies both the procedure itself and the Customers insert. [dbo].[GlobalAuditLog] is hard-coded with the dbo prefix — it's a shared table that spans tenants, so the cross-schema reference is explicit and fixed. When SchemaQuench deploys this procedure for tenant_acme, every {{SchemaName}} resolves to tenant_acme. The procedure that lands in the database is a fully-qualified, unambiguous definition scoped to that tenant's schema.
{{SchemaName}} is available in the procedure body as a plain string value — it's a script token, so it substitutes as text before the script executes. That's what lets you use it in a VALUES clause as a tenant identifier string, not just as a schema qualifier. For the full token reference, see Script Token Mechanics.
Do not rely on search_path for unqualified references inside schema-template procedures. search_path is a session-level setting and its interaction with stored procedures on PostgreSQL is subtle — functions and procedures created without a fixed SET search_path can resolve objects against the caller's path at invocation time, not the author's intent. Always qualify tenant-scoped references with "{{SchemaName}}". explicitly, as the TenantCRM demo does throughout.
Shared dimension tables — countries, plans, global audit log — belong to dbo (SQL Server) or public (PostgreSQL) and are deployed by the Shared template. Per-tenant tables reference them. That's a foreign key crossing the schema boundary.
The Customers table in TenantWorkspace/Tables/Customers.json demonstrates the pattern:
{
"Name": "[Customers]",
"Columns": [ ... ],
"ForeignKeys": [
{
"Name": "[FK_Customers_Countries]",
"Columns": "[CountryCode]",
"RelatedTableSchema": "[dbo]",
"RelatedTable": "[Countries]",
"RelatedColumns": "[Code]"
}
]
}RelatedTableSchema: "[dbo]" is the key. Without it, SchemaQuench assumes the related table is in the same schema as the declaring table — for a schema template, that would mean tenant_acme.Countries, which doesn't exist. The explicit RelatedTableSchema overrides that assumption and the FK is created correctly from tenant_acme.Customers.CountryCode to dbo.Countries.Code. The same mechanism works for procedures: RecordActivity.sql references [dbo].[GlobalAuditLog] directly in its body — a hard-coded cross-schema reference, correct and intentional.
Cross-schema FKs from a schema template to a Shared template's tables work correctly, but the Shared template must come before the schema template in TemplateOrder. Tables in Shared must exist before TenantWorkspace tries to create FKs that reference them. In TenantCRM/Product.json, TemplateOrder is ["Initialize", "Shared", "TenantWorkspace"] — this ordering is the guarantee. Never deploy the schema template before the template it references.
There are three ways to bring a new tenant into the system. Choose the one that fits your operational style.
Call dbo.OnboardTenant (SQL Server) or public.onboard_tenant (PostgreSQL) — the canonical atomic CREATE-SCHEMA-plus-INSERT pattern. The procedure creates the schema and inserts the tenant row in a single transaction — there's no window where a tenant row exists without a schema or vice versa. This is what the TenantCRM demo ships:
-- SQL Server
EXEC dbo.OnboardTenant
@Name = N'tenant_acme',
@DisplayName = N'Acme Corporation',
@PlanID = 2;
-- PostgreSQL
CALL public.onboard_tenant('tenant_acme', 'Acme Corporation', 2);After the call, re-quench. TenantWorkspace discovers the new tenant via SchemaIdentificationScript, finds the schema already exists (because OnboardTenant created it), and runs the full iteration: tables, procedures, views, migrations, data delivery. The tenant is fully provisioned in one operation.
A DBA creates the schema independently — CREATE SCHEMA tenant_acme — then inserts the tenant row via SQL, perhaps into an orchestration table that feeds your discovery query. The next quench picks up the new tenant. This works well when tenant provisioning goes through a separate workflow and the DBA prefers to own the schema creation step.
When CreateSchemaIfMissing: true, the iteration pipeline creates any schema returned by SchemaIdentificationScript that doesn't yet exist before running the iteration. Useful for CI environments, local development, and any environment where the deployment user has CREATE SCHEMA permission and manual pre-create adds friction without benefit.
CreateSchemaIfMissing defaults to false. The default is deliberate. A typo in your discovery query — WHERE Status = 'Actve' accidentally becoming WHERE Status = 'Acte' or a wrong join returning extra rows — can silently create schemas you didn't intend. In production, prefer the OnboardTenant helper or manual pre-create so that tenant schema creation is always an explicit, audited act. Reserve CreateSchemaIfMissing: true for environments where it's safe to auto-provision and you trust the discovery query fully.
CreateSchemaIfMissing: true requires the deployment user to have CREATE SCHEMA permission on SQL Server, or CREATE on the database on PostgreSQL. If the deployment user runs with least-privilege credentials, this setting may require a privilege grant separate from the credentials used for table and procedure deployment.
When a deployment fails at 2am, the first question is which tenant. The [Schema: <name>] prefix in the deployment log is how you find out — every log line that scopes to a specific tenant carries it: begin, success, individual object actions, migration tracking writes, error messages.
[localhost].[TenantCRM] [Schema: tenant_acme] Begin Quench
[localhost].[TenantCRM] [Schema: tenant_beta] Begin Quench
[localhost].[TenantCRM] [Schema: tenant_acme] Successfully Quenched
[localhost].[TenantCRM] [Schema: tenant_beta] Successfully QuenchedWith AllowParallel: true (the default, used by both TenantCRM demos), iterations interleave. The log above shows both tenants beginning before either completes. Timestamps on the log lines resolve ambiguity if you need to trace a specific event.
With AllowParallel: false, iterations run serially — one tenant's lines complete before the next begins. Reach for it when you want 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.
If one tenant's iteration fails and ContinueOnSchemaFailure: true is set, the failed tenant surfaces a per-iteration error line and the rest of the tenants continue. The exit code for the quench reflects the failure, but the other tenants are unaffected.
WhatIf output for large-N schema-template deployments is verbose — one full diff section per tenant, in the tenant's schema. For a product with 50 or 100 tenants, the WhatIf log covers the same changes repeated across every iteration. WhatIf is most useful here during development and debugging of a new migration, where you want to confirm the generated SQL for one or two tenants before committing to the full fan-out. We'll iterate on summarization as real usage patterns emerge.
Both patterns deploy from the same schema package format. The choice is a database-design and operational decision, not a tooling one.
| Database-per-tenant | Schema-per-tenant | |
|---|---|---|
| License cost | One license per tenant (SQL Server Standard/Enterprise) | One license total |
| Operational isolation | Full — each tenant has their own process space | Shared — tenants share one database process |
| Backup granularity | Per-tenant backup and point-in-time restore | Database-level backup only |
| Deployment blast radius | Bounded to one tenant | Full database on failure (mitigated by ContinueOnSchemaFailure) |
| Tenant onboarding cost | Provision a new database | Create a schema and a row |
| MySQL support | Yes | No — MySQL has no schema-inside-database concept |
| Best fit when | Regulatory isolation required; per-tenant SLAs; SQL Server licensing is per-instance | High tenant count; per-tenant license cost is prohibitive; operational simplicity matters |
Neither pattern is better in the abstract. The four tradeoffs — license cost, operational isolation, backup granularity, blast radius — usually pick the winner before you consult any other factor.
Once the per-tenant shape is governed by a schema template, the next operational question is which tenants live where. A single SaaS product often runs in several regions — US-East, EU-West, APAC — with a different tenant list in each. The same canonical package should ship to all of them; what should differ is the per-region tenant roster.
This is what Target.TemplateTargets is for. The package stays one source of truth. Each region's SchemaQuench settings file declares which tenants belong on that target, and SchemaQuench reconciles existence — optionally provisioning what's missing — before deploying. New tenants onboard by editing the settings file and re-running the same package.
Author the template the way you would for a normal schema-per-tenant fan-out, but use the validator-recommended placeholder script that returns no rows. The placeholder marks the template as schema-fan-out without depending on a per-region tenant table:
{
"Name": "TenantWorkspace",
"SchemaIdentificationScript": "SELECT 'CONFIG-DRIVEN' AS SchemaName WHERE 1=0"
}The placeholder is the signal that the universe lives in settings, not in a discovery query. Everything else in the template — tables, procedures, views, migration scripts — looks identical to the schema-template authoring shown earlier in this chapter.
In Region A's SchemaQuench.settings.json, declare the regional tenant roster under Target.TemplateTargets:
{
"Target": {
"TemplateTargets": {
"TenantWorkspace": {
"Schemas": ["tenant_acme", "tenant_globex", "tenant_initech"],
"CreateIfMissing": true
}
}
}
}CreateIfMissing: true says: provision any schema in this list that doesn't yet exist on the target. The package and the settings together declare the desired state; SchemaQuench reconciles it.
The first SchemaQuench run against a fresh Region A target finds zero of the three schemas. With CreateIfMissing: true, SchemaQuench issues idempotent CREATE SCHEMA DDL for each missing schema (CREATE SCHEMA IF NOT EXISTS "tenant_acme" on PostgreSQL, the sys.schemas-guarded EXEC('CREATE SCHEMA [tenant_acme]') pattern on SQL Server) and then deploys the full template into each one. A single quench creates the rosters AND populates them.
The next run finds all three schemas already present. SchemaQuench skips the create step (the DDL is idempotent regardless) and deploys the template's current shape into each tenant. The same package that brought a fresh region online now keeps every existing tenant's schema synchronized with the latest model — no separate "provisioning script" pipeline, no second tool, no manual reconciliation.
A new tenant signs up in Region A. Edit the Region A settings file:
{
"Target": {
"TemplateTargets": {
"TenantWorkspace": {
"Schemas": ["tenant_acme", "tenant_globex", "tenant_initech", "tenant_newco"],
"CreateIfMissing": true
}
}
}
}Commit the change to source control. The next CI deployment finds the three existing schemas already populated and one missing — tenant_newco — provisions it, deploys the full template into it, and continues. Onboarding is a one-line edit and a normal deployment. Nothing about the package itself changed. Other regions are unaffected: their settings files declare their own rosters, and their own deployments reconcile only against their own targets.
The pattern composes with the database axis too. TemplateTargets.<template>.Databases declares per-template database fan-out the same way; with CreateIfMissing: true, SchemaQuench provisions missing databases (using the engine's admin connection) before deploying. Pure-database-fan-out templates that don't need schema iteration — such as a per-tenant shared-services bundle — can use just Databases, and database-per-tenant deployments on MySQL use this exclusively.
If you already have hand-replicated schemas — a tenant_acme schema and a tenant_beta schema that were originally created by copying SQL scripts — SchemaTongs can cast the first one into a schema template and rewrite the cross-schema references for you.
Point SchemaTongs at one of your existing tenant schemas using Source.Schema:
{
"Source": {
"Schema": "tenant_acme"
},
"Template": {
"SchemaIdentificationScript": "SELECT [Name] FROM dbo.Tenants WHERE [Status] = N'Active'"
}
}SchemaTongs extracts the schema's tables and procedures, then replaces qualified references to tenant_acme. in SQL bodies with {{SchemaName}}., and sets RelatedTableSchema on any FK that points to a shared schema like dbo. The resulting package is a schema template — ready to deploy and govern every tenant's schema from one source. The full mechanics live in the SchemaTongs reference.
MySQL does not have a schema-inside-database concept that maps to the pattern described on this page. A MySQL "database" and a MySQL "schema" are the same thing — CREATE DATABASE and CREATE SCHEMA are synonyms. There is no way to create isolated object namespaces inside a single MySQL database.
Multi-tenant deployments on MySQL use database-per-tenant: one database per tenant, DatabaseIdentificationScript returning every tenant database name. Everything described in the Database-per-tenant section applies. The schema-template fields (SchemaIdentificationScript, CreateSchemaIfMissing, AllowParallel, ContinueOnSchemaFailure) are supported on SQL Server and PostgreSQL only.
If you're deploying TenantCRM or a similar multi-tenant product to MySQL, model each tenant as their own database. The DatabaseIdentificationScript returning a list of tenant databases is the direct equivalent — one iteration per tenant, the same schema package fanned out across all of them.