DEV Community: Jordan Quagliatini

How to validate your prisma raw query results

Jordan Quagliatini — Mon, 27 Mar 2023 12:00:00 +0000

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.

Simple Value Types in Typescript

Jordan Quagliatini — Thu, 09 Mar 2023 15:00:00 +0000

Value Types, or Value Objects, are core components of OOP design inheriting principles from Domain Driven Design, like entities and agregates. More often than not, entities have an identity: a unique domain identifier. They usually have a id field. Examples includes Users, Invoices, etc. Their content only doesn't differenciate enought between two entities.

On the other hand, Value Types, don't have a specific identity, but are identified by their content. It's not who they are, but what's their content. Like and Address, or an Email Address. They can introduce more semantic to your code, increase the safety, maintainability and testability. All this comes at the expanse of some degree of complexity. Especially in hydration and serialization scenarios.

For a lot of Value Types, they encapsulate a primitive value, wrapped and protected by domain specifications. Some are specific to a domain, and other cover more general use cases. In my opinion, introducing more complexity for domain Value Types is alright, but it's not for the generic ones. It's especially frustrating to have complex Value Type, when no behaviour is attached to theme.

How would you impement a simple, yet generic Value Type in Typescript?

Let's dive into an example, from a previous article, where we created a Window Iterable.

class AtLeastOneWindowIterable<T> implements Iterable<T[] | undefined> {
  protected constructor(
    private readonly values: readonly T[],
    private readonly size: number
  ) {}

  static of<U>(values: readonly U[]) {
    return {
      by: (size: number) => new AtLeastOneWindowIterable(
        values,
        size,
      ),
    };
  }
}

What if we increased the security by introducing a Value Type that only allow positive integers for the window size.

Let's start with the type:

type StrictlyPositiveInt = number & { __name: 'StrictlyPositiveInt' };

Adding the & { __name: string } is common practice to create "Branded Types".
It prevents TS from keeping the structural equivalence between a number and StrictlyPostiveInt. If that the case, we can't simply assign a number. The following won't work, and that's exactly our intention:

const a: StrictlyPositiveInt = 3;
  //  〰️ Type 'number' is not assignable to type 'StrictlyPositiveInt'.

We need a constructor:

function StrictlyPositiveInt(v: any): StrictlyPositiveInt {
  const n = Number(v);

  if (!Number.isFinite(n)) throw new Error(`${v} is not a number`);
  if (Math.round(n) !== n) throw new Error(`${n} is not an integer`);
  if (n <= 0) throw new Error(`${n} must be positive`);

  return n as StrictlyPositiveInt;
}

If you ever used zod, that's equivalent to

import { z } from 'zod';

const StrictlyPositiveInt = z.number().int().min(1).brand('StrictlyPositiveInt');
type StrictlyPostiveIntT = z.infer<typeof StrictlyPositiveInt>;

I encourage you to read their page on branded types.

Using it, becomes quite easy:

class AtLeastOneWindowIterable<T> implements Iterable<T[] | undefined> {
  protected constructor(
    private readonly values: readonly T[],
    private readonly size: StrictlyPositiveInt
  ) {}

  static of<U>(values: readonly U[]) {
    return {
      by: (size: number) => new this(
        values,
        StrictlyPositiveInt(size),
      ),
    };
  }
}

Notice here, that the Iterable encapsulates the usage of the Value Type. In my opinion there are few reasons to expose internal rules to the outside world. From experience, having entities responsible for creating their Value Types is quite maintainable, even for more complex scenarios.

The only added complexity here comes from the exceptions. How you handle it will depend on your use cases. But usually, I just don't and prefer to let it bubble up.

Now, let's assume for a minute, that we need to send this StrictlyPositiveInt over the wire. No fuss or worries. We simply stringify it.

JSON.stringify(StrinctlyPositiveInt(32_000))

Since, our Value Type, is only some type decoration, and runtime validation, there is no complexity. And since, our Iterable takes a number as input, the hydration is also painless. Win - Win!

Branded Types, Value Types, Value Objects. There are a ton of names for a simple logic encapsulation. Do you use it? How do you name it? Let me know.

Prevent Prisma from reaching bind parameters limit

Jordan Quagliatini — Thu, 02 Mar 2023 15:00:00 +0000

