Declare identity and types native to each engine

🧪

Hands-on lab

Declare an auto-incrementing surrogate key and native-type columns on three engines — Orders (SQL Server), Catalog (PostgreSQL), Sessions (MySQL) — and verify that SchemaSmith preserves every engine's native encoding exactly as declared.

Demos/Learn/course9-module-02 →

You’ve got three engines in your fleet. You add a column on each one and the column names are the same — but the declarations aren’t. They don’t have to be. They shouldn’t be.

Hey folks. I’m Forge Barrett, master of the Content Forge here at SchemaSmith.

This is Module 2, and we’re slowing down on the fidelity side. Module 1 showed you the rhythm: same workflow, same deploy command, three independent packages. Here we pull the hood up and look at what’s under it. Because the DDL isn’t the same across engines — and if you’ve been treating it like it should be, you’ve been working around your engines instead of working with them.

Three engines. Three genuinely different native models. One tool that honors each of them. Let’s see what that actually means.

The thesis: native, not lowest-common-denominator

Every multi-engine tool faces a choice. Normalize everything to a common abstraction — one identity: true flag, one json type — and translate for each engine at deploy time. Or follow each engine’s native model and let the package look like it belongs in that engine.

SchemaSmith chose the second path. Each package is native to its engine, not a universal format that gets compiled down. That means the JSON you write for SQL Server looks like SQL Server. The JSON for PostgreSQL looks like PostgreSQL. MySQL looks like MySQL. No translation layer hiding what the engine is actually doing.

The payoff: you use each engine’s real strengths. The cost: the packages aren’t interchangeable. That’s not a bug — that’s the point.

Two places where this plays out most sharply: how each engine handles an auto-incrementing surrogate key, and how each engine handles a column you’d call “stores a JSON payload.” Three engines, three genuinely different native encodings for each concept. Let’s work through both.

Identity three ways: one concept, three native encodings

The concept is simple — an auto-incrementing integer primary key. The kind of thing every table of any substance has. You’d think this would be the easiest thing to normalize. It’s not, because the three engines didn’t implement it the same way, and SchemaSmith doesn’t pretend they did.

SQL Server: identity lives in the DataType

SQL Server bakes identity into the column’s type declaration. There’s no separate Identity property — it’s part of the type string itself. Here’s [EventId] on the [OrderEvent] table in the Orders package:

{
  "Name": "[EventId]",
  "DataType": "INT IDENTITY(1, 1)"
}

INT IDENTITY(1, 1) — start at 1, increment by 1. That’s the SQL Server native form. The seed and step values are right there in the DataType string, because that’s where SQL Server’s engine puts them.

There is no "Identity": true property. Don’t invent one. If you try it, it’ll be silently ignored or land in Extensions. The identity encoding is "DataType": "INT IDENTITY(1, 1)" and nothing else.

PostgreSQL: a separate Generated property

PostgreSQL’s identity columns are a different mechanism. The type is just int4 — a clean four-byte integer. The identity behavior comes from a separate Generated property. Here’s history_id on price_history in the Catalog package:

{
  "Name": "history_id",
  "DataType": "int4",
  "Generated": "GENERATED ALWAYS AS IDENTITY"
}

Two properties, cleanly separated. The Generated value is the PostgreSQL DDL syntax verbatim — GENERATED ALWAYS AS IDENTITY — because that’s what PostgreSQL uses and SchemaSmith models it exactly. You could also declare GENERATED BY DEFAULT AS IDENTITY if your table needs that behavior. The string maps straight to what PostgreSQL will execute.

MySQL: a boolean flag

MySQL takes yet another approach. The auto-increment behavior is a first-class boolean property on the column, separate from the type. Here’s `HitId` on `PageHit` in the Sessions package:

{
  "Name": "`HitId`",
  "DataType": "int",
  "AutoIncrement": true
}

No seed, no step, no syntax string. MySQL’s AUTO_INCREMENT is a boolean engine feature — on or off. SchemaSmith follows that: "AutoIncrement": true. The backtick-quoted name and lowercase "int" are MySQL conventions, and they’re there because this package is native to MySQL.

