---
title: "Read a SQLite database over HTTP in C#"
url: https://recursiveai.org/onelite/cookbook/read-sqlite-database-over-http-csharp/
description: "Read a SQLite database over HTTP in C# with OneLite’s HttpRangeVfs: query a .db file on a web server with range requests instead of downloading it. Real output: two lookups in a 2.2 MB file fetch two 64 KiB blocks, an IN query two more, and a file that changes on the server is an I/O error."
version: "0.3.0-preview.2"
---

# Read a SQLite database over HTTP in C#

Open the file’s URL with `Database.OpenReadOnlyAsync` and OneLite’s `HttpRangeVfs`. OneLite then reads the database with HTTP range requests, in 64 KiB blocks, and fetches only the blocks a query needs. `HttpRangeVfs` is read-only and new in 0.3.0-preview.1; the happy-path methods (`QueryAsync`, `ScalarAsync`, `Table<T>`) work on it from 0.3.0-preview.2.

```shell
dotnet new console -o HttpQuery && cd HttpQuery
dotnet add package RecursiveAI.OneLite --version 0.3.0-preview.2
```

Put this in `Program.cs`:

```csharp
using RecursiveAI.OneLite;
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());
long requests = 0, bytes = 0;   // counts already reported
await using var db = await Database.OpenReadOnlyAsync(
    "http://localhost:5080/books.db", new DatabaseOptions { Vfs = http });
Fetched("open");

var books = db.Table<Book>();
foreach (long id in new[] { 7L, 90000L })
    Print(await books.GetAsync(id));
Fetched("two lookups");

foreach (Book b in await db.QueryAsync<Book>(
             $"select * from books where id in (30000, 60000)"))
    Print(b);
Fetched("in (...)");

Console.WriteLine("Now change books.db on the server, then press Enter.");
Console.ReadLine();
try
{
    Print(await books.GetAsync(50000L));
}
catch (OneLiteException e)
{
    Console.WriteLine(e.Message);
    Console.WriteLine($"inner: {e.GetBaseException().GetType().Name}");
}

void Print(Book? b) => Console.WriteLine($"{b!.Id} {b.Title} ({b.Year})");

void Fetched(string step)
{
    Console.WriteLine($"{step}: {http.RequestCount - requests} requests, " +
                      $"{http.BytesFetched - bytes:N0} bytes");
    (requests, bytes) = (http.RequestCount, http.BytesFetched);
}

[OneLiteRow]
public class Book
{
    public long Id { get; set; }
    public string Title { get; set; } = "";
    public int Year { get; set; }
}
```

## 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:

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

```shell
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"
```

```text
2203648
```

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

```shell
cd DbServer
```

```csharp
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:

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

## Run it

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

```shell
cd HttpQuery
dotnet run
```

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

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

```text
open: 3 requests, 65,536 bytes
7 Book 7 (1907)
90000 Book 90000 (1900)
two lookups: 3 requests, 131,072 bytes
30000 Book 30000 (1900)
60000 Book 60000 (1900)
in (...): 2 requests, 131,072 bytes
Now change books.db on the server, then press Enter.
disk I/O error
inner: RemoteFileChangedException
```

The whole run fetched 327,680 bytes of a 2,203,648-byte file. `HttpRangeVfs` downloads whole aligned blocks, 64 KiB by default, so every byte count is a number of blocks, not of the 4,096-byte pages the query read: opening fetched one block (65,536 bytes), the two primary-key lookups two (131,072 = 2 × 65,536), and `id in (30000, 60000)` two more, one seek for each value. `RequestCount` counts every request, “heads and ranges”; `BytesFetched` counts only the bytes downloaded. A bigger `BlockSize` in `HttpRangeOptions` “means fewer round trips on a scan and more bytes for a point lookup.”

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](https://recursiveai.org/onelite/docs/vfs/), “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 such as `ExecuteAsync` fails with `SQLITE_READONLY`, “attempt to write a readonly database”, and `Database.OpenAsync` on the URL fails with “unable to open database file”.
- **Journals and write-ahead logs are refused.** From [docs/vfs.md](https://recursiveai.org/onelite/docs/vfs/): “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. Lookups by key and `IN` on the key or the first column of an index seek: on a fresh open, `where id in (90000, 7, 7, null)` made 3 requests for 131,072 bytes and returned 7 and 90000. 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](https://recursiveai.org/onelite/docs/browser/).
- A preview: APIs may change between previews.

[VFS and HttpRangeVfs](https://recursiveai.org/onelite/docs/vfs/) · [The happy path](https://recursiveai.org/onelite/docs/happy-path/) · [All recipes](https://recursiveai.org/onelite/cookbook/)