Converting CSV & JSON to Production-Ready SQL Schemas

Turning raw CSV or JSON data into a well-typed SQL schema sounds simple — until you hit edge cases. Nullable columns, reserved keywords as column names, date formats that vary row by row, and dialect-specific quirks can turn a five-minute task into an afternoon of debugging. This guide walks through the entire pipeline: from type detection to multi-dialect DDL generation, with real code and practical advice.

If you want to try this interactively, open the CSV & JSON to SQL converter — it runs entirely in the browser and handles everything described here automatically.

1. Type Detection Algorithms

The foundation of schema generation is inferring column types from raw string values. A robust type detector checks every value in a column against a priority-ordered list of type matchers.

The priority cascade

Order matters. You test from most specific to least specific — if you check VARCHAR first, everything matches and you lose type information. The correct order is:

// Type detection priority (most → least specific)
const TYPE_PRIORITY = [
  'BOOLEAN',     // true/false, yes/no, 1/0
  'INTEGER',     // whole numbers, no decimal point
  'NUMERIC',     // numbers with decimal points
  'TIMESTAMP',   // ISO 8601, common date formats
  'VARCHAR'      // fallback — everything is a string
];

Pattern matching per type

Each type needs a regex or parsing function. Here are production-tested patterns:

const TYPE_MATCHERS = {
  BOOLEAN: (v) => /^(true|false|yes|no|0|1)$/i.test(v.trim()),

  INTEGER: (v) => /^-?\d{1,18}$/.test(v.trim()),
  // Cap at 18 digits to stay within BIGINT range

  NUMERIC: (v) => /^-?\d+\.\d+$/.test(v.trim()) &&
                   !isNaN(parseFloat(v)),

  TIMESTAMP: (v) => {
    // ISO 8601: 2024-01-15, 2024-01-15T09:30:00Z
    if (/^\d{4}-\d{2}-\d{2}/.test(v.trim())) {
      return !isNaN(Date.parse(v));
    }
    // US format: 01/15/2024, MM-DD-YYYY
    if (/^\d{1,2}[\/\-]\d{1,2}[\/\-]\d{4}/.test(v.trim())) {
      return !isNaN(Date.parse(v));
    }
    return false;
  }
};

function detectType(values) {
  const nonNull = values.filter(v => v !== '' && v !== null);
  if (nonNull.length === 0) return 'VARCHAR';

  for (const type of TYPE_PRIORITY) {
    if (type === 'VARCHAR') return 'VARCHAR';
    if (nonNull.every(v => TYPE_MATCHERS[type](v))) return type;
  }
  return 'VARCHAR';
}

Key insight: Every non-null value in the column must match a type for that type to be selected. A single non-numeric string in a column of integers demotes the entire column to VARCHAR.

VARCHAR length sizing

Don't blindly use VARCHAR(255). Scan the actual data and add a buffer:

function varcharSize(values) {
  const maxLen = Math.max(...values.map(v => (v || '').length));
  if (maxLen <= 50) return 50;
  if (maxLen <= 255) return 255;
  if (maxLen <= 1000) return 1000;
  return 4000; // or use TEXT for very long values
}

2. Handling Edge Cases

Null and empty values

CSV files represent nulls inconsistently. Your parser needs to treat all of these as NULL:

const NULL_VARIANTS = [
  '', 'null', 'NULL', 'Null',
  'NA', 'N/A', 'n/a',
  'None', 'none',
  '#N/A', '#NULL!',
  'undefined', '-'
];

function isNullValue(v) {
  return v === null || v === undefined ||
         NULL_VARIANTS.includes(v.trim());
}

When a column has nulls, the generated DDL should omit NOT NULL constraints. If every value is null, default to VARCHAR.

Reserved keyword escaping

Column names like order, group, select, table, index, and user are reserved words in most SQL dialects. They must be quoted:

const RESERVED_WORDS = new Set([
  'select', 'from', 'where', 'order', 'group', 'by',
  'table', 'index', 'user', 'key', 'column', 'value',
  'date', 'time', 'timestamp', 'type', 'status', 'name',
  'role', 'grant', 'check', 'primary', 'foreign', 'references'
]);

