JSON to SQL INSERT — JavaScript source

/**
 * json-to-sql - Pure JSON → SQL INSERT converter.
 * Language: JavaScript (ES module).
 *
 * CosmoDev polyglot showcase port of the `json-to-sql` tool, ported from
 * src/lib/jsonToSql.ts. Display source - part of CosmoDev's polyglot tool
 * pages (https://dev.cosmolabs.org).
 *
 * Design notes:
 *   - Pure & side-effect free. Never throws: malformed JSON is reported via
 *     the returned `error` field, matching the TypeScript contract exactly.
 *   - Strings: single quotes are always doubled (' -> ''). MySQL mode
 *     additionally escapes the backslash and the control bytes that can
 *     terminate a statement (NUL, LF, CR, SUB).
 *   - Scalars: null/undefined -> NULL; booleans -> TRUE/FALSE (1/0 in MySQL);
 *     non-finite numbers -> NULL; numbers rendered via String() semantics.
 *   - Objects/arrays become a JSON text literal (compact, unescaped Unicode).
 */

/** @typedef {'standard' | 'mysql' | 'postgres'} Dialect */

/**
 * INSERT builder configuration.
 * @typedef {Object} Options
 * @property {string} table                       Target table name (sanitized).
 * @property {Dialect} [dialect='standard']       SQL flavour for escaping/quoting.
 * @property {boolean} [quoteIdentifiers=true]    Wrap identifiers in quotes.
 */

/**
 * Conversion result.
 * @typedef {Object} Result
 * @property {boolean} ok
 * @property {string} sql
 * @property {number} rows
 * @property {string|null} error
 */

/**
 * Escape a string for safe inclusion in a single-quoted SQL literal.
 * Single quotes are always doubled; MySQL mode additionally neutralises the
 * backslash and the statement-terminating control bytes.
 *
 * @param {string} s
 * @param {Dialect} [dialect='standard']
 * @returns {string}
 */
export function escapeSqlString(s, dialect = 'standard') {
  // ANSI behaviour everywhere: doubling the quote neutralises it.
  let out = s.replace(/'/g, "''");
  if (dialect === 'mysql') {
    // Backslash is escaped FIRST, so the backslashes introduced by the
    // replacements below are not themselves re-doubled (matches the TS order).
    out = out
      .replace(/\\/g, '\\\\')   // \  -> \\
      .replace(/\0/g, '\\0')    // NUL
      .replace(/\n/g, '\\n')    // LF
      .replace(/\r/g, '\\r')    // CR
      .replace(/\x1a/g, '\\Z'); // SUB - ends input on some MySQL clients
  }
  return out;
}

/**
 * Render a JavaScript value as a SQL literal.
 *
 * @param {unknown} value
 * @param {Dialect} dialect
 * @returns {string}
 */
export function sqlLiteral(value, dialect) {
  if (value === null || value === undefined) return 'NULL';
  if (typeof value === 'boolean') {
    // MySQL has no BOOL literal - it aliases TINYINT(1).
    return dialect === 'mysql' ? (value ? '1' : '0') : value ? 'TRUE' : 'FALSE';
  }
  if (typeof value === 'number') {
    // NaN / ±Infinity cannot be represented as SQL number literals.
    return Number.isFinite(value) ? String(value) : 'NULL';
  }
  if (typeof value === 'string') {
    return `'${escapeSqlString(value, dialect)}'`;
  }
  // Plain objects and arrays are stored verbatim as JSON text.
  return `'${escapeSqlString(JSON.stringify(value), dialect)}'`;
}

/**
 * Quote an identifier per dialect, or leave it bare when quoting is disabled.
 */
function quoteIdent(name, dialect, quoteIdentifiers) {
  if (!quoteIdentifiers) return name;
  return dialect === 'mysql' ? `\`${name}\`` : `"${name}"`;
}

/**
 * Reduce an identifier to the safe subset [A-Za-z0-9_], defaulting to "tbl"
 * when nothing usable remains. Runs before quoting, even when quoting is off.
 */
function sanitizeIdent(name) {
  const cleaned = String(name ?? '').replace(/[^A-Za-z0-9_]/g, '_');
  return cleaned || 'tbl';
}

/**
 * Convert a JSON string into a single multi-row INSERT statement.
 *
 * A bare JSON object is treated as one row; a JSON array as many. Rows may
 * have differing shapes - the column list is the union of every key in
 * first-seen order, and missing values are emitted as NULL.
 *
 * @param {string} jsonString
 * @param {Options} opts
 * @returns {Result}
 */
export function jsonToInsert(jsonString, opts) {
  let data;
  try {
    data = JSON.parse(jsonString);
  } catch (e) {
    return { ok: false, sql: '', rows: 0, error: e.message };
  }

  // A bare object is one row; an array is many.
  const arr = 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 every row's keys, first-seen order. A column absent from a given
  // row is emitted as NULL below, so heterogeneous schemas unify cleanly.
  const cols = [];
  for (const row of arr) {
    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.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 };
}