A little known fact about the Prisma engine, is that there is a limit to the number of parameter you can provide. Even if it will try to chunk the query, you can sometimes fall into the trap and see this message:

too many bind variables in prepared statement, expected maximum of 32767, received 32768

As stated in this error message, you can only provide at most 32767 parameters. It might seem like a lot. It's really not. I found myself falling into this trap quite easily. And at the time the error message was closer to the one in this github issue:

Misleading "Can't reach database server" error when more than `32766` variables are bound inside an `IN` clause in Postgres #8832

some-user123 posted on Aug 19, 2021

Bug description

After upgrading to 2.29.1 my app is not starting properly anymore. I tested it with 2.29.0 --> same error, versions below (e.g. 2.28.0 and 2.27.0 are working fine).

I'm using Prisma in a NestJS application running in a Docker container with an Postgres DB running in a separate container.

I receive this error:

/app/node_modules/@prisma/client/runtime/index.js:35943
          throw new import_engine_core3.PrismaClientKnownRequestError(message, e.code, this.prisma._clientVersion, e.meta);
                ^

PrismaClientKnownRequestError3 [PrismaClientKnownRequestError]:
Invalid `prisma.XXX.findMany()` invocation:


  Can't reach database server at `db`:`5432`

Please make sure your database server is running at `db`:`5432`.
    at cb (/app/node_modules/@prisma/client/runtime/index.js:35943:17)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async XXX.XXX (/app/dist/XXX/XXX.service.js:41:28) {
  code: 'P1001',
  clientVersion: '2.29.0',
  meta: { database_host: 'db', database_port: 5432 }
}

Before I receive this error message prisma migrate deploy runs successfully and two DB query are executed successfully. Therefore I highly doubt the error message, that the DB is not reachable. (Plus the error does not occur with the same DB and earlier versions of prisma.)

Executing the same app with DEBUG="*" (removed lines from express and NestJS for brevity):

yarn run v1.22.5
$ /app/node_modules/.bin/prisma migrate deploy
2021-08-19T20:01:12.805Z prisma:loadEnv project root found at /app/package.json
2021-08-19T20:01:12.863Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:12.864Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:12.864Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:12.867Z prisma:engines using Node API: false
2021-08-19T20:01:12.867Z prisma:engines binaries to download query-engine, migration-engine, introspection-engine, prisma-fmt
Prisma schema loaded from prisma/schema.prisma
2021-08-19T20:01:14.543Z prisma:getConfig Using Query Engine Binary at: /app/node_modules/@prisma/engines/query-engine-linux-musl
Datasource "db": PostgreSQL database "XXX", schema "public" at "db:5432"
2021-08-19T20:01:14.631Z prisma:getConfig Using Query Engine Binary at: /app/node_modules/@prisma/engines/query-engine-linux-musl
2021-08-19T20:01:14.953Z prisma:migrateEngine:rpc starting migration engine with binary: /app/node_modules/@prisma/engines/migration-engine-linux-musl
2021-08-19T20:01:14.968Z prisma:migrateEngine:rpc SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"diagnoseMigrationHistory","params":{"migrationsDirectoryPath":"/app/prisma/migrations","optInToShadowDatabase":false}}
2021-08-19T20:01:14.975Z prisma:migrateEngine:stderr {"timestamp":"2021-08-19T20:01:14.974615292+00:00","level":"INFO","fields":{"message":"Starting migration engine RPC server","git_hash":"1be4cd60b89afa04b192acb1ef47758a39810f3a"},"target":"migration_engine"}
2021-08-19T20:01:15.075Z prisma:migrate:deploy {
  diagnoseResult: '{\n' +
    '  "history": null,\n' +
    '  "failedMigrationNames": [],\n' +
    '  "editedMigrationNames": [],\n' +
    '  "hasMigrationsTable": true\n' +
    '}'
}
2021-08-19T20:01:15.075Z prisma:migrateEngine:rpc SENDING RPC CALL {"id":2,"jsonrpc":"2.0","method":"listMigrationDirectories","params":{"migrationsDirectoryPath":"/app/prisma/migrations"}}
2021-08-19T20:01:15.076Z prisma:migrate:deploy {
  listMigrationDirectoriesResult: {
    migrations: [
      'XXX',
      'XXX',
      'XXX',
      'XXX',
      'XXX',
      'XXX',
      'XXX'
    ]
  }
}

