Prevent Prisma from reaching bind parameters limit

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:

https://github.com/prisma/prisma/issues/8832

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 ☕️

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 }
});

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