How to validate your prisma raw query results
Tags: prisma, sql, typescript
Published: 2023-03-27 12:00:00

Recently I investigated [slonik](https://github.com/gajus/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](https://zod.dev) validators. Being the validation og, I'm not surprised. Working on a OOP focused team, I personnaly use [class-validator](https://github.com/typestack/class-validator) and `class-transformer` from typestack.

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

```ts

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:

```ts
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:

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

Which will transform and validate our data:

```ts
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:


```ts
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

```ts
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:


```ts
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.