7 migrations found in prisma/migrations
2021-08-19T20:01:15.077Z prisma:migrateEngine:rpc SENDING RPC CALL {"id":3,"jsonrpc":"2.0","method":"applyMigrations","params":{"migrationsDirectoryPath":"/app/prisma/migrations"}}
2021-08-19T20:01:15.261Z prisma:getConfig Using Query Engine Binary at: /app/node_modules/@prisma/engines/query-engine-linux-musl

No pending migrations to apply.
Done in 4.50s.
yarn run v1.22.5
$ node dist/main
2021-08-19T20:01:24.008Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:24.010Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:24.010Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:24.772Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:24.776Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:24.777Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:24.933Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:25.051Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:25.051Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:25.051Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:25.172Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:25.227Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:25.227Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:25.227Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:25.277Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:25.343Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:25.343Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:25.343Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:25.491Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:25.544Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:25.544Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:25.544Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:25.561Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:25.648Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:25.648Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:25.648Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:25.702Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:25.802Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:25.803Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:25.803Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:25.814Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:25.942Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:25.942Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:25.942Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:25.953Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:26.007Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:26.007Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:26.007Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:26.032Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:26.086Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:26.087Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:26.087Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:26.167Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:26.214Z prisma:tryLoadEnv Environment variables not found at null
2021-08-19T20:01:26.215Z prisma:tryLoadEnv Environment variables not found at undefined
2021-08-19T20:01:26.215Z prisma:tryLoadEnv No Environment variables loaded
2021-08-19T20:01:26.296Z prisma:client clientVersion: 2.29.0
2021-08-19T20:01:27.066Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:27.090Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.091Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.091Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.092Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.093Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.093Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.094Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.094Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.095Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.096Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.096Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.097Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.098Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.150Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.184Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.208Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.268Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.306Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.385Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.414Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.433Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '37334' ] }
2021-08-19T20:01:27.483Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.519Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.558Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.612Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:27.891Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:27.891Z prisma:engine stdout Started http server on http://127.0.0.1:37334
2021-08-19T20:01:27.913Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.923Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:27.923Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:27.971Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:28.012Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:28.012Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '45999' ] }
2021-08-19T20:01:28.032Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:28.032Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:28.032Z prisma:engine Active provider: postgresql
2021-08-19T20:01:28.134Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:28.350Z prisma:engine stdout Started http server on http://127.0.0.1:45999
2021-08-19T20:01:28.356Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:28.460Z prisma:client Prisma Client call:
2021-08-19T20:01:28.468Z prisma:client prisma.YYY.findMany({
  include: {
    YYY: {
      select: {
        YYY: true
      }
    },
    YYY: {
      select: {
        YYY: true
      }
    }
  }
})
2021-08-19T20:01:28.484Z prisma:client Generated request:
2021-08-19T20:01:28.493Z prisma:client query {
  findManyYYY {
    YYY
    YYY {
      YYY
    }
    YYY {
      YYY
    }
  }
}

2021-08-19T20:01:28.496Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:28.497Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:28.502Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:28.545Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:28.547Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '32929' ] }
2021-08-19T20:01:28.590Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:28.590Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:28.590Z prisma:engine Active provider: postgresql
2021-08-19T20:01:28.657Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:28.887Z prisma:engine stdout Started http server on http://127.0.0.1:32929
2021-08-19T20:01:28.902Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:28.941Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:29.045Z prisma:client Prisma Client call:
2021-08-19T20:01:29.045Z prisma:client prisma.ZZZ.findMany({
  include: {
    ZZZ: {
      orderBy: {
        ZZZ: 'desc'
      },
      take: 1
    }
  }
})
2021-08-19T20:01:29.045Z prisma:client Generated request:
2021-08-19T20:01:29.045Z prisma:client query {
  findManyZZZ {
    ZZZ
    ZZZ
    ZZZ
    ZZZ(
      orderBy: [
        {
          ZZZ: desc
        }
      ]
      take: 1
    ) {
      ZZZ
      ZZZ
      ZZZ
    }
  }
}

