What is the problem this feature will solve?
The public node:sqlite JavaScript API can only read or write a BLOB or TEXT value in full. Reading a 512 MiB value materializes 512 MiB in JavaScript, and there is no API for overwriting part of a value in place.
SQLite has an API for exactly this. sqlite3_blob_open() and the related calls give a handle that reads and writes one value in chunks. node:sqlite owns the sqlite3* connection and does not expose it to JavaScript, so an ordinary userland implementation cannot use the existing connection.
A custom loadable SQLite extension can receive that connection and call the incremental BLOB API, so this is not literally impossible outside core. It does, however, require shipping native code and designing an extension-specific bridge instead of using the public JavaScript API.
What is the feature you are proposing to solve the problem?
database.openBlob(options), returning a BlobHandle with byteLength, read(), write(), reopen(), close(), and [Symbol.dispose].
const { lastInsertRowid } = database
.prepare('INSERT INTO files (name, data) VALUES (?, zeroblob(?))')
.run('greeting.txt', 11);
using blob = database.openBlob({
table: 'files',
column: 'data',
row: lastInsertRowid,
});
blob.write(Buffer.from('hello'), { position: 0 });
blob.write(Buffer.from(' world'), { position: 5 });reopen() moves an existing handle to another row in the same column, which is how SQLite supports reusing a handle across many rows.
The handle cannot resize a value. Writable handles keep SQLite's transaction open until the last one is closed, and close or disposal can therefore report a deferred commit error. Incremental writes modify raw bytes rather than executing an SQL UPDATE: they do not run triggers or re-check constraints, and writing TEXT can create invalid UTF-8. SQLite also disallows virtual tables, tables with generated columns, WITHOUT ROWID tables, and writable handles on certain indexed or foreign-key columns.
The change is additive and wraps the already-bundled SQLite. It adds no new dependency and does not change existing statement behavior.
What alternatives have you considered?
Rewriting or reading the whole value. This is the public API available today. It is simplest for small values, but a partial update still replaces the whole value and reading any range materializes all of it.
A loadable SQLite extension. This can use sqlite3_blob_open() on node:sqlite's existing connection. It avoids a second connection, but requires custom native code and an extension-specific JavaScript/SQL bridge for a facility SQLite already exposes directly.
A second connection from userland. A native addon or another SQLite binding can open the same file and use incremental BLOB I/O while the application keeps using node:sqlite. This is workable, but transaction ownership, snapshots, busy handling, and locking are then split across connections.
Splitting large values across rows. With control of the schema, ordered chunk rows provide bounded-memory reads and logical partial replacement. They do not help with existing or externally produced schemas and are not in-place updates of one SQLite value.
A stream or async API in core. Streams can be composed over the primitive in userland. SQLite's handle and transaction are still synchronous and stateful, so an async API would require a broader worker and connection design rather than simply making these calls non-blocking.
I have a working implementation in draft at #65444, with API documentation, tests, and a one-machine large-value benchmark. I am opening the issue as the first-contributor guide suggests, to check whether there is appetite for the feature and whether the API shape is right before it goes further.
What is the problem this feature will solve?
The public
node:sqliteJavaScript API can only read or write a BLOB or TEXT value in full. Reading a 512 MiB value materializes 512 MiB in JavaScript, and there is no API for overwriting part of a value in place.SQLite has an API for exactly this.
sqlite3_blob_open()and the related calls give a handle that reads and writes one value in chunks.node:sqliteowns thesqlite3*connection and does not expose it to JavaScript, so an ordinary userland implementation cannot use the existing connection.A custom loadable SQLite extension can receive that connection and call the incremental BLOB API, so this is not literally impossible outside core. It does, however, require shipping native code and designing an extension-specific bridge instead of using the public JavaScript API.
What is the feature you are proposing to solve the problem?
database.openBlob(options), returning aBlobHandlewithbyteLength,read(),write(),reopen(),close(), and[Symbol.dispose].reopen()moves an existing handle to another row in the same column, which is how SQLite supports reusing a handle across many rows.The handle cannot resize a value. Writable handles keep SQLite's transaction open until the last one is closed, and close or disposal can therefore report a deferred commit error. Incremental writes modify raw bytes rather than executing an SQL
UPDATE: they do not run triggers or re-check constraints, and writing TEXT can create invalid UTF-8. SQLite also disallows virtual tables, tables with generated columns,WITHOUT ROWIDtables, and writable handles on certain indexed or foreign-key columns.The change is additive and wraps the already-bundled SQLite. It adds no new dependency and does not change existing statement behavior.
What alternatives have you considered?
Rewriting or reading the whole value. This is the public API available today. It is simplest for small values, but a partial update still replaces the whole value and reading any range materializes all of it.
A loadable SQLite extension. This can use
sqlite3_blob_open()onnode:sqlite's existing connection. It avoids a second connection, but requires custom native code and an extension-specific JavaScript/SQL bridge for a facility SQLite already exposes directly.A second connection from userland. A native addon or another SQLite binding can open the same file and use incremental BLOB I/O while the application keeps using
node:sqlite. This is workable, but transaction ownership, snapshots, busy handling, and locking are then split across connections.Splitting large values across rows. With control of the schema, ordered chunk rows provide bounded-memory reads and logical partial replacement. They do not help with existing or externally produced schemas and are not in-place updates of one SQLite value.
A stream or async API in core. Streams can be composed over the primitive in userland. SQLite's handle and transaction are still synchronous and stateful, so an async API would require a broader worker and connection design rather than simply making these calls non-blocking.
I have a working implementation in draft at #65444, with API documentation, tests, and a one-machine large-value benchmark. I am opening the issue as the first-contributor guide suggests, to check whether there is appetite for the feature and whether the API shape is right before it goes further.