JSON to SQL DDL Schema Generator

Infer relational SQL table schemas and generate CREATE TABLE and INSERT INTO queries from JSON datasets.

4.9 / 5· 148 verified ratings
•
100% Client-Side Privacy
•
Verified 2026 Engine
Loading tool engine...
Frequently Used With:JSON to TypeScript Interface →JSON to Kotlin Data Class Converter →Python Dictionary to JSON →SQL Query Formatter →
Institutional-Grade Accuracy & Client-Side Privacy Guarantee

Unlike conventional calculator sites that send your financial numbers, payload data, or code snippets across remote web servers, Affi-Chain processes all mathematical derivations, algorithms, and data parsing 100% locally within your browser sandbox. No tracking cookies, no server-side retention, and zero transmission lag.

What is JSON to SQL Schema Inference?

JSON to SQL Schema Inference is the programmatic process of analyzing unstructured or semi-structured JavaScript Object Notation (JSON) documents, inferring their underlying data types, structural relationships, and constraints, and synthesizing production-ready Data Definition Language (DDL) CREATE TABLE statements for relational database management systems (RDBMS).

In modern application engineering, companies routinely transition data between schemaless NoSQL document stores (such as MongoDB, Couchbase, or DynamoDB) and strongly typed relational data warehouses (such as PostgreSQL, MySQL, Snowflake, and BigQuery). Writing SQL schemas manually for complex JSON documents containing dozens of fields, nested objects, and arrays is slow, error-prone, and hazardous:

  • Specifying VARCHAR(255) for a field that contains multi-paragraph customer notes causes runtime query truncation errors.
  • Defaulting high-precision financial amounts to floating-point FLOAT or REAL types causes catastrophic IEEE 754 rounding drift.
  • Failing to detect ISO 8601 timestamps prevents queries from leveraging database index scans on temporal ranges.

An automated SQL schema inference engine inspects data topologies, validates string heuristics against RFC patterns (UUIDs, timestamps, IPv4/IPv6 addresses, emails), and generates syntactically valid DDL tailored to specific database dialect targets.


Dialect-Specific Data Type Inference Matrix

JSON defines only six basic value types: string, number, boolean, null, object, and array. A schema generator inspects the lexical value of these primitives and maps them to appropriate relational database types:

JSON Value PatternInferred Semantic TypePostgreSQL DDLMySQL 8.0+ DDLSQLite 3 DDLMicrosoft SQL Server
"550e8400-e29b-41d4-a716-446655440000"RFC 4122 UUIDUUIDCHAR(36) / BINARY(16)TEXTUNIQUEIDENTIFIER
"2026-11-18T21:45:00.000Z"ISO 8601 TimestampTIMESTAMPTZDATETIME(6)TEXTDATETIMEOFFSET
"2026-11-18"ISO 8601 DateDATEDATETEXTDATE
"alice@affichain.com"Email / Short String ($\le 255$)VARCHAR(255)VARCHAR(255)TEXTNVARCHAR(255)
"Long multi-line text..."Large Text ($> 255$ chars)TEXTLONGTEXTTEXTNVARCHAR(MAX)
42Integer within signed 32-bit rangeINTEGERINTINTEGERINT
9876543210Integer exceeding 32-bit rangeBIGINTBIGINTINTEGERBIGINT
19.99Decimal currency / floatNUMERIC(12, 2)DECIMAL(12, 2)REALDECIMAL(12, 2)
true / falseBoolean FlagBOOLEANTINYINT(1)INTEGERBIT
{"role": "admin", "team": 4}Nested Object (Denormalized)JSONBJSONTEXTNVARCHAR(MAX)
["apple", "banana", "orange"]Array of PrimitivesTEXT[] or JSONBJSONTEXTNVARCHAR(MAX)

Schema Architecture: Normalized 3NF Tables vs. Denormalized JSONB

When transitioning JSON data to relational SQL, database architects must choose between two primary structural paradigms:

flowchart TD
    JSONInput["Input JSON with Nested Children & Arrays"] --> Strategy{Relational Modeling Strategy}
    
    Strategy -- "Denormalized Document Storage" --> SingleTable["Single Table Architecture"]
    SingleTable --> IngestJSONB["Store Nested Objects in JSONB / JSON Column"]
    SingleTable --> GINIndex["Index via GIN or Functional Expression Index"]
    
    Strategy -- "Strict 3NF Normalization" --> MultiTable["Multi-Table Relational Architecture"]
    MultiTable --> PrimaryTable["Parent Table with Primary Key (id)"]
    MultiTable --> ChildTables["Child Tables for Arrays / Sub-Objects"]
    MultiTable --> ForeignKey["Link Child Tables via FOREIGN KEY (parent_id)"]

