Defining Tables as JSON

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 on every supported platform — only the data types, quoting styles, and platform-native features change. You build the same mental model for schema definition on every supported engine; 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, FullTextIndex, 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.

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.

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.

MySQL and MariaDB carry a table's partition definition in the table DDL itself — there is no separate scheme object to point at, the way SQL Server has one — so the package carries the whole definition, declared through the table's Partitioning object.

It is applied when the table is created, and a change on a deployed table is refused. ALTER TABLE … PARTITION BY rewrites every row, and comparing two layouts cannot tell you whether a split or a merge was intended — so a declaration that disagrees with the deployed table names both and stops rather than attempting an ambiguous change.

{
  "Name": "`order_history`",
  "Columns": [
    { "Name": "`Id`", "DataType": "int", "Nullable": false },
    { "Name": "`Placed`", "DataType": "date", "Nullable": false }
  ],
  "Indexes": [
    { "Name": "PRIMARY", "PrimaryKey": true, "Unique": true, "IndexColumns": "`Id`" }
  ],
  "Partitioning": {
    "Method": "RANGE",
    "Expression": "Id",
    "Partitions": [
      { "Name": "p_early", "Values": "1000000" },
      { "Name": "p_rest",  "Values": "MAXVALUE" }
    ]
  }
}

The comparison ignores backticks, whitespace and case, because the engines do not agree on how they report a partition expression back: MySQL 5.7 returns the text you wrote, while MySQL 8, MariaDB 10.2 and MariaDB 11.4 all return a rewritten form. Without normalizing, the same package would look changed on one engine and unchanged on another.

MySQL requires every UNIQUE and PRIMARY KEY to contain every partitioning column — the engine's rule, not SchemaSmith's, and a definition breaking it fails with the engine's own error. Leaving Partitioning unset means SchemaSmith does not manage partitioning at all, so a table someone partitioned by hand is left exactly as it is.

Set EnableCDC to true and the table records its inserts, updates and deletes into a change table that SQL Server maintains. Downstream readers consume what happened instead of polling the live table for differences.

{
  "Name": "[Orders]",
  "Schema": "[dbo]",
  "EnableCDC": true
}

The column set you capture is locked in at the moment CDC is enabled. A table tracking three columns keeps capturing those three columns, whatever schema changes happen afterwards — which is what makes a schema change on a tracked table worth understanding.

CDC must be enabled on the database first, using sys.sp_cdc_enable_db. SchemaSmith does not do this for you, and that is deliberate: the call changes retention, cleanup jobs and storage for every table in the database, which is not a decision one table’s package should make. Enabling CDC at the database level is an administrator’s election, and the package respects that boundary.

Declare EnableCDC without running that command first and the table still deploys. Capture is reported as downgraded in the deploy log, the affected tables are named, and the command to enable it is printed — the outcome is reported, never silent. If the target’s unsupported-feature policy is set to fail, the deploy fails before touching any columns instead, and again names the tables.

When a deploy changes the columns of a tracked table, SchemaSmith creates a second capture instance covering the new column set while leaving the original instance and its captured history untouched. The command to remove the old instance is printed in the log, but removing it is your call, because only you know when your downstream readers have finished draining it. That command is sys.sp_cdc_disable_table.

Setting EnableCDC back to false disables capture on the table and drops its capture instances and their history. That is a deliberate opt-out, not a side effect of any schema change.