Improving Prisma raw queries typing.
Tags: prisma, typescript, sql, postgres
Published: 2023-02-25 11:09:34


I’ve been working more and more with raw queries with prisma. Especially for aggregation use cases. Let’s face it Prisma best-in-class DSL is great for direct data access, but it becomes less interesting when agregating data between multiple tables. Another aspect, is when using feature specific to Postgresql like json agregations.

At first, I looked into linting operations, and I found the great [safeql][u:safeql]. It’s still in its infancy, and the performance wasn’t great on my machine though. I’l try to keep an eye on it, since it looks quite promising.

In this situation, we can’t really tell statically when queries might not work anymore.

One way to improve the maintenance is to use dynamic mapping variables provided by Prisma. Sadly, out of the box, Prisma only provides the names of fields. Those of the schema, not the table.

That being said, they exposed some intricate data in the `dmmf`. I don’t really know what `dmmf` stands for (_data model mapping_ I suppose), but it’s everything I was looking for!

I played a bit with it, and thought that I could use it to provide a helper constant, that I named `P`.

More essentially, I needed to come up with a dynamic type to ease the maintenance. Oh boy was I pleased to have template litteral types!

```ts
import { Prisma } from '@prisma/client';

export type PType = {
  [K in keyof typeof Prisma.ModelName]: {
    tableName: Prisma.Sql;
    fields: {
      [KK in keyof typeof Prisma[`${K}ScalarFieldEnum`]]: Prisma.Sql;
    };
  };
};
```

Basically, we iterate over each Prisma model name using the constant `Prisma.ModelName`.

```ts
[K in keyof typeof Prisma.ModelName]: {
```

then, we need to do the same thing for any _scalar_ field, so any field in db.

```ts
[KK in keyof typeof Prisma[`${K}ScalarFieldEnum`]]: Prisma.Sql;
```

The thing, is that Prisma generates a dynamic field including the model name. So, for example if I have a `Payment` model in my schema

```prisma
model Payment {
  id String @db.Uuid @id
}
```

Prisma will generate a `PaymentScalarFieldEnum` enum.

```ts
// node_modules/.prisma/client/index.d.ts
export const PaymentScalarFieldEnum: {
  id: 'id'
};
export type PaymentScalarFieldEnum = (typeof PaymentScalarFieldEnum)[keyof typeof PaymentScalarFieldEnum];
```

Being exposed in the `Prisma` namespace, we can use a template literal type to access this enum, using the _currently iterated model name_!

Now you can enjoy this sweet autocompletion.


## Intialization at runtime

Types are great, but nothing without any runtime equivalent. We need to initialize our `P` constant. Fortunately, the implementation is quite similar to the type defininition.

We need, `Prisma.ModelName`, all the `Prisma.*ScalarFieldEnum` and another mysterious variable: `Prisma.dmmf.datamodel`.

`dmmf` exposes a lot of internal mapping made by the Prisma engine. `models` exposes our models declared in the schema:

```shell
> Prisma.dmmf.models
...
models: [
  {
    name: 'Payment',
    dbName: 'payments',
    fields: [Array],
    primaryKey: [Object],
    uniqueFields: [],
    uniqueIndexes: [],
    isGenerated: false
  },
]
...
```

and each field, will expose similar information:

```shell
> Prisma.dmmf.models.find(x => x.name === 'Payment').fields
[
  {
    name: 'createdAt',
    dbName: 'created_at',
    kind: 'scalar',
    isList: false,
    isRequired: true,
    isUnique: false,
    isId: false,
    isReadOnly: false,
    hasDefaultValue: true,
    type: 'DateTime',
    default: { name: 'dbgenerated', args: [Array] },
    isGenerated: false,
    isUpdatedAt: false
  }
]
```

Note that the type `Prisma.DMMF.Field` exposes a `dbNames` key, while we find a `dbName` key here. The type is quite flexible, So I didn't mind, but I was surprised at first.

Anyway, now that we have everything prepared, we only need to iterate over the enums, and create our object:

```ts
import { Prisma } from '@prisma/client';

export const P = Object.fromEntries(
  Object.values(Prisma.ModelName).map((modelName) => {
    const model = Prisma.dmmf.datamodel.models.find(
      ({ name }) => name === modelName
    ) as Prisma.DMMF.Model;
    return [
      modelName,
      {
        tableName: Prisma.raw(model.dbName ?? model.name),
        fields: Object.fromEntries(
          Object.values(Prisma[`${modelName}ScalarFieldEnum`]).map(
            (field) => {
              const dmmfField = model.fields
                .filter(
                  ({ kind }) => !["object", "unsupported"].includes(kind)
                )
                .find(({ name }) => name === field) as Prisma.DMMF.Field;
              return [field, Prisma.raw(dmmfField.dbName ?? dmmfField.name)];
            }
          )
        )
      },
    ];
  })
) as unknown as PType;
```

So now, I can use `P` in my raw queries directly:

```ts
const prisma = new PrismaClient();
const clientId = '7d2c979c-f356-4ac0-8005-2b1f1f196a31';
const [{ paidAmount }] = await prisma.$queryRaw<readonly [{ paidAmount: bigint }]>`
  SELECT SUM("${P.Payment.fields.amount}") AS "paidAmount"
  FROM "${P.Payment.tableName}"
  WHERE "${P.Payment.fields.clientId}" = ${clientId}
`;
```

Of course, this is an extremely simplified use case where Prisma DSL is far better.
But I can see myself using it.

Let me know if you can see any improvement.

[u:safeql]: https://safeql.dev


---

> This was originally published on my [blog](https://blog.jquagliatini.fr/en/posts/2023-02-24-ts-prisma-raw/). Hoping to post more international content in the future