1. Denormalized JSON Storage (PostgreSQL JSONB / MySQL JSON)

  • Strengths: Maximum ingestion throughput; zero structural friction when JSON schemas evolve; eliminates complex multi-table JOIN statements.
  • Weaknesses: Cannot enforce relational foreign key constraints on nested properties; consumes higher storage due to duplicated JSON key names; updates to deeply nested elements require read-modify-write operations.

2. Normalized Relational Schema (Third Normal Form - 3NF)

  • Strengths: Guaranteed ACID integrity; strict data typing; optimal B-Tree index traversal; minimal storage overhead.
  • Weaknesses: Requires writing separate DDL for child tables; complex ETL pipelines required to break arrays into separate relational rows.

Practical Implementation Examples

Python: Multi-Dialect SQL DDL Generator

The following production script inspects arbitrary JSON document lists, detects UUIDs, ISO timestamps, integers, floating-point numbers, and reserved SQL keywords, and outputs clean PostgreSQL and MySQL DDL:

import json
import re
from typing import Dict, Any, List

# Regex signatures for advanced semantic detection
UUID_REGEX = re.compile(r'^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[1-5][0-9a-fA-F]{3}-[89abAB][0-9a-fA-F]{3}-[0-9a-fA-F]{12}$')
ISO_DATETIME_REGEX = re.compile(r'^\d{4}-\d{2}-\d{2}[T ]\d{2}:\d{2}:\d{2}(?:\.\d+)?(?:Z|[+-]\d{2}:\d{2})?$')
ISO_DATE_REGEX = re.compile(r'^\d{4}-\d{2}-\d{2}$')

RESERVED_KEYWORDS = {"user", "order", "group", "table", "limit", "select", "check", "primary", "index"}

def infer_sql_type(value: Any, dialect: str = "postgres") -> str:
    if value is None:
        return "TEXT"
    
    if isinstance(value, bool):
        return "BOOLEAN" if dialect == "postgres" else "TINYINT(1)"
        
    if isinstance(value, int):
        if -2147483648 <= value <= 2147483647:
            return "INTEGER" if dialect == "postgres" else "INT"
        return "BIGINT"
        
    if isinstance(value, float):
        return "NUMERIC(14, 4)" if dialect == "postgres" else "DECIMAL(14, 4)"
        
    if isinstance(value, str):
        if UUID_REGEX.match(value):
            return "UUID" if dialect == "postgres" else "CHAR(36)"
        if ISO_DATETIME_REGEX.match(value):
            return "TIMESTAMPTZ" if dialect == "postgres" else "DATETIME(6)"
        if ISO_DATE_REGEX.match(value):
            return "DATE"
        if len(value) <= 255:
            return f"VARCHAR({max(len(value) * 2, 64)})"
        return "TEXT" if dialect == "postgres" else "LONGTEXT"
        
    if isinstance(value, (dict, list)):
        return "JSONB" if dialect == "postgres" else "JSON"
        
    return "TEXT"

def generate_sql_ddl(table_name: str, records: List[Dict[str, Any]], dialect: str = "postgres") -> str:
    # Discover all column types by surveying records
    column_types: Dict[str, str] = {}
    
    for row in records:
        for k, v in row.items():
            if k not in column_types or column_types[k] == "TEXT":
                if v is not None:
                    column_types[k] = infer_sql_type(v, dialect)
    
    quote_char = '"' if dialect == "postgres" else '`'
    
    ddl_lines = [f"CREATE TABLE {quote_char}{table_name}{quote_char} ("]
    
    has_explicit_id = False
    for col_name, col_type in column_types.items():
        quoted_col = f"{quote_char}{col_name}{quote_char}"
        if col_name.lower() in ("id", "uuid") and not has_explicit_id:
            ddl_lines.append(f"    {quoted_col} {col_type} PRIMARY KEY,")
            has_explicit_id = True
        else:
            ddl_lines.append(f"    {quoted_col} {col_type},")
            
    # Strip trailing comma
    ddl_lines[-1] = ddl_lines[-1].rstrip(',')
    ddl_lines.append(");")
    
    return "\n".join(ddl_lines)

