SQL to JSON Schema

Paste CREATE TABLE SQL and get back a JSON Schema Draft 7 object with properties (column → typed entry with optional format) and a required array for NOT NULL columns without a default. Multiple tables produce a $defs map so you can $ref individual tables from an OpenAPI spec or validator.

How to use the SQL to JSON Schema

Paste one or more CREATE TABLE statements and click Generate JSON Schema. If a single table is pasted, the output is a standalone JSON Schema object. If multiple tables are pasted, the output is a JSON Schema with a $defs map containing one sub-schema per table.

  1. Type mapping — SQL integer types map to JSON Schema "type": "integer"; VARCHAR and TEXT become "type": "string"; DECIMAL/FLOAT become "type": "number"; BOOLEAN becomes "type": "boolean"; DATE becomes string with "format": "date"; TIMESTAMP/DATETIME becomes string with "format": "date-time"; JSON/JSONB columns become "type": "object".
  2. Required array — a column is added to required if it has NOT NULL and no DEFAULT value. Primary key columns (which are implicitly NOT NULL) are also required unless they are auto-increment (auto-generated server-side).
  3. Copy and use — paste into an OpenAPI spec under components/schemas, use with AJV, Zod, or any JSON Schema validator, or import into Postman for request validation.

What is JSON Schema and how does it relate to SQL tables?

JSON Schema is a vocabulary for annotating and validating JSON documents. A schema describes the expected shape of a JSON object — which properties exist, their types, which are required, and what format constraints apply. JSON Schema Draft 7 (and its successor Draft 2019-09 and 2020-12) is the standard used by OpenAPI 3.0, AJV, Zod, and most API validation frameworks. Generating schemas from SQL DDL bridges the gap between the database schema (which defines the canonical data shape) and the API layer (which exposes and validates it).

The type mapping between SQL and JSON Schema is straightforward for most types but has a few conventions worth noting. JSON Schema has no native date or timestamp type — instead, strings with a format keyword ("date", "date-time", "time") represent temporal values. JSON Schema distinguishes integer from number (which allows decimals) — SQL INT maps to integer, DECIMAL/FLOAT to number. SQL BOOLEAN maps directly to JSON Schema boolean. JSON/JSONB columns are emitted as object since their internal structure is not captured in the DDL.

The required array is derived from SQL nullability: a column is required in the JSON Schema sense when it must always be present and non-null. This corresponds to NOT NULL without a server-side default — columns with a DEFAULT can be omitted from API requests and the database fills in the value. Auto-increment primary keys are treated as optional since the client does not supply them on insert.

Common use cases

  • OpenAPI request body schemas — paste the generated schema under components/schemas in your OpenAPI spec and reference it in POST/PUT request body definitions.
  • API validation — use the schema with AJV or a Zod conversion to validate incoming JSON request bodies against the database schema at the API layer.
  • Type generation — feed the JSON Schema into json-schema-to-typescript (json2ts) or similar tools to generate TypeScript interfaces that match the database tables.
  • Postman / Insomnia validation — import the schema into Postman test scripts to auto-validate response payloads against the expected table shape.
  • Documentation — JSON Schema has rich tooling for rendering human-readable docs (Redoc, Swagger UI); use the generated schemas to document API data models.

Frequently asked questions

What is the difference between single-table and multi-table output?
A single CREATE TABLE produces a top-level JSON Schema object with type: object, properties, and required at the root. Multiple tables produce a wrapper schema with $defs containing one sub-schema per table (keyed by table name). Reference individual tables with $ref: "#/$defs/users".

Are VARCHAR length limits included in the schema?
Yes — VARCHAR(n) columns get a maxLength: n keyword in the JSON Schema string property. CHAR(n) also gets minLength and maxLength both set to n to enforce fixed length.

How are DECIMAL/NUMERIC columns handled?
They are emitted as type: number. If the SQL has precision and scale (e.g. DECIMAL(10,2)), the schema adds a multipleOf: 0.01 hint based on the scale to indicate precision. This is advisory — validators enforce it only if they support multipleOf.

Does the output work with OpenAPI 3.0?
Yes — OpenAPI 3.0 uses a subset of JSON Schema Draft 7. The generated output is compatible. Place each table schema under components/schemas in your openapi.yaml, then $ref them from path operation requestBody or response schemas.

How are auto-increment primary keys treated?
AUTO_INCREMENT and SERIAL columns are marked as readOnly: true in the schema (server-generated) and are excluded from the required array. This correctly models the pattern where clients omit the id on POST requests and the server assigns it.