🐛 BUG: statement too long when importing to D1

Which Cloudflare product(s) does this pertain to?

What version(s) of the tool(s) are you using?

wrangler 3.17.1

What version of Node are you using?

19.9.0

What operating system are you using?

Linux (Ubuntu)

Describe the Bug

I'm trying to migrate a D1 DB using the legacy backend to the new backend but I get an error from the Cloudflare API: statement too long. The DB has a single table with only ~150 rows but some columns contain a lot of text, so I imagine that one or more rows must hit the API's statement length limit.

Is this expected? Is there another way to import larger rows into D1?

Please provide a link to a minimal reproduction

Not applicable

Please provide any relevant error logs

npx wrangler d1 execute <db> --file <dump>.sql                
🌀 Mapping SQL input into an array of statements
🌀 Parsing 148 statements
🌀 Executing on remote database <db> (uuid):
🌀 To execute on your local development database, pass the --local flag to 'wrangler d1 execute'

✘ [ERROR] A request to the Cloudflare API (/accounts/<id>/d1/database/<uuid>/query) failed.

  statement too long [code: 7500]

Found my own workaround.

Since adding this data worked via Workers in the first place (there doesn't seem to be any statement length limit there), I built one for batch importing the data (8 MB). Here it is, in case anyone runs into the same issue:

View source

```js // Note: I'm using Cloudflare Pages so this is a Pages Function, deployed as a Worker. Might need to be adapted to your use case export async function onRequest(context) { // 1. get json data. I got this from my sqlite3 dump: https://stackoverflow.com/a/67186486 const response = await fetch("/dump.json"); const json = await response.json(); // 2. prep statement const stmt = context.env..prepare( "INSERT INTO (id, created_at, title, ) VALUES (?1, ?2, ?3, )" ); // 3. prep batch statement const stmts = json.map((a) => stmt.bind( a.id, a.created_at, a.title, // ) ); // 4. exec batch statement const result = await context.env..batch(stmts); // 5. log all returned data with a dummy status code return new Response(JSON.stringify(result), { status: 201 }); } ```

All rows were successfully written, so my issue is solved. I'll keep it open for a bit anyways, in case the D1 team wants to look at why the regular import mechanism fails with long statements.

mnik01 commented 6 months ago

Thank you! It's helped to me with same issue

Found my own workaround.

Since adding this data worked via Workers in the first place (there doesn't seem to be any statement length limit there), I built one for batch importing the data (8 MB). Here it is, in case anyone runs into the same issue:

View source All rows were successfully written, so my issue is solved. I'll keep it open for a bit anyways, in case the D1 team wants to look at why the regular import mechanism fails with long statements.

cloudflare / workers-sdk