SchemaSmith Concept

Defining Tables as JSON

Every table in SchemaSmith is a JSON file inside your schema package's Tables folder, declaring the desired state of that table. SchemaQuench computes the DDL to get there.

Pick your platform Shared properties Columns Indexes
JSON schema files defining database tables with columns, indexes, and constraints

Every table definition file declares exactly one table.

How SchemaSmith models tables

In SchemaSmith, your database schema lives as declarative JSON files, not as a collection of ALTER scripts or numbered migrations. Every table is one JSON file. That file describes the table as it should be: its name, schema namespace, columns, indexes, constraints, foreign keys, check expressions, and — depending on your platform — platform-specific features like XML indexes, full-text indexes, or statistics. There's no hand-written DDL, no rollback files, no version numbers. You shape the JSON; SchemaQuench reads it and computes the minimal set of DDL statements needed to transform your live database into the declared state.

The process is straightforward. You create a schema package — a directory tree with your JSON table definitions — and pass it to SchemaQuench. SchemaQuench diffs your live database against the JSON declarations, generates the necessary CREATE or ALTER statements, and executes them in order. On SQL Server, you declare columns with INT IDENTITY(1, 1) syntax; on PostgreSQL, you use INTEGER GENERATED ALWAYS AS IDENTITY; on MySQL, INT AUTO_INCREMENT. The JSON structure is identical across all three platforms — only the data types, quoting styles, and platform-native features change. You build the same mental model for schema definition across SQL Server, PostgreSQL, and MySQL; the file format doesn't shift, only the dialect inside it does.

Tables aren't defined in isolation. The JSON arrays in each table file capture the full picture: Columns (with nullable flags, defaults, and check expressions), Indexes (with clustering and uniqueness directives), CheckConstraints (table-level business rules), and ForeignKeys (referential integrity). On platforms that support them, you also declare XMLIndexes, FullTextIndexes, and Statistics (SQL Server); ExcludeConstraints, sequences, composite types, and enums (PostgreSQL); or Engine and Collation settings (MySQL). SchemaQuench reads these arrays, aligns them against the live database, and generates the diff. Run the same package against dev, staging, and production and you get the same predictable deployment every time.

Two cross-cutting features get their own homes: DataDelivery declares how a table's reference rows land in each target database, and conditional application uses ShouldApplyExpression to scope tables, columns, indexes, and constraints to specific environments or server versions. Both appear as properties on the shared table JSON structure below.

Important: same shape, platform-native contents

All three platforms use the same JSON table structure, but the contents are platform-specific. Data types differ: SQL Server's INT IDENTITY, PostgreSQL's GENERATED AS IDENTITY, MySQL's AUTO_INCREMENT. Quoting differs: SQL Server uses [brackets], PostgreSQL uses bare names or "double quotes", MySQL uses `backticks` or bare names. A SQL Server dbo.Customers.json is not a PostgreSQL public.customers.json — the file structure rhymes, but the contents are platform-specific. Each platform gets its own schema package.

Table JSON format — shared properties

Every table definition file declares exactly one table. The shared properties below appear on every platform; platform-specific extensions follow in the next section.

Property Type Default Required Description
Name string Yes Table name. Use the platform's quote style: [Customer] (SQL Server), "customer" (PostgreSQL), `customer` (MySQL). Bare names are also accepted.
Columns array [] Yes Column definitions. See Columns.
Indexes array [] No Index and constraint definitions. See Indexes.
ForeignKeys array [] No Foreign key definitions. See Foreign keys.
CheckConstraints array [] No Table-level check constraint definitions. See Check constraints.
ShouldApplyExpression string No SQL expression evaluated at quench time. If it returns false (or 0), the entire table is skipped on this database. Tokens are resolved before evaluation. See Conditional Application.
OldName string "" No Previous table name. When set, the table is renamed during quench. Clear after the rename has been deployed everywhere.
DataDelivery object null No Declarative data delivery configuration for this table. See Data delivery.
Extensions object null No Open metadata bag. See Custom Properties.

The ShouldApplyExpression field appears on tables, columns, indexes, foreign keys, check constraints, indexed views, materialized views, and several platform-specific components. Wherever it appears, it works the same way: the engine resolves tokens, runs the expression against the target database, and skips the component if the result is falsy.

Platform-specific table properties

Each platform's table definition extends the shared properties with engine-specific fields. SchemaSmith models each variant as a platform-typed wrapper object; a schema package ships one file per table, written for exactly one platform.

Platform Wrapper object Engine-specific properties
SQL Server SqlServerTable Data compression, temporal tables, XML indexes, statistics, full-text indexes, change data capture, fill-factor reconciliation.
PostgreSQL PostgreSqlTable Extended statistics, exclude constraints, row-level security, storage access methods, persistence overrides, fill factor.
MySQL MySqlTable Storage engine, row format, character set, collation, table comment, auto-increment seed, full-text indexes.

See the platform-specific reference

Each wrapper appears in worked examples on the platform's daily workflows page: SQL Server, PostgreSQL, MySQL.

Columns

Every entry in the Columns array defines one column. The shared shape is small; platform-specific column subclasses add fields where the engine genuinely differs.

Shared column properties