2021-08-19T20:01:29.110Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:29.110Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:29.110Z prisma:engine Active provider: postgresql
2021-08-19T20:01:34.239Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:34.239Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:34.239Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:34.240Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '44488' ] }
2021-08-19T20:01:34.279Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:34.412Z prisma:engine stdout Started http server on http://127.0.0.1:44488
2021-08-19T20:01:34.413Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:34.414Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:34.414Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:34.415Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:34.448Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:34.448Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '35514' ] }
2021-08-19T20:01:34.473Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:34.473Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:34.473Z prisma:engine Active provider: postgresql
2021-08-19T20:01:34.488Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:34.636Z prisma:engine stdout Started http server on http://127.0.0.1:35514
2021-08-19T20:01:34.637Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:34.638Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:34.638Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:34.638Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:34.654Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:34.656Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '35121' ] }
2021-08-19T20:01:34.674Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:34.687Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:34.687Z prisma:engine Active provider: postgresql
2021-08-19T20:01:34.729Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:34.846Z prisma:engine stdout Started http server on http://127.0.0.1:35121
2021-08-19T20:01:34.846Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:34.847Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:34.847Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:34.847Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:34.879Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:34.880Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '37987' ] }
2021-08-19T20:01:34.927Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:34.927Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:34.927Z prisma:engine Active provider: postgresql
2021-08-19T20:01:34.968Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:35.158Z prisma:engine stdout Started http server on http://127.0.0.1:37987
2021-08-19T20:01:35.158Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:35.159Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:35.159Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:35.159Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:35.179Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:35.179Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '45040' ] }
2021-08-19T20:01:35.197Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:35.198Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:35.198Z prisma:engine Active provider: postgresql
2021-08-19T20:01:35.215Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:35.278Z prisma:engine stdout Started http server on http://127.0.0.1:45040
2021-08-19T20:01:35.279Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:36.386Z prisma:client Prisma Client call:
2021-08-19T20:01:36.386Z prisma:client prisma.XXX.findMany({
  select: {
    XXX: true,
    XXX: true,
    XXX: true,
    XXX: true,
    XXX: true,
    XXX: true,
    XXX: true,
    XXX: true,
    XXX: {
      select: {
        XXX: true,
        XXX: true,
        XXX: true,
        XXX: true,
        XXX: true
      }
    }
  }
})
2021-08-19T20:01:36.386Z prisma:client Generated request:
2021-08-19T20:01:36.386Z prisma:client query {
  findManyXXX {
    XXX
    XXX
    XXX
    XXX
    XXX
    XXX
    XXX
    XXX
    XXX {
      XXX
      XXX
      XXX
      XXX
      XXX
    }
  }
}

2021-08-19T20:01:36.386Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:36.386Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:36.388Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:36.418Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:36.418Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '38534' ] }
2021-08-19T20:01:36.436Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:36.436Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:36.436Z prisma:engine Active provider: postgresql
2021-08-19T20:01:36.469Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:36.557Z prisma:engine stdout Started http server on http://127.0.0.1:38534
2021-08-19T20:01:36.558Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:36.561Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:36.561Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:36.562Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:36.577Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:36.578Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '41161' ] }
2021-08-19T20:01:36.608Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:36.609Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:36.610Z prisma:engine Active provider: postgresql
2021-08-19T20:01:36.738Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:36.916Z prisma:engine stdout Started http server on http://127.0.0.1:41161
2021-08-19T20:01:36.918Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:36.922Z prisma:engine { cwd: '/app/prisma' }
2021-08-19T20:01:36.922Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:36.923Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:36.951Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:36.956Z prisma:engine { flags: [ '--enable-raw-queries', '--port', '43908' ] }
2021-08-19T20:01:36.986Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:36.987Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:36.987Z prisma:engine Active provider: postgresql
2021-08-19T20:01:37.035Z prisma:engine stdout Starting a postgresql pool with 65 connections.
2021-08-19T20:01:37.208Z prisma:engine stdout Started http server on http://127.0.0.1:43908
2021-08-19T20:01:37.209Z prisma:engine Search for Query Engine in /app/node_modules/.prisma/client
2021-08-19T20:01:37.250Z plusX Execution permissions of /app/node_modules/.prisma/client/query-engine-linux-musl are fine
2021-08-19T20:01:37.330Z prisma:engine Client Version: 2.29.0
2021-08-19T20:01:37.332Z prisma:engine Engine Version: query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a
2021-08-19T20:01:37.333Z prisma:engine Active provider: postgresql
2021-08-19T20:01:39.418Z prisma:client:fetcher Error: Can't reach database server at `db`:`5432`

