D1_ERROR: too many SQL variables at offset 340: SQLITE_ERROR
Prisma 6.10.1 / D1 adapter
This error occurs when calling findMany with 100 rows, each including nested 100 rows.
If I reduce the number of rows on both (e.g. take:10) it works.
Is this an error with the chunking or a limitation with D1 adapter with no plan of being supported?
10 Replies
You selected the bot's instantaneous insights. Let me compile your answer right away!
The error D1_ERROR: too many SQL variables at offset ... when using Prisma with the D1 adapter is a known limitation related to the maximum number of SQL variables allowed in a single query by Cloudflare D1 (currently 100 variables per query) D1 Limits. When you use findMany with nested includes, the number of variables can quickly exceed this limit, especially with 100 parent rows each including 100 nested rows.
Prisma has implemented "chunking" to split queries and stay within these limits. As of Prisma 5.15.0, chunking for D1 was improved and many cases are now handled correctly. However, there are still edge cases—especially with nested queries or subqueries—where chunking does not fully prevent exceeding the variable limit. This is a known issue and has been reported by multiple users. For example, including subqueries or complex where clauses can still push the query over the limit, resulting in the same error you are seeing. The Prisma team is aware and working on further improvements, but as of now, this is a limitation you may encounter Bug: [D1] Error in performIO: Error: D1_ERROR: too many SQL variables, Bug: [D1] Error in performIO: Error: D1_ERROR: too many SQL variables (subqueries).
Workarounds include reducing the number of rows fetched at once (as you did with take: 10), or paginating your queries. There is no indication in the knowledge sources that this is fully resolved in Prisma 6.10.1, and it remains a limitation of the D1 adapter and the D1 platform.
In summary: This is a known limitation with D1 and the Prisma D1 adapter. Chunking helps, but does not cover all cases, especially with nested includes. Reducing query size or paginating is currently required.
🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly.
In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.
Hey!
Can you try the smart placement setting as described here and see if it fixes the issue for you?
https://github.com/prisma/prisma/discussions/23646#discussioncomment-9083299
GitHub
Preview feature feedback:
@prisma/adapter-d1 / Cloudflare D1 supp...Support for the Cloudflare D1 is in preview behind the driverAdapters preview feature with the release of 5.12.0 on April 2nd. Please share your feedback about @prisma/adapter-d1 in this discussion...
Didn't make a difference, Smart Placement is more about latency. My issue is that the query isn't even executing due to the error above.
It works with the Rust query engine, but I assume that if D1 support is being considered for Prisma 7, this would need to be fixed?
@Francisco Hermida, I ran into this recently. The issue for me was with Prisma still overusing the IN keyword instead of doing JOIN’s. (It’s trying to create hundreds of parameters rather than doing the join.) There’s supposed to be an experimental flag to stop it from doing this, but it didn’t work for me.
I had to use a raw query to force it where this is happening.
https://www.prisma.io/docs/orm/prisma-client/queries/relation-queries#relation-load-strategies-preview
You can log queries from prisma client:
This will show you the parameters it’s creating. Your case sounds very close to mine.
Thank you @rando
@rando did you end up creating an issue for this?
GitHub
GitHub - franciscohermida/repro-prisma-issues
Contribute to franciscohermida/repro-prisma-issues development by creating an account on GitHub.
What is the repro for? I’m using Prisma with D1 fairly comfortably. For your listed issues:
1 - Do I need to keep url = "file:./db.sqlite" when using D1?
You shouldn’t. But I remember finding a contradiction in Prisma. If you’re using the config/generator for Cloudflare, the datasource URL shouldn’t be required. But the CLI will still throw an error if it’s not there. I simply set it in my .env with the locally created .sqlite file. Actual migrations need to be handled by wrangler.
I don’t really understand your setup your GitHub but if you’re using the wrangler CLI to manage things, this will end up in .wrangler/state/v3/d1/miniflare-D1DatabaseObject/....sqlite
DATABASE_URL="file:../.wrangler/state/v3/d1/miniflare-D1DatabaseObject/....sqlite"
2 - When creating a migration there is no --schema for migrate diff
You need a migration to diff migrations. If you’re starting with Prisma use wrangler d1 migrate create to create an empty migration and wrangler d1 migrate apply to run it.
Right now it’s best to simply use Prisma’s CLI to copy into wrangler migrations. Use wrangler to create new files and apply them.
3 - Generate Prisma Client using prisma-client (the new ts client)
This seems very specific? To a version or config? This is what you need for D1. You MUST use BOTH prisma-client and moduleFormat = "esm". Using only one doesn't seem to work. You also need to previewFeatures to get WASM instead of Rust and D1 compatibility.
4 - Warning with sourcemap:
Never seen it. But another note, you shouldn’t need the comonjs plugin you’re using with Vite. Prisma has esm.
Your GitHub has no schema.prisma so I don’t know if you’re also creating a contradicting config by trying to generate WASM & ESM but using a vite plugin to transform prisma’s client to ESM? I suspect this plugin probably doesn’t work for every library anyway.
If you’re using the new queryCompiler, the runtime imported from "@prisma/client/runtime/wasm-compiler-edge" is already ESM anyway. So I would start by removing that plugin altogether.
This is kinda why I hate "minimal repros" tbh. They never say anything about what you’re actually seeing and doing. Are you using Wrangler and Prisma migrations together as you need to? There's no way to say if you’re creating an empty migration first. There's nothing to say about intent.
It’s better to simply speak from experience. Real world problems go beyond "does it work? what do I type to make it work?"
There's an entire workflow here that can’t be shown in a simple GitHub like this.
If a "minimal repro" were good enough to ask a question, an AI can answer it.Sorry, this repro was not connected with what I asked you it was for @Nurul . I just wanted to know if you ended up creating an issue so I could upvote it.
The readme was an old response for an previous request from someone from prisma that wanted to understand my confusion as I was navigating all these new changes from prisma.
1 - I should according to this video, it causes no problem and stops errors: https://www.youtube.com/watch?v=HKM4uTXS_Uw
2 - I explained what I was doing wrong at the end of the section 2
3 - At the time I think it was throwing errors, but the current version has fixed it all. It was being used with cloudflare/vite-plugin
4 - Again an issue with cloudflare/vite-plugin when using the prisma-client-js, that was the only way I found that could fix it, but now it is not needed anymore because the prisma-client is working without the errors I mentioned
My repo has a schema.prisma in server/db1/schema/schema.prisma
Prisma
YouTube
NEW Prisma 6.6 - ESM Support, Migrations for D1, and More!
Prisma 6.6 is a huge release and is full of some great new features and long awaited improvements. This release brings support for ESM, the ability to use migratons with Cloudflare D1 and Turso, and some great new AI capabilities to improve your workflow.
📣 Release notes 6.6: https://github.com/prisma/prisma/releases/tag/6.6.0
Prisma + Cloud...
I don't think either of the two threads I created here actually required a repro to demonstrate the issues, they seem fairly universal and should probably be caught by existing tests. But a repro was requested anyway (in the other thread).
The repro clearly demonstrates both issues I'm specifically referring to in this thread and the other.
That’s an aside, I get a little annoyed when I see "help" become boilerplate. Or links to irrelevant GitHub issues that are obviously just the result of a keyword search. "Do you have a repro?" feels like buying time 99% of the time.
I agree all of these issues should be covered by tests for all supported db’s and adapters. The config combos especially.
That’s why I’m just saying what’s worked for me. I had to discover the D1 workflow with trial and error.
Prisma covers migrations well, but there's not a lot of help with how to generate a client that works on Workers:
https://www.prisma.io/docs/orm/overview/databases/cloudflare-d1
Cloudflare’s docs don’t work either:
https://developers.cloudflare.com/d1/tutorials/d1-and-prisma-orm/
Eventually I just had to read through the entire config page and think about what I needed:
https://www.prisma.io/docs/orm/prisma-schema/overview/generators
I guess I would hope a human is very familiar with these docs and working with Prisma in a way that can speak to them. Unfortunately, there are so many bad answers around this topic, even the AI’s really only have slop to work with.