# SQL — Quickstart: Your First DDL Transform Should Write Nothing: a SQL Quickstart The most accurate description of your data is almost certainly a `CREATE TABLE` statement sitting in a migrations folder. It is reviewed, versioned, and executable - and it is legible to exactly one kind of consumer. Getting it out of that folder and into a data contract, a JSON Schema, or another vendor's dialect is usually where the accuracy stops. # Your First DDL Transform Should Write Nothing: a SQL Quickstart The most accurate description of your data is almost certainly a `CREATE TABLE` statement sitting in a migrations folder. It is reviewed, versioned, and executable - and it is legible to exactly one kind of consumer. Getting it out of that folder and into a data contract, a JSON Schema, or another vendor's dialect is usually where the accuracy stops. This quickstart does that in a single HTTP call, and the call writes nothing. On the CoreModels transform surface, SQL DDL is the format key `sql`, and it works in both directions: `CREATE TABLE` text decodes into the model, and the model encodes back out as `CREATE TABLE` text. We are going to use the stateless mapping route, so you can run this against any project you can read, on the first day, without touching a single stored node. ## What you need - The API host - `https://coremodels.example.com` below; substitute your deployment. Transform routes carry no `api/` prefix. - A bearer token in `$TOKEN`, and a project id in `$PROJECT_ID` (32 hex characters). On this route the project scopes authorization only; **Viewer** is enough. - `curl` and `jq`. Every request needs `Authorization: Bearer $TOKEN` and `Content-Type: application/json`. ## The table Save this as `shipment.sql`. Five columns and one table-level constraint - enough to see how the surface treats structure, requiredness, precision, and the things SQL knows that a portable model does not. ```sql CREATE TABLE shipment ( shipment_id INTEGER NOT NULL, tracking_code VARCHAR(64) NOT NULL, carrier VARCHAR(120), shipped_at TIMESTAMP, weight_kg DECIMAL(8,2), PRIMARY KEY (shipment_id) ); ``` You do not have to tell us which database this came from. Decode is dialect-tolerant: one parser reads PostgreSQL, MySQL, and SQL Server spellings - double quotes, backticks, or square brackets; `IF NOT EXISTS`; `--` and `/* */` comments; inline `COMMENT` clauses; `ENUM(...)` columns - without a dialect declaration. ## The one call We will turn the table into an ODCS data contract (`odcs` - a Bitol Open Data Contract Standard v3 YAML contract, one of the encode formats we implement). The route is `schema/map`: it decodes the source, produces a mapping plan, validates that plan through the engine's gate, executes it, and encodes the result. Inference matches the source against a **target hint** - a schema to map toward - and it refuses to run without one. For a straight format conversion there is nothing to map toward except the source itself, so the source is its own hint. That is the one non-obvious move in this whole article, and it is worth knowing early: omit the hint and the call returns `success: false` with `{ "path": "inference", "message": "The inference resolver requires a target IR to match against." }` in `errors`. ```bash jq -n --rawfile s shipment.sql '{ sourceFormat: "sql", sourceSchema: $s, targetFormat: "odcs", targetHintFormat: "sql", targetHintSchema: $s, mapping: { kind: "inferred" } }' > map.json curl -sS -X POST \ "https://coremodels.example.com/graph/transform/schema/map/$PROJECT_ID" \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json" \ --data-binary @map.json > response.json ``` `jq -n --rawfile` is doing real work here: the DDL travels as a JSON string, and hand-escaping newlines is how quickstarts go wrong. ## The output Every transform response uses the same envelope - `success`, `lossiness`, `errors`, a payload key (`schema` here), and on the mapping routes a `plan`. Text formats arrive as a string, so read them with `-r`: ```bash jq -r '.schema' response.json ``` ```yaml apiVersion: v3.1.0 kind: DataContract id: sql-schema version: 1.0.0 status: active schema: - name: shipment properties: - name: shipment_id logicalType: integer required: true - name: tracking_code logicalType: string required: true - name: carrier logicalType: string - name: shipped_at logicalType: timestamp - name: weight_kg logicalType: number ``` Column names survived exactly. `NOT NULL` became `required: true` on the two columns that had it. `TIMESTAMP` became `timestamp` and `DECIMAL(8,2)` became `number` - the contract's own vocabulary, not a guess. ## Read the ledger before you celebrate ```bash jq '.lossiness' response.json ``` ```json [ { "kind": "ConstraintRelaxation", "path": "Type[shipment]", "explanation": "SQL PRIMARY constraint not modelled by the IR." }, { "kind": "ConstraintRelaxation", "path": "Type[shipment]", "explanation": "SQL PRIMARY constraint not modelled by the IR." } ] ``` `success: true` means the call ran. It does not mean nothing changed. `lossiness` is the account of everything whose fidelity shifted, and reading it is the entire discipline of using this surface well. Each record carries a `kind` - one of `StructuralDrop`, `TypeApproximation`, `ConstraintRelaxation`, `SemanticNarrowing` - a `path` naming the exact construct, and a plain-English `explanation`. Two things to take from this ledger. First, the finding itself: the model is made of types, elements, requiredness, references, and vocabularies. It has no concept of a primary key, so `PRIMARY KEY (shipment_id)` was dropped and said so, at the table, rather than letting you discover the absence three tools downstream. Table-level `UNIQUE` and `CHECK` clauses are reported the same way. Treat this as a review checklist: re-establish the key wherever this contract becomes a database again. Second, the reason it appears twice: the ledger is aggregated across every stage of the call - source decode, hint decode, plan, gate, execute, encode. We decoded the same DDL twice, once as the source and once as the hint, so the same relaxation is reported once per decode. Nothing is deduplicated, because deduplication would mean deciding which report you did not need. ## The same call, a different target Change two fields and the same source comes out as MySQL DDL: ```bash jq -n --rawfile s shipment.sql '{ sourceFormat: "sql", sourceSchema: $s, targetFormat: "sql", vendor: "mysql", targetHintFormat: "sql", targetHintSchema: $s, mapping: { kind: "inferred" } }' | curl -sS -X POST \ "https://coremodels.example.com/graph/transform/schema/map/$PROJECT_ID" \ -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \ --data-binary @- | jq -r '.schema' ``` ```sql CREATE TABLE `shipment` ( `shipment_id` INTEGER NOT NULL, `tracking_code` VARCHAR(64) NOT NULL, `carrier` VARCHAR(120), `shipped_at` TIMESTAMP, `weight_kg` DECIMAL(8,2) ); ``` `vendor` applies to SQL output only and takes `postgres` (the default), `mysql`, or `sqlserver` (the alias `mssql` is accepted). It changes identifier quoting, where column comments go, whether a controlled vocabulary can be expressed as an inline `ENUM(...)`, and - for models that do not already carry a SQL type spelling - which concrete types get emitted. Notice what did *not* change: `VARCHAR(64)`, `DECIMAL(8,2)`, and `TIMESTAMP` came through verbatim. When a model enters as SQL, we keep each column's original type spelling and re-emit it exactly, because a round-trip that silently rewrites `DECIMAL(8,2)` into something "equivalent" is not a round-trip. Vendor-idiomatic types are chosen only when there is no original spelling to honor - a model that arrived as JSON Schema, Avro, or LinkML, or one that came out of a CoreModels project. ## What the conversion actually did Underneath both calls, the same structural map ran: the table became a **Type** (labeled `shipment`), each column became an **Element** with a primitive kind, `NOT NULL` became the required flag, a foreign key would have become a typed reference to another table, and an `ENUM(...)` column would have become a governed **Taxonomy** with one term per value. Column comments are the annotation carrier - a comment that parses as a JSON object carrying `x-maps-to` or `x-sia-role` is lifted into formal semantic mappings and roles, while a comment that is not JSON is preserved as a description. The response also carried a `plan`: the executed mapping, operation by operation, as a reviewable and replayable artifact. ```json { "kind": "ElementMapping", "origin": "Inferred", "sourceElementIds": ["shipmentShippedAt"], "targetElementIds": ["shipmentShippedAt"] } ``` Those ids are ours - node ids must be alphanumeric, so `shipment` plus `shipped_at` becomes `shipmentShippedAt` while the label keeps your original `shipped_at`. Store the plan and the same conversion can be replayed later, byte for byte, without asking inference to guess again. Three habits carry over to every other format on this surface: pick the format key, read the ledger, keep the plan. The full route list, roles, and ready-to-paste bodies for every format are in the transform API guide in the CoreModels docs.