Please make sure your database server is running at `db`:`5432`.
    at BinaryEngine.graphQLToJSError (/app/node_modules/@prisma/client/runtime/index.js:29972:16)
    at BinaryEngine.request (/app/node_modules/@prisma/client/runtime/index.js:29857:24)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async cb (/app/node_modules/@prisma/client/runtime/index.js:35913:26)
    at async XXX.XXX (/app/dist/XXX/XXX.service.js:41:28)
/app/node_modules/@prisma/client/runtime/index.js:35943
          throw new import_engine_core3.PrismaClientKnownRequestError(message, e.code, this.prisma._clientVersion, e.meta);
                ^

PrismaClientKnownRequestError3 [PrismaClientKnownRequestError]:
Invalid `prisma.XXX.findMany()` invocation:


  Can't reach database server at `db`:`5432`

Please make sure your database server is running at `db`:`5432`.
    at cb (/app/node_modules/@prisma/client/runtime/index.js:35943:17)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async XXX.XXX (/app/dist/XXX/XXX.service.js:41:28) {
  code: 'P1001',
  clientVersion: '2.29.0',
  meta: { database_host: 'db', database_port: 5432 }
}
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

How to reproduce

See above

Expected behavior

App should run with 2.29.0 and 2.29.1 as it does with 2.28.0 ;-)

Prisma information

generator client { provider = "prisma-client-js" }

datasource db { provider = "postgresql" url = env("DATABASE_URL") }

...

Environment & setup

OS: Linux (node:16-alpine), I cannot reproduce this problem on Windows at the moment
Database: PostgreSQL
Node.js version: 16.6.2

Prisma Version

prisma               : 2.29.0
@prisma/client       : 2.29.0
Current platform     : linux-musl
Query Engine         : query-engine 1be4cd60b89afa04b192acb1ef47758a39810f3a (at node_modules/@prisma/engines/query-engine-linux-musl)
Migration Engine     : migration-engine-cli 1be4cd60b89afa04b192acb1ef47758a39810f3a (at node_modules/@prisma/engines/migration-engine-linux-musl)
Introspection Engine : introspection-core 1be4cd60b89afa04b192acb1ef47758a39810f3a (at node_modules/@prisma/engines/introspection-engine-linux-musl)
Format Binary        : prisma-fmt 1be4cd60b89afa04b192acb1ef47758a39810f3a (at node_modules/@prisma/engines/prisma-fmt-linux-musl)
Default Engines Hash : 1be4cd60b89afa04b192acb1ef47758a39810f3a
Studio               : 0.419.0

View on GitHub

The thing is that in a lot of cases this error won't appear since Prisma does its magic to try to chunk the query. This chunking operation is made in a really naive way (sorry Prisma team, you are great!).

Here is how I try to bypass generally this error, granted in a convoluted way.

tl;dr;: go here

Example

Let's imagine a CRM-like application, where Clients are our main models. In addition, let's imagine that we use a multi tenancy pattern, where each Client is associated with a companyId.



model Client {
  id         String @db.Uuid @default(uuid())
  companyId  Int    @map("company_id")
  name       String
  categories String[]

  @@id([companyId, id])
  @@map("clients")
}

More often than not, we might want to batch actions on clients. But since, we will always have a companyId criteria, Prisma won't be able to chunk the query correctly.

A simple query as the following, will fail with our P2035 error since Prisma will chunk ids in 32267 parameters, forgetting companyId.



await prisma.client.findMany({
  where: { id: { in: ids }, companyId }
});

First attempts

My first attempt was to notice when there might be a lot of parameters provided to the query. Usually, that's when a IN is involved. I'm not a big fan of such queries, and I'm still trying to find the perfect optimization. But maybe in a future article.

A simple implementation I used with lodash's chunk:



async function findClients(companyId: number, ids?: readonly string[]) {
  const clients = await prisma.$transaction(
    _.chunk(ids, 32_000)
      .map(_ids => prisma.client.findMany({
        where: { companyId, id: { in: _ids } }
      }))
  );
  return clients.flat();
}

You might have noticed that ids is optional. This is great since Prisma DSL supports undefined as a value to sate the absence of criteria. It's not so great since we use it to create our chunks. In our case, we always want at least one query to execute. The thing is that mapping on an empty array always produce an empty array. Hence, if we dont provide any id criteria, we won't return any client. Which is not what we want here.

Iteration #1

As a first iteration, I just repeated the same snippet over and over. It was enough to solve my problem, and was consise.



