Bulk INSERT and UPDATE in Node.js Without an ORM

The CSV upload pipeline needed to process 15K to 30K records per file. For each record, the system either inserts a new row or updates an existing one. The naive implementation ran one INSERT or UPDATE per record.

At 20K records, that is 20K queries. Each one a round trip to the database. At any reasonable connection latency, this is slow. In production it was why uploads were timing out.

Problem

Individual row operations have two costs: the query execution cost and the round-trip overhead. For a simple INSERT, the round-trip cost dominates. Running 20K sequential inserts means 20K round trips.

At a conservative 2ms per round trip, 20K operations is 40 seconds of pure network overhead, not counting query execution. With connection pool pressure under concurrent load, this gets worse.

The other issue was atomicity. If the process ran individual inserts and something failed at record 15000, the first 14999 records were already committed. Recovery required identifying what succeeded and what did not. Manual cleanup before a safe re-upload.

Solution

Two SQL patterns replaced individual operations: multi-row INSERT for new records, CASE WHEN UPDATE for existing ones.

For inserts, I built a VALUES list from the batch of new records. One query with up to 3000 value groups instead of 3000 individual queries. The database inserts all rows in a single operation.

For updates, I built a CASE WHEN statement mapping each record ID to its new values. One UPDATE query with CASE branches for each record instead of one UPDATE per record.

Batch size is 3000. Large enough to reduce round trips significantly, small enough to stay within practical query size limits and keep memory predictable.

Everything runs inside a transaction. If the batch fails, the entire upload rolls back. No partial state. Clean re-upload path.

Result

A batch of 3000 new records goes from 3000 queries to 1 insert query. A batch of 3000 updates goes from 3000 queries to 1 update query.

For a 20K record upload split into batches of 3000, that is 14 queries instead of 20000.

Upload time for 20K records dropped from timeout-range to about 8 seconds.

The rollback capability eliminated the manual cleanup problem. Operations teams can safely retry failed uploads without checking for partial data first.