Handle Nullable JSON Fields in SQL Schema Generation

Nullable vs NOT NULL Columns

In JSON, a field can be null, absent from the object entirely, or have a value. In SQL, every column is either NULL (allows missing data) or NOT NULL (requires a value). Getting this distinction right is critical for data integrity.

Example JSON

{
  "id": 1,
  "name": "Alice",
  "email": "alice@example.com",
  "phone": null,
  "bio": null,
  "avatar_url": "https://cdn.example.com/alice.jpg"
}

Generated SQL

CREATE TABLE users (
  id INTEGER NOT NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(20) NULL,
  bio TEXT NULL,
  avatar_url VARCHAR(2048) NULL
);

How the Converter Decides

1. Value is null — The column is marked as NULL (nullable).
2. Value is present and non-null — The column is marked as NOT NULL by default.
3. Multiple samples — If a field is null in some records and non-null in others, the column is marked NULL.

Why NOT NULL Matters

Using NOT NULL constraints has several benefits:

• Data integrity — Prevents accidental insertion of incomplete records.
• Query simplicity — No need for COALESCE() or IS NOT NULL checks in every query.
• Index efficiency — Some databases handle NOT NULL columns more efficiently in indexes.
• ORM compatibility — ORMs can generate cleaner code when nullability is explicit.

Default Values for Nullable Columns

Instead of allowing NULL, consider whether a default value makes more sense:

phone VARCHAR(20) NULL,               -- genuinely optional
bio TEXT NOT NULL DEFAULT '',           -- empty string instead of NULL
avatar_url VARCHAR(2048) NULL,          -- NULL means "use default avatar"
is_active BOOLEAN NOT NULL DEFAULT TRUE -- always has a state

The NULL Trap

Be cautious with NULL in comparisons. In SQL, NULL = NULL is NULL (not TRUE), and NULL <> 'value' is also NULL. Use IS NULL and IS NOT NULL for null checks, and consider COALESCE() for fallback values.

Multiple-Sample Analysis

When you paste multiple JSON records into the tool, the converter compares all records to determine nullability. A field that is present in some records but missing in others is treated as nullable, since the database column must accommodate all rows.