Improving Prisma raw queries typing.

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. 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!

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.

[K in keyof typeof Prisma.ModelName]: {

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

[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

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

Prisma will generate a PaymentScalarFieldEnum enum.

// 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:

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

and each field, will expose similar information:

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

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:

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.

This was originally published on my blog. Hoping to post more international content in the future