Error encountered when filtering a UUID field in a collection

Expected behavior

I have a collection called ‘Account’, with a field called id which is a primary key and has type UUID:

  const Account = sequelize.define('account', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: Sequelize.literal('uuid_generate_v4()'),
      allowNull: false,
    },
...

In Forest Admin, when I select the “Account” collection and try to filter it using ‘is’ or ‘starts with’, I expect to see Account records, filtered accordingly.

Actual behavior

The webpage shows ‘Your server encountered an error’.

(Importantly, without filter the collection works fine).

Failure Logs

The following appears as soon as I try to filter:

When filtering with 'Starts with":

mpathy_backoffice | [forest] 🌳🌳🌳  Unexpected error: operator does not exist: uuid ~~ unknown
empathy_backoffice | {
empathy_backoffice |   "name": "SequelizeDatabaseError",
empathy_backoffice |   "parent": {
empathy_backoffice |     "length": 200,
empathy_backoffice |     "name": "error",
empathy_backoffice |     "severity": "ERROR",
empathy_backoffice |     "code": "42883",
empathy_backoffice |     "hint": "No operator matches the given name and argument types. You might need to add explicit type casts.",
empathy_backoffice |     "position": "86",
empathy_backoffice |     "file": "parse_oper.c",
empathy_backoffice |     "line": "731",
empathy_backoffice |     "routine": "op_error",
empathy_backoffice |     "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" LIKE '45%';"
empathy_backoffice |   },
empathy_backoffice |   "original": {
empathy_backoffice |     "length": 200,
empathy_backoffice |     "name": "error",
empathy_backoffice |     "severity": "ERROR",
empathy_backoffice |     "code": "42883",
empathy_backoffice |     "hint": "No operator matches the given name and argument types. You might need to add explicit type casts.",
empathy_backoffice |     "position": "86",
empathy_backoffice |     "file": "parse_oper.c",
empathy_backoffice |     "line": "731",
empathy_backoffice |     "routine": "op_error",
empathy_backoffice |     "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" LIKE '45%';"
empathy_backoffice |   },
empathy_backoffice |   "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" LIKE '45%';",
empathy_backoffice |   "stack": "SequelizeDatabaseError: operator does not exist: uuid ~~ unknown\n    at Query.formatError (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:354:16)\n    at query.catch.err (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:71:18)\n    at tryCatcher (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23)\n    at Promise._settlePromiseFromHandler (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:547:31)\n    at Promise._settlePromise (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:604:18)\n    at Promise._settlePromise0 (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:649:10)\n    at Promise._settlePromises (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:725:18)\n    at _drainQueueStep (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:93:12)\n    at _drainQueue (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:86:9)\n    at Async._drainQueues (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:102:5)\n    at Immediate.Async.drainQueues [as _onImmediate] (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:15:14)\n    at runCallback (timers.js:705:18)\n    at tryOnImmediate (timers.js:676:5)\n    at processImmediate (timers.js:658:5)"

When filtering with ‘is’:

empathy_backoffice | [forest] 🌳🌳🌳  Unexpected error: invalid input syntax for type uuid: "34"
empathy_backoffice | {
empathy_backoffice |   "name": "SequelizeDatabaseError",
empathy_backoffice |   "parent": {
empathy_backoffice |     "length": 101,
empathy_backoffice |     "name": "error",
empathy_backoffice |     "severity": "ERROR",
empathy_backoffice |     "code": "22P02",
empathy_backoffice |     "position": "88",
empathy_backoffice |     "file": "uuid.c",
empathy_backoffice |     "line": "137",
empathy_backoffice |     "routine": "string_to_uuid",
empathy_backoffice |     "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" = '34';"
empathy_backoffice |   },
empathy_backoffice |   "original": {
empathy_backoffice |     "length": 101,
empathy_backoffice |     "name": "error",
empathy_backoffice |     "severity": "ERROR",
empathy_backoffice |     "code": "22P02",
empathy_backoffice |     "position": "88",
empathy_backoffice |     "file": "uuid.c",
empathy_backoffice |     "line": "137",
empathy_backoffice |     "routine": "string_to_uuid",
empathy_backoffice |     "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" = '34';"
empathy_backoffice |   },
empathy_backoffice |   "sql": "SELECT count(*) AS \"count\" FROM \"public\".\"account\" AS \"account\" WHERE \"account\".\"id\" = '34';",
empathy_backoffice |   "stack": "SequelizeDatabaseError: invalid input syntax for type uuid: \"34\"\n    at Query.formatError (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:354:16)\n    at query.catch.err (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:71:18)\n    at tryCatcher (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/util.js:16:23)\n    at Promise._settlePromiseFromHandler (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:547:31)\n    at Promise._settlePromise (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:604:18)\n    at Promise._settlePromise0 (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:649:10)\n    at Promise._settlePromises (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/promise.js:725:18)\n    at _drainQueueStep (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:93:12)\n    at _drainQueue (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:86:9)\n    at Async._drainQueues (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:102:5)\n    at Immediate.Async.drainQueues [as _onImmediate] (/usr/src/app/node_modules/sequelize/node_modules/bluebird/js/release/async.js:15:14)\n    at runCallback (timers.js:705:18)\n    at tryOnImmediate (timers.js:676:5)\n    at processImmediate (timers.js:658:5)"
empathy_backoffice | }

Context

Please provide any relevant information about your setup.

  • We are using Typescript
  • Package Version: forest-express-sequelize 7.12.2
  • Express Version: 4.17.1
  • Sequelize Version: 5.22.4
  • Database Dialect: PostGres
  • Database Version: 12.4
  • Project Name: Empathy Backoffice

Hi @Oded_Magger,

Thank you for your feedback.

The UI allows making a “start with” filter on a UUID field. But as soon as the field is not a string, the filter fails to be applied. I am creating a ticket to ask for an explicit cast to be made.

The UI allows making a “is” filter with any string. Since only a valid UUID is possible in this case, please type the entire UUID when using the “is” filter.
I am creating a ticket here, to block any filter value other than a valid UUID.

Regards

Thanks Sliman,

Regarding the second item (using ‘is’) - if the input is not a valid UUID, I would expect the filtering to fail gracefully (e.g. return 0 records) rather than show a scary “server error”.

Our non-tech Forest Admin users were sure our services crashed because of that error :frowning: .

Yes in this case the UI should block instead of the request going to the server.