Bug: D1 adapter lacks atomic operations (batch), causing data loss on array updates

Describe the Bug

When updating a document/global with array fields using the D1 adapter, data can be permanently lost if any error occurs between the DELETE and INSERT operations. This is because the adapter executes individual statements without using D1's batch() API for atomic operations.

Reproduction Steps

1. Create a Global with an array field containing multiple items
2. Edit any field in the Global (not necessarily the array)
3. If any error occurs during save (network timeout, Worker limit, validation error in afterChange hook, etc.)
4. The array data may be permanently deleted

Root Cause Analysis

The upsertRow function in @payloadcms/drizzle uses a DELETE + INSERT pattern for arrays:

// node_modules/@payloadcms/drizzle/dist/upsertRow/index.js (lines 555-565)
if (operation === 'update') {
    for (const arrayTableName of Object.keys(rowToInsert.arrays)){
        await deleteExistingArrayRows({...}); // DELETE ALL - committed immediately!
    }
}
await insertArrays({...}); // INSERT - if this fails, data is already gone!

The D1 adapter uses defaultBeginTransaction() which is a no-op:

// node_modules/payload/dist/database/defaultBeginTransaction.js
export function defaultBeginTransaction() {
    return () => Promise.resolve(null); // No actual transaction!
}

This means each SQL statement is auto-committed individually. If DELETE succeeds but INSERT fails for any reason, the data is permanently lost with no way to rollback.

Expected Behavior

Array updates should be atomic - either all operations succeed, or none do. D1 supports this via the batch() API:

// What SHOULD happen:
const results = await db.batch([
    db.delete(arrayTable).where(eq(table._parentID, parentID)),
    db.insert(arrayTable).values(newRows),
]);
// All statements succeed or all fail together

Real-World Impact

We've experienced multiple incidents of complete data loss in production:

• A Global with 11 business locations was completely wiped when saving an unrelated field
• Users see "Something went wrong" error but the DELETE already committed
• No way to recover without manual backup restoration

Environment

Payload Info:
  payload: 3.70.0
  @payloadcms/db-d1-sqlite: 3.70.0
  @payloadcms/drizzle: 3.70.0
Database: Cloudflare D1
Deployment: Cloudflare Workers

Suggested Fix

1. Short-term: Use db.batch() in upsertRow to group DELETE and INSERT operations atomically for the D1 adapter
2. Long-term: Implement proper transaction support for D1 adapter using batch operations

Workaround

We've implemented a beforeChange hook that detects when arrays would be emptied and blocks the operation:

// Simplified example
if (incomingArray.length === 0 && originalArray.length > 0) {
    throw new APIError("Operation blocked: data would be lost", 400);
}

However, this doesn't protect against failures that occur AFTER the hook passes but BEFORE the INSERT completes.

Additional Context

Cloudflare D1 documentation on batch operations: https://developers.cloudflare.com/d1/build-with-d1/d1-client-api/#batch-statements

The batch API guarantees atomicity:

"Batched statements are SQL transactions. If a statement in the sequence fails, then an error will be returned for that specific statement, and it will abort or roll back the entire sequence."

Related

This is distinct from #15070 (stale D1 binding) - that issue is about operations failing silently. This issue is about the lack of atomicity even when operations work correctly individually.