[D1] Raise SQLITE_LIMIT_SQL_LENGTH

[jamesthomp]

I'm trying to migrate a sqlite DB to D1, which contains string values up to around 500kb. I originally thought this would work in D1, because it appears D1 can store strings up to around 2MB given the SQLITE_LIMIT_LENGTH. However when trying to import the table by running npx wrangler d1 execute database --remote --file=backup.sql I'm running into:

✘ [ERROR] statement too long: SQLITE_TOOBIG

And even after getting the file down to a single insert of the form:

INSERT INTO data VALUES('<200kb string>')

I'm getting the error preventing me from loading this table into D1.

Could the SQLITE_LIMIT_SQL_LENGTH limit be raised to match SQLITE_LIMIT_LENGTH so that we can import values as large as those that can be stored? Or am I missing something here and there is a way I can get this row imported into D1?

cc: @Frederik-Baetens who recently increased a related limit in #2682

[a-robinson]

I assume that you're inlining the value directly in your statement rather than passing it as a parameter? If so you're hitting the maximum statement length (which is not the same as the maximum value length): https://www.sqlite.org/limits.html#max_sql_length

Our implementation limits a given SQL statement to 100,000 bytes, as recommended by sqlite's security guidelines:

workerd/src/workerd/util/sqlite.c++

Line 1064 in 7e1333e

sqlite3_limit(db, SQLITE_LIMIT_SQL_LENGTH, 100000);

I'd recommend you try passing the value as a parameter in a prepared statement rather than inlined in the statement text.

[jamesthomp]

Thanks - I’m pretty sure what you say would work but unless I’m missing something obvious it would be a bit of nontrivial work because I’ve essentially got a .sql dump from my existing DB and I’m trying to execute it with the wrangler CLI. I can’t see how I’d use prepared statements easily in this case.

[a-robinson]

You're right, that does make importing dumps from other sqlite DBs tricky. I've made the team aware of the limitation, but unfortunately this isn't something the team is working on at the moment, so the only avenue available for now is to find a workaround.