Read a SQLite database over HTTP in C#

Register OneLite’s HttpRangeVfs with the ADO.NET provider and use the file’s URL as the data source. OneLite then reads the database with HTTP range requests, in 64 KiB blocks, and fetches only the blocks a query needs. It is read-only and new in 0.3.0-preview.1.

dotnet new console -o HttpQuery && cd HttpQuery
dotnet add package RecursiveAI.OneLite.Data --version 0.3.0-preview.1

Put this in Program.cs:

using System.Data.Common;
using RecursiveAI.OneLite.Data;
using RecursiveAI.OneLite.Storage.Vfs;
 
// A read-only backend that fetches the file in 64 KiB blocks with HTTP range requests.
var http = new HttpRangeVfs(new HttpClient());
OneLiteConnection.RegisterVfs("http", http);
 
await using var conn = new OneLiteConnection(
    "Data Source=http://localhost:5080/books.db;Vfs=http;Mode=ReadOnly");
await conn.OpenAsync();
 
await PrintBook(7);
await PrintBook(90000);
Console.WriteLine($"{http.RequestCount} requests, {http.BytesFetched:N0} bytes fetched");
 
Console.WriteLine("Now change books.db on the server, then press Enter.");
Console.ReadLine();
try
{
    await PrintBook(50000);
}
catch (DbException e)
{
    Console.WriteLine(e.Message);
    Console.WriteLine($"inner: {e.GetBaseException().GetType().Name}");
}
 
async Task PrintBook(long id)
{
    await using DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "select id, title, year from books where id = @id";
    cmd.Parameters.Add(new OneLiteParameter("@id", id));
    await using DbDataReader r = await cmd.ExecuteReaderAsync();
    while (await r.ReadAsync())
        Console.WriteLine($"{r.GetInt64(0)} {r.GetString(1)} ({r.GetInt64(2)})");
}

Serve a database to try it

Any server that answers Range requests with 206 and sends an ETag or Last-Modified header works: nginx, a CDN, object storage. To try it locally, make a 100,000-row database with the sqlite3 shell (3.53.4, from sqlite.org) and serve it from a minimal ASP.NET Core app, whose static files support all three:

cd .. && dotnet new web -o DbServer && mkdir DbServer/wwwroot

sqlite3 DbServer/wwwroot/books.db "create table books
    (id integer primary key, title text, year int);
  with recursive n(i) as (select 1 union all select i + 1 from n where i < 100000)
  insert into books select i, 'Book ' || i, 1900 + i % 125 from n;" \
  "select page_count * page_size from pragma_page_count, pragma_page_size"

2203648

Go into DbServer and replace its Program.cs with this:

cd DbServer

var app = WebApplication.Create(args);
// Static files answer Range requests and send ETag and Last-Modified.
app.UseStaticFiles(new StaticFileOptions { ServeUnknownFileTypes = true });
app.Run();

Start it and leave it running:

dotnet run --urls http://localhost:5080

Run it

In a second terminal, from the folder that holds both projects:

cd HttpQuery
dotnet run

When the program asks, change the file on the server from a third terminal, then press Enter:

sqlite3 DbServer/wwwroot/books.db "update books set title = 'Changed' where id = 1"

7 Book 7 (1907)
90000 Book 90000 (1900)
6 requests, 196,608 bytes fetched
Now change books.db on the server, then press Enter.
SQLite Error 10: 'disk I/O error'.
inner: RemoteFileChangedException

Two primary-key lookups in a 2,203,648-byte file fetched three 64 KiB blocks. Opening the file reads the first block and records its size, ETag and Last-Modified; every later request carries If-Match or If-Unmodified-Since. After the update the server has another version, so the next read that needs a new block fails with SQLite’s I/O error (SQLITE_IOERR_READ), and the RemoteFileChangedException inside it says so. In the words of docs/vfs.md, “Mixing pages of two versions would read a database that never existed”: open the file again to read the new version.

Limits

• Read-only. A write fails with “attempt to write a readonly database”, and opening read-write fails.
• Journals and write-ahead logs are refused. From docs/vfs.md: “Opening the main file for the engine HEADs -journal (and -wal when the header says WAL): a non-empty one makes the open fail with CANTOPEN, because a hot journal cannot be played back and a log may hold commits the file lacks.” Run against a file with a -journal beside it and a WAL file with a live -wal, both opens failed with “unable to open database file”. A WAL-mode file with no -wal reads normally.
• The server must cooperate. It must answer Range with 206; “a server that sends neither header [ETag nor Last-Modified] is refused at open (CANTOPEN) unless AllowNoValidator is set.”
• A change goes unnoticed while the cache covers it. “A reader whose pager cache already holds every page it needs makes no request, so it keeps a consistent view of the version it opened and does not notice a change.”
• Queries that scan fetch the whole file. select count(*) from books read all 2,203,648 bytes. In this preview so did where id in (7, 90000), where the two id = @id lookups above read 196,608 bytes. Check BytesFetched for the queries you run.
• In the browser it runs through HttpClient (fetch), async only, and a cross-origin server needs CORS for Range and If-Match: see In the browser.
• A preview: APIs may change between previews.

VFS and HttpRangeVfs · ADO.NET provider docs · All recipes