CSV to SQL Importer — TypeScript source

// Pure CSV → SQL import generator - no React, no DOM, deterministic. The
// unit-test surface for the CSV to SQL Importer tool. Reuses the RFC 4180
// parser from ./csv (csvToRows); emits three formats: batched multi-row
// INSERT statements, a Postgres COPY FROM STDIN block, and a MySQL LOAD DATA
// statement with the CSV payload. Numeric-looking fields stay bare and
// verbatim (no float round-trip, so the Go twin can match byte-for-byte),
// empty fields become NULL, everything else is an escaped string literal.
// Never throws.

import { csvToRows } from './csv';

export type Dialect = 'standard' | 'mysql' | 'postgres';
export type Format = 'insert' | 'copy' | 'load-data';

export interface Options {
  table: string;
  /** Output format. Default 'insert'. */
  format?: Format;
  /** Identifier quoting + string escaping for INSERT. Default 'standard'.
   *  COPY always uses Postgres rules; LOAD DATA always uses MySQL rules. */
  dialect?: Dialect;
  /** Rows per INSERT statement. Default 100; values <= 0 fall back to 100. */
  batchSize?: number;
  /** When true (default): numeric-looking text emits bare, '' emits NULL.
   *  When false: every field is a quoted string (empty stays ''). */
  inferTypes?: boolean;
  /** Quote identifiers ("col" / `col`). Default true. */
  quoteIdentifiers?: boolean;
  /** File referenced by LOAD DATA output. Default 'import.csv'. */
  fileName?: string;
}

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

/** Numeric literal shape (integer, decimal, or scientific). Matched text is
 *  emitted VERBATIM - "007", "1e3", ".5" all pass through unchanged, which
 *  keeps TypeScript and Go byte-identical (no float formatting ever). */
const NUMERIC_RE = /^-?(?:\d+(?:\.\d+)?|\.\d+)(?:[eE][+-]?\d+)?$/;

/** Escape a single-quoted SQL literal: '' doubling everywhere; MySQL mode
 *  additionally escapes the backslash and statement-hostile control bytes.
 *  (Same contract as jsonToSql's escapeSqlString, kept local so this lib and
 *  its ports stay self-contained.) */
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;
}

/** Render one CSV field as an INSERT value literal. */
export function fieldLiteral(
  value: string,
  opts: { dialect?: Dialect; inferTypes?: boolean } = {},
): string {
  const dialect = opts.dialect ?? 'standard';
  if (opts.inferTypes ?? true) {
    if (value === '') return 'NULL';
    if (NUMERIC_RE.test(value)) return value;
  }
  return `'${escapeSqlString(value, dialect)}'`;
}

/** Replace characters SQL identifiers cannot carry with '_'. */
export function sanitizeIdent(name: string): string {
  return (name || '').replace(/[^A-Za-z0-9_]/g, '_');
}

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

/** Header row -> SQL column names: trim, sanitize, empty -> col<N>, and
 *  de-duplicate deterministically (second occurrence gets _2, then _3...). */
function sanitizeHeaders(headers: string[]): string[] {
  const out: string[] = [];
  const seen = new Map<string, number>();
  headers.forEach((h, i) => {
    let id = sanitizeIdent(h.trim());
    if (!id) id = `col${i + 1}`;
    const n = (seen.get(id) ?? 0) + 1;
    seen.set(id, n);
    if (n > 1) id = `${id}_${n}`;
    out.push(id);
  });
  return out;
}

/** Quote a CSV field for re-emission (RFC 4180): quote when it carries a
 *  comma, quote, or newline; double embedded quotes. */
function csvEscape(field: string): string {
  if (/[",\n\r]/.test(field)) return '"' + field.replace(/"/g, '""') + '"';
  return field;
}

/** LOAD DATA file reference: keep path-safe characters only. */
function sanitizeFileName(name: string): string {
  const cleaned = (name || '').replace(/[^A-Za-z0-9._\-/]/g, '');
  return cleaned || 'import.csv';
}

export function csvToSql(csv: string, opts: Options): Result {
  const fail = (error: string): Result => ({ ok: false, sql: '', rows: 0, error });

  const text = csv.trim();
  if (!text) return fail('No rows to import.');

  // csvToRows on trimmed non-empty text always yields >= 1 row.
  const rows = csvToRows(text);
  const headers = rows[0];
  const data = rows.slice(1);
  if (data.length === 0) return fail('No data rows below the header.');

  const format = opts.format ?? 'insert';
  const dialect = opts.dialect ?? 'standard';
  const inferTypes = opts.inferTypes ?? true;
  const quoteIdentifiers = opts.quoteIdentifiers ?? true;

  const cols = sanitizeHeaders(headers);
  // Quoting semantics follow the format: INSERT honors the dialect option,
  // COPY is Postgres, LOAD DATA is MySQL.
  const identDialect: Dialect = format === 'copy' ? 'postgres' : format === 'load-data' ? 'mysql' : dialect;
  const table = quoteIdent(sanitizeIdent(opts.table) || 'tbl', identDialect, quoteIdentifiers);
  const colList = cols.map((c) => quoteIdent(c, identDialect, quoteIdentifiers)).join(', ');

  if (format === 'copy' || format === 'load-data') {
    // Payload: sanitized header + data rows, re-emitted as clean CSV (an
    // unquoted empty field is NULL under COPY csv semantics).
    const payload = [
      cols.map(csvEscape).join(','),
      ...data.map((row) => cols.map((_, ci) => csvEscape(row[ci] ?? '')).join(',')),
    ];
    if (format === 'copy') {
      const sql =
        `COPY ${table} (${colList}) FROM STDIN WITH (FORMAT csv, HEADER true);\n` +
        `${payload.join('\n')}\n\\.`;
      return { ok: true, sql, rows: data.length, error: null };
    }
    const sql =
      `LOAD DATA LOCAL INFILE '${sanitizeFileName(opts.fileName ?? '')}'\n` +
      `INTO TABLE ${table}\n` +
      `FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'\n` +
      `LINES TERMINATED BY '\\n'\n` +
      `IGNORE 1 LINES;\n\n${payload.join('\n')}`;
    return { ok: true, sql, rows: data.length, error: null };
  }

  // INSERT: batched multi-row statements.
  const batchSize = opts.batchSize && opts.batchSize > 0 ? opts.batchSize : 100;
  const stmts: string[] = [];
  for (let i = 0; i < data.length; i += batchSize) {
    const chunk = data.slice(i, i + batchSize);
    const values = chunk.map(
      (row) =>
        `  (${cols.map((_, ci) => fieldLiteral(row[ci] ?? '', { dialect, inferTypes })).join(', ')})`,
    );
    stmts.push(`INSERT INTO ${table} (${colList}) VALUES\n${values.join(',\n')};`);
  }
  return { ok: true, sql: stmts.join('\n'), rows: data.length, error: null };
}