Schema Packages

What Is a Schema Package

A schema package is a folder (or a .zip file containing that folder) that holds everything needed to define, deploy, and maintain one or more databases. It contains:

JSON configuration files that identify the product and each target database template
JSON table definitions that describe every managed table in full detail
SQL script files organized into folders that map to specific phases of deployment
JSON schema files that enable CI validation of JSON configuration and table definitions

A schema package is a point-in-time artifact — a built snapshot of your table definitions and migration scripts, ready to be deployed through SchemaQuench.

A single schema package can target multiple databases. Each database is represented by a template within the package, and the product configuration controls which templates are deployed and in what order.

Folder Structure

The top-level folder contains Product.json and the Templates directory. Script folder names vary by platform, but the overall structure is consistent:

<ProductRoot>/
  Product.json
  .json-schemas/
    products.schema
    templates.schema
    tables.schema
  Templates/
    <TemplateName>/
      Template.json
      Tables/
      MigrationScripts/    (or Before Scripts/ + After Scripts/)
      Functions/
      Views/
      Procedures/
      Triggers/
      Table Data/
      ...additional platform-specific folders

Product.json

The root configuration file for the entire schema package. It declares the product name, the validation script used to confirm the target environment before deployment, the ordered list of templates, and product-level script tokens.

Template.json

Each template directory contains a Template.json that identifies which database this template targets, defines the script folder layout and their deployment phase assignments (quench slots), and sets template-level script tokens that override product-level tokens of the same name.

Tables/

One JSON file per managed table. Each file fully describes the table's columns, indexes, foreign keys, check constraints, and data delivery configuration. Files may be organized into subdirectories; SchemaQuench reads them recursively.

Script Folders

Script folders contain .sql files that SchemaQuench executes during specific deployment phases (quench slots). The assignment of a folder to a quench slot is declared in Template.json, not by folder name alone. Files within each folder execute in alphabetical path order.

.json-schemas/

Three JSON Schema files validate the structure of Product.json, Template.json, and table definition files. Use them in CI pipelines to catch configuration errors — required properties, correct value types — before deployment. Generated automatically by SchemaTongs and SchemaHammer. No runtime effect.

How Tools Interact with Schema Packages

Tool	Role	Read / Write
SchemaTongs	Creates and updates packages from an existing database. On re-extraction, preserves custom properties and data delivery configuration.	Read + Write (folder only)
SchemaQuench	Reads packages and applies their definitions to a target database. Compares desired state against current state and makes necessary changes. Strictly a consumer.	Read only (folder or zip)
DataTongs	Extracts data from a source database and updates table JSON files with data delivery configuration. Generates content files and scripts in the Table Data folder.	Read + Write (folder only)
SchemaHammer	Visual interface for browsing and editing packages. Can create new packages from scratch, edit JSON and SQL files, and apply changes to a configured server.	Read + Write (folder only)

Zip Package Support

Schema packages can be distributed as .zip files. Point SchemaPackagePath at the .zip file instead of a folder and SchemaQuench handles the rest transparently.

Read-only — the archive is opened once at initialization and all reads happen from in-memory contents.
Case-insensitive path matching — file lookups inside the zip ignore case for cross-platform compatibility.
Path normalization — backslashes are converted to forward slashes, leading and trailing slashes are stripped.
Folder-only for authoring tools — SchemaTongs, DataTongs, and SchemaHammer always write to folders. Zip the output afterward if needed for distribution.

First Run vs Subsequent Runs

SchemaQuench handles the same schema package differently depending on whether the target database is being set up for the first time or has already been deployed:

First Run (New Database)

All table definitions are treated as new — tables, columns, indexes, and constraints are created
All migration scripts run in quench-slot order (Before Scripts, then After Scripts)
Object scripts (functions, views, procedures, triggers) are applied fresh
Data delivery scripts execute if configured

Subsequent Runs (Existing Database)

Table definitions are compared against current state — only differences are applied
Previously completed migration scripts are skipped (tracked by checkpointing)
Object scripts are re-applied to ensure they match the package definition
Data delivery runs according to its configured merge strategy

This idempotent behavior means you can safely run SchemaQuench multiple times against the same database. Only actual differences result in changes.

Version Control Considerations

Schema packages are designed to be stored in version control. The folder structure maps cleanly to a repository layout:

One file per table — changes to different tables produce clean, reviewable diffs with minimal merge conflicts
Migration scripts accumulate — Before/After scripts build up over the project lifetime. Each runs once (tracked by checkpointing) and remains as historical record.
Script tokens externalize environment specifics — connection-specific values live in token overrides, keeping the package environment-neutral
.json-schemas/ optional — can be committed for CI validation or excluded (regenerated automatically by SchemaTongs)

Platform Differences

While the schema package concept is consistent across platforms, the default script folders vary based on each database engine's object types:

Feature	SQL Server	PostgreSQL	MySQL
Default script folders	13	16	8
Migration scripts	`MigrationScripts/Before/` + `After/`	`Before Scripts/` + `After Scripts/`	`Before Scripts/` + `After Scripts/`
Platform-specific objects	DDL Triggers, XML Schema Collections, Full-Text Catalogs/StopLists	Domain Types, Enum Types, Composite Types, Aggregates, Sequences, Rules	Events

Each platform has its own schema definitions and templates. A schema package built for one platform cannot be deployed to another.