Orderable collections w/ any `group` field, crashes when using SQLite adapter

[sxxov]

Describe the Bug

what a specific, annoying bug.

basically, if you have all of the following conditions:

1. you enable orderable: true on collection Foo
2. you have a field with the type group on your collection Foo
3. you're using the @payloadcms/db-sqlite adapter for the project
4. you're creating the 2nd document within the collection

once all conditions are met, it will throw:

do not enter
[18:52:51] ERROR: UNIQUE constraint failed: products._order
    err: {
      "type": "Error",
      "message": "UNIQUE constraint failed: products._order",
      "stack":
          Error: UNIQUE constraint failed: products._order
              at Object.next (/../node_modules/libsql/index.js:335:20)
              at Statement.all (/../node_modules/libsql/index.js:360:16)
              at executeStmt (file:///../node_modules/@libsql/client/lib-esm/sqlite3.js:261:34)
              at Sqlite3Client.execute (file:///../node_modules/@libsql/client/lib-esm/sqlite3.js:79:16)
              at LibSQLPreparedQuery.values (/...next/server/chunks/ssr/node_modules_drizzle-orm_2d038255._.js:7890:63)
              at LibSQLPreparedQuery.all (/../.next/server/chunks/ssr/node_modules_drizzle-orm_2d038255._.js:7836:33)
              at QueryPromise.all (/../.next/server/chunks/ssr/node_modules_drizzle-orm_2d038255._.js:6870:32)
              at QueryPromise.execute (/../.next/server/chunks/ssr/node_modules_drizzle-orm_2d038255._.js:6879:45)
              at QueryPromise.then (/../.next/server/chunks/ssr/node_modules_drizzle-orm_2d038255._.js:5849:21)
              at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
      "rawCode": 2067,
      "code": "SQLITE_CONSTRAINT_UNIQUE",
      "libsqlError": true
    }
 PATCH /api/products/2?depth=0&fallback-locale=null 500 in 31ms

Link to the code that reproduces this issue

https://github.com/sxxov/payload/tree/repro/orderable-group-crash

Reproduction Steps

using the repro above, run:

pnpm test:int _community

you will then observe the crash described above, happening when we call payload.create the 2nd time.

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

area: core

Environment Info

Binaries:
  Node: 23.8.0
  npm: 10.9.2
  Yarn: N/A
  pnpm: 9.7.1
Relevant Packages:
  payload: 3.34.0
  next: 15.3.0
  @payloadcms/db-postgres: 3.34.0
  @payloadcms/live-preview-react: 3.34.0
  react: 19.1.0
  react-dom: 19.1.0
Operating System:
  Platform: darwin
  Arch: arm64
  Version: Darwin Kernel Version 24.3.0: Thu Jan  2 20:24:22 PST 2025; root:xnu-11215.81.4~3/RELEASE_ARM64_T6041
  Available memory (MB): 24576
  Available CPU cores: 12

[sxxov]

for anyone else facing this issue, the workaround is to not use group fields 🤷

[akhrarovsaid]

Hey @sxxov

This may be duplicative of #12085

[sxxov]

@akhrarovsaid seems like you might be right. however, the issue on sqlite seems to be happening much more prevalently, as it completely blocks you from creating any new documents, rather than just reordering them

[sxxov]

closing in favour of #12085

[sxxov]

it seems like this specific crash still persists even though #12085 is fixed. reopening

cc: @akhrarovsaid

[sxxov]

reposting my workaround here for visibility:

commenting out the following lines in node_modules/@payloadcms/drizzle/dist/transform/read/traverseFields.js fixes the crash:

- if ('_order' in ref) {
-     delete ref._order;
- }
+ // if ('_order' in ref) {
+ //     delete ref._order;
+ // }

payload/packages/drizzle/src/transform/read/traverseFields.ts

Line 670 in babf4f9

delete ref._order

[GermanJablo]

solved in #12422