阅读中文版本

Build a CSV to SQLite ETL Pipeline

An extract-transform-load pipeline that reads CSV files, validates and typecasts rows against a schema, loads them into SQLite in batches, and reports rows accepted and rejected.

etl-pipeline/
├── src/
│   ├── extract.js
│   ├── transform.js
│   ├── load.js
│   ├── report.js
│   └── cli.js
├── test/
│   ├── transform.test.js
│   └── pipeline.test.js
└── package.json

import { readFile } from 'node:fs/promises';

export function parseCsv(text) {
  const rows = text.trim().split(/\r?\n/).map((line) => line.split(','));
  const header = rows.shift();
  return rows.filter((r) => r.length > 1).map((cells) => {
    const row = {};
    header.forEach((h, i) => { row[h.trim()] = (cells[i] ?? '').trim(); });
    return row;
  });
}

export async function extract(paths) {
  const rows = [];
  for (const path of paths) rows.push(...parseCsv(await readFile(path, 'utf8')));
  return rows;
}

export function transform(rows, schema) {
  const accepted = [];
  const rejected = [];
  rows.forEach((row, index) => {
    const out = {};
    let reason = null;
    for (const col of schema) {
      const raw = row[col.name];
      if (!raw) {
        if (col.required) { reason = 'missing ' + col.name; break; }
        out[col.name] = null;
        continue;
      }
      if (col.type === 'integer') {
        const n = Number.parseInt(raw, 10);
        if (Number.isNaN(n)) { reason = 'bad integer in ' + col.name; break; }
        out[col.name] = n;
      } else out[col.name] = raw;
    }
    if (reason) rejected.push({ index, reason });
    else accepted.push(out);
  });
  return { accepted, rejected };
}

export function load(db, table, columns, rows) {
  const placeholders = columns.map(() => '?').join(', ');
  const stmt = db.prepare('INSERT INTO ' + table + ' (' + columns.join(', ') + ') VALUES (' + placeholders + ')');
  let inserted = 0;
  db.exec('BEGIN');
  try {
    for (const row of rows) {
      stmt.run(...columns.map((c) => row[c] ?? null));
      inserted++;
    }
    db.exec('COMMIT');
    return inserted;
  } catch (err) {
    db.exec('ROLLBACK');
    throw err;
  }
}

export function summarize({ read, accepted, rejected, loaded }) {
  return [
    'read:     ' + read,
    'accepted: ' + accepted,
    'rejected: ' + rejected.length,
    'loaded:   ' + loaded,
  ].join('\n');
}

export function rejectionSample(rejected, limit = 5) {
  return rejected.slice(0, limit).map((r) => 'row ' + r.index + ': ' + r.reason);
}

import { extract } from './extract.js';
import { transform } from './transform.js';
import { load } from './load.js';

export async function runPipeline({ db, paths, schema, table }) {
  const raw = await extract(paths);
  const { accepted, rejected } = transform(raw, schema);
  const columns = schema.map((c) => c.name);
  const types = schema.map((c) => c.name + ' ' + (c.type === 'integer' ? 'INTEGER' : 'TEXT')).join(', ');
  db.exec('CREATE TABLE IF NOT EXISTS ' + table + ' (' + types + ')');
  const loaded = load(db, table, columns, accepted);
  return { read: raw.length, accepted: accepted.length, rejected, loaded };
}

import { readFile } from 'node:fs/promises';
import { DatabaseSync } from 'node:sqlite';
import { runPipeline } from './pipeline.js';
import { summarize } from './report.js';

const [csvArgs, schemaPath, dbPath] = [process.argv.slice(2, -2), process.argv.at(-2), process.argv.at(-1)];
const schema = JSON.parse(await readFile(schemaPath, 'utf8'));
const db = new DatabaseSync(dbPath ?? ':memory:');
const result = await runPipeline({ db, paths: csvArgs, schema, table: 'records' });
console.log(summarize(result));

Project overview

ETL is often taught as three big frameworks, but the useful version is three small functions with a clear contract between them. This project builds that version and makes the rejection path as first-class as the happy path.

Rows flow from CSV, through a schema-driven transform, into SQLite. Anything that fails validation is collected with a reason rather than aborting the load, and the summary tells you exactly what happened at each stage.

The project is deliberately sized so that every stage ships something you can run: the first phase produces a working command or endpoint, and each later phase adds one capability without forcing a rewrite of what came before. That property — always runnable, never half-migrated — is what makes the tutorial usable as a weekend build rather than a sketch that only works once the final commit lands.

