typeorm: Using "in" operator with uuid types causes "invalid input syntax" error

I believe the answer to this question is in this other issue: https://github.com/typeorm/typeorm/issues/1985

All you have to do is use the new ‘…’ operator to spread the array inside the In clause of your query, like so:

export const queryBatchFunction = <T extends { id: string }>(repository: Repository<T>) => async (
  keys: string[],
): Promise<(T | null)[]> => {
  const docs = await repository
    .createQueryBuilder("TableName")
    .where(`TableName.id IN (:...keys)`, { keys })
    .getMany();

  return keys.map(k => docs.find(d => d.id === k) || null);
};

Note the :...key thing, but I don’t see this’s being mentioned anywhere in the changelog… Am I missing anything?

_Originally posted by @lednhatkhanh in https://github.com/typeorm/typeorm/issues/1985#issuecomment-383270029_

Similar effect for inside query builder…

qb.andWhere("v.customer IN (:customers)", { customers: ["548b47a3-61b9-48f4-b1f2-eb83011a20d5"]});

throwing this error:

UnhandledPromiseRejectionWarning: QueryFailedError: invalid input syntax for type uuid: "{"548b47a3-61b9-48f4-b1f2-eb83011a20d5"}"
app_1      |     at new QueryFailedError (/app/src/error/QueryFailedError.ts:9:9)
app_1      |     at Query.callback (/app/src/driver/postgres/PostgresQueryRunner.ts:178:30)
app_1      |     at Query.handleError (/app/node_modules/pg/lib/query.js:128:19)
app_1      |     at Client._handleErrorMessage (/app/node_modules/pg/lib/client.js:335:17)
app_1      |     at Connection.emit (events.js:314:20)
app_1      |     at Connection.EventEmitter.emit (domain.js:483:12)
app_1      |     at /app/node_modules/pg/lib/connection.js:115:12
app_1      |     at Parser.parse (/app/node_modules/pg-protocol/src/parser.ts:102:9)
app_1      |     at Socket.<anonymous> (/app/node_modules/pg-protocol/src/index.ts:7:48)
app_1      |     at Socket.emit (events.js:314:20)

Interestingly, the search will work if I add the parameter value inside the query, but only if I use single quotes like here:

qb.andWhere("v.customer IN ('548b47a3-61b9-48f4-b1f2-eb83011a20d5')");

I have been able to solve this by following the instructions in this issue.

Essentially just change your query to

qb.andWhere("v.customer IN (:...customers)", { customers: ["548b47a3-61b9-48f4-b1f2-eb83011a20d5"]});

This is happening for Raw as well.

manager.transaction(async (entityManager) => {
  const requests = await entityManager.find(Request, {
    select: ['id'],
    where: {
      tenantId: tenant,
      state: 'pending',
      createdAt: Raw((alias) => `EXTRACT(MINUTES FROM (NOW() - ${alias})) > :minutes`, {
        minutes,
      }),
    },
  });

  //...
});

Logs:

query failed: SELECT id FROM "request" "Request" WHERE "Request"."tenantId" = $1 AND "Request"."state" = $2 AND "Request"."createdAt" = $3 -- PARAMETERS: ["tenant1","pending",{"_type":"raw","_value":[],"_useParameter":true,"_multipleParameters":true,"_objectLiteralParameters":{"minutes":1}}]
error: error: invalid input syntax for type timestamp: "{"_type":"raw","_value":[],"_useParameter":true,"_multipleParameters":true,"_objectLiteralParameters":{"minutes":1}}"

I’ve tried using QueryBuilder instead, with the same result.

const consentRequests = await entityManager
  .createQueryBuilder()
  .select('id')
  .from(Request, 'Request')
  .where({
    tenantId: tenant,
    state: 'pending',
    createdAt: Raw((alias) => `EXTRACT(MINUTES FROM (NOW() - ${alias})) > :minutes`, {
      minutes,
    }),
  })
  .getMany();

#7247 suggests that using entityManager.getRespository(Request).find or entityManager.getRespository(Request).createQueryBuilder might work, but I got the same result with both.