UNIQUE constraint failed: No proper error message in the ui

[tobidemski]

Describe the Bug

Hello,

when I try to save a document with the same value in the "page" field it shows me a toast notification at the bottom right corner with the message "Something went wrong." I would expect that I get a more user friendly and localized translated error message like "The field 'page' must be unique and the current value is already in use"

I have the following collection:

export const Pages: CollectionConfig = {
  slug: 'Pages',
  admin: {
    useAsTitle: 'page',
  },
  versions: {
    drafts: {
      validate: true,
    },
    maxPerDoc: 1,
  },

  labels: {
    singular: {
      de: 'Seite',
      en: 'Page',
    },
    plural: {
      de: 'Seiten',
      en: 'Pages',
    },
  },
  access: {
    read: ({ req }) => (req.user ? true : false),
  },
  fields: [
    {
      name: 'page',
      type: 'text',
      required: true,
      unique: true,
      minLength: 1,
    },
  ],
}

In the terminal I can see the following message:

[18:00:28] ERROR: UNIQUE constraint failed: pages.page
    err: {
      "type": "Error",
      "message": "UNIQUE constraint failed: pages.page",
      "stack":
          Error: UNIQUE constraint failed: pages.page
              at Object.next (D:\Projects\project\project.Cms\node_modules\libsql\index.js:335:20)
              at Statement.all (D:\Projects\project\project.Cms\node_modules\libsql\index.js:360:16)
              at executeStmt (file:///D:/Projects/project/project.Cms/node_modules/@libsql/client/lib-esm/sqlite3.js:261:34)
              at Sqlite3Client.execute (file:///D:/Projects/project/project.Cms/node_modules/@libsql/client/lib-esm/sqlite3.js:79:16)
              at LibSQLPreparedQuery.values (file:///D:/Projects/project/project.Cms/node_modules/drizzle-orm/libsql/session.js:182:59)
              at LibSQLPreparedQuery.all (file:///D:/Projects/project/project.Cms/node_modules/drizzle-orm/libsql/session.js:126:29)
              at QueryPromise.all (file:///D:/Projects/project/project.Cms/node_modules/drizzle-orm/sqlite-core/query-builders/insert.js:145:28)
              at QueryPromise.execute (file:///D:/Projects/project/project.Cms/node_modules/drizzle-orm/sqlite-core/query-builders/insert.js:154:41)
              at QueryPromise.then (file:///D:/Projects/project/project.Cms/node_modules/drizzle-orm/query-promise.js:21:17)
      "rawCode": 2067,
      "code": "SQLITE_CONSTRAINT_UNIQUE",
      "libsqlError": true
    }
 POST /api/Pages?locale=de&depth=0&fallback-locale=null&draft=true 500 in 14ms

Is this a bug or do I have to configure something?

Link to the code that reproduces this issue

https://github.com/tobidemski/payload-uniquefield-errormessage

Reproduction Steps

use Payload with sqlite Create a unique field and try to save a document with the same value. Use my collection as an example.

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

area: docs

Environment Info

Binaries:
  Node: 18.20.8
  npm: N/A
  Yarn: N/A
  pnpm: N/A
Relevant Packages:
  payload: 3.39.1
  next: 15.3.2
  @payloadcms/db-postgres: 3.39.1
  @payloadcms/email-nodemailer: 3.39.1
  @payloadcms/graphql: 3.39.1
  @payloadcms/next/utilities: 3.39.1
  @payloadcms/payload-cloud: 3.39.1
  @payloadcms/richtext-lexical: 3.39.1
  @payloadcms/translations: 3.39.1
  @payloadcms/ui/shared: 3.39.1
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: win32
  Arch: x64
  Version: Windows 10 Pro
  Available memory (MB): 32510
  Available CPU cores: 16

[anyuj]

One possible workaround is to manually write a validation for the field, where you can customize error message like in the Validation doc. There might be better way to do it. At least this is how I do it.

[tobidemski]

I would prefer to avoid building my whole custom logic to check if I already have a document with the value in my collection since payload already provide the core logic for it.

Could it be that the error lies in the the following lines? https://github.com/payloadcms/payload/blob/8199a7d32a06a3771f93b8ccf55a8ce288814617/packages/drizzle/src/upsertRow/index.ts#L383C25-L383C30

it only checks for the error code 23505 which is the code for postgresql 23505: https://www.postgresql.org/docs/current/errcodes-appendix.html

But since I use sqlite for my local environment I get a different code of 2067 based on the documentation its the same meaning as 23505.

2067: https://www.sqlite.org/rescode.html#constraint_unique

And also the error object looks like that:

{
  rawCode: 2067,
  code: 'SQLITE_CONSTRAINT_UNIQUE',
  libsqlError: true
}

So the code is a string instead of a code number.

It seems like the whole error logic on unique contrains are based on postgresql and doesn't work at all for sqlite.

Maybe we need an additional check and logic on that side to prevent that an exception is thrown?

[anyuj]

Oh, I checked the wrong file earlier, so I thought there was only generic error message. And yeah, I think you found the right point.

[JarrodMFlesch]

I would prefer to avoid building my whole custom logic to check if I already have a document with the value in my collection since payload already provide the core logic for it.

Could it be that the error lies in the the following lines? https://github.com/payloadcms/payload/blob/8199a7d32a06a3771f93b8ccf55a8ce288814617/packages/drizzle/src/upsertRow/index.ts#L383C25-L383C30

it only checks for the error code 23505 which is the code for postgresql 23505: https://www.postgresql.org/docs/current/errcodes-appendix.html

But since I use sqlite for my local environment I get a different code of 2067 based on the documentation its the same meaning as 23505.

2067: https://www.sqlite.org/rescode.html#constraint_unique

And also the error object looks like that:

{
  rawCode: 2067,
  code: 'SQLITE_CONSTRAINT_UNIQUE',
  libsqlError: true
}

So the code is a string instead of a code number.

It seems like the whole error logic on unique contrains are based on postgresql and doesn't work at all for sqlite.

Maybe we need an additional check and logic on that side to prevent that an exception is thrown?

Spot on! Thanks for looking into this. This PR has the fix and will be in the next release.

[github-actions[bot]]

🚀 This is included in version v3.43.0

[github-actions[bot]]

This issue has been automatically locked. Please open a new issue if this issue persists with any additional detail.