A CSV import built for a few million rows

More than one application needed the same capability: get CSV data into PayloadCMS collections, and get it back out again. The complication was volume.

The files could run to millions of records, which makes throughput a design constraint rather than something to profile afterwards. Reading a file that size into memory to validate it is not a slow version of the right approach — it is a different approach that stops working.

Designing for the volume

So the data streams, and validation happens as it passes rather than in a pass of its own. Nothing reaches the database without being checked, because a bulk import is precisely where bad data gets in at scale, and a half-succeeded import of a few million rows is a worse problem to be handed than a failed one.

Transforming on the way through

The format a client’s file happens to use is rarely the shape the collection wants. I added a transformer that works in both directions, so records can be adjusted on the way from a CSV into the database and on the way back out.

One hook, both directions. That matters more than it sounds: it gives the awkward-format problem a single place to live, instead of being solved slightly differently by every caller that hits it.

What it generalises to

Validate at the boundary and stream through it, and the size of the input stops being a question about correctness — it becomes a question about time, which is a much easier kind of problem to have.

The transformer is the part I would reach for again. Most integration pain is not the transfer, it is the hundred small shape mismatches around it, and those are worth giving one deliberate home rather than letting them accumulate at every call site.