function quoteIdentifier(name, dialect) {
  const needsQuoting = RESERVED_WORDS.has(name.toLowerCase()) ||
                       /[^a-zA-Z0-9_]/.test(name) ||
                       /^\d/.test(name);

  if (!needsQuoting) return name;

  switch (dialect) {
    case 'mysql':    return '`' + name + '`';
    case 'mssql':    return '[' + name + ']';
    default:         return '"' + name + '"'; // PostgreSQL, SQLite, ANSI
  }
}

Special characters in column names

CSV headers often contain spaces, dots, or dashes — First Name, order.id, is-active. You have two options:

Sanitize — replace non-alphanumeric characters with underscores: first_name, order_id, is_active
Quote — preserve the original name inside dialect-appropriate quotes: "First Name"

For production schemas, sanitizing is almost always the better choice. Quoted identifiers with spaces force every downstream query to use quotes too.

function sanitizeColumnName(name) {
  return name
    .trim()
    .replace(/[^a-zA-Z0-9_]/g, '_')  // Replace special chars
    .replace(/_{2,}/g, '_')           // Collapse multiple underscores
    .replace(/^_|_$/g, '')            // Trim leading/trailing
    .replace(/^\d/, 'col_$&')         // Prefix if starts with digit
    .toLowerCase();
}

3. Dialect Differences: PostgreSQL vs MySQL vs SQLite

SQL is not one language — it's a family. Each dialect has its own type names, quoting rules, and syntax quirks.

Concept	PostgreSQL	MySQL	SQLite
Boolean	`BOOLEAN`	`TINYINT(1)`	`INTEGER`
Integer	`INTEGER` / `BIGINT`	`INT` / `BIGINT`	`INTEGER`
Decimal	`NUMERIC`	`DECIMAL(18,6)`	`REAL`
Timestamp	`TIMESTAMP`	`DATETIME`	`TEXT`
String	`VARCHAR(n)`	`VARCHAR(n)`	`TEXT`
Identifier quoting	Double quotes `"col"`	Backticks `col`	Double quotes `"col"`
String literal escaping	Single quotes + `''`	Single quotes + `\'` or `''`	Single quotes + `''`
Auto-increment	`SERIAL` / `GENERATED ALWAYS`	`AUTO_INCREMENT`	`AUTOINCREMENT`

Generating dialect-aware DDL

function mapType(detectedType, dialect) {
  const typeMap = {
    postgresql: {
      BOOLEAN: 'BOOLEAN', INTEGER: 'INTEGER',
      NUMERIC: 'NUMERIC', TIMESTAMP: 'TIMESTAMP',
      VARCHAR: (size) => `VARCHAR(${size})`
    },
    mysql: {
      BOOLEAN: 'TINYINT(1)', INTEGER: 'INT',
      NUMERIC: 'DECIMAL(18,6)', TIMESTAMP: 'DATETIME',
      VARCHAR: (size) => `VARCHAR(${size})`
    },
    sqlite: {
      BOOLEAN: 'INTEGER', INTEGER: 'INTEGER',
      NUMERIC: 'REAL', TIMESTAMP: 'TEXT',
      VARCHAR: () => 'TEXT'
    }
  };

  const map = typeMap[dialect];
  if (detectedType === 'VARCHAR') return map.VARCHAR(255);
  return map[detectedType];
}

4. Batch INSERT Optimization

Generating one INSERT per row is correct but slow. In production, you want batched inserts — and the batch strategy depends on the dialect.

Multi-row VALUES syntax

Supported by PostgreSQL, MySQL, and SQLite (3.7.11+). This is 5–20× faster than individual inserts:

INSERT INTO users (id, name, email, salary) VALUES
  (1, 'Alice', 'alice@example.com', 75000),
  (2, 'Bob', 'bob@test.org', 62000),
  (3, 'Charlie', 'charlie@dev.io', 91000),
  (4, 'Diana', 'diana@corp.net', 58000),
  (5, 'Eve', 'eve@startup.co', 84000);

Batch size considerations