# Verification
sample_data = [
    {
        "id": "e3b0c442-98fc-1c14-9af3-4c0038ae7372",
        "username": "johndoe",
        "email": "john@example.com",
        "login_count": 482,
        "is_active": True,
        "last_login": "2026-11-18T19:30:00Z",
        "metadata": {"preferred_theme": "dark", "beta_user": True}
    }
]

print("-- PostgreSQL DDL:")
print(generate_sql_ddl("users", sample_data, dialect="postgres"))
print("\n-- MySQL DDL:")
print(generate_sql_ddl("users", sample_data, dialect="mysql"))

TypeScript / Node.js Relational Schema Inference Utility

export interface SchemaInferenceResult {
  tableName: string;
  postgresDdl: string;
  columnSummary: Array<{ name: string; type: string; isNullable: boolean }>;
}

export function inferSqlTable(tableName: string, data: Record<string, any>[]): SchemaInferenceResult {
  const columns = new Map<string, { type: string; nullable: boolean }>();

  data.forEach(item => {
    Object.keys(item).forEach(key => {
      const val = item[key];
      const isNull = val === null || val === undefined;

      let detectedType = 'TEXT';
      if (!isNull) {
        if (typeof val === 'boolean') detectedType = 'BOOLEAN';
        else if (typeof val === 'number') detectedType = Number.isInteger(val) ? 'INTEGER' : 'NUMERIC(12,2)';
        else if (typeof val === 'object') detectedType = 'JSONB';
        else if (typeof val === 'string') {
          if (/^[0-9a-fA-F-]{36}$/.test(val)) detectedType = 'UUID';
          else if (/^\d{4}-\d{2}-\d{2}/.test(val)) detectedType = 'TIMESTAMPTZ';
          else detectedType = val.length > 255 ? 'TEXT' : 'VARCHAR(255)';
        }
      }

      if (!columns.has(key)) {
        columns.set(key, { type: detectedType, nullable: isNull });
      } else if (isNull) {
        columns.get(key)!.nullable = true;
      }
    });
  });

  const columnLines: string[] = [];
  const columnSummary: SchemaInferenceResult['columnSummary'] = [];

  columns.forEach((props, name) => {
    const isPk = name.toLowerCase() === 'id';
    const line = `  "${name}" ${props.type}${isPk ? ' PRIMARY KEY' : ''}${!props.nullable && !isPk ? ' NOT NULL' : ''}`;
    columnLines.push(line);
    columnSummary.push({ name, type: props.type, isNullable: props.nullable });
  });

  const postgresDdl = `CREATE TABLE "${tableName}" (\n${columnLines.join(',\n')}\n);`;

  return { tableName, postgresDdl, columnSummary };
}

Critical Edge Cases & Common Relational Pitfalls

1. Inconsistent Numeric Types Across JSON Objects

In document databases like MongoDB, JavaScript allows one document to store an integer ("amount": 50) while another document in the same collection stores a floating-point number ("amount": 50.75). If a schema generator inspects only the first record, it will define the column as INTEGER, causing subsequent decimal values to be silently truncated or rejected with runtime database insert errors. A robust inference engine must scan multiple records and elevate the column to NUMERIC / DECIMAL whenever a floating-point value is observed.

2. SQL Reserved Keyword Collisions

JSON object keys frequently utilize terms that are reserved words in standard SQL:

  • order (conflicts with ORDER BY)
  • group (conflicts with GROUP BY)
  • user (conflicts with the CURRENT_USER pseudo-table)
  • table (conflicts with DDL table definitions)

DDL generators must systematically quote column identifiers using dialect-compliant characters—double quotes ("col") in PostgreSQL and SQLite, or backticks (`col`) in MySQL—to prevent fatal syntax parsing errors.

3. The 64-Bit Integer Precision Boundary

JSON specifications define numbers as IEEE 754 double-precision floats, which accurately represent integers only up to $2^{53} - 1$ ($9,007,199,254,740,991$). Relational databases support 64-bit signed integers (BIGINT) up to $2^{63} - 1$ ($9,223,372,036,854,775,807$). When ingesting Twitter Snowflake IDs or distributed 64-bit entity IDs via JSON, numbers exceeding $2^{53} - 1$ must be serialized as strings to prevent irreversible bit corruption.

Frequently Asked Questions

How are JSON dates mapped in PostgreSQL?
ISO 8601 timestamp strings are automatically inferred as TIMESTAMP WITH TIME ZONE in PostgreSQL.

Explore Related Utilities