"ERROR: operator does not exist: uuid = character varying"

[andrestone]

Package versions and database engine type (please complete the following information):

• Database Engine: postgres
• TypeORM Version: 0.2.32
• Driver Version 2.1.0

Describe the bug Code below makes an invalid request to DataApi

 await Article.findOneOrFail({
  where: {
    id: "e279da6d-e0dc-4fce-9027-297ba928d4b3",
  },
});

{ "message": ERROR: operator does not exist: uuid = character varying\n  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.\n  Position: [...]",
    "code": "BadRequestException",
     [...]
}

Probably introduced by #77.

[daverickdunn]

I've spent half my day trying to figure this out... 😭

Edit: Can confirm that the issue is not present on version 2.0.5

[ArsenyYankovsky]

We currently cannot automatically cast parameters. Please try to use explicit casting as described here

[daverickdunn]

Was going to suggest a regex solution, but I can see that's what you had previously. What was the issue that caused you to remove it?

~~Edit: Perhaps it's worth noting that regex in the original solution only matched UUID v4.~~ Edit 2: Nm, I misread the regex. Edit 3: I assume the issue was that when users stored UUIDs as text fields this would cast to UUID instead of text?

[ArsenyYankovsky]

Yes, I think it's a bad idea to implicitly cast something for a user they cannot opt-out from. However, @seawatts is working on a different solution that will allow us to automatically cast parameters based on the knowledge of the entity.

[harm-less]

@ArsenyYankovsky Yes, that's a valid point. I'll await @seawatts solution, because this hack removal broke our app while it fixed this issue (#83) in a later release for us 😅

In case someone has the same issue as I do but wants to have it fixed in the meantime, I've reintroduced the UUID hack into this fork. This is basically version 2.2.0 including the hack: https://github.com/harm-less/typeorm-aurora-data-api-driver

Be aware, I'll remove this package once a permanent fix is released.

[AlexBrandes]

@ArsenyYankovsky Could you provide an update on the plan for this issue going forward? Will implicit casting always be required for queries involving UUID fields?

I found explicit casting of all UUID fields too cumbersome to maintain because the casting syntax doesn't work with other drivers. Our local tests using postgres driver were failing unless each query was special cased for the test environment to remove the value: '', cast: 'uuid' config. In the meantime I've downgraded to 2.0.6 which behaves in the way I would expect.

[ArsenyYankovsky]

@AlexBrandes I guess the best solution is to have an option for an easy opt-in. I guess a good start is a configuration option. The ultimate solution would be for the driver to provide a repository that has access to the metadata and could the information to cast automatically.

[ArsenyYankovsky]

@AlexBrandes @harm-less You should be able to switch the UUID hack on with formatOptions.enableUuidHack in the new 2.3.0 driver.

[harm-less]

Awesome @ArsenyYankovsky, I've put it on my todo list 😉