Stream a large CSV export in Node.js

Approach

Use a database cursor stream (pg-query-stream), transform rows to CSV lines, and pipeline() them into the HTTP response. pipeline handles backpressure and cleans up all streams on errors or client disconnects.

Solution

import { pipeline } from "node:stream/promises";
import { Transform } from "node:stream";
import QueryStream from "pg-query-stream";

const csvEscape = (v: unknown) => {
  const s = v === null || v === undefined ? "" : String(v);
  return /[",\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
};

app.get("/api/exports/orders.csv", requireAuth, async (req, res) => {
  const client = await pool.connect();
  try {
    const rows = client.query(new QueryStream(
      "SELECT id, status, total_cents, created_at FROM orders WHERE tenant_id = $1 ORDER BY id",
      [req.user!.tenantId], { batchSize: 1000 },
    ));
    res.setHeader("Content-Type", "text/csv; charset=utf-8");
    res.setHeader("Content-Disposition", 'attachment; filename="orders.csv"');
    res.write("id,status,total_cents,created_at\n");
    await pipeline(
      rows,
      new Transform({
        objectMode: true,
        transform(row, _enc, cb) {
          cb(null, [row.id, row.status, row.total_cents, row.created_at.toISOString()].map(csvEscape).join(",") + "\n");
        },
      }),
      res,
    );
  } finally {
    client.release();
  }
});

Pitfalls

• SELECT * into an array for huge tables.
• Ignoring backpressure with manual res.write loops.
• CSV injection: values starting with =, +, -, @ may execute in spreadsheets — prefix with ' for untrusted data.

Variations

• Generate exports asynchronously to S3 and email a link for very large datasets.

Further reading: Node.js: stream.pipeline