SQL CREATE TABLE to JSON Schema

Converting a database schema into a JSON Schema or TypeScript interface by hand is tedious and error-prone. This tool parses your CREATE TABLE DDL and produces a faithful structural definition you can drop straight into validation middleware, API contracts or typed clients — all in the browser, so proprietary schemas stay private.

How it works

The converter tokenises each CREATE TABLE name ( … ) statement and splits the body on top-level commas (respecting parentheses so DECIMAL(10,2) isn’t broken apart). For every column definition it extracts:

1. The name (with surrounding `, " or [] quotes stripped).
2. The type and any size/precision, e.g. VARCHAR(255), DECIMAL(10,2).
3. Constraints — NOT NULL, DEFAULT …, inline PRIMARY KEY.

It then maps each SQL type to its JSON-Schema equivalent:

INT/BIGINT/SMALLINT/SERIAL  -> integer
DECIMAL/NUMERIC/FLOAT/REAL  -> number
VARCHAR/TEXT/CHAR/UUID      -> string (maxLength from the size)
BOOLEAN/BOOL/TINYINT(1)     -> boolean
DATE                        -> string, format: date
TIMESTAMP/DATETIME          -> string, format: date-time

A column is added to the schema’s required array when it is NOT NULL or the primary key and has no DEFAULT. For TypeScript output, nullable columns become optional (name?: type).

Worked example

Input DDL:

CREATE TABLE orders (
  id          SERIAL          PRIMARY KEY,
  user_id     INT             NOT NULL,
  total       DECIMAL(10,2)   NOT NULL,
  status      VARCHAR(20)     NOT NULL DEFAULT 'pending',
  notes       TEXT,
  created_at  TIMESTAMP       NOT NULL DEFAULT NOW()
);

Generated JSON Schema (draft-07):

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "title": "orders",
  "type": "object",
  "properties": {
    "id":         { "type": "integer" },
    "user_id":    { "type": "integer" },
    "total":      { "type": "number" },
    "status":     { "type": "string", "maxLength": 20 },
    "notes":      { "type": "string" },
    "created_at": { "type": "string", "format": "date-time" }
  },
  "required": ["id", "user_id", "total"]
}

status and created_at are NOT NULL but have a DEFAULT so they are omitted from required. notes is nullable, so it is optional. The TypeScript output makes nullable columns notes?: string.

Practical uses

• Request validation. Drop the JSON Schema into an ajv or zod validation step in your API handler to ensure POST bodies match the table structure.
• API documentation. JSON Schema is the source format for OpenAPI component schemas; paste the output under components/schemas in your OpenAPI document.
• Typed clients. The TypeScript interface output gives you a ready-made type for query results without depending on an ORM.
• Migration review. Converting a new table before committing the migration is a quick sanity check that the schema is consistent with how you intend to use it in code.

Tips and notes

• Table-level constraints (PRIMARY KEY (...), FOREIGN KEY (...), CONSTRAINT …, INDEX …) are detected and skipped — they are not columns.
• An unrecognised type falls back to string so the output is always valid JSON Schema.
• TINYINT(1) is treated as boolean to match the common MySQL convention.
• Paste several CREATE TABLE statements at once; each becomes its own schema object or interface.
• Nothing is sent to a server — the parser runs entirely in your browser.