CSV to SQL Importer — JavaScript source

// csv-to-sql — pure CSV → SQL import generator. JavaScript port (canonical TS:
// src/lib/csv-to-sql.ts; Go twin: cli/csv-to-sql). RFC 4180 parse, header
// sanitizing into SQL identifiers, then batched INSERTs, a Postgres COPY
// block, or a MySQL LOAD DATA statement. Numeric-looking text emits bare and
// verbatim; empty fields become NULL. Never throws.
'use strict';

function csvToRows(csv) {
  const rows = [];
  let field = '';
  let row = [];
  let inQ = false;
  for (let i = 0; i < csv.length; i++) {
    const ch = csv[i];
    if (inQ) {
      if (ch === '"') {
        if (csv[i + 1] === '"') { field += '"'; i++; } else inQ = false;
      } else field += ch;
    } else if (ch === '"') inQ = true;
    else if (ch === ',') { row.push(field); field = ''; }
    else if (ch === '\n') { row.push(field); rows.push(row); row = []; field = ''; }
    else if (ch !== '\r') field += ch;
  }
  if (field.length > 0 || row.length > 0) { row.push(field); rows.push(row); }
  return rows;
}

const NUMERIC = /^-?(\d+(\.\d+)?|\.\d+)([eE][+-]?\d+)?$/;

function escapeSqlString(s, dialect) {
  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;
}

function fieldLiteral(value, dialect, inferTypes) {
  if (inferTypes) {
    if (value === '') return 'NULL';
    if (NUMERIC.test(value)) return value; // verbatim — no float round-trip
  }
  return "'" + escapeSqlString(value, dialect) + "'";
}

function sanitizeIdent(name) {
  return name.replace(/[^A-Za-z0-9_]/g, '_');
}

function sanitizeHeaders(headers) {
  const seen = new Map();
  return headers.map((h, i) => {
    let id = sanitizeIdent(h.trim()) || 'col' + (i + 1);
    const n = (seen.get(id) || 0) + 1;
    seen.set(id, n);
    return n > 1 ? id + '_' + n : id;
  });
}

function csvEscape(field) {
  return /[",\n\r]/.test(field) ? '"' + field.replace(/"/g, '""') + '"' : field;
}

function csvToSql(csv, opts) {
  const text = csv.trim();
  const rows = csvToRows(text);
  if (!text || rows.length < 2) return { ok: false, sql: '', rows: 0, error: 'No rows to import.' };

  const { table, format = 'insert', dialect = 'standard', batchSize = 100,
    inferTypes = true, quoteIdentifiers = true, fileName = 'import.csv' } = opts;
  const cols = sanitizeHeaders(rows[0]);
  const data = rows.slice(1);
  const identDialect = format === 'copy' ? 'postgres' : format === 'load-data' ? 'mysql' : dialect;
  const q = (name) => quoteIdentifiers
    ? (identDialect === 'mysql' ? '`' + name + '`' : '"' + name + '"') : name;
  const tbl = q(sanitizeIdent(table) || 'tbl');
  const colList = cols.map(q).join(', ');

  if (format === 'copy' || format === 'load-data') {
    const payload = [cols.map(csvEscape).join(','),
      ...data.map((r) => cols.map((_, ci) => csvEscape(r[ci] || '')).join(','))];
    if (format === 'copy') {
      return { ok: true, rows: data.length, error: null,
        sql: `COPY ${tbl} (${colList}) FROM STDIN WITH (FORMAT csv, HEADER true);\n${payload.join('\n')}\n\\.` };
    }
    const clean = fileName.replace(/[^A-Za-z0-9._\-/]/g, '') || 'import.csv';
    return { ok: true, rows: data.length, error: null,
      sql: `LOAD DATA LOCAL INFILE '${clean}'\nINTO TABLE ${tbl}\n` +
        `FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'\n` +
        `LINES TERMINATED BY '\\n'\nIGNORE 1 LINES;\n\n${payload.join('\n')}` };
  }

  const stmts = [];
  for (let i = 0; i < data.length; i += Math.max(1, batchSize)) {
    const values = data.slice(i, i + Math.max(1, batchSize)).map((r) =>
      '  (' + cols.map((_, ci) => fieldLiteral(r[ci] || '', dialect, inferTypes)).join(', ') + ')');
    stmts.push(`INSERT INTO ${tbl} (${colList}) VALUES\n${values.join(',\n')};`);
  }
  return { ok: true, sql: stmts.join('\n'), rows: data.length, error: null };
}

// Example:
// csvToSql('id,name\n1,Ada\n2,', { table: 'users' }).sql
// → 'INSERT INTO "users" ("id", "name") VALUES\n  (1, \'Ada\'),\n  (2, NULL);'