The finished tool carries no hidden services and no hosted dependencies. Everything it needs lives in the repository and a machine with the stated runtime, so a reader can clone, install, run the tests, and trust that the behaviour they see locally is the behaviour the tutorial promised. Where a real deployment needs an external system, that seam is called out explicitly rather than smuggled in through a default.

It is written for a working developer who wants the whole shape of a real tool in one readable pass rather than a tour of a framework. Each phase states the decision it makes and why, then shows the code that encodes it, so the reader can stop after any phase with something that runs and resume later without relearning the whole design. The prose is deliberately honest about trade-offs: where a simpler approach would break at scale, that ceiling is named instead of hidden behind a benchmark.

The transform is the heart of the pipeline: it typecasts good rows and quarantines bad ones with a reason:

export function transform(rows, schema) {
  const accepted = [];
  const rejected = [];
  rows.forEach((row, index) => {
    const out = {};
    for (const col of schema) {
      const raw = row[col.name];
      if (raw == null || raw === '') {
        if (col.required) return rejected.push({ index, reason: 'missing ' + col.name });
        out[col.name] = null;
        continue;
      }
      const value = col.type === 'integer' ? Number.parseInt(raw, 10) : raw;
      if (col.type === 'integer' && Number.isNaN(value)) {
        return rejected.push({ index, reason: 'bad integer in ' + col.name });
      }
      out[col.name] = value;
    }
    accepted.push(out);
  });
  return { accepted, rejected };
}

Because rejection collects a reason and an index, a data-quality problem in the source file is diagnosable without re-running the load.

Setup

This project targets Node 22 for the built-in SQLite module, so the load stage needs no native dependency.

Set up the workspace before writing any logic. The table below pins every dependency to a version so the behaviour you observe matches the tutorial; floating versions are the most common reason a reader’s output diverges from the text. Install exactly what the table lists, then confirm the toolchain reports the expected version before continuing.

Two configuration choices deserve attention up front. First, the project keeps its runtime entry and its test entry as separate scripts so that running tests never starts a server or writes to a data directory. Second, all tunables are read from one place — a small config module or environment variables — so that nothing depends on the working directory a command happens to be launched from.

Create the project and install:

mkdir etl-pipeline && cd etl-pipeline
npm init -y
npm install -D vitest@^2.1.0

Configure the pipeline entry and the tests:

{
  "name": "etl-pipeline",
  "type": "module",
  "scripts": {
    "etl": "node src/cli.js",
    "test": "vitest run"
  }
}

Point the database path at an environment variable so tests use :memory: and never write a development database.

Phase 1: Extract stage

Read one or more CSV files into raw row objects using the header, keeping all values as strings. Keeping each phase self-contained is what makes the whole project reviewable one commit at a time.

The first hard decision is where the boundary between parsing and doing sits. Input arrives as raw text — argv, environment variables, a file on disk — and this phase converts it exactly once into a typed object that the rest of the program trusts. Confining that conversion to a single module means every later layer receives clean, already-validated input and never re-checks a string, which removes a whole class of bugs where two modules quietly disagree about what a flag or a field means. The second decision is failure visibility: invalid input must produce an actionable message and a non-zero exit rather than a stack trace surfacing from three layers down. Getting these two boundaries right early is what lets the remaining phases stay small.

import { readFile } from 'node:fs/promises';

export function parseCsv(text) {
  const rows = text.trim().split(/\r?\n/).map((line) => line.split(','));
  const header = rows.shift();
  return rows.filter((r) => r.length > 1).map((cells) => {
    const row = {};
    header.forEach((h, i) => { row[h.trim()] = (cells[i] ?? '').trim(); });
    return row;
  });
}

export async function extract(paths) {
  const rows = [];
  for (const path of paths) rows.push(...parseCsv(await readFile(path, 'utf8')));
  return rows;
}

Acceptance for this phase: Extraction reads every file, keeps values as strings, and ignores a trailing empty line without producing an empty row.

Phase 2: Transform stage

Apply the declared schema: typecast, enforce required columns, and split rows into accepted and rejected. Keeping each phase self-contained is what makes the whole project reviewable one commit at a time.

This phase defines the data model that every other module speaks. The choice of shape matters more than it looks: flat records are easy to serialize and diff, nested structures model relationships but complicate equality checks, and identifiers must be stable across edits so that references never silently rebind. The code here stays pure — functions take data and return data, with no clocks, no disk, and no printing — because pure functions are the only kind that can be tested exhaustively without a fixture harness. When the model is right the surrounding code almost writes itself; when it is wrong every later phase pays interest.