async function findClients(companyId: number, ids: readonly string[] = []) {
  const clients = await prisma.$transaction(
    (ids.length > 0 ? _.chunk(ids, 32_000) : [undefined])
      .map(_ids => prisma.client.findMany({
        where: { companyId, id: { in: _ids } }
      }))
  );
  return clients.flat();
}

Iteration #2

Can't we sprinkle some OOP, and answer our problem?

Instead of using our chunk method, I'd like a window iterator.

Fortunately, Iterable are built into JS:



class AtLeastOneWindowIterable<T> implements Iterable<T[] | undefined> {

So basically, I'm reusing the contract of my previous statement in a more generic way:



(ids.length > 0 ? _.chunk(ids, 32_000) : [undefined])

I like to have a fluent builder method to initialize our iterable.



class AtLeastOneWindowIterable<T> implements Iterable<T[] | undefined> {
  protected constructor(
    private readonly values: readonly T[],
    private readonly size: number,
  ) {}

  static of<U>(values: readonly U[]) {
    return {
      by: (size: number) => new AtLeastOneWindowIterable(
        values,
        size,
      ),
    };
  }
}

I can now use it, to chunk our array in windows of 2 elements:



AtLeastOneWindowIterable.of([1, 2, 3, 4]).by(2);
// [[1, 2], [3, 4]]

That being said, we don't have an Iterable yet, and this code won't compile. We need to add a Symbol.iterator method.



class AtLeastOneWindowIterable<T> implements Iterable<T[] | undefined> {
  [Symbol.iterator]() {
    if (this.values.length === 0) {
      return [undefined][Symbol.iterator]();
    }

    const copy = [...this.values];
    const out: T[[]] = [];
    while (copy.length) {
      out.push(copy.splice(0, this.size));
    }

    return out[Symbol.iterator]();
  }
}

What's going on here? Basically, if we don't have any value, we iterate over a single value: undefined. That's why we used the protocol Iterable<T[] | undefined>. Otherwise, we simply chunk over the values. We could use lodash here, but it's quite fun to experiment.

Now, we can iterate over the chunks, or windows:



for (const chunk of AtLeastOneWindowIterable.of([1, 2, 3, 4]).by(2)) {
  // 1. chunk <==> [1, 2]
  // 2. chunk <==> [3, 4]
}

Usually, when playing with collections I like to go the lazy way, and use generators, giving the following:



*[Symbol.iterator]() {
  const copy = [...this.values];
  if (copy.length === 0) yield undefined;
  while (copy.length) yield copy.splice(0, this.size);
}

This seems more readable for who knows generators – notice the * at the start of the method. They're heavily under-used in application development in my opinion. If you're not familiar with them, I beg you to read the Iteration protocol and Iterators and generators documents.

Alright, we already have something usable. Prisma provides a "functional" interface with $transaction, and we are going to use our windows with promises. So we might as well add a map method. It's a piece of cake now that we can iterate over each window.



map<U>(mapper: (items?: T[]) => U): U[] {
  const out: U[] =  [];
  for (const chunks of this) {
    out.push(mapper(chunks));
  }

  return out;
}

Arrays have a more familiar interface than Iterable, so we'll return one. Notice that our chunk Array should be of type T[][], but that we return a U[]. Nothing should prevent users to return an aggregation of the chunks. Also, the mapper have a different type than the standard one from Array. We have an optional value, since the possible values are T[] or undefined. This will require users to handle the empty Array case.

Another implementation would look like the following:



map<U>(mapper: (items?: T[]) => U): U[] {
  return Array.from(this).map(items => mapper(items));
}

If we wrap it up:



class AtLeastOneWindowIterable<T> implements Iterable<T[] | undefined> {
  protected constructor(
    private readonly values: readonly T[],
    private readonly size: number
  ) {}

  *[Symbol.iterator]() {
    const copy = [...this.values];
    if (copy.length === 0) yield undefined;
    while (copy.length) yield copy.splice(0, this.size);
  }

  map<U>(mapper: (items?: T[]) => U): U[] {
    return Array.from(this).map(items => mapper(items));
  }