The divergence is the lesson

Same concept — a surrogate key that increments automatically. Three genuinely different native implementations:

Engine	How identity is declared
SQL Server	In the DataType string: "DataType": "INT IDENTITY(1, 1)"
PostgreSQL	Separate property: "DataType": "int4" + "Generated": "GENERATED ALWAYS AS IDENTITY"
MySQL	Boolean flag: "DataType": "int" + "AutoIncrement": true

SchemaSmith generates the correct DDL for each one. SQL Server gets [EventId] INT IDENTITY(1, 1). PostgreSQL gets history_id int4 GENERATED ALWAYS AS IDENTITY. MySQL gets `HitId` int AUTO_INCREMENT. You declared into each engine’s native model. The tool honored it.

Why not normalize to one form? Because normalizing loses information. SQL Server’s IDENTITY has explicit seed and step values. PostgreSQL’s GENERATED clause has ALWAYS vs BY DEFAULT semantics. MySQL’s AUTO_INCREMENT interacts with the storage engine and the table’s character set. A single identity: true abstraction doesn’t carry those differences — it papers over them. Native encoding keeps every capability visible and controllable.

Native types: three ways to store a JSON payload

Now let’s look at types. Specifically: what does each engine give you when you want to store structured JSON data in a column?

This is where the engines diverge most visibly — and where the “NVARCHAR vs jsonb vs json” question from Module 1 gets its real answer.

SQL Server: NVARCHAR(MAX)

SQL Server doesn’t have a native JSON type. NVARCHAR(MAX) is the correct native form — it’s a Unicode character string with no length ceiling. Here’s [Detail] on [OrderEvent]:

{
  "Name": "[Detail]",
  "DataType": "NVARCHAR(MAX)"
}

That’s not a SchemaSmith workaround. That’s not a gap. That’s SQL Server’s native answer to “store arbitrary text/JSON.” SQL Server’s JSON support (JSON_VALUE, OPENJSON, FOR JSON PATH) works against NVARCHAR columns — the engine doesn’t require a specialized type to enable JSON operations. Declaring NVARCHAR(MAX) is the right call, and it’s the native one.

If you see NVARCHAR(MAX) in an OrderEvent column and wonder why there’s no jsonb equivalent — you’re looking for something that doesn’t exist in SQL Server. That’s the fidelity point: the package represents what the engine actually has.

PostgreSQL: jsonb and text[]

PostgreSQL has a native jsonb type — a binary representation of JSON that supports indexing, querying, and containment operators at the engine level. And it has native array types. Here’s attributes and tags on price_history:

{
  "Name": "attributes",
  "DataType": "jsonb"
}

{
  "Name": "tags",
  "DataType": "text[]"
}

Two native PostgreSQL types in one table. jsonb is a first-class JSON type with GIN index support and @> operators. text[] is a typed array — a PostgreSQL capability that has no direct equivalent in SQL Server or MySQL. SchemaSmith doesn’t simulate these or substitute a common denominator — it declares them natively because the Catalog package is a PostgreSQL package.

MySQL: json and enum

MySQL has a native json type (introduced in 5.7, validated by the engine at insert time) and native enum types. Here’s `Meta` and `Channel` on `PageHit`:

{
  "Name": "`Meta`",
  "DataType": "json"
}

{
  "Name": "`Channel`",
  "DataType": "enum('web','ios','android')",
  "CharacterSet": "utf8mb4"
}

json — MySQL’s native JSON storage with path expressions and JSON_EXTRACT. And enum('web','ios','android') — a MySQL-native type that enforces its value set at the engine level. Those lowercase values in the enum are not an accident: MySQL preserves enum values exactly as declared, case included. SchemaSmith preserves them exactly as you wrote them, because changing case would change the engine behavior.

The native-type picture side by side

“Store a JSON payload” — three engines, three answers:

Engine	JSON column	What the engine actually has
SQL Server	"DataType": "NVARCHAR(MAX)"	No native JSON type; NVARCHAR is correct and sufficient
PostgreSQL	"DataType": "jsonb"	Native binary JSON with index support
MySQL	"DataType": "json"	Native JSON with engine-level validation

