Incremental BLOB I/O writes byte ranges of one stored binary value without passing the entire value to each write.
dotnet add package RecursiveAI.OneLiteRequires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.
using RecursiveAI.OneLite; using var db = Database.Open(":memory:"); using var connection = db.Connect(); using (var create = connection.Prepare( "CREATE TABLE files(data BLOB)")) create.Step(); using (var insert = connection.Prepare( "INSERT INTO files VALUES (zeroblob(8))")) insert.Step(); using var blob = connection.OpenBlob( "main", "files", "data", 1, writable: true); blob.Write(0, new byte[] { 1, 2, 3, 4 }); blob.Write(4, new byte[] { 5, 6, 7, 8 }); Console.WriteLine($"Bytes: {blob.Length}");
Bytes: 8Unzip the example, then dotnet run.
The full program
The input is generated by (offset + i) % 251, so it needs no file. The program writes and reads the value with bounded buffers, catches a bounds failure and compares hashes.
using RecursiveAI.OneLite; using System.Security.Cryptography; using var db = Database.Open(":memory:"); using var connection = db.Connect(); void Execute(string sql) { using var statement = connection.Prepare(sql); statement.Step(); } Execute("CREATE TABLE files(id INTEGER PRIMARY KEY, data BLOB)"); Execute("INSERT INTO files VALUES (1, zeroblob(10240))"); Execute("BEGIN"); using var expected = IncrementalHash.CreateHash(HashAlgorithmName.SHA256); using var actual = IncrementalHash.CreateHash(HashAlgorithmName.SHA256); using (var blob = connection.OpenBlob("main", "files", "data", 1, writable: true)) { byte[] chunk = new byte[1024]; for (int offset = 0; offset < blob.Length; offset += chunk.Length) { int count = Math.Min(chunk.Length, blob.Length - offset); for (int i = 0; i < count; i++) chunk[i] = (byte)((offset + i) % 251); blob.Write(offset, chunk.AsSpan(0, count)); expected.AppendData(chunk, 0, count); } for (int offset = 0; offset < blob.Length; offset += chunk.Length) { int count = Math.Min(chunk.Length, blob.Length - offset); blob.Read(offset, chunk.AsSpan(0, count)); actual.AppendData(chunk, 0, count); } Console.WriteLine($"Bytes: {blob.Length}"); try { blob.Write(blob.Length, new byte[] { 1 }); } catch (Exception error) { Console.WriteLine($"Bounds: {error.GetType().Name}: {error.Message}"); } } Execute("COMMIT"); string digest = Convert.ToHexString(actual.GetHashAndReset()); Console.WriteLine($"SHA-256: {digest}"); Console.WriteLine($"Matches input: {digest == Convert.ToHexString(expected.GetHashAndReset())}");
dotnet run prints:
Bytes: 10240 Bounds: OneLiteException: SQL logic error SHA-256: 957161DCE6C65864066E98F463FEEE573D8242998094E2F6F186DBF9DBAA968C Matches input: True
An out-of-range read or write throws OneLiteException with SQL logic error; the handle’s length never changes.
Variations
Exercise a short final chunk. Replace the buffer declaration in the full program; the byte length and digest remain unchanged.
byte[] chunk = new byte[333];Bytes: 10240 Bounds: OneLiteException: SQL logic error SHA-256: 957161DCE6C65864066E98F463FEEE573D8242998094E2F6F186DBF9DBAA968C Matches input: True
Related recipes
The short answer
Insert a zeroblob of the final byte length, then open the row’s BLOB with Connection.OpenBlob. Call Write(offset, span) for each bounded chunk and Read(offset, span) to retrieve ranges. The handle never grows the value. Close writable handles before committing an explicit transaction.
The download generates its bytes in code, writes them in chunks, reads them back and compares SHA-256 digests. It uses an in-memory database so rerunning it does not overwrite an existing file. There is no sample database to obtain or binary input hidden in the download.
What is the BLOB type in SQLite?
A BLOB stores bytes rather than character data. For incremental I/O, the offsets and lengths count the bytes of the stored value, not text characters. The SQLite incremental BLOB interface selects one column in one row by row ID. OneLite exposes that model as OpenBlob(schema, table, column, rowid, writable).
main is the schema name, not a database filename. files is the table, data is the column and 1 is the row ID in the example. A table declared WITHOUT ROWID cannot be opened this way. Neither can a view or virtual table. For writable handles, indexed columns and foreign-key columns are also excluded. Check the OneLite BLOB reference before applying the pattern to an existing schema.
How do I write a SQLite BLOB in C#?
Allocate the value first. The short snippet uses eight bytes so each write is visible. The full program allocates 10,240 bytes and reuses a 1,024-byte buffer. The final write takes the smaller of the buffer length and the remaining space. That same calculation works when the last chunk is short, as the 333-byte variation demonstrates.
The expected hash is updated while generating each chunk. The actual hash is updated while reading each chunk back. The program never needs a second array as large as the entire BLOB to compare the result. This is a bounded-buffer example, not a claim about the database engine’s total memory usage.
The handle reads and writes the pages holding the requested byte range, including overflow pages, through the pager. Writes participate in the database’s journal or WAL like other page changes. Incremental I/O is therefore a database operation, not a way to patch database-file bytes outside transaction control.
What is the SQLite BLOB size limit?
For an open OneLite handle, the immediate limit is its existing Length: a read or write outside the value fails with OneLiteException and SQL logic error. The sample deliberately writes one byte at Length to show that boundary. It does not test or claim a maximum database-wide BLOB size. To store a larger value, replace or allocate the value at the required size and open a new handle; additional chunk writes cannot extend it.
Lifetime matters as much as bounds. Updating any column of that row, replacing it or deleting it on the same connection expires the handle. Changing another row does not. An expired handle cannot be revived with Reopen. A rollback aborts open handles. Do not keep a handle across a row replacement and expect its offsets to address the new value.
The full program encloses all chunk operations in one explicit transaction and disposes the handle before COMMIT. Committing while a writable handle remains open fails with SQLITE_BUSY, according to the pinned documentation. A handle holds a read transaction until closed, and a writable one also holds the write transaction; in rollback modes that prevents other connections from writing. Keep its scope limited to the work that actually needs it.