$queryRaw doesn't create prepared statements correctly

[Noor0]

function getUsers(ids: number[], filter?: string) {
  const filterStatement = `%${filter}%`
  return $queryRaw`SELECT * FROM users WHERE id IN (${Prisma.join([ids])}) ${filter ? Prisma.sql`OR email LIKE ${filterStatement}` : Prisma.empty}`
}

The query above works fine outside of tests but, in a test with jest-prisma It creates a bad prepared-statement that looks like this in the logs

postgres-1  | 2024-03-05 08:10:26.809 UTC [348] ERROR:  syntax error at or near "$2" at character 45
postgres-1  | 2024-03-05 08:10:26.809 UTC [348] STATEMENT:
postgres-1  | 	      SELECT * FROM users WHERE id IN ($1) $2
postgres-1  |

and results in a PrismaClient error

PrismaClientKnownRequestError:
    Invalid `prisma.$queryRaw()` invocation:


    Raw query failed. Code: `42601`. Message: `ERROR: syntax error at or near "$2"`

[shu-bc]

same issue here.
it seems that nested sqltag expression like this does not work as expected

${id? Prisma.sql`and c.id != ${id}` : Prisma.empty}

[danielpowell4]

I've found that it seems to work nicely when you assign the sql to a separate variable rather

// errors
prisma.$executeRaw`sql-here`

// works
const mySql = Prisma.sql`sql-here`
prisma.$executeRaw(mySql)

more in-depth example:

// util to turn array of objects into friendly sql values
export const buildSqlValuesList = <T extends Record<string, unknown>>(
  items: T[],
  order: (keyof T)[],
  casts = {} as Partial<Record<keyof T, (_value: unknown) => Prisma.Sql | null>>,
): Prisma.Sql => {
  if (!items.length) {
    throw new Error('Cannot build SQL values list from empty array');
  }

  const rows = items.map((item) => {
    const values = order.map((key) => {
      const value = item[key];
      const cast = casts[key];
      if (cast) return cast(value) || Prisma.sql`NULL`;
      return Prisma.sql`${value}`;
    });
    return Prisma.join(values, undefined, '(', ')');
  });

  return Prisma.join(rows);
};


const itemUpdates = [{ uuid: 'some-uuid', status: 'COMPLETE' });

const updateItemValues = buildSqlValuesList(
    itemUpdates,
    ['uuid', 'status'],
    {
      uuid: (val) => Prisma.sql`${val}::uuid`,
      status: (val) => Prisma.sql`${val}::enum_status`,
    },
  );

const updateItemSql = Prisma.sql`
    UPDATE "production_item"
    SET
      "last_updated" = NOW()
      , "status" = "v"."status"
    FROM (VALUES ${updateItemValues}) AS "v" ("uuid", "status")
    WHERE "item"."uuid" = "v"."uuid"
  `;
await prisma.$executeRaw(updateItemSql);