  static of<U>(values: readonly U[]) {
    return {
      by: (size: number) => new AtLeastOneWindowIterable(
        values,
        size,
      ),
    };
  }
}

Nice! Time for a short break ☕️

Safely querying prisma

Having a window iterable is key to our solution, but is not really what we're looking for here. We still need to cable it with Prisma.

Let's imagine a simple query:



const companyId = 1001;
const ids: string[] = [];

const clients = await prisma.client.findMany({
  where: { id: { in: ids }, companyId }
});

In our case, ids could have more than the limit of parameters in Prisma. So we can convert our query, with our iterable.



const clients = await prisma.$transaction(
  AtLeastOneWindowIterable.of(ids).by(32_000)
    .map((_ids) => prisma.client.findMany({
      where: { id: { in: ids }, companyId },
    }))
).then(_clients => _clients.flat());

As we said earlier, we can simply use the map function to map over each ids, and in case of an empty array, we simply use undefined in the Prisma query. Notice how lean our types are. We don't have any. The inference does its job, and we optimized some use cases.

What if we could generalise the solution?

Let's start by creating a generic function that extrtacts this call:



function batchedQuery<T, QueryableResult>(
  values: readonly T[],
  queryable: (_p: PrismaClient, vs?: T[]) => Prisma.PrismaPromise<QueryableResult>,
  p: PrismaClient,
  size = 32_000
) {
  return p.$transaction(
    AtLeastOneWindowIterable.of(values).by(size)
    .map((vs) => queryable(p, vs))
  );
}

The main element here is the queryable. We transform the query, into a "lazy" evaluation query, with a function. To prevent having closures, this method will receive the PrismaClient as well as the parameter window. Keeping our previous query example, this would give:



const clients = await batchedQuery(
  ids,
  (p, _ids) => p.client.findMany({
    where: { id: { in: _ids }, companyId },
  }),
  prisma
).then(_client => _clients.flat());

We hide the usage of AtLeastOneWindowIterable, but it seems less readble. Let's imagine a OOP version:



type Queryable<T, Result> = (p: PrismaClient, vs?: T[]) => Prisma.PrismaPromise<Result>;
class BatchPrismaClient {
  constructor(
    private readonly prisma: PrismaClient,
    private readonly size = 32_000,
  ) {}

  over<T>(values: readonly T[]) {
    return {
      with: <Result>(queryable: Queryable<T, Result>) =>
        this.prisma.$transaction(
          AtLeastOneWindowIterable.of(values).by(this.size)
            .map((vs) => queryable(this.prisma, vs))
        ),
    }
  }
}

It might seem convoluted, but using it will feel more natural:



const companyId = 1001;
const ids: string[] = [];
const batch = new BatchPrismaCient(new PrismaClient());

const clients = await batch.over(ids).with(
  (prisma, _ids) => prisma.client.findMany(
    { where: { id: { in: _ids }, companyId } },
  ),
).then((_clients) => _clients.flat());

And here we have it! We could even go further, but that's already satisfying, testable, and maintainable.

Feel free to ping me on twitter, if you already encountered this error, and what solution you found!

The full code



class AtLeastOneWindowIterable<T> implements Iterable<T[] | undefined> {
  protected constructor(
    private readonly values: readonly T[],
    private readonly size: number
  ) {}

  *[Symbol.iterator]() {
    const copy = [...this.values];
    if (copy.length === 0) yield undefined;
    while (copy.length) yield copy.splice(0, this.size);
  }

  map<U>(mapper: (items?: T[]) => U): U[] {
    return Array.from(this).map(items => mapper(items));
  }

  static of<U>(values: readonly U[]) {
    return {
      by: (size: number) => new AtLeastOneWindowIterable(
        values,
        size,
      ),
    };
  }
}

type Queryable<T, Result> = (p: PrismaClient, vs?: T[]) => Prisma.PrismaPromise<Result>;
class BatchPrismaClient {
  constructor(
    private readonly prisma: PrismaClient,
    private readonly size = 32_000,
  ) {}

  over<T>(values: readonly T[]) {
    return {
      with: <Result>(queryable: Queryable<T, Result>) =>
        this.prisma.$transaction(
          AtLeastOneWindowIterable.of(values).by(this.size)
            .map((vs) => queryable(this.prisma, vs))
        ),
    }
  }
}

Improving Prisma raw queries typing.

Jordan Quagliatini — Sat, 25 Feb 2023 11:09:34 +0000

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