Plus engine-specific bonus capabilities: PostgreSQL gives you text[] arrays. MySQL gives you enum('web','ios','android') with case-preserved values. Those don’t have a common equivalent, and SchemaSmith doesn’t fake one.

SQL Server’s NVARCHAR(MAX) is native fidelity, not a gap. The package could have used a fake nativejson abstraction that gets compiled to NVARCHAR(MAX). That would hide what’s happening. Instead, the package shows you what SQL Server actually stores — and that information is useful. When a DBA looks at an [OrderEvent] column and sees NVARCHAR(MAX), they immediately know the JSON support story for that engine. No abstraction to unpeel.

Deploying the three packages

Each package deploys with the same command pattern. Three independent runs — one per engine, one per connection string:

Orders / SQL Server:

schemaquench --ConfigFile:quench.settings.json --LogPath:"./logs"

Targeting the orders database on localhost,11433.

Catalog / PostgreSQL:

schemaquench --ConfigFile:quench.settings.json --LogPath:"./logs"

Targeting the catalog database on localhost:15432.

Sessions / MySQL:

schemaquench --ConfigFile:quench.settings.json --LogPath:"./logs"

Targeting the sessions database on localhost:13306.

Same command structure. Three independent runs. Each one goes clean and re-runs clean — no-op on the second pass. Convergence across all three engines, with native DDL each time.

What lands in the database

After deploy, the in-database reality matches the native declarations exactly:

• SQL Server — [EventId] is an IDENTITY column (seed 1, increment 1). [Detail] is nvarchar(MAX). The identity encoding you wrote into the DataType string is what the engine enforces.
• PostgreSQL — history_id is generated always as identity. attributes is jsonb. tags is text[]. The Generated property maps to a real engine-level identity sequence, and the jsonb column supports GIN indexing and JSON path operations out of the box.
• MySQL — `HitId` is auto_increment. `Meta` is json with engine-level JSON validation. `Channel` is enum('web','ios','android') with those exact lowercase values preserved. Re-run is a clean no-op on all three.

The native declarations you wrote produced native database objects. No translation artifacts. No least-common-denominator rounding.

Why native fidelity matters

Here’s the alternative: a tool that normalizes everything. One identity: true flag for all three engines. One json type that gets compiled to NVARCHAR(MAX) on SQL Server and json on MySQL and jsonb on PostgreSQL. Tidy, consistent, cross-engine.

And it costs you something. You lose the ability to declare GENERATED BY DEFAULT AS IDENTITY on PostgreSQL when you need it. You lose the IDENTITY seed and step values on SQL Server. You can’t declare text[] because the abstraction layer doesn’t know about PostgreSQL arrays. You can’t write enum('web','ios','android') because enums aren’t in the common model. The normalization layer trimmed your capabilities to fit the smallest common shape.

SchemaSmith’s bet is the opposite: each engine’s native capabilities are worth keeping. The packages aren’t interchangeable — but they weren’t supposed to be. An Orders database on SQL Server and a Catalog database on PostgreSQL are different services with different strengths. The tool should honor those strengths, not round them off.

Brackets vs lowercase vs backticks aren’t cosmetic. NVARCHAR and text and varchar(60) aren’t interchangeable. INT IDENTITY(1, 1) and int4 + GENERATED ALWAYS AS IDENTITY and int + AutoIncrement: true are three different native mechanisms for the same concept. The fidelity is in those differences, and the packages hold them exactly.

MariaDB — same family, its own metal

You select Platform: MariaDb and SchemaSmith emits native MariaDB DDL — it doesn’t retarget the MySQL package. MariaDB is its own platform in the MySQL family: same forge, its own metal.

It shares MySQL’s dialect, but diverges in two different ways — and they are worth telling apart.

Different syntax for the same thing. These are dialect differences; SchemaSmith emits the right native form for the platform you selected:

