Payload CMS Large Imports: Fast Transactions & Drizzle

If you need to import tens of thousands of rows into Payload without taking the system down, there are two fast paths: payload.db.* direct database methods and raw Drizzle ORM via the Postgres adapter. Both bypass the Local API's hook and validation pipeline, which is exactly what you want for bulk writes. The key to keeping imports safe is PostgreSQL transactions — small batches committed often — so a failure mid-import rolls back cleanly and you can retry from a checkpoint instead of starting over. This article covers how both approaches work, how to generate your Drizzle schema, and when to pick one over the other.

I hit this problem on a client import job: about 80,000 product records that needed to land in a Payload collection as fast as possible. The Local API worked in development but hammered the database under load because every document went through the full Payload lifecycle — hooks, access control, validation, re-fetching the document after save. For a single document operation that overhead is fine. For 80,000 rows in sequence it is a bottleneck.

What transactions are and why they matter here

A PostgreSQL transaction is a unit of work that either commits entirely or rolls back entirely. When you write to the database inside a transaction, the changes are invisible to other connections until you commit. If anything goes wrong before the commit — an error, a crash, a constraint violation — the entire batch reverts automatically.

For large imports, transactions give you two things. First, atomicity within a batch: a partial write never lands in the database. Second, a recovery point: if you checkpoint after each committed batch, a failure only loses the batch in progress, not everything before it.

The pattern that works is small batches committed often. A transaction holding 10,000 rows open is risky: it holds locks longer, consumes memory, and a failure loses the whole thing. A transaction holding 100–500 rows committed in a loop is safe and fast. The overhead of opening and closing a transaction is negligible compared to the risk reduction.

Three ways to write to Payload — and which one to use for imports

Payload gives you three layers to write data.

The Local API (payload.create, payload.update) runs the full document lifecycle: access control, validation, hooks, re-fetching. It is the right default for application logic but the wrong tool for bulk imports. Every row triggers the same overhead as a web request.

payload.db.* is Payload's direct database layer. It skips hooks, access control, and validation and writes straight to the adapter. It still lives inside Payload's abstraction, so it works correctly with Payload's transaction model (beginTransaction, commitTransaction, rollbackTransaction). This is the right default for production imports into Payload collections.

Raw Drizzle is the Postgres adapter's underlying ORM, exposed directly via payload.db.drizzle. It gives you full SQL expressiveness: bulk upserts, set-based updates, join-heavy inserts, conflict handling. The trade-off is that you are working outside Payload's abstraction entirely and need to know the generated table schema. This is the right tool when a single SQL statement can replace thousands of row-by-row operations.

Generating the Drizzle schema

Before you can use raw Drizzle, you need the generated table schema so TypeScript knows what columns exist. Payload ships a command for this:

npx payload generate:db-schema

This outputs a file — typically payload-generated-schema.ts — containing the Drizzle table definitions, relations, and enums derived from your Payload collections. You import from this file when writing raw Drizzle queries. Every time you add or change a collection field you need to re-run this command to keep the schema in sync.

Importing with payload.db.* and PostgreSQL transactions

This is the recommended default for most Payload import jobs. You get direct-to-database performance, Payload's transaction primitives, and no need to manage the generated schema yourself.

// File: src/scripts/import-batch.ts
import { getPayload } from 'payload'
import config from '@payload-config'

type SourceRow = {
  externalId: string
  title: string
  slug: string
}

async function importBatch(rows: SourceRow[]): Promise<void> {
  const payload = await getPayload({ config })

  const transactionID = await payload.db.beginTransaction()

  try {
    for (const row of rows) {
      await payload.db.create?.({
        collection: 'products',
        data: {
          externalId: row.externalId,
          title: row.title,
          slug: row.slug,
        },
        req: { transactionID },
        returning: false,
      })
    }

    await payload.db.commitTransaction(transactionID)
  } catch (err) {
    await payload.db.rollbackTransaction(transactionID)
    throw err
  }
}

async function runImport(source: SourceRow[]): Promise<void> {
  const BATCH_SIZE = 250

  for (let i = 0; i < source.length; i += BATCH_SIZE) {
    const batch = source.slice(i, i + BATCH_SIZE)
    await importBatch(batch)
    console.log(`Committed batch ${i / BATCH_SIZE + 1} — rows ${i + 1}–${i + batch.length}`)
  }
}

A few things worth understanding in this pattern. payload.db.beginTransaction() returns a transaction ID string. You pass that ID into req: { transactionID } on each create call, which tells Payload's adapter to run the write inside the open transaction. returning: false tells the adapter not to re-fetch the created row — that alone removes a round-trip per row, which adds up across thousands of inserts. After the loop, commitTransaction flushes everything. If any write throws, rollbackTransaction clears the whole batch cleanly.

