SQLite

The Shiny.DocumentDb.Sqlite package provides the default SQLite-backed document store. It is the recommended provider for mobile, desktop, embedded, and single-process scenarios — full LINQ-to-SQL translation, JSON indexes, spatial queries via R*Tree, hot backup, and a ClearAllAsync shortcut.

When to Use

• Mobile (.NET MAUI iOS/Android) and desktop apps
• Single-process embedded storage
• Local-first / offline-capable apps
• Blazor WebAssembly when you need full SQL features (paired with SQLitePCLRaw.bundle_wasm)

If you need encryption at rest, use SQLCipher instead. For browser persistence without a native binary, see IndexedDB.

Installation

dotnet add package Shiny.DocumentDb.Sqlite

For vector / similarity search (sqlite-vec) on iOS, Android, and desktop, also add the companion package — it ships the native binaries and wires them up with one call. See Vector search › SQLite.

dotnet add package Shiny.DocumentDb.Sqlite.VectorSupport

SQLitePCLRaw version and the security advisory

Shiny.DocumentDb.Sqlite uses the SQLitePCLRaw 2.1.x native bundle that Microsoft.Data.Sqlite brings, and NuGet flags it on restore:

warning NU1903: Package 'SQLitePCLRaw.lib.e_sqlite3' 2.1.11 has a known high severity vulnerability, https://github.com/advisories/GHSA-2m69-gcr7-jv3q

The advisory is CVE-2025-6965: in SQLite before 3.50.2, a query with more aggregate terms than there are columns available can corrupt memory. Triggering it means running an attacker-crafted SQL statement against the database. Shiny.DocumentDb generates its SQL from your typed and string queries and binds values as parameters. So for a typical mobile app, with a local database whose SQL the user can’t reach, this is not a critical problem. It deserves more attention on a server that passes untrusted input into raw SQL (Query(whereClause, …)).

The library stays on 2.1.x on purpose. SQLitePCLRaw 3.x ships no SQLCipher bundle, and an app gets exactly one SQLitePCLRaw core, so moving to 3.x breaks SQLCipher.

An app that does not use SQLCipher can opt into the patched 3.x build itself:

<PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="3.0.5" />

Caution

Doing this breaks Shiny.DocumentDb.Sqlite.SqlCipher for now. Encrypted opens fail with “the native SQLite library ‘e_sqlite3’ doesn’t support encryption”. Don’t opt in if your app uses SQLCipher.

Setup

1. Direct instantiation

   using Shiny.DocumentDb.Sqlite;
   
   // Quick setup
   var store = new SqliteDocumentStore("Data Source=mydata.db");
   
   // Full options
   var store = new SqliteDocumentStore(new DocumentStoreOptions
   {
       DatabaseProvider = new SqliteDatabaseProvider("Data Source=mydata.db")
   });

2. Dependency injection

   using Shiny.DocumentDb;
   using Shiny.DocumentDb.Sqlite;
   
   services.AddDocumentStore(opts =>
   {
       opts.DatabaseProvider = new SqliteDatabaseProvider("Data Source=mydata.db");
   });

Storage Layout

CREATE TABLE IF NOT EXISTS "documents" (
    Id        TEXT NOT NULL,
    TypeName  TEXT NOT NULL,
    Data      TEXT NOT NULL,
    CreatedAt TEXT NOT NULL,
    UpdatedAt TEXT NOT NULL,
    PRIMARY KEY (Id, TypeName)
);

The Data column stores raw JSON text. Property access translates to json_extract(Data, '$.path'). Deep Upsert runs server-side via SQLite’s json_patch() (RFC 7396).

Backup

SqliteDocumentStore.Backup(path) uses the SQLite Online Backup API — the store stays usable during the copy.

var store = new SqliteDocumentStore("Data Source=mydata.db");
await store.Backup("/path/to/backup.db");

Backup is not on the IDocumentStore interface — it lives on the concrete SqliteDocumentStore type. Marked [UnsupportedOSPlatform("browser")] so it produces a compiler warning when called from browser-targeted code.

Clear All

SqliteDocumentStore.ClearAllAsync() deletes every document across every table, including spatial sidecar tables. Useful for tearing down test fixtures or signing a user out of a local-first app.

var store = new SqliteDocumentStore("Data Source=mydata.db");
await store.ClearAllAsync();

Spatial Queries

SQLite uses R*Tree virtual tables for WithinRadius, WithinBoundingBox, and NearestNeighbors. Sidecar tables are created and synced automatically on insert/update/upsert/remove/clear. Register the GeoPoint property at setup:

var options = new DocumentStoreOptions
{
    DatabaseProvider = new SqliteDatabaseProvider("Data Source=mydata.db")
};
options.ConfigureDocument<Restaurant>(cfg => cfg.MapSpatialProperty(r => r.Location));

var store = new SqliteDocumentStore(options);

See Spatial Queries for the full API.

Blazor WebAssembly

The SQLite provider is WASM-compatible when paired with SQLitePCLRaw.bundle_wasm:

• WAL pragma skipped on OperatingSystem.IsBrowser()
• Spatial disabled (R*Tree unavailable in WASM-compiled SQLite)
• Backup unsupported in the browser
• Use Data Source=:memory: or Emscripten OPFS-mounted paths

For most WASM scenarios, the lighter IndexedDB provider is recommended.

Indexes

await store.CreateIndexAsync<User>(u => u.Name);
// CREATE INDEX IF NOT EXISTS idx_json_User_name
//   ON "documents" (json_extract(Data, '$.name'))
//   WHERE TypeName = 'User';

Indexes are partial by type so multiple types sharing the same table do not pay for each other’s indexes. See Indexes & Transactions.

Notes

• Reader-many / writer-one concurrency model.
• Identifiers are quoted with " — types named Order, Group, User work without collision.
• Upsert is RFC 7396 deep merge via json_patch.
• Raw SQL queries use json_extract(Data, '$.path').