Feature	MySQL	MariaDB
Invisible indexes	CREATE INDEX ... INVISIBLE, read from IS_VISIBLE	No INVISIBLE keyword — CREATE INDEX ... IGNORED, read from the inverted INFORMATION_SCHEMA.STATISTICS.IGNORED ('YES' = ignored)
Dropping a CHECK constraint	ALTER TABLE ... DROP CHECK name	Rejects DROP CHECK — uses ALTER TABLE ... DROP CONSTRAINT name
Column-default reporting	Bare form in INFORMATION_SCHEMA.COLUMN_DEFAULT	Quotes string literals, marks no-default with a literal NULL, parenthesizes functions like current_timestamp() — SchemaSmith folds this back to MySQL’s canonical form so an unchanged column doesn’t phantom-modify every deploy

The same feature, arriving at a different version. These are not dialect differences at all — the capability simply lands in a different release on each engine, so the version you need is not the same:

Feature	MySQL	MariaDB
CHECK constraints	8.0.16	supported at the 10.2 floor — no version gate at all
Invisible indexes	8.0	10.6
Descending index key parts	8.0	10.8
Automatic data delivery	needs JSON_TABLE (8.0); 5.7 has no row source, so delivery is skipped	works at 10.2 — the rows are shredded through a recursive CTE instead

That second table is why “MariaDB is just MySQL” is a trap. On a 10.2 server you get CHECK constraints that a MySQL 5.7 server cannot give you, and you lose invisible indexes that MySQL 8.0 would have. The engine gates each feature on the platform’s own introduction version and degrades what the target can’t do, so the same package stays deployable either way — but the capability set genuinely differs.

You pick the platform; SchemaSmith emits the right native form automatically and handles the divergence for you. For the authoritative list, see the SchemaQuench reference.

What’s next

You’ve seen the fidelity axis: three native models, none of them averaged together, each one honored in the package that targets it.

Module 3 is about organizing these native services in practice — per-service repos, tables grouped into subfolders, and file-less connection config so credentials never touch source control. The declarations stay native. The structure gets leaner.

---

Three packages. Three metals. Each one shaped in its own native form because the alloy determines what you can build with it. A smith who knows iron doesn’t treat it like copper just because both conduct heat. Know your engine. Declare into it. Trust the tool to honor what you wrote.

Subscribe and stick around. Next time, we’ll forge into how to organize these native packages as they scale.

Until then, may your declarations hold the grain of every metal you work.

— Forge

Check yourself: SQL Server's [Detail] column is declared as NVARCHAR(MAX), not as a JSON type. Does this mean SchemaSmith has a gap for SQL Server JSON support — or is there something else going on?

There is no gap. SQL Server does not have a native JSON type — NVARCHAR(MAX) is the correct and native form. SQL Server’s JSON functions (JSON_VALUE, OPENJSON, FOR JSON PATH) all operate against NVARCHAR columns; no specialized type is required. Declaring NVARCHAR(MAX) is exactly what a SQL Server DBA would do by hand. SchemaSmith models what the engine actually has rather than inventing an abstraction — that IS the native fidelity. If a tool instead used a synthetic “json” type that compiled down to NVARCHAR(MAX), you’d lose visibility into what SQL Server is storing and gain nothing at the engine level.

Check yourself: The three packages encode an auto-incrementing surrogate key three different ways. What are the three encodings, and why doesn't SchemaSmith normalize them to a single identity flag?

SQL Server bakes identity into the DataType string: “DataType”: “INT IDENTITY(1, 1)” — no separate property, the seed and step are right there. PostgreSQL separates the type and the behavior: “DataType”: “int4” paired with “Generated”: “GENERATED ALWAYS AS IDENTITY” — a distinct property reflecting PostgreSQL’s own DDL syntax. MySQL uses a boolean flag: “DataType”: “int” paired with “AutoIncrement”: true — because MySQL’s AUTO_INCREMENT is a simple on/off engine feature. A single normalized identity flag would paper over real differences: SQL Server’s seed/step values, PostgreSQL’s ALWAYS vs BY DEFAULT semantics, MySQL’s storage-engine interaction. Each native encoding carries information that a common abstraction would discard. SchemaSmith follows each engine’s native model so that information stays visible and controllable.

Mark this lesson complete