The runImport function slices the source into 250-row batches. That number is a starting point — tune it based on row size and relation complexity. Wider rows with many relations should use smaller batches. Simple flat rows can go higher.

Importing with raw Drizzle

Raw Drizzle is the better tool when your import logic is naturally set-based: upsert by external key, bulk-update from a staging table, resolve conflicts with onConflictDoUpdate. One SQL statement can replace thousands of per-row operations.

First, generate and import your schema:

npx payload generate:db-schema

Then write the import:

// File: src/scripts/import-drizzle.ts
import { getPayload } from 'payload'
import config from '@payload-config'
import { products } from './payload-generated-schema'
import { sql } from '@payloadcms/db-postgres/drizzle'

type SourceRow = {
  externalId: string
  title: string
  slug: string
}

async function importBatchDrizzle(rows: SourceRow[]): Promise<void> {
  const payload = await getPayload({ config })

  await payload.db.drizzle.transaction(async (tx) => {
    const values = rows.map((row) => ({
      external_id: row.externalId,
      title: row.title,
      slug: row.slug,
    }))

    await tx
      .insert(products)
      .values(values)
      .onConflictDoUpdate({
        target: products.external_id,
        set: {
          title: sql`excluded.title`,
          slug: sql`excluded.slug`,
        },
      })
  })
}

This sends one insert statement per batch instead of one per row. The onConflictDoUpdate clause turns every batch into an idempotent upsert: rows that already exist update in place, new rows insert. That means you can replay a failed batch without creating duplicates.

The transaction callback here is Drizzle's own API — tx is a Drizzle transaction object, not a Payload transaction ID. Do not mix Payload's beginTransaction / req.transactionID model with Drizzle's tx callback in the same batch. They are separate transaction propagation mechanisms and mixing them is untested in most Payload versions.

When to use which

The decision is straightforward in practice.

Use payload.db.* when you are inserting or updating Payload-managed documents one by one within a batch, your rows map directly to collection fields, and you do not need SQL-level conflict resolution. This covers most production import jobs.

Use raw Drizzle when you can express the batch as a single SQL statement — a bulk upsert by external key, an update from a joined staging table, or a set-based deduplication pass. The gain is real here: one statement replacing 500 round-trips is a meaningful difference. Row-by-row loops in raw Drizzle without set-based SQL offer no meaningful advantage over payload.db.* and require managing the generated schema yourself.

In both cases, preprocess and validate data outside the transaction. Keep transactions short — open, write, commit, move on. Record a checkpoint after each successful commit so a failure only costs you the current batch.

FAQ

Can I use the Local API for imports if I run hooks later?

You can, but it is usually the wrong shape. The Local API runs validation, access control, and all hooks synchronously on every document. For hundreds or thousands of rows that overhead compounds quickly. A better approach is to import directly with payload.db.* or Drizzle, then run any post-import hook logic as a separate background task using Payload Jobs.

How large should batches be?

Start at 100–250 rows and tune from there. Smaller batches reduce the blast radius of a failure, are easier to checkpoint, and hold locks for less time. Larger batches reduce transaction overhead. For flat collections with no relations, 500–1000 rows per batch is often fine. For collections with array fields or nested relations, keep batches smaller.

What happens if I forget to call rollbackTransaction after an error?

PostgreSQL will eventually roll back the transaction automatically when the connection closes or times out, but you should not rely on that. Always call payload.db.rollbackTransaction(transactionID) in a catch block. Leaving open transactions is a common source of lock contention in production.

Do direct DB writes fire Payload hooks?

No. Both payload.db.* and raw Drizzle bypass the Payload document lifecycle entirely. That means beforeChange, afterChange, beforeOperation, and afterOperation hooks do not run. If your collection relies on hooks to derive fields, generate slugs, or sync related documents, you need to handle that logic separately in your import pipeline.

Can I read back an inserted row inside the same transaction?

With payload.db.* and returning: false, no — you explicitly skipped the re-fetch. With raw Drizzle, yes, you can query inside the same tx callback and the write will be visible within the transaction. This is useful for resolving foreign key IDs during the import.

Conclusion

For large Payload imports, the Local API is the wrong starting point. payload.db.* with short committed batches covers most production import jobs: it is fast, stays inside Payload's adapter model, and gives you clean transaction control with beginTransaction, commitTransaction, and rollbackTransaction. Raw Drizzle via payload.db.drizzle is the right escalation when your logic is genuinely set-based — one SQL statement doing what thousands of row writes would otherwise do. Generate your schema with npx payload generate:db-schema, keep batches small, checkpoint after every commit, and validate data before the transaction opens.

The full import architecture from this article — batched writes, short transactions, checkpointing, idempotent upserts — scales from a one-time migration to a recurring sync job without changing the underlying shape.

Let me know in the comments if you have questions, and subscribe for more practical development guides.

Thanks, Matija