JSON to SQL INSERT — TypeScript source

// Pure JSON → SQL INSERT converter - no React, no DOM, deterministic.
// Escapes strings (single quotes doubled; backslash handling per dialect),
// handles null/boolean/number, serializes nested values as JSON. Never throws.

export type Dialect = 'standard' | 'mysql' | 'postgres';

export interface Options {
  table: string;
  dialect?: Dialect;
  quoteIdentifiers?: boolean;
}

export interface Result {
  ok: boolean;
  sql: string;
  rows: number;
  error: string | null;
}

export function escapeSqlString(s: string, dialect: Dialect = 'standard'): string {
  let out = s.replace(/'/g, "''");
  if (dialect === 'mysql') {
    out = out
      .replace(/\\/g, '\\\\')
      .replace(/\0/g, '\\0')
      .replace(/\n/g, '\\n')
      .replace(/\r/g, '\\r')
      .replace(/\x1a/g, '\\Z');
  }
  return out;
}

export function sqlLiteral(value: unknown, dialect: Dialect): string {
  if (value === null || value === undefined) return 'NULL';
  if (typeof value === 'boolean') {
    if (dialect === 'mysql') return value ? '1' : '0';
    return value ? 'TRUE' : 'FALSE';
  }
  if (typeof value === 'number') {
    return Number.isFinite(value) ? String(value) : 'NULL';
  }
  if (typeof value === 'string') return `'${escapeSqlString(value, dialect)}'`;
  // objects/arrays → JSON text literal
  return `'${escapeSqlString(JSON.stringify(value), dialect)}'`;
}

function quoteIdent(name: string, dialect: Dialect, quoteIdentifiers: boolean): string {
  if (!quoteIdentifiers) return name;
  return dialect === 'mysql' ? `\`${name}\`` : `"${name}"`;
}

function sanitizeIdent(name: string): string {
  const cleaned = (name || '').replace(/[^A-Za-z0-9_]/g, '_');
  return cleaned || 'tbl';
}

export function jsonToInsert(jsonString: string, opts: Options): Result {
  let data: unknown;
  try {
    data = JSON.parse(jsonString);
  } catch (e) {
    return { ok: false, sql: '', rows: 0, error: (e as Error).message };
  }

  const arr: unknown[] = Array.isArray(data) ? data : [data];
  if (arr.length === 0) return { ok: false, sql: '', rows: 0, error: 'No rows to insert.' };
  if (!arr.every((r) => r !== null && typeof r === 'object' && !Array.isArray(r))) {
    return { ok: false, sql: '', rows: 0, error: 'Rows must be objects.' };
  }

  const dialect = opts.dialect ?? 'standard';
  const quoteIdentifiers = opts.quoteIdentifiers ?? true;
  const table = quoteIdent(sanitizeIdent(opts.table), dialect, quoteIdentifiers);

  // Union of keys, first-seen order.
  const cols: string[] = [];
  for (const row of arr as Record<string, unknown>[]) {
    for (const k of Object.keys(row)) if (!cols.includes(k)) cols.push(k);
  }
  const colList = cols.map((c) => quoteIdent(c, dialect, quoteIdentifiers)).join(', ');

  const valueLists = (arr as Record<string, unknown>[]).map((row) => {
    const vals = cols.map((c) => sqlLiteral(c in row ? row[c] : null, dialect));
    return `  (${vals.join(', ')})`;
  });

  const sql = `INSERT INTO ${table} (${colList}) VALUES\n${valueLists.join(',\n')};`;
  return { ok: true, sql, rows: arr.length, error: null };
}