Prisma prepared statement error when running simple query

Lang: Node DB: MySQL ORM: Prisma

I have a problem with running this query in prisma. My database contains currently abou 100 000 books. When run this command I get an error:

Error occurred during query execution: ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1390, message: "Prepared statement contains too many placeholders", state: "HY000" })), transient: false })

let books = await prisma.book.findMany({
    include: {
        authors: true,
    },
});

when i try to include something else like publications in the books it works. Just the authors and there is not many of them compared to publications. By the way there is only one property fullname in the authors.

Any suggestion are appreciated!

Solution

MySQL has a hard limit of 2¹⁶-1 parameters in a given query.

Reference: https://github.com/mysql/mysql-server/blob/trunk/sql/sql_prepare.cc#L1502-L1506

if (stmt->m_param_count > static_cast<uint>(UINT_MAX16)) {
  /* Error code to be defined in 5.0 */
  my_error(ER_PS_MANY_PARAM, MYF(0));
  return true;
}

ER_PS_MANY_PARAM is the error message you got.

But why would your supposedly simple Prisma query exceed this limit?

I am not a Prisma user, but I have a guess at what it's doing. It seems when you use include it generates two queries. First it gathers all the rows from your book table, each of which have an author id reference. Then it generates a second query like SELECT * FROM authors WHERE id IN (?, ?, ?, ?, ...) and passes the author id's from all the rows of your 100000 books. Of course there are a lot of duplicates, but Prisma doesn't bother to reduce the list.

I did some searches and found confirmation that Prisma does not support true joins. It gathers related rows using additional queries.

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

Currently, there is no way to join tables together. Queries that include relations only include the relational data by using separate queries.

...

It simply makes Prisma unusable for heavy usage, especially with a lot of joins.

(Issue is not resolved as of November 2023)

I think for your case, for join queries is to forget about using include if the number of rows in the outer query may be large.

The best workaround is to use raw SQL queries directly, and write your query to use JOIN syntax. See https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access for documentation.

Update: In Feb 20, 2024, Prisma announced a preview feature that implements real joins. Sort of — for MySQL, it is implemented as correlated subqueries.

https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-the-best-join-strategy-preview

This probably illustrates why object-relational mapping is so difficult: the result of a query may not correspond 1-to-1 with a single base table, so the mapping may be non-obvious for an ORM tool to do.