No Annotation Slot: How the SQL DDL Coder Carries Meaning, and Where It Stops

Every other format on the CoreModels transform surface has somewhere to put meaning. JSON Schema has x- keywords. Avro has custom attributes. LinkML has slot_uri and meaning. OWL is meaning. SQL DDL has a name, a type, and a handful of constraints — and no annotation slot at all.

That austerity shapes the whole coder. Its answer comes in three parts: structure maps to structure, exact spellings ride in a dotted extras namespace, and semantics ride in comments — the one free-text channel every dialect offers. What follows is the tour of both directions: what maps, what rides along, every condition that writes a lossiness record, what round-trips, and the edges we know about.

The format key is sql. Decode is dialect-tolerant: one parser reads PostgreSQL, MySQL, and SQL Server spellings without a dialect declaration. Encode is dialect-specific: postgres (default), mysql, or sqlserver selects quoting, type defaults, enum handling, and comment placement.

The structural map

SQL construct	Model construct	Notes
CREATE TABLE t (...)	Type	id sanitized to camelCase alphanumerics; the original name kept verbatim as the label; provenance stamped SQL
column	Element	label is the original column name; id is table + column, camelCased
column type	primitive kind	four buckets, plus the verbatim spelling in extras
NOT NULL	required	round-trips in both directions
inline REFERENCES t(c), or table-level FOREIGN KEY (c) REFERENCES t (c)	type reference	the element's value type points at the referenced table's Type
ENUM('a','b') or SET(...) column	Taxonomy + reference	taxonomy id is the element id plus Enum; one term per value
column comment, inline or COMMENT ON COLUMN	semantic mappings, role, or description	the carrier — below

Ids are sanitized because node ids must be alphanumeric; separators fold away and camel-case the next word. sensor_reading becomes sensorReading, and its taken_at column becomes sensorReadingTakenAt. Sanitization does not lower-case what you shouted: FULL_NAME in Customer becomes CustomerFULLNAME. Labels always keep the original text, and encode writes from labels, so your naming survives the trip — but mapping guides and plans address constructs by id, so copy ids from a returned plan rather than deriving them by eye.

Schema-qualified table names reduce to their local part: analytics.web_event imports as web_event.

One structural reduction to know: a foreign key records which table is referenced, not which column. REFERENCES invoice(invoice_id) becomes a reference to the invoice type, and re-encoding emits REFERENCES "invoice" with no column list.

Types: four buckets in, verbatim spelling back out

Decode strips any (args), lower-cases the base name, and sorts it into one of four recognized kinds, with String as the everything-else fallback:

Kind	Recognized spellings
Integer	int, integer, bigint, smallint, tinyint, serial, bigserial, smallserial, int4, int8
Double	decimal, numeric, float, double, real, money, number, dec
Boolean	bool, boolean, bit
DateTime	date, datetime, datetime2, timestamp, timestamptz, smalldatetime, time
String	everything else — varchar, char, text, uuid, json, blob, …

The bucket is only half the story. The original spelling, arguments included, is preserved verbatim under the extras key sql.type. DECIMAL(12,2) buckets as Double for the benefit of every other format and stays DECIMAL(12,2) in the bag. Precision and scale never become model structure; they survive as the verbatim spelling or not at all.

On encode, a preserved spelling wins. Only an element with no sql.type — a model that arrived as JSON Schema, Avro, LinkML, or came out of a CoreModels project — gets the vendor default:

Kind	postgres	mysql	sqlserver
Integer	INTEGER	INTEGER	INTEGER
Double	NUMERIC	NUMERIC	DECIMAL(18,2)
Boolean	BOOLEAN	TINYINT(1)	BIT
DateTime	TIMESTAMP	DATETIME	DATETIME2
String and the rest	VARCHAR(255)	VARCHAR(255)	NVARCHAR(255)

That rule defines what a stateless SQL-to-SQL "dialect conversion" actually is: for a model that entered as SQL, re-encoding to another vendor changes quoting, enum treatment, and comment placement — but the column types come back in their original spellings. A T-SQL NVARCHAR(200) exported as PostgreSQL stays NVARCHAR(200). It is a deliberate trade of speculative rewriting for round-trip fidelity. Route the model through a project when you want the vendor table applied, since a project stores the modeled type rather than the source text.

The carrier: comments that are secretly JSON

Semantics travel in column comments, and the rules are worth knowing exactly.

Decoding. MySQL's inline COMMENT '...' clause is read during column parsing; standalone COMMENT ON COLUMN table.column IS '...' statements are applied in a second pass after all tables exist. Either way the text is inspected: if it parses as a JSON object, x-maps-to entries become formal semantic mappings (standard → URI) and x-sia-role becomes the element's role. If it is not JSON — plain prose, or JSON that fails to parse — it is kept as a description under the extras key sql.comment.

Encoding. An element carrying mappings or a role gets a JSON-object comment rebuilt from them; otherwise a stored description is written back as-is. MySQL output carries comments inline on the column; postgres and sqlserver output collects them into COMMENT ON COLUMN statements appended after the tables. One table in, two dialects out:

CREATE TABLE patient (
  mrn VARCHAR(32) COMMENT '{"x-sia-role":"Identifier","x-maps-to":{"schema.org":"https://schema.org/identifier"}}',
  enrolled_on TIMESTAMP
);

