NestJS Postgres Prisma - Error type 'string' is not assignable to parameter type 'TemplateStringsArray | Sql'

Question

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;
  }

Answer 1

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.

Answer 2

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;