Property Type Default Description
Name string Column name.
DataType string Platform-appropriate data type with precision/scale/length. SQL Server: NVARCHAR(50), INT IDENTITY(1,1). PostgreSQL: VARCHAR(50), INTEGER GENERATED ALWAYS AS IDENTITY. MySQL: VARCHAR(50), INT AUTO_INCREMENT.
Nullable bool false Whether the column allows NULL.
Default string Default constraint expression — e.g., getdate() (SQL Server), now() (PostgreSQL), CURRENT_TIMESTAMP (MySQL).
ShouldApplyExpression string Conditional inclusion. See Conditional Application.
OldName string "" Previous column name for rename detection. Clear after the rename has deployed everywhere.
Extensions object null Custom metadata for this column. See Custom Properties.

User-defined types

When a database uses user-defined types (CREATE TYPE / CREATE DOMAIN), the DataType value is the type name. The type must be created in the appropriate types script folder (DataTypes/ on SQL Server; Domain Types/, Enum Types/, or Composite Types/ on PostgreSQL) before the table quench runs.

Computed and generated columns

SQL Server uses ComputedExpression; PostgreSQL and MySQL use GenerationExpression with the platform's syntax. A minimal SQL Server example:

{
  "Name": "FullName",
  "ComputedExpression": "[FirstName] + ' ' + [LastName]",
  "Persisted": true
}

See the platform-specific reference

Each platform adds engine-specific column fields — collation, data masking, generated-column syntax, character sets. Worked examples live on the SQL Server, PostgreSQL, and MySQL daily workflows pages.

Indexes

Every entry in the Indexes array defines an index or key constraint on the table. The shared shape covers the common cases; per-platform index types extend it.

Shared index properties

Property Type Default Description
Name string Index or constraint name.
PrimaryKey bool false true for a primary key constraint.
Unique bool false true for a unique index.
UniqueConstraint bool false true for a UNIQUE constraint (as opposed to a unique index).
IndexColumns string Comma-separated column names with optional sort direction.
IncludeColumns string Comma-separated INCLUDE / covering columns where supported.
FilterExpression string Filtered / partial index WHERE clause.
ShouldApplyExpression string Conditional inclusion. See Conditional Application.
Extensions object null Custom metadata. See Custom Properties.

Examples

SQL Server quoting; clustered primary key.

{
  "Name": "[PK_Product_ProductID]",
  "PrimaryKey": true,
  "Unique": true,
  "Clustered": true,
  "IndexColumns": "[ProductID]"
}

PostgreSQL filtered / partial index.

{
  "Name": "ux_customer_email_active",
  "Unique": true,
  "IndexColumns": "email",
  "FilterExpression": "is_active = true"
}

SQL Server covering index with INCLUDE columns.

{
  "Name": "[IX_Customer_Name]",
  "IndexColumns": "[LastName] ASC, [FirstName] ASC",
  "IncludeColumns": "[Email], [Phone]"
}

See the platform-specific reference

Each platform extends the shared index properties with engine-native features — clustered and columnstore indexes, access methods, index types, visibility flags. Worked examples live on the SQL Server, PostgreSQL, and MySQL daily workflows pages.

Foreign keys

Referential integrity lives in the table JSON alongside columns and indexes, not in separate migration scripts. The ForeignKeys array captures each relationship: the local columns, the related table they reference, and the optional cascade actions for deletes and updates. SchemaQuench reads the array, diffs it against the live database, and adds, drops, or recreates constraints to match.

Property Type Default Description
Name string Constraint name.
Columns string Comma-separated local column names.
RelatedTableSchema string platform default Schema of the referenced table (dbo on SQL Server, public on PostgreSQL, omitted on MySQL).
RelatedTable string Referenced table name.
RelatedColumns string Comma-separated referenced column names.
DeleteAction string null "NO ACTION", "CASCADE", "SET NULL", "SET DEFAULT", or "RESTRICT" where supported.
UpdateAction string null Same values as DeleteAction.
ShouldApplyExpression string Conditional inclusion. See Conditional Application.
Extensions object null Custom metadata. See Custom Properties.

For composite foreign keys, list all columns in both Columns and RelatedColumns in matching order.

Check constraints

Table-level check constraints sit in the CheckConstraints array. Used when CheckConstraintStyle is "TableLevel" in Product.json, or when a check constraint spans multiple columns.

Property Type Default Description
Name string Constraint name.
Expression string Boolean SQL expression.
ShouldApplyExpression string Conditional inclusion. See Conditional Application.
Extensions object null Custom metadata. See Custom Properties.

When CheckConstraintStyle is "ColumnLevel" (the default), single-column check constraints are written as CheckExpression on the column instead. Multi-column constraints always use the CheckConstraints array.

Last reviewed May 2026 by the SchemaSmith Team.

Pick your platform

Each platform reference covers the complete table JSON format, the DDL SchemaQuench emits, and the platform-native concepts it handles for that engine.

SQL Server

Walk through adding tables with INT IDENTITY, modifying columns, writing CREATE OR ALTER procedures, extracting drift with SchemaTongs, and bootstrapping new databases with the Initialize template.

SQL Server workflows

PostgreSQL

Walk through adding tables with GENERATED AS IDENTITY, modifying columns, writing CREATE OR REPLACE functions, extracting drift with SchemaTongs, and bootstrapping new databases with the Initialize template.

PostgreSQL workflows

MySQL

Walk through adding tables with AUTO_INCREMENT, modifying columns, writing procedures with DELIMITER //, extracting drift with SchemaTongs, and bootstrapping new databases with the Initialize template.

MySQL workflows