How to validate your prisma raw query results

Recently I investigated slonik, as an alternative for prisma when building raw queries. The tool use an interesting validation pattern, that I wanted to replicate with Prisma.

---

Slonik relies heavily on zod validators. Being the validation og, I'm not surprised. Working on a OOP focused team, I personnaly use class-validator and class-transformer from typestack.

Let's imagine that we need to retrieve all the unpaid amount for a company.

await prisma.$queryRaw`
  SELECT (
    COALESCE(SUM(due_amount), 0) -
    COALESCE(SUM(paid_amount), 0)
  ) AS "dueAmount"
  FROM (
    SELECT invoice.amount AS due_amount,
           SUM(payments.amount) AS paid_amount
    FROM invoice LEFT JOIN payments
      ON payments."invoiceId" = invoice.id
  ) _
`

Prisma allows us to type the request:

await prisma.$queryRaw<[{ dueAmount: bigint }]>`

But there are a few problems here:

• Since all types are provided by us, they might change
• The type is never validated against the query
• We might make a transparent mistake

For example, we could mistake the bigint for number. It might work a lot of the time, but we also could stumble on cases where it won't without it being apparent.

One way to fix this, is to add runtime type validation.

With class validator, we could use the following DTO:

class StillDueAmountDto {
  @IsNumber({ allowInfinity: false, allowNaN: false })
  @Transform(({ value }) => Number(value))
  dueAmount!: number;
}

Which will transform and validate our data:

const [stillDueAmountRaw] = await prisma.$queryRaw<[StillDueAmountDto]>`
  ...
`;

const stillDueAmount = plainToInstance(
  StillDueAmountDto,
  stillDueAmountRaw
)

await validateOrReject(stillDueAmount);
// stillDueAmount -> StillDueAmountDto { dueAmount: number }

It would be cumbersome to do that everytime, so we could create a tiny prisma client decorator:

export class ValidatedRawClient {
  constructor(private readonly prisma: PrismaClient) {}

  async $queryRaw<T extends object>(
    ctor: ClassConstructor<T>,
  ) {
    return async (...args: Parameters<PrismaClient['$queryRaw']>) => {
      // I never saw prisma return anything
      // other than an array
      const result = await this.prisma.$queryRaw<T[]>(...args);
      const instances = plainToInstance(ctor, result);

      await Promise.all(
        ([] as T[])
          .concat(instances)
          .map(i => validateOrReject(i))
      );
      return instances;
  }
}

Next we can use it in a similar way as slonik

const p = new ValidatedRawClient(new PrismaClient());

const stillDueAmounts = await p.$queryRaw(StillDueAmountDto)`
  SELECT ...
`;

One caveat though, we lose the SQL syntax highlighting in VSCode. We can change the function to keep the original entry signature:

export class ValidatedRawClient {
  constructor(private readonly prisma: PrismaClient) {}

  async $queryRaw(
    ...args: Parameters<PrismaClient['$queryRaw']>
  ) {
    return {
      as: async <T extends object>(
        ctor: ClassConstructor<T>
      ) => {
        // I never saw prisma return anything
        // other than an array
        const result = await this.prisma.$queryRaw<T[]>(...args);
        const instances = plainToInstance(ctor, result);

        await Promise.all(
          ([] as T[])
            .concat(instances)
            .map(i => validateOrReject(i))
        );
        return instances;
      }
    }
  }
}

const p = new ValidatedRawClient(new PrismaClient());
const stillDueAmounts = await p.$queryRaw`
  SELECT ...
`.as(StillDueAmountDto);

So here we have a runtime guard, which mean that we should rely on testing to catch changes in schema early, but it looks like a good starting point.

Let me know, if you see any improvements on the API.