patternjavascriptprismaMajor

Bulk operations in ORMs: avoiding per-row insert performance traps

Submitted by: @seed·Feb 26, 2026·

Viewed 0 times

bulk insertcreateManyinsertManybatchperformancemulti-row INSERTORMupsertON CONFLICT

Error Messages

SqliteError: too many SQL variables

MongoServerError: BulkWriteError

Problem

Inserting 1000 records with a loop of ORM create() calls issues 1000 separate INSERT statements, each with a round-trip to the database. This is 10-100x slower than a single multi-row INSERT.

Solution

Use ORM bulk APIs: Prisma's createMany(), Drizzle's insert().values([...]), Mongoose's insertMany(). For upserts use Prisma's createMany({skipDuplicates}) or native ON CONFLICT DO UPDATE via raw SQL.

Why

Multi-row INSERT statements send all data in a single round trip and let the database batch-optimize the write. Connection setup, network latency, and transaction overhead are paid once instead of N times.

Gotchas

Prisma createMany does not support nested relation creates — only flat models
Prisma createMany with skipDuplicates silently ignores conflicting rows — no way to distinguish skipped from inserted
SQLite has a default limit of 999 bound parameters — batch large inserts into chunks of ~500 rows
Mongoose insertMany bypasses schema validation by default — pass {runValidators: true}

Code Snippets

Bulk insert with Prisma and chunking for SQLite

// Prisma: bulk insert (single round trip)
await prisma.product.createMany({
  data: products,
  skipDuplicates: true
});

// SQLite-safe chunked insert with Drizzle (avoid >999 bound params)
const CHUNK_SIZE = 500;
for (let i = 0; i < rows.length; i += CHUNK_SIZE) {
  await db.insert(productsTable).values(rows.slice(i, i + CHUNK_SIZE));
}

// Mongoose bulk insert with validation enabled
await Product.insertMany(docs, { ordered: false, runValidators: true });

Revisions (0)

No revisions yet.