D1 SQLite adapter fails with "too many SQL variables" error on UPDATE operations for large schemas

Describe the Bug

Description

The @payloadcms/db-d1-sqlite adapter throws a SQLITE_ERROR: too many SQL variables error when attempting to save (update) documents in collections with large schemas. This occurs because SQLite has a hard limit on the number of bound parameters in a single SQL statement (typically 999 or 32766), and the D1 adapter generates UPDATE statements that exceed this limit for complex collections.

While the adapter implements limitedBoundParameters: true and batches INSERT operations to work around this limitation, UPDATE operations are not batched, causing saves to fail on large schemas.

Additional Context

This issue affects code migrations from PostgreSQL to D1, as PostgreSQL doesn't have this parameter limit. The same collection configuration works fine with @payloadcms/db-postgres but fails with @payloadcms/db-d1-sqlite.

The error occurs on both manual saves and autosave operations, making it impossible to use collections with large schemas on D1 without the blocksAsJSON workaround.

Environment

• Payload Version: 3.63.0
• Database Adapter: @payloadcms/db-d1-sqlite version 3.63.0
• Node Version: v20.x
• Operating System: macOS
• Deployment: Cloudflare Workers with D1

Collection Configuration

The collection has approximately 200+ fields organized into nested groups and arrays:

• Multiple nested group fields with sub-fields
• 5 array fields with complex objects
• Various relationship fields
• Custom display fields for calculations

Example structure (simplified):

{
  slug: 'properties',
  fields: [
    {
      name: 'info',
      type: 'group',
      fields: [
        // ~50 fields here
        {
          name: 'agentNotes',
          type: 'array',
          fields: [/* multiple fields */]
        },
        {
          name: 'images',
          type: 'array',
          fields: [/* multiple fields */]
        },
        // ... more arrays
      ]
    },
    {
      name: 'financials',
      type: 'group',
      fields: [/* ~40 fields */]
    },
    {
      name: 'dueDiligence',
      type: 'group',
      fields: [/* ~30 fields with arrays */]
    },
    // ... more groups
  ]
}

Analysis

Looking at the D1 adapter source code:

INSERT operations are batched in insert.ts:

if (this.limitedBoundParameters && Array.isArray(values)) {
  const results: Record<string, unknown>[] = []
  const colsPerRow = Object.keys(values[0]).length
  const maxParams = 100
  const maxRowsPerBatch = Math.max(1, Math.floor(maxParams / colsPerRow))
  // ... batching logic
}

UPDATE operations are NOT batched - they generate a single SQL statement with all fields

The test file sqlite-bound-parameters-limit.int.spec.ts only tests WHERE clause batching for queries, not UPDATE operations

Potential Solutions

-Implement batched UPDATE operations similar to INSERT operations
-Break large UPDATE statements into multiple smaller UPDATEs (update subsets of columns)
-Better document the limitation and recommend blocksAsJSON: true for large schemas

Workarounds

The current workaround is to use blocksAsJSON: true in the adapter configuration:
db: sqliteD1Adapter({   binding: cloudflare.env.D1,  blocksAsJSON: true, // Reduces SQL parameters by storing arrays as JSON})
However, this requires a migration and changes the data structure, which may not be desirable for all use cases.

Related Code
index.ts - Sets limitedBoundParameters: true
insert.ts - Implements batching for INSERTs
packages/drizzle/src/updateOne.ts and updateMany.ts - Do not implement batching
sqlite-bound-parameters-limit.int.spec.ts - Tests only query batching

Link to the code that reproduces this issue

https://github.com/payloadcms/payload/blob/main/test/database/sqlite-bound-parameters-limit.int.spec.ts#L0-L30

Reproduction Steps

Steps to Reproduce

1. Create a collection with a large number of fields (in my case, ~200+ fields including nested groups and arrays)
2. Create a document in this collection
3. Attempt to edit and save the document
4. Observe the error: Error: too many SQL variables at offset XXXX: SQLITE_ERROR

Expected Behavior

UPDATE operations should be batched similar to INSERT operations when limitedBoundParameters: true, allowing documents in large collections to be saved successfully.

Actual Behavior

The save operation fails with:
caused by: Error: too many SQL variables at offset 3604: SQLITE_ERROR
at D1DatabaseSessionAlwaysPrimary._sendOrThrow (cloudflare-internal:d1-api:140:24)
at async cloudflare-internal:d1-api:374:36
PATCH /api/properties/3?depth=0&draft=true&autosave=true&locale=undefined 500

Which area(s) are affected? (Select all that apply)

db: d1-sqlite

Environment Info

> cross-env NODE_OPTIONS=--no-deprecation payload info

npm warn Unknown env config "npm-globalconfig". This will stop working in the next major version of npm.
npm warn Unknown env config "verify-deps-before-run". This will stop working in the next major version of npm.
npm warn Unknown env config "_jsr-registry". This will stop working in the next major version of npm.

Binaries:
  Node: 25.2.1
  npm: 11.6.2
  Yarn: 1.22.22
  pnpm: 10.23.0
Relevant Packages:
  payload: 3.63.0
  next: 15.4.7
  @payloadcms/db-d1-sqlite: 3.63.0
  @payloadcms/drizzle: 3.63.0
  @payloadcms/email-resend: 3.63.0
  @payloadcms/graphql: 3.63.0
  @payloadcms/live-preview: 3.63.0
  @payloadcms/live-preview-react: 3.63.0
  @payloadcms/next/utilities: 3.63.0
  @payloadcms/plugin-cloud-storage: 3.63.0
  @payloadcms/plugin-form-builder: 3.63.0
  @payloadcms/plugin-nested-docs: 3.63.0
  @payloadcms/plugin-redirects: 3.63.0
  @payloadcms/plugin-search: 3.63.0
  @payloadcms/plugin-seo: 3.63.0
  @payloadcms/richtext-lexical: 3.63.0
  @payloadcms/storage-r2: 3.63.0
  @payloadcms/translations: 3.63.0
  @payloadcms/ui/shared: 3.63.0
  react: 19.1.0
  react-dom: 19.1.0
Operating System:
  Platform: darwin
  Arch: arm64
  Version: Darwin Kernel Version 25.0.0: Wed Sep 17 21:41:45 PDT 2025; root:xnu-12377.1.9~141/RELEASE_ARM64_T6000
  Available memory (MB): 16384
  Available CPU cores: 10