I'm trying to build a NestJS Monorepo service using Microservices architecture with PostgreSQL as a database, Prisma as ORM and TypeScript as primary language. But I keep getting the error below when I try executing a Postgres query.
src/infrastructure/persistence/work.repository.postgres.ts:188:7 - error TS2345: Argument of type 'string' is not assignable to parameter of type 'TemplateStringsArray | Sql'.
I checked the data types and they seem to be compatible.
Please help me fix this.
Stack: TypeScript, PostgreSQL, Node.JS, Express and NestJS.
Thanks in advance!
async findWriterNumber(writerAddress: string): Promise<number> {
const maxNumber = await this.prismaService.$queryRaw<{
max: number;
}>(
`SELECT coalesce(max('writerNumber') + 1, 0) as max
FROM "Work"
LEFT OUTER JOIN "WriterID"
ON "Work"."id" = "WorkID"."workId" AND "WorkID"."address" = '${writerAddress}'`,
);
return maxNumber[0].max;
}
Found a Simple Fix:
Remove ( ) around the sql statement.
async findWriterNumber(writerAddress: string): Promise<number> {
const maxNumber = await this.prismaService.$queryRaw<{
max: number;
}>
`SELECT coalesce(max('writerNumber') + 1, 0) as max
FROM "Work"
LEFT OUTER JOIN "WriterID"
ON "Work"."id" = "WorkID"."workId" AND "WorkID"."address" = '${writerAddress}'`;
return maxNumber[0].max;
}
A prepared statement will translate the query to "select coalesce ... from work where ... address=$1" and send along $1 as a parameter - so you don't need wrap it around quotations.
Another solution is to use the Prisma.sql helper:
const maxNumber = await this.prismaService.$queryRaw<{ max: number; }>(
Prisma.sql`SELECT coalesce(max('writerNumber') + 1, 0) as max
FROM "Work"
LEFT OUTER JOIN "WriterID"
ON "Work"."id" = "WorkID"."workId"
AND "WorkID"."address" = '${writerAddress}'`,
);
return maxNumber[0].max;