export function transform(rows, schema) {
  const accepted = [];
  const rejected = [];
  rows.forEach((row, index) => {
    const out = {};
    let reason = null;
    for (const col of schema) {
      const raw = row[col.name];
      if (!raw) {
        if (col.required) { reason = 'missing ' + col.name; break; }
        out[col.name] = null;
        continue;
      }
      if (col.type === 'integer') {
        const n = Number.parseInt(raw, 10);
        if (Number.isNaN(n)) { reason = 'bad integer in ' + col.name; break; }
        out[col.name] = n;
      } else out[col.name] = raw;
    }
    if (reason) rejected.push({ index, reason });
    else accepted.push(out);
  });
  return { accepted, rejected };
}

Acceptance for this phase: A row missing a required column is rejected with a named reason, an optional blank becomes null, and a valid row passes through with a numeric id.

Phase 3: Load stage

Insert accepted rows into SQLite through a prepared statement inside one transaction, rolling back on error. Keeping each phase self-contained is what makes the whole project reviewable one commit at a time.

The core operation is where the project delivers its value, and the temptation is to cram every option into one function. Resist it. The main path should read as a short, obvious sequence — load, transform, emit — with the branching behaviour factored into small decisions that can be tested on their own. This phase also fixes the error contract: which failures are fatal, which are recoverable, and how a partial result is reported. A tool that can explain what it did and why it stopped is far easier to trust in a pipeline than one that only signals success or silence.

export function load(db, table, columns, rows) {
  const placeholders = columns.map(() => '?').join(', ');
  const stmt = db.prepare('INSERT INTO ' + table + ' (' + columns.join(', ') + ') VALUES (' + placeholders + ')');
  let inserted = 0;
  db.exec('BEGIN');
  try {
    for (const row of rows) {
      stmt.run(...columns.map((c) => row[c] ?? null));
      inserted++;
    }
    db.exec('COMMIT');
    return inserted;
  } catch (err) {
    db.exec('ROLLBACK');
    throw err;
  }
}

Acceptance for this phase: Loading accepted rows inserts exactly that many rows, and an error mid-load rolls back so the table row count is unchanged.

Phase 4: Report stage

Summarise read, accepted, rejected, and loaded counts and print a few rejection reasons. Keeping each phase self-contained is what makes the whole project reviewable one commit at a time.

Integration is where the project stops being a toy. Input may arrive in a format an external system produced, output may need to match a contract another system consumes, and the two rarely line up without adjustment. This phase owns the adapters — the small translation functions between the outside world’s shape and the internal model — and it owns the boundary decisions about what to do when the two disagree: coerce, reject, or warn and continue. Keeping adapters thin and explicit means a change to an external format touches one file, and the internal model stays stable as the ecosystem around it moves.

export function summarize({ read, accepted, rejected, loaded }) {
  return [
    'read:     ' + read,
    'accepted: ' + accepted,
    'rejected: ' + rejected.length,
    'loaded:   ' + loaded,
  ].join('\n');
}

export function rejectionSample(rejected, limit = 5) {
  return rejected.slice(0, limit).map((r) => 'row ' + r.index + ': ' + r.reason);
}

Acceptance for this phase: The summary prints aligned counts and the rejection sample lists the first few reasons with their row indexes.

Phase 5: Pipeline wiring

Connect extract, transform, and load, create the target table, and return the stage counts. Keeping each phase self-contained is what makes the whole project reviewable one commit at a time.

The user-facing surface determines whether the project feels finished. A command-line tool owes its caller predictable stdout, meaningful stderr, and honest exit codes; a service owes a stable request and response schema plus consistent status handling. This phase wires the internal modules to that surface and nothing else — it contains no business logic of its own, only translation between the outside contract and the internal calls. That separation is what lets the same core serve a CLI today and an HTTP endpoint tomorrow without duplication, and it is why the tests for this layer assert process-level behaviour rather than poking at internals.

import { extract } from './extract.js';
import { transform } from './transform.js';
import { load } from './load.js';

export async function runPipeline({ db, paths, schema, table }) {
  const raw = await extract(paths);
  const { accepted, rejected } = transform(raw, schema);
  const columns = schema.map((c) => c.name);
  const types = schema.map((c) => c.name + ' ' + (c.type === 'integer' ? 'INTEGER' : 'TEXT')).join(', ');
  db.exec('CREATE TABLE IF NOT EXISTS ' + table + ' (' + types + ')');
  const loaded = load(db, table, columns, accepted);
  return { read: raw.length, accepted: accepted.length, rejected, loaded };
}

