Zensurf.Data.SqliteSys 1.2.3

Zensurf.Data.SqliteSys

Zensurf's fully custom, hardened, high-performance SQLite ADO.NET provider for .NET 10.

Zensurf.Data.SqliteSys.dll is a from-scratch replacement for Microsoft.Data.Sqlite, built for the Zensurf app: faster on real-world query patterns, more reliable under concurrency, and secure by default — including shipping a current, CVE-patched native SQLite instead of whatever an old bundle pinned.

It is drop-in shaped: the types are named SqliteConnection, SqliteCommand, SqliteDataReader, SqliteParameter, SqliteTransaction, SqliteConnectionStringBuilder … so migrating is usually just swapping the using Microsoft.Data.Sqlite; for using Zensurf.Data.SqliteSys;.

using Zensurf.Data.SqliteSys;

using var connection = new SqliteConnection("Data Source=zensurf.db;Journal Mode=WAL");
connection.Open();

using var command = connection.CreateCommand();
command.CommandText = "SELECT title FROM history WHERE url = @url";
command.Parameters.AddWithValue("@url", "https://example.com");
Console.WriteLine(command.ExecuteScalar());

// Typed scalar + fluent parameters (no casts, no boxing round-trips in user code):
using var count = connection.CreateCommand();
count.CommandText = "SELECT visits FROM history WHERE url = @url";
long visits = count.WithParameter("@url", "https://example.com").ExecuteScalar<long>();

command.SetParameter("@url", "https://other.example"); // updates in place or adds
string? title = await command.ExecuteScalarAsync<string>(cancellationToken);

ExecuteScalar<T> converts NULL/DBNull to default, and understands int, bool, enums, Guid, DateTime/DateTimeOffset/DateOnly/TimeOnly/TimeSpan (same storage rules as the reader) and string (UTF-8 for blobs). It is NativeAOT/trim safe.

Why it's faster

Measured against Microsoft.Data.Sqlite 10.0.9 on the same machine, same native SQLite 3.53.3, .NET 10, file databases in WAL mode (bench/ project, best of 5 runs):

¹ Both are sub-microsecond per open; ours does strictly more per cycle (hardening, foreign-key enforcement, rollback safety check). Reproduce with dotnet run -c Release --project bench/Zensurf.Data.SqliteSys.Benchmarks.

How:

• Direct source-generated interop. Every native call is a .NET 10 [LibraryImport] stub straight into sqlite3 — no SQLitePCLRaw provider/delegate indirection layer. NativeAOT-friendly, no runtime marshalling stubs.
• Per-connection prepared-statement cache (LRU, default 64 entries, Statement Cache Size keyword). Microsoft.Data.Sqlite only reuses a prepared statement while the same SqliteCommand instance is reused; the extremely common "new command per query" pattern re-prepares every time there. Here any command on the connection gets the cache hit — that's the 2× rows above.
• Connection pooling with warm handles: pooled physical connections keep their statement caches, so Open() after Close() resumes at full speed. Parsed connection strings are cached too.
• UTF-8 end-to-end. SQL text is encoded once and reused; column text/blob reads are a single managed allocation from SQLite's buffer; string binds use stackalloc/pooled buffers, producing no garbage.
• Allocation-free typed reads. GetFieldValue<T> uses exact-type fast paths (Unsafe.As) — no boxing for long, double, bool, DateTime, Guid, ….
• Cached binding plans. Parameter names are decoded once per prepared statement; a re-executed, unmodified parameter collection binds by remembered slot, and a fresh collection whose parameters are in statement order binds directly — zero name search and zero plan allocations in both hot paths (odd orders and renames fall back to tolerant name resolution).
• Readerless ExecuteNonQuery/ExecuteScalar. Direct execution paths with no reader, list, or registration allocations per call.
• No per-call mutex. Connections open with SQLITE_OPEN_NOMUTEX — safe under the ADO.NET one-thread-per-connection contract (Full Mutex=True restores serialized mode).
• [SuppressGCTransition] hot calls. In NOMUTEX mode, the short, non-blocking column reads, binds, changes() and last_insert_rowid() skip the GC transition entirely (step/prepare/reset never do).
• Fused reader getters. NULL detection only consults column_type when the value is 0 / a null pointer; the current row's statement and column count are cached.
• Lazy reset / clear. Statements are reset only if actually stepped, and bindings are cleared only when a large payload was bound; a single-entry "last SQL" lookup skips the LRU dictionary for repeated queries.

