Blob size limit at ~512MB despite MAX_LENGTH=2147483647

[kkshinkai]

Issue

Better-sqlite3 cannot insert blobs larger than ~512MB, even though MAX_LENGTH is compiled as 2147483647 (2GB).

Expected behavior

Should be able to insert blobs up to the configured limit (default 1GB or 2GB).

Actual behavior

• Maximum successful blob size: 536,870,881 bytes (~512 MB)
• Minimum failing blob size: 536,870,882 bytes
• Error: string or blob too big

Test code

const Database = require('better-sqlite3');
const db = new Database('test.db');
db.pragma('journal_mode = WAL');
db.exec('CREATE TABLE test (id INTEGER PRIMARY KEY, data BLOB)');

const stmt = db.prepare('INSERT INTO test (id, data) VALUES (?, ?)');

// This works:
const buf1 = Buffer.alloc(536870881, 0);
stmt.run(1, buf1);

// This fails:
const buf2 = Buffer.alloc(536870882, 0);
stmt.run(2, buf2); // Error: string or blob too big

Environment

• better-sqlite3 version: 11.10.0
• SQLite version: 3.49.2
• MAX_LENGTH (compile option): 2147483647
• Node.js version: v22.20.0
• OS: macOS

[neoxpert]

Well, the error message "string or blob too big" is thrown by SQlite, not better-sqlite3. Where did you get the "MAX_LENGTH" option from? The actual compiler option is prefixed with SQLITE_ (see limits).

Edit: Haven't dug too deep into it yet, but for me this sounds like there could be some encoding issue, like the buffer is using UTF-8 internally, what might cause the actual amount of used bytes change going from being wrapped inside the buffer to a plain char* pointer when handing over to sqlite.

[neoxpert]

Looks like there is another (runtime) limit in place with SQLITE_LIMIT_LENGTH.

With a very pragmatic check in the Database constructor

int max = sqlite3_limit(db_handle, SQLITE_LIMIT_LENGTH, -1);
printf("Max SQLite length: %d\n", max);

The output yields Max SQLite length: 536870888. Changing the limit with sqlite3_limit(db_handle, SQLITE_LIMIT_LENGTH, 1024 * 1024 * 1024); caused your test case to complete successfully.

Afaik right now there is no way of changing the SQLITE_LIMIT_LENGTH from the JavaScript side at the moment. So for now you would have to handle this with a custom build.

[houd1ni]

Kinda related architecturally #241