Acceptance for this phase: A small CSV runs end to end, the target table is created if absent, and the returned counts match the input.

Phase 6: CLI and tests

Wire the pipeline to a CLI with a schema file, then cover transform and the full pipeline with tests. Keeping each phase self-contained is what makes the whole project reviewable one commit at a time.

The final phase turns a working program into a dependable one. Tests are the visible part, but the discipline behind them is deeper: every claim the earlier phases made is pinned by an executable check, and every failure mode the tutorial mentioned has a test that reproduces it before the fix. Edge cases — empty input, malformed records, concurrent invocations, interrupted writes — are enumerated and asserted rather than assumed away. The payoff is a suite that fails loudly and precisely when someone changes a decision, which is exactly the signal a maintainer wants the moment they touch the code.

import { readFile } from 'node:fs/promises';
import { DatabaseSync } from 'node:sqlite';
import { runPipeline } from './pipeline.js';
import { summarize } from './report.js';

const [csvArgs, schemaPath, dbPath] = [process.argv.slice(2, -2), process.argv.at(-2), process.argv.at(-1)];
const schema = JSON.parse(await readFile(schemaPath, 'utf8'));
const db = new DatabaseSync(dbPath ?? ':memory:');
const result = await runPipeline({ db, paths: csvArgs, schema, table: 'records' });
console.log(summarize(result));

Acceptance for this phase: Running with a CSV, schema, and database path prints the stage summary, and a missing schema file exits non-zero with an error.

Design trade-offs

Minimal dependencies over framework convenience. A framework would hand over plumbing for free, but it would also own the interesting decisions this project exists to teach. Keeping the dependency surface small means the lifecycle is visible end to end, the install is instant, and the tool runs anywhere the stated runtime does. The cost is owning the edge cases yourself — which is precisely why each of them gets a test.

Explicit contracts over clever conventions. Where a convention would infer behaviour from a filename or a folder, the project asks for a named export or a declared field instead. It reads as slightly more verbose and is far easier to review: a reader sees the intent on the line that owns it rather than reconstructing it from directory layout. Debugging also gets cheaper, because the failure points at the declaration instead of the convention that silently produced it.

Deterministic behaviour over convenience defaults. Anything that could vary between machines — time, ordering, encoding, locale — is injected or pinned rather than read from the ambient environment. That choice costs a little ceremony and buys reproducible output, which is the property that makes the output diffable in review and the tests trustworthy across the team’s machines.

Testing strategy

Run npx vitest. Transform tests cover missing required columns, bad integer casts, optional blanks, and a good row. Load tests load accepted rows into an in-memory database and assert the row count and a rolled-back transaction on error. A pipeline test runs a small CSV end to end and asserts the summary counts.

Tests are organised by layer so a failure points at the decision that broke. Pure unit tests cover the transformation logic without touching disk or network and run in milliseconds; integration tests exercise the real entry point against a temporary working directory and assert the observable contract; a small end-to-end tier proves the whole path produces the documented output. Keeping the fast tiers large and the slow tier thin is what lets the suite run on every save while still catching integration mistakes.

Every test asserts a specific promise from the phases above rather than merely covering lines. A passing suite therefore means the tool kept its word: filters compose, identifiers stay stable, failures exit non-zero with a readable message, and the output matches the format a downstream consumer expects. When someone changes a contract the matching test fails with an exact diff, turning a silent regression into a line in the pull request.

Running the suite in CI is the last line of defence, but the same command runs locally before every commit, which is what keeps the feedback loop short. A green run means the documented behaviour still holds; a red run names the exact promise that broke. Treat the suite as executable documentation and it stays current, because a stale assertion fails the moment the code and the text disagree.

The rollback test is the important one: it proves a mid-load failure leaves the database unchanged, which is the property a transaction exists to guarantee.

Extending the project

Additional sources such as JSON or an API are new extractors; additional target tables are schema declarations; and a dry-run mode is the transform without the load. Because stages communicate through plain arrays, each can be tested in isolation.

The structure leaves obvious seams for growth. Each capability that could be added — a new output format, an extra filter, an interactive mode — lands in one layer because the boundaries between parsing, logic, and presentation are already drawn. Extensions that would break the current design are equally visible: the moment a feature needs cross-process coordination or a database, the storage interface is where the change belongs, and its tests tell you immediately whether the surrounding code survives the swap.