MySQL — default max_allowed_packet is 64 MB. Stay under it. 500–1000 rows per batch is typical.
PostgreSQL — no hard row limit per INSERT, but 1000 rows per statement is a good target for readability and memory.
SQLite — SQLITE_MAX_COMPOUND_SELECT defaults to 500. Keep batches at or below 500 rows.

function generateBatchInserts(tableName, columns, rows, batchSize = 500) {
  const statements = [];
  const colList = columns.map(c => quoteIdentifier(c)).join(', ');

  for (let i = 0; i < rows.length; i += batchSize) {
    const batch = rows.slice(i, i + batchSize);
    const valueRows = batch.map(row =>
      '  (' + row.map(v => escapeValue(v)).join(', ') + ')'
    ).join(',\n');

    statements.push(
      `INSERT INTO ${tableName} (${colList}) VALUES\n${valueRows};`
    );
  }
  return statements.join('\n\n');
}

function escapeValue(v) {
  if (v === null || v === undefined || v === '') return 'NULL';
  if (typeof v === 'number') return String(v);
  if (typeof v === 'boolean') return v ? 'TRUE' : 'FALSE';
  // Escape single quotes by doubling them
  return "'" + String(v).replace(/'/g, "''") + "'";
}

PostgreSQL COPY alternative

For truly large datasets (100K+ rows), INSERT statements are not the right tool. PostgreSQL's COPY command is orders of magnitude faster:

COPY users (id, name, email, salary)
FROM '/data/users.csv'
WITH (FORMAT csv, HEADER true, NULL '');

The CSV & JSON to SQL tool generates batch INSERT statements with configurable batch sizes and dialect targeting.

5. Handling JSON-Specific Challenges

JSON brings its own complexities compared to flat CSV data.

Nested objects

A JSON object like {"user": {"name": "Alice", "age": 30}} needs to be flattened. Common strategies:

Dot-notation flattening — user.name becomes user_name
Store as JSONB — PostgreSQL's JSONB column type keeps nested data queryable
Normalize — split into separate tables with foreign keys

function flattenObject(obj, prefix = '') {
  const result = {};
  for (const [key, val] of Object.entries(obj)) {
    const newKey = prefix ? `${prefix}_${key}` : key;
    if (val && typeof val === 'object' && !Array.isArray(val)) {
      Object.assign(result, flattenObject(val, newKey));
    } else if (Array.isArray(val)) {
      result[newKey] = JSON.stringify(val); // Store arrays as JSON text
    } else {
      result[newKey] = val;
    }
  }
  return result;
}

Inconsistent keys across records

Unlike CSV, JSON records can have different keys. You must scan all records to build the full column set:

function collectAllKeys(records) {
  const keySet = new Set();
  for (const record of records) {
    for (const key of Object.keys(flattenObject(record))) {
      keySet.add(key);
    }
  }
  return [...keySet];
}

6. Best Practices Checklist

Always scan all rows for type detection. Sampling only the first 10 rows can miss edge cases — a column that looks like integers for 99 rows might have a decimal on row 100.
Treat empty strings as NULL unless you have a specific reason not to. Most databases distinguish between '' and NULL, and NULLs are almost always the correct semantic choice for missing data.
Quote all identifiers defensively. Even if a column name isn't a reserved word today, it might become one in a future SQL version. Quoting everything costs nothing.
Validate generated SQL before execution. Parse the output through a SQL validator or run it against a test database. One malformed value can break the entire batch.
Use transactions. Wrap batch inserts in BEGIN/COMMIT blocks. If one batch fails, the database stays clean.
Add IF NOT EXISTS to CREATE TABLE statements for idempotent DDL. This makes scripts re-runnable without dropping data.
Consider CREATE TABLE ... AS SELECT for one-time imports where you control the source completely.
Profile your data first. Use a tool like DataDevKIT's CSV & JSON to SQL converter to preview the schema before committing to a structure.

Wrapping Up

Converting CSV and JSON to SQL is more nuanced than it first appears. A production-grade converter needs careful type detection, comprehensive null handling, dialect-aware quoting, and efficient batch strategies. The patterns in this guide are battle-tested across millions of rows and multiple database systems. Start with the interactive converter for quick exploration, then use the code patterns here when you need to build it into a pipeline.