SQL to JSON Schema: Validate Your API Against Your Database

JSON Schema is the standard for describing and validating JSON data structures. When your data lives in a relational database, keeping your API validation schema in sync with your SQL schema is a challenge. Automating that conversion eliminates an entire class of drift-related bugs.

What Is JSON Schema?

JSON Schema is a declarative format for defining the structure, types, and constraints of JSON data:

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "properties": {
    "id": { "type": "integer" },
    "username": { "type": "string", "maxLength": 50 },
    "email": { "type": "string", "format": "email" },
    "created_at": { "type": "string", "format": "date-time" }
  },
  "required": ["id", "username", "email"]
}

Validators like Ajv (JavaScript), jsonschema (Python), and JSON Schema Validator (Java) use this definition to check incoming data — returning precise error messages when validation fails.

SQL to JSON Schema: Type Mapping

SQL and JSON Schema use different type systems. The conversion follows predictable rules:

SQL Type	JSON Schema Type
INT, INTEGER, BIGINT	integer
FLOAT, DOUBLE, DECIMAL	number
VARCHAR, TEXT, CHAR	string
BOOLEAN, BOOL, TINYINT(1)	boolean
DATE	string with "format": "date"
DATETIME, TIMESTAMP	string with "format": "date-time"
JSON, JSONB	object or {}
UUID	string with "format": "uuid"
ENUM('a','b','c')	string with "enum": ["a","b","c"]

SQL NOT NULL columns become required fields in JSON Schema. Nullable columns are excluded from the required array.

Example: SQL CREATE TABLE to JSON Schema

Given:

CREATE TABLE users (
  id          INT           NOT NULL AUTO_INCREMENT,
  username    VARCHAR(50)   NOT NULL,
  email       VARCHAR(255)  NOT NULL,
  bio         TEXT,
  status      ENUM('active','inactive','banned') NOT NULL DEFAULT 'active',
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

The generated JSON Schema:

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "title": "users",
  "type": "object",
  "properties": {
    "id":         { "type": "integer" },
    "username":   { "type": "string", "maxLength": 50 },
    "email":      { "type": "string", "maxLength": 255 },
    "bio":        { "type": "string" },
    "status":     { "type": "string", "enum": ["active","inactive","banned"] },
    "created_at": { "type": "string", "format": "date-time" }
  },
  "required": ["id", "username", "email", "status", "created_at"]
}

Use Cases

API Request Validation

Validate incoming POST/PUT request bodies against the schema before touching the database:

import Ajv from 'ajv';
import addFormats from 'ajv-formats';
import userSchema from './schemas/users.json';

const ajv = new Ajv();
addFormats(ajv);
const validate = ajv.compile(userSchema);

app.post('/users', (req, res) => {
  if (!validate(req.body)) {
    return res.status(400).json({ errors: validate.errors });
  }
  // proceed with safe data
});

OpenAPI / Swagger Documentation

JSON Schema is a subset of OpenAPI Schema Objects. Your generated schemas can be embedded directly in an OpenAPI spec, keeping API documentation in sync with your data model.

Contract Testing

In microservice architectures, JSON Schema provides a machine-readable contract. Consumer-driven contract tests can validate that a producer's response matches the consumer's expected schema.

Data Migration Validation

Before migrating data between systems, validate source records against the target schema. This catches type mismatches, missing required fields, and out-of-range values before they cause import failures.

Convert Your SQL Schema

The SQL to JSON Schema tool on InfraHub converts CREATE TABLE statements to JSON Schema instantly. Paste your DDL, and get a ready-to-use JSON Schema that you can drop into your validation layer.

It handles:

• Standard SQL types and their JSON Schema equivalents
• NOT NULL → required fields
• ENUM values → enum arrays
• VARCHAR length → maxLength constraints
• Multiple tables in one input

Everything runs in your browser — your schema structure never leaves your machine.