If point lookups are slow on your machine (Windows especially)

Per-query time in the DELETE journal default is often dominated by file-lock syscalls and antivirus hooks, not by SQLite or the provider. Two provider-level fixes:

1. Journal Mode=WAL — read transactions get dramatically cheaper (the benchmark above runs both providers in WAL for this reason). Use ForZensurf(...) to get this plus sane durability defaults.
2. Locking Mode=Exclusive — for a single-connection database (e.g. a per-profile store owned by one app), the file lock is taken once and never released, eliminating per-query lock overhead entirely. Do not combine with pooling to the same file from multiple connections.

Why it's more reliable

• BEGIN IMMEDIATE by default. Deferred transactions that upgrade to a write lock mid-transaction are SQLite's classic deadlock; taking the write lock up front makes concurrent writers queue on the busy timeout instead. (BeginTransaction(deferred: true) opts back in.)
• Foreign keys ON by default (Foreign Keys=False to opt out). SQLite and Microsoft.Data.Sqlite both leave referential integrity off.
• Busy-timeout is always armed (from Default Timeout/Busy Timeout), so lock contention waits instead of failing instantly.
• Close is safe: open readers and blobs are drained, pending transactions rolled back, and pooled handles are verified before reuse. Session-registered functions/collations never leak into the next pooled use.
• First-class WAL support: Journal Mode=WAL in the connection string, connection.WalCheckpoint(...) for explicit checkpoints, and PRAGMA optimize runs automatically when a physical connection retires (Auto Optimize=False to disable).
• Failed statements are evicted from the cache; poisoned connections are never re-pooled.

Why it's more secure (the CVE story)

Two layers:

1. A current native SQLite. The native library is where SQLite CVEs live, and the default Microsoft stack ships whatever its pinned SQLitePCLRaw bundle carries — e.g. Microsoft.Data.Sqlite 8.x pins SQLite 3.41.2 (2023) unless the app manually overrides it. That build predates fixes such as:

• CVE-2025-6965 — memory corruption via FTS integer overflow (fixed in SQLite 3.50.2)
• CVE-2025-29087 / CVE-2025-3277 — concat_ws() integer overflow → out-of-bounds access (fixed in 3.49.1+)
• CVE-2023-7104 — sessions-extension heap overread (fixed in 3.43.x)

Zensurf.Data.SqliteSys references SQLitePCLRaw.lib.e_sqlite3 3.53.3 (native binaries only — versioned with upstream SQLite), and its loader prefers that build, falling back to the system SQLite only when the bundle is absent. Check what you're running at runtime with SqliteSysRuntime.NativeVersion, or pin your own build via SqliteSysRuntime.SetNativeLibraryPath(...) / ZENSURF_SQLITE_NATIVE_PATH.

2. Runtime hardening that Microsoft.Data.Sqlite leaves off. Applied on every connection unless Hardened=False:

Plus the usual: parameters are always bound (never interpolated), savepoint/identifier names are quoted-escaped, and Password= fails loudly on non-SEE builds instead of silently ignoring encryption.

Feature highlights

Everything you expect from an ADO.NET provider, plus:

// User-defined SQL, straight from C#:
connection.CreateFunction<string, string>("domain_of", url => new Uri(url).Host, isDeterministic: true);
connection.CreateAggregate<long, long>("sum_squares", 0, (acc, x) => acc + x * x);
connection.CreateCollation("nocase_nl", (a, b) => string.Compare(a, b, StringComparison.OrdinalIgnoreCase));

// Modern batching:
using var batch = new SqliteBatch(connection);
batch.BatchCommands.Add(new SqliteBatchCommand("INSERT INTO tabs(url) VALUES (@u)") { Parameters = { new("@u", url) } });
batch.ExecuteNonQuery();

// Instant snapshot / restore (great for tests and profile migration):
byte[] snapshot = connection.Serialize();
otherConnection.Deserialize(snapshot);

NativeAOT and trimming