CREATE TABLE `patient` (
  `mrn` VARCHAR(32) COMMENT '{"x-sia-role":"Identifier","x-maps-to":{"schema.org":"https://schema.org/identifier"}}',
  `enrolled_on` TIMESTAMP
);

CREATE TABLE "patient" (
  "mrn" VARCHAR(32),
  "enrolled_on" TIMESTAMP
);

COMMENT ON COLUMN patient.mrn IS '{"x-sia-role":"Identifier","x-maps-to":{"schema.org":"https://schema.org/identifier"}}';

Between them, mrn is a String element with role Identifier, one mapping to https://schema.org/identifier, and sql.type of VARCHAR(32). The dotted extras namespace for this format is exactly two keys — sql.type for the verbatim column type and sql.comment for a non-semantic comment — with lifted mappings and roles living in the annotation bag proper.

The lossiness inventory

The coder itself writes exactly two patterns of record:

1. Decode. A table-level PRIMARY KEY, UNIQUE, or CHECK clause produces a ConstraintRelaxation at Type[<table>]: SQL PRIMARY constraint not modelled by the IR. (or UNIQUE, or CHECK). The model has types, elements, requiredness, references, and vocabularies — not keys or check expressions.
2. Encode. A taxonomy-backed element targeted at postgres or sqlserver produces a ConstraintRelaxation at Element[<id>]: "has no inline enum; emitted as VARCHAR (the allowed-value constraint is not enforced)." MySQL expresses the vocabulary as a real ENUM(...) and writes no record.

Everything else you see in a SQL conversion's ledger comes from the engine, not the coder — most often a StructuralDrop at Type[<type>].<element> reading "Element is a member of the mapped type but no operation maps or drops it.", which means the mapping plan had nothing to say about that column.

Equally important is what is dropped without a record, because the column tokenizer recognizes only NOT NULL, REFERENCES, and COMMENT among column attributes: DEFAULT clauses, auto-increment markers, and inline column-level PRIMARY KEY / UNIQUE pass through silently, as do table-level KEY / INDEX lines. Move keys to table-level clauses if you want them itemized, and diff an export against its source once if your DDL leans on defaults.

What round-trips

Decode → encode → decode → encode over MySQL is stable: the second pass produces text identical to the first, with structure, requiredness, references, vocabularies, and any x-maps-to mapping intact. The test suite pins the same claims — the structural map from a reference table, the x-maps-to lift out of a column comment, a MySQL round-trip whose signature is identical across the loop, and the vendor defaults from a fresh model.

Two inputs fail outright rather than reporting lossiness: an empty payload (The SQL DDL is empty.) and text with no CREATE TABLE at all (No CREATE TABLE statement was found.).

Every edge we know about

• Bracket-quoted schema-qualified names. CREATE TABLE [dbo].[Patient] binds the table name to dbo, because the bracket form matches the first bracketed token. Unquoted dbo.Patient reduces correctly to Patient. Strip the schema qualifier, or leave it unquoted.
• CHARACTER VARYING(50) parses as CHARACTER: the second word is only re-attached when it stands alone, as in DOUBLE PRECISION. Prefer VARCHAR(50) in DDL you intend to round-trip.
• Enum values are sanitized as term ids and re-emitted as ids. ENUM('in progress','on-hold','DONE') comes back as ENUM('inProgress', 'onHold', 'DONE'); the originals are kept as term labels. Machine-shaped enum values round-trip exactly.
• A JSON-object comment with no SIA keys is consumed. COMMENT '{"owner":"team-a"}' parses as JSON, offers no x-maps-to or x-sia-role, and is not kept as a description. Prose comments and malformed JSON are kept.
• Apostrophes double on the inline-comment loop. COMMENT 'it''s fine' re-emits as COMMENT 'it''''s fine'; the COMMENT ON COLUMN path unescapes correctly. Keep inline comments apostrophe-free.
• COMMENT ON COLUMN statements are emitted unquoted, in PostgreSQL syntax, for both postgres and sqlserver output. Identifiers containing spaces, and SQL Server targets generally, need a hand edit.
• ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY is not read. Only CREATE TABLE bodies contribute; declare foreign keys inline or as table-level clauses.
• Other statements are ignored without error — CREATE INDEX, CREATE VIEW, INSERT, and anything else that is not CREATE TABLE or COMMENT ON COLUMN.
• A COMMENT ON COLUMN naming a column that does not exist is ignored, silently.
• A truncated CREATE TABLE Broken ( succeeds with nothing in it. The statement header matched, the body did not parse, and the result is an empty model — check that the type count is what you expected. CREATE TABLE e1 (); likewise yields a type with no elements.
• Generated DDL is a description, not a migration. No key constraints are emitted, and tables are written in model order rather than dependency order.
• An ENUM column mapped through inference stops execution with Referenced taxonomy '<id>' does not resolve., because a SQL enum arrives unnamed and inference matches by label. Add a TaxonomyMapping to the plan, or move the table through a project's import and export, which do not run the mapping engine.

The stance

One promise runs through all of it: never guess silently where you can preserve verbatim or report honestly. Types the model cannot hold structurally are carried as spellings; meaning the format cannot hold natively goes through the one channel SQL offers, named so you can find it; constraints that cannot be represented are reported at the exact path. That is what makes a format with no annotation slot a full citizen of a transform surface built on semantic fidelity.

For the routes and tools that drive this coder — import, export, stateless mapping, and replayable plans — see the transform API guide in the CoreModels docs.