阅读中文版本

Build a CSV to SQL Converter with Type Inference

A CLI that reads CSV files, infers column types, generates CREATE TABLE and batched INSERT statements, and streams the conversion so million-row files convert with flat memory.

csv2sql/
├── bin/csv2sql.js
├── src/
│   ├── reader.js
│   ├── infer.js
│   ├── emit.js
│   └── convert.js
├── test/
│   ├── infer.test.js
│   └── emit.test.js
└── package.json

async function* readRows(file) {
  const splitter = createCsvSplitter();
  for await (const chunk of fs.createReadStream(file, { encoding: 'utf8' })) {
    for (const row of splitter.push(chunk)) {
      if (row.length) yield row;
    }
  }
  const rest = splitter.flush();
  if (rest.length) yield rest;
}

const TYPES = ['integer', 'float', 'boolean', 'date', 'string'];
function propose(values) {
  let current = 'integer';
  for (const v of values) {
    if (v === '') continue;
    const t = classify(v);
    current = widen(current, t);
    if (current === 'string') break;
  }
  return current;
}

function quoteValue(value, type) {
  if (value === '') return 'NULL';
  if (type === 'string') return "'" + value.replace(/'/g, "''") + "'";
  if (type === 'date') return "DATE '" + value + "'";
  return value;
}

await pipeline(
    readRows(input),
    batchInserts({ size: batchSize }),
    async function* (sqlChunks) {
      for (const chunk of sqlChunks) {
        const canContinue = writer.write(chunk);
        if (!canContinue) await once(writer, 'drain');
      }
    },
  );

Project overview

CSV to SQL sounds like a solved problem until the input has a million rows, a column of leading-zero IDs, and a cell containing a quoted newline. This project builds the converter that survives those inputs: streaming end to end, types inferred with documented rules, and SQL emitted with escaping you can audit.

The architecture is a pipeline with backpressure as the load-bearing wall. The output writer sets the pace, the iterator chain propagates pauses, and the reader never reads faster than SQL is written. Constant memory is a test assertion here, not an aspiration.

The finished tool delivers five capabilities:

• Streaming conversion with constant memory regardless of input size — a million-row file converts with a flat heap.
• Type inference per column — integer, float, boolean, date, and string — with per-cell fallback reporting when strict mode rejects mixed types.
• Correct SQL string escaping, including quotes, newlines inside cells, and the NULL-versus-empty-string distinction under a flag.
• Configurable INSERT batch size and dialect options for placeholders or literal values.
• A dry-run summary printing the inferred schema and row count before writing anything.

The pipeline has four stages, each with one job: a CSV reader yields parsed rows one at a time through an async iterator, a type inference pass consumes a configurable sample window and then validates the remainder, an emitter generates the CREATE TABLE header and batches rows into multi-row INSERTs, and the conversion composer wires them with backpressure flowing from the output stream writer through the iterator to the reader. Nothing buffers the full input.

Setup

Verify the prerequisites before starting: Node.js 20 LTS or later, an understanding of SQL identifier and literal quoting rules, and familiarity with Node streams and async iterators.

The dependency footprint is one runtime package plus the test runner:

Create the project and install:

mkdir csv2sql && cd csv2sql
npm init -y
npm install commander@^12.0.0
npm install -D vitest@^2.1.0

Configure the environment in package.json — a bin entry for global install, the test script, and no other configuration; every runtime option arrives as a CLI flag:

{
  "name": "csv2sql",
  "bin": { "csv2sql": "bin/csv2sql.js" },
  "scripts": { "test": "vitest run" }
}

The directory structure mirrors the pipeline: reader.js, infer.js, and emit.js are pure modules with no file I/O of their own, and convert.js is the only module that composes streams.

Phase 1: Streaming CSV reader

The reader is an async iterator over CSV lines that handles quoted cells, escaped quotes, embedded newlines, and CRLF, yielding arrays of strings.

// src/reader.js
import { createReadStream } from 'node:fs';

export async function* readRows(file) {
  const splitter = createCsvSplitter();
  for await (const chunk of createReadStream(file, { encoding: 'utf8' })) {
    for (const row of splitter.push(chunk)) {
      if (row.length) yield row;
    }
  }
  const rest = splitter.flush();
  if (rest.length) yield rest;
}

The splitter is a small state machine, and its correctness lives in the boundaries: a chunk can end mid-quote, mid-escape, or mid-newline, and the machine holds that partial state across pushes instead of assuming chunks align with rows. A quoted cell containing a newline is the canonical stress case — the terminator inside quotes is data, not a row boundary — and the reader tests feed chunks split exactly at those boundaries and assert the parsed rows are identical to a single-read baseline.

CRLF gets its own rule: \r\n is one terminator, and a trailing \r is stripped before cell parsing rather than after tokenizing, so a cell that legitimately ends with carriage return inside quotes survives. These are the details that decide whether the converter works on exported spreadsheets or only on hand-typed files.

Acceptance for this phase: reader tests feed chunks split mid-quote and mid-newline and assert rows parse identically to a single-read baseline, including newlines inside quoted cells.

Phase 2: Type inference

Inference samples the first N rows to propose per-column types, then validates every cell against the proposal, widening or reporting mismatches per the strictness flag.

// src/infer.js
const TYPES = ['integer', 'float', 'boolean', 'date', 'string'];

export function propose(values) {
  let current = 'integer';
  for (const v of values) {
    if (v === '') continue;
    const t = classify(v);
    current = widen(current, t);
    if (current === 'string') break;
  }
  return current;
}

function classify(v) {
  if (/^[+-]?\d+$/.test(v) && !/^0\d/.test(v)) return 'integer';
  if (/^[+-]?\d+\.\d+$/.test(v)) return 'float';
  if (/^(true|false)$/i.test(v)) return 'boolean';
  if (/^\d{4}-\d{2}-\d{2}$/.test(v)) return 'date';
  return 'string';
}

function widen(a, b) {
  if (a === b) return a;
  if (a === 'integer' && b === 'float') return 'float';
  return 'string';
}

The rule set is explicit because type inference fails when it is silent: integers stay integers unless a float appears, leading zeros force strings (007 is an ID, not a 7 — phone numbers and zip codes look numeric), ISO-shaped values propose dates, and any mismatch widens toward string. Strict mode refuses to widen and reports the exact row and column instead, because silently stringifying a broken integer column hides the upstream bug that broke it.

The two-pass structure — sample to propose, full scan to validate — keeps inference cheap on huge files while still catching the one bad cell at row 900000. The proposal window is configurable precisely because the trade-off is real: a bigger window proposes more accurately, a smaller one starts streaming sooner.

Acceptance for this phase: inference tests cover integer columns that stay integers, integer columns that widen to float on one value, ISO dates, true and false spellings, and strict-mode rejection reports naming the exact row and column.

Phase 3: SQL emitter with escaping

The emitter generates the CREATE TABLE header from inferred types and batched multi-row INSERTs, escaping strings and NULLs per dialect rules.

// src/emit.js
export function quoteValue(value, type, { emptyAsNull = true } = {}) {
  if (value === '' && emptyAsNull) return 'NULL';
  if (value === '') return "''";
  if (type === 'string') return "'" + value.replace(/'/g, "''") + "'";
  if (type === 'date') return "DATE '" + value + "'";
  return value;
}

export function quoteIdentifier(name) {
  return /[^\w]/.test(name) ? '"' + name.replace(/"/g, '""') + '"' : name;
}

export function createTable(tableName, columns, types) {
  const defs = columns.map((c) => `${quoteIdentifier(c)} ${sqlType(types[c])}`).join(', ');
  return `CREATE TABLE ${quoteIdentifier(tableName)} (${defs});`;
}

Escaping is the correctness surface of this tool — SQL injection is not the threat here, but a quote inside a value must double, a newline inside a quoted cell must survive into the literal, and an identifier with spaces must be quoted or the generated SQL is simply wrong. The emitter tests pin exact output strings for each case rather than asserting “contains the value,” because a test that tolerates malformed SQL is a test that ships malformed SQL.

The NULL-versus-empty-string distinction is a data modeling decision the tool refuses to make silently: emptyAsNull is an explicit flag, the default is documented, and both behaviors are tested with pinned strings. Batched INSERTs group N rows per statement — N configurable — because one INSERT per row is a hundredfold slowdown on real databases, while unbounded batches recreate the memory problem the streaming design just solved.

Acceptance for this phase: emitter tests assert escaped quotes double, newlines inside string literals survive, NULL and empty string remain distinct under the flag, and identifiers with spaces are quoted.

Phase 4: Pipelined conversion

The composer wires reader, inference, and emitter with backpressure so the writer controls the pace, and adds the dry-run summary mode.

// src/convert.js
import { pipeline } from 'node:stream/promises';
import { once } from 'node:events';

export async function convert({ input, output, batchSize, tableName }) {
  const writer = createWriteStream(output);
  const reader = readRows(input);

  // first pass: sample for inference, then rewind via a second read
  const schema = await inferSchema(input, { sampleSize: 1000 });
  writer.write(createTable(tableName, schema.columns, schema.types) + '\n');

  await pipeline(
    reader,
    batchInserts({ schema, size: batchSize, tableName }),
    async function* (sqlChunks) {
      for (const chunk of sqlChunks) {
        const canContinue = writer.write(chunk);
        if (!canContinue) await once(writer, 'drain');
      }
    },
  );
  writer.end();
}

The backpressure contract is the phase’s whole point: when writer.write returns false, iteration pauses until the drain event, and that pause propagates back through the batcher to the file read stream. Every stage that ignores the write return value converts constant memory into a slow leak that only appears on files big enough to matter — which is exactly when an OOM stack trace in a production pipeline is most expensive.

The dry-run mode closes the loop for humans: it prints the inferred schema and row count without writing output, so a data engineer inspects the table shape before committing to a load. It runs the same reader and inference code, so the preview is always honest about what the real conversion would emit.

Acceptance for this phase: converting a generated one-million-row CSV completes with heap usage flat, verified by a test that samples process.memoryUsage during conversion, and dry-run prints the schema without creating output.

Design trade-offs

Async iterators over a stream library. A full transform-stream pipeline would be idiomatic, but async generators compose with plain for await loops, test as plain functions, and make the backpressure handoff visible as the write/drain dance rather than hiding it in framework plumbing.

Sample-then-validate over full-scan inference. Full-scan inference is simpler and perfectly accurate; it also buffers nothing but delays the first output byte until the last input byte is read. The two-pass design trades a bounded second read for immediate streaming, and the sample size is a flag because the trade-off belongs to the user, not the tool.

Pinned-string escaping tests over round-trip parsing. Round-tripping generated SQL through a real parser is stronger in theory; in practice it tests the parser as much as the emitter, and failures point at the wrong module. Pinned strings make the escaping rules executable documentation — read the expectation, see the rule.

NULL-by-default over empty-string-by-default. SQL convention favors NULL for missing, spreadsheets export empty cells, and the flag exists so the minority case is opt-in rather than a surprise.

Testing strategy

Run npx vitest. Reader tests split inputs at every boundary type — mid-cell, mid-escape, mid-newline, mid-CRLF — and compare against a single-read baseline, so splitter state bugs fail with a diff of two row arrays. Inference tests table-drive proposals and widening, plus the strict-mode mismatch reports with exact row-and-column coordinates. Emitter tests pin exact SQL strings for escaping, NULL, dates, and quoted identifiers.

The conversion test is the capstone: it generates a million rows on the fly, converts them, re-parses the SQL with a counting parser, and asserts both the row count and the flat memory profile — sampling process.memoryUsage during the run so any buffering stage shows up as a slope in the numbers, which is a far better failure message than an out-of-memory stack trace in production.

A smaller fixture file with a hand-written expected SQL output deserves a place in the suite as well: one CREATE TABLE and two INSERT batches, maybe forty lines total, checked in beside the fixtures. It serves a different audience than the million-row test — a contributor changing the emitter reads the expected file and understands the output contract in one glance, and the test updating that file is the reviewable diff that says “the SQL format changed on purpose.”

Troubleshooting playbook

Rows lost when input uses CRLF line endings. Every second row is empty or values carry a trailing carriage return. The cause is a splitter that treats \r and \n as independent terminators: a CRLF file then yields a phantom empty row after every real one. The fix is treating \r\n as a single terminator and stripping a trailing \r before cell parsing, which the reader’s boundary tests pin with a CRLF fixture.

Values like 007 become integers and lose data. A zip-code column arrives as 7 instead of 007. The cause is numeric classification without a leading-zero rule. The fix is the classify guard — ^0\d forces string — and it is deliberately conservative: a false string costs one CAST, a lost zero costs the data.

Memory grows on huge files. Heap climbs steadily during conversion despite the streaming design. The cause is a stage ignoring the writer.write return value — usually a batcher that accumulates without flushing. The fix is auditing every write for the drain dance, and the million-row memory test exists so this class of bug fails in CI with the slope as evidence rather than in production as an outage.

Extending the project

The dialect layer is the natural next step: placeholder-style INSERTs (? per value) for prepared-statement loaders are one flag away, since the emitter already separates type rendering from value quoting. Column-name transformation — snake_case mapping, header-row skips — belongs in the inference stage where names are already flowing past. Multi-table conversion with a foreign-key map is a real feature with real design surface, and the documented scope boundary keeps it out until needed.

For deployment, install as a global CLI or run through npx in data pipelines. Use it to bootstrap tables before switching to a native bulk loader for ongoing ingestion — parameterized bulk loaders outperform generated SQL at very high volumes — and let the dry-run summary make schema inspection safe before any load commits. A note on generated SQL versus parameterized statements, because the distinction decides where this tool belongs. The emitter produces literal SQL — values inlined into the INSERT text — which is correct for its purpose: a file you can inspect, diff, and pipe into a database shell. It is not the right shape for application code, where parameterized statements (a ? placeholder per value, bound by the driver) are the injection-safe convention. The dialect flag acknowledges the boundary: when a pipeline needs to feed a prepared-statement loader instead, the placeholder mode emits column lists and row tuples without literals, and the two modes share the entire inference and batching machinery. The escaping rules in the literal path still matter, because a generated file that fails to escape a quote is a file that breaks mid-load, and the pinned-string emitter tests are what keep that path trustworthy.