Defining Tables as JSON

Computed and generated columns

A generated column derives its value from an expression over other columns in the same row — the engine keeps it current on every insert and update, with no triggers and no application-layer logic. SQL Server uses ComputedExpression; PostgreSQL and MySQL use GenerationExpression. Whether that value is stored on disk or recomputed on every read is a per-platform choice — see each tab below.

• SQL Server
• PostgreSQL
• MySQL

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

{
  "Name": "full_name",
  "DataType": "TEXT",
  "Generated": "ALWAYS",
  "GenerationExpression": "first_name || ' ' || last_name",
  "Nullable": false
}

{
  "Name": "full_name",
  "DataType": "VARCHAR(255)",
  "GenerationExpression": "CONCAT(first_name, ' ', last_name)",
  "Nullable": false
}

Always Encrypted (SQL Server)

Always Encrypted lets SQL Server store sensitive column data in encrypted form that the server itself cannot read — only authorized clients holding the Column Master Key can decrypt. It is a SQL Server feature with no PostgreSQL or MySQL equivalent. SchemaTongs extracts encrypted columns and captures all three encryption properties; SchemaQuench declares them with the exact ENCRYPTED WITH (…) syntax SQL Server requires — extract once, deploy everywhere the CMK is distributed, no hand-written DDL. These three properties appear on SQL Server column definitions only and are ignored on other platforms.

When EncryptionType is not "NONE", SchemaQuench emits this clause into the column DDL:

ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [EncryptionKey],
    ENCRYPTION_TYPE = [EncryptionType],
    ALGORITHM = '[EncryptionAlgorithm]'
)

{
  "Name": "[SSN]",
  "DataType": "NVARCHAR(11)",
  "Nullable": false,
  "Collation": "Latin1_General_BIN2",
  "EncryptionType": "DETERMINISTIC",
  "EncryptionKey": "[CustomerCEK]",
  "EncryptionAlgorithm": "AEAD_AES_256_CBC_HMAC_SHA_256"
}

Changing encryption on a populated column is not supported in-quench. If you change EncryptionType, EncryptionKey, or EncryptionAlgorithm on a column that already has data — or add encryption to a column that was previously plaintext — SchemaQuench raises a hard error before any DDL runs, names the column, and leaves it untouched. This is a fundamental SQL Server constraint, not a tooling limitation: a standard server holds no Column Master Key and cannot re-encrypt data server-side. The guard fires in both live and WhatIf mode, so the constraint shows up in previews. The workaround is a full-table rebuild — a Before script that creates the new table with the target encrypted schema and copies rows over a Column Encryption Setting=Enabled connection (the driver handles decrypt/re-encrypt client-side), then an After script that recreates foreign keys, constraints, and extended properties. Run it in a maintenance window.