The library is fully NativeAOT compatible (IsAotCompatible=true, zero trim/AOT analyzer warnings):

• All native calls are source-generated [LibraryImport] stubs — no runtime marshalling, no DllImport IL stubs to generate at runtime.
• User-defined function callbacks are [UnmanagedCallersOnly] function pointers, not marshalled delegates.
• Typed argument conversion (CreateFunction<T1..>, GetFieldValue<T>) is plain type checks — no reflection.
• Version-dependent SQLite exports are resolved with NativeLibrary.TryGetExport.

Proof, not promises: samples/Zensurf.Data.SqliteSys.AotSmoke is published with PublishAot=true and executed in CI — a fully AOT-compiled binary running 24 checks across connections, pooling, typed reads, user functions/aggregates/collations, DbBatch, blob I/O, serialize/deserialize, GetSchema, and WAL checkpoints.

dotnet publish samples/Zensurf.Data.SqliteSys.AotSmoke -c Release -r linux-x64
./samples/Zensurf.Data.SqliteSys.AotSmoke/bin/Release/net10.0/linux-x64/publish/Zensurf.Data.SqliteSys.AotSmoke
# AOT SMOKE OK — 24 checks passed, native SQLite 3.53.3 (e_sqlite3)

Connection string reference

Microsoft-compatible keywords: Data Source (aliases DataSource, Filename), Mode (ReadWriteCreate | ReadWrite | ReadOnly | Memory), Cache (Default | Private | Shared), Password, Foreign Keys, Recursive Triggers, Default Timeout (alias Command Timeout), Pooling.

Zensurf extensions:

For the Zensurf app there's a tuned one-liner — SqliteConnectionStringBuilder.ForZensurf("path/to.db") → WAL, Synchronous=Normal, 16 MiB cache, memory temp store, 5 s busy timeout, foreign keys + hardening on.

Behavioral differences from Microsoft.Data.Sqlite

Deliberate, documented divergences:

1. Foreign keys default ON, transactions default IMMEDIATE, hardening default ON (see above; each has an opt-out).
2. AddWithValue(name, null) binds SQL NULL (instead of requiring DBNull.Value).
3. GetInt32/GetInt16/GetByte are checked casts — silent truncation becomes OverflowException.
4. GetFieldValue<string>/<byte[]>/<int?> return null for SQL NULL (GetString/typed getters still throw InvalidCastException, and GetFieldValue<object> still yields DBNull.Value).
5. Pooling never retains private :memory: databases — every Open() of a private memory DB is fresh. Named shared memory databases (Mode=Memory;Cache=Shared) do share.
6. A reader disposed early still executes unreached statements except pure SELECTs, which are skipped (side-effect-free and faster).

Building

dotnet build                                            # library + tests + bench
dotnet test                                             # 122 tests
dotnet run -c Release --project bench/Zensurf.Data.SqliteSys.Benchmarks

Requires the .NET 10 SDK. The library targets net10.0, uses the latest C#, and has a single dependency: SQLitePCLRaw.lib.e_sqlite3 (native SQLite binaries for every RID — no managed code).

Native library resolution order

1. SqliteSysRuntime.SetNativeLibraryPath(path)
2. ZENSURF_SQLITE_NATIVE_PATH environment variable
3. Bundled e_sqlite3 (SQLite 3.53.3, from NuGet runtimes)
4. System SQLite (sqlite3 / libsqlite3.so.0 / winsqlite3.dll)

Version-dependent APIs (sqlite3_changes64, serialize/deserialize, sqlite3_key) are detected at load time and degrade gracefully on older system libraries.

Repository layout

src/Zensurf.Data.SqliteSys/        the provider (→ Zensurf.Data.SqliteSys.dll)
  Interop/                         [LibraryImport] bindings, loader, safe handles
  Internal/                        statement cache, pool, executor, type coercion
xUnit suite (122 tests)
bench/…Benchmarks                  head-to-head vs Microsoft.Data.Sqlite
samples/…AotSmoke                  NativeAOT-published end-to-end smoke test (runs in CI)

License

MIT — see LICENSE. SQLite itself is public domain. This is an independent implementation; no Microsoft.Data.Sqlite code is included.