Search on collection is sending an error - sequelize-typescript UUID are interpreted as string

[NicolasF]

Feature(s) impacted

Search on CapitalDepositCaseModel collection

Observed behavior

On the Forest Admin Frontend, in a collection, when searching for something

We get an error

Expected behavior

The application should successfully return the result of the searching query.

Failure Logs

[forest] 🌳🌳🌳  Unexpected error: function lower(uuid) does not exist
{
  "name": "SequelizeDatabaseError",
  "parent": {
    "length": 204,
    "name": "error",
    "severity": "ERROR",
    "code": "42883",
    "hint": "No function matches the given name and argument types. You might need to add explicit type casts.",
    "position": "91",
    "file": "parse_func.c",
    "line": "627",
    "routine": "ParseFuncOrColumn",
    "sql": "[ContainsSensitiveData]"
  },
  "stack": "SequelizeDatabaseError: function lower(uuid) does not exist"
}

Context

• Project name: Swan Social Capital Deposit
• Team name: Swan
• Environment name: Master
• Agent type & version: 8.2.8

The issue seems similar to this one : Search on collection is sending an error - "function lower(uuid) does not exist"

It seems related to the fact that although they are UUID, our id fields are treated as Strings.

---

Let’s focus on the CapitalDepositCaseModel, which takes an id, as a UUID v4.

In our model, we defined the id of our capitalDepositCase as a UUID using sequelize-typescript :

// capital-deposit-case.model.ts
@Table({ tableName: CapitalDepositCaseModel.tableName, timestamps: false })
export class CapitalDepositCaseModel extends Model<
  CapitalDepositCaseModel,
  Partial<CapitalDepositCaseModel>
> {
  public static tableName = "CapitalDepositCase";

  @IsUUID(4)
  @Default(DataType.UUIDV4)
  @PrimaryKey
  @Column
  id!: string;

  ...
}

In our database, it is saved as UUID

We do not have specified the type of the id field on the config file

// capital-deposit-case.config.forest.ts
forest.collection("CapitalDepositCaseModel", {
  fields: [
    {
      field: "CapitalDepositCaseDocument",
      type: ["String"],
      reference: "CapitalDepositCaseDocumentModel.id",
    },
  ],
...
})

The final configuration seems to infer the id column as a string instead of a UUID.

// .forestadmin-schema.json
{
    "name": "CapitalDepositCaseModel",
    "nameOld": "CapitalDepositCaseModel",
    "icon": null,
    "integration": null,
    "isReadOnly": false,
    "isSearchable": true,
    "isVirtual": false,
    "onlyForRelationships": false,
    "paginationType": "page",
    "fields": [{
      "field": "id",
      "type": "String",
      "defaultValue": null,
      "enums": null,
      "integration": null,
      "isFilterable": true,
      "isPrimaryKey": true,
      "isReadOnly": false,
      "isRequired": true,
      "isSortable": true,
      "isVirtual": false,
      "reference": null,
      "inverseOf": null,
      "validations": []
    }...
}

At the end, when performing the query, we get the Unexpected error: function lower(uuid) does not exist error.

One possibility may be to override all our uuid fields by filling their values in the *config.forest.ts files, but this would take a bit of work, and I am not reassured that this would break because of our relationships (although searching in relationship seems to imply using the extended search that we actually do not need, it is okay if we only search on the selected collection fields).

Thank you for your support,

Nicolas

[anon94532230]

Hi @NicolasF !
As you can see here, other people fixed this issue by using UUID as the datatype of their field in the models.
Can you try to do the same ?

[NicolasF]

Hi @anon94532230,

Thank you for your answer, as we are using sequelize-typescript, we do not have direct access to

sequelize.define('myTable', { ... myfields })

I guess it would be possible to override the dynamically generated models, but I would rather not.

[anon94532230]

I’m not very knowledgeable in typescript, but I see that with sequelize-typescript you caan still specify the datatype for the @column decorator: sequelize-typescript - npm
Tell me if this helps or not ! If it’s not the case I’ll ask a Forest team member that knows more than me on the subject

[anon94532230]

Like it’s done here on the UserVehicle.model.ts

[paulclavier]

Hello Nicolas_Sailly,

I work with NicolasF on this project, and I have tried what you suggested upper, which did not happen to be successful.

I eventually narrowed down the scope of the issue:

Our .forestadmin-schema.json file identifies ids in our database as String rather than Uuid.
I can see in our sequelize migration files that our ids are defined like Sequelize UUIDs:

    id: {
      type: Sequelize.UUID,
      primaryKey: true,
      allowNull: false,
      defaultValue: Sequelize.UUIDV4,
    },

Therefore I am wondering from where the issue might come from

[paulclavier]

Also I have checked: In our DB the id columns are indeed of the uuid type in our DB (image comes from my DB explorer).

There I think we might have a problem about how forest reads the models we define

[jeffladiray]

Hey @paulclavier and @NicolasF,

Looking at the code available here, it seems like indeed, there is an issue here.

Your field should be indeed of type Uuid, not String. The only thing I can spot in what you shared is Sequelize.UUID, opposed to Sequelize.DataTypes.UUID, but I’m pretty sure this should be an issue at all.

Would it be possible to have the transpiled js definition of your ts model, shared in the first message?

Thanks in advance

[paulclavier]

Hello @jeffladiray ,

I tried:

• deleting dist/ folder
• deleting forestadmin-schema.json file
• building application
• starting application

The pictures shows the .model.js from the dist (same for all Ids)
I still get

"field": "id",
"type": "String",

for all my ids …

[jeffladiray]

Thanks for the test. I’ll try to reproduce the issue on my end on a typescript project, and get back to you if I’m able to reproduce.

I’m pretty sure the issue comes from

  id!: string;

But I need a reproductible example in order to create a bug ticket

Sorry for the inconvenience.

[NicolasF]

Hi @jeffladiray,

Did you manage to reproduce the issue on your side ?

Have a nice day,

[Arnaud_Moncel]

Hi @NicolasF unfortunately i’m not able to reproduce
with

import { Table, Column, Model, PrimaryKey, DataType, Default, IsUUID } from 'sequelize-typescript'

@Table
export default class Pkuuid extends Model {
  public static tableName = "pkuuid";

  @IsUUID(4)
  @Default(DataType.UUIDV4)
  @PrimaryKey
  @Column(DataType.UUIDV4)
  id!: string;
}

forest-express-sequelize: 8.4.10 generate this schema

{
  "field": "id",
  "type": "Uuid",
  "defaultValue": null,
  "enums": null,
  "integration": null,
  "isFilterable": true,
  "isPrimaryKey": true,
  "isReadOnly": false,
  "isRequired": false,
  "isSortable": true,
  "isVirtual": false,
  "reference": null,
  "inverseOf": null,
  "validations": []
}

[NicolasF]

Hi Arnaud,

Using forest-express-sequelize: 8.4.10 fixes the issue.

The solution is to upgrade to forest-express-sequelize: 8.4.10

Thank you, and have a nice day