Prevent Prisma from reaching bind parameters limit
Tags: prisma, typescript, oop, algorithms
Published: 2023-03-02 15:00:00

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:

{% embed 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](#the-full-code)**

----

## Example

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

```prisma
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`.

```ts
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`](https://lodash.com/docs/4.17.15#chunk):

```ts
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 `map`ping 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.

```ts
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](https://doc.rust-lang.org/std/slice/struct.Windows.html).

Fortunately, `Iterable` are built into JS:

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

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

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

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

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

```ts
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.

```ts
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_:

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

```ts
*[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](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Iteration_protocols) and [Iterators and generators](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/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.

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

  return out;
}
```

`Array`s 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:

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

If we wrap it up:

```ts
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](https://media.giphy.com/media/Od0QRnzwRBYmDU3eEO/giphy.gif)

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

```ts
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.

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

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

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

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

```ts
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

```ts
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))
        ),
    }
  }
}
```