DEV Community

Cover image for Prevent Prisma from reaching bind parameters limit
Jordan Quagliatini
Jordan Quagliatini

Posted on • Originally published at blog.jquagliatini.fr

Prevent Prisma from reaching bind parameters limit

#prisma #typescript #oop #algorithms

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 avatar
some-user123 posted on

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")
}
Enter fullscreen mode Exit fullscreen mode

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 }
});
Enter fullscreen mode Exit fullscreen mode

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();
}
Enter fullscreen mode Exit fullscreen mode

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();
}
Enter fullscreen mode Exit fullscreen mode

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> {
Enter fullscreen mode Exit fullscreen mode

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



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

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,
      ),
    };
  }
}
Enter fullscreen mode Exit fullscreen mode

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]]
Enter fullscreen mode Exit fullscreen mode

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]();
  }
}
Enter fullscreen mode Exit fullscreen mode

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]
}
Enter fullscreen mode Exit fullscreen mode

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);
}
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

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));
}
Enter fullscreen mode Exit fullscreen mode

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,
      ),
    };
  }
}
Enter fullscreen mode Exit fullscreen mode

Nice! Time for a short break ☕️

Great success

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 }
});
Enter fullscreen mode Exit fullscreen mode

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());
Enter fullscreen mode Exit fullscreen mode

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))
  );
}
Enter fullscreen mode Exit fullscreen mode

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());
Enter fullscreen mode Exit fullscreen mode

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))
        ),
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

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());
Enter fullscreen mode Exit fullscreen mode

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))
        ),
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

blamsa0mine profile image

🚀 Build a Star Rating System with TypeScript & CSS

A0mineTV -

nicooprat profile image

Using BroadcastChannel API with Vue to sync a ref across multiple tabs

Nico Prat -

jamir_hossain_8800f85fdd5 profile image

PHP OOP Part-2: Constructor and Destructor

Jamir Hossain -

joodi profile image

Goodbye Postman, Hello Insomnia: A Faster Way to Test APIs ⚡

Joodi -