OneLite is a free, open-source (MIT or Apache-2.0) SQLite-compatible database for .NET from Recursive AI. NuGet · GitHub · Docs.
Quick start
From an empty directory with the .NET 10 SDK:
dotnet new console -f net10.0 dotnet add package \ RecursiveAI.OneLite --version 1.0.0
Run a rollback on a new disk database. The Storage.Pager namespace supplies JournalMode; Delete selects a rollback journal rather than write-ahead logging (WAL).
Replace Program.cs with the following and run dotnet run.
using RecursiveAI.OneLite; using RecursiveAI.OneLite.Storage.Pager; using var db = Database.OpenDefault($"pager-{Guid.NewGuid():N}.db", new DatabaseOptions { JournalMode = JournalMode.Delete }); db.ExecuteScript("CREATE TABLE t(x); INSERT INTO t VALUES (1);"); db.ExecuteScript("BEGIN; UPDATE t SET x = 2; ROLLBACK;"); Console.WriteLine(db.Scalar<long>($"SELECT x FROM t"));
1The rollback journal saves the old page bytes so rollback can restore the value 1.
From a B-tree lookup to a file read
Suppose a query looks up row ID 1. The SQL planner chooses how to find it; for an integer primary key, that can mean searching the table’s B-tree. The B-tree knows how to interpret the keys and child pointers inside a page. The pager doesn’t need to know what a row is. The request it sees is for a numbered page.
- The B-tree asks for the root page. If it needs to descend, it reads a child page number from that page and asks for the child.
- On a cache hit, OneLite’s pager hands back a frame, a memory buffer that holds the page’s bytes. The pager keeps the buffer in the cache while the caller is using it.
- On a miss that needs the existing bytes, the pager asks the virtual file system (VFS) to read the page. OneLite numbers pages from 1, so page 3 of a 4,096-byte-page database starts at byte 8,192. The pager fills a frame, and the B-tree can continue its search.
A pager cache miss is not necessarily a physical disk read. The VFS is the interface for operations such as reading, writing, locking, and syncing; an ordinary file read can still be served from the operating system’s cache. The pager’s cache has a different job: it tracks which database pages are in use and which have changes not yet written to the database file. See SQLite’s pager overview and the OneLite page calls.
What happens to a page during a rollback-mode commit?
Take a single database file in DELETE journal mode with synchronous=FULL. This is the rollback-journal path, not write-ahead logging (WAL). When an existing page is made writable, the pager preserves the old contents in the rollback journal and marks the cached page dirty, meaning it has changes not yet written to the database file. The B-tree can now change the cached copy. The journal holds what would be needed to undo database-file changes, not a record of the SQL statement.
- Protect the old page first. The pager makes the journal durable before overwriting the original page in the database file. A writer also needs exclusive access to write the database file.
- Write and sync the database. Dirty pages go to their database offsets. A “hot” journal is a rollback journal left by an interrupted write that still needs to be applied. The next opener can use it to restore the old state, including pages already overwritten.
- Retire the journal. In
DELETEmode that means unlinking it. In the SQLite rollback commit model, deleting the journal is the commit point; seeing new bytes in the database file is not enough on its own. Modes such asTRUNCATEandPERSISTretire the journal without unlinking it.
Inside OneLite: its pager docs name those commit phases PrepareCommit, WriteDatabase, and FinishCommit. They also describe how storage work crosses the VFS boundary: a pager operation may return Pending with a request to do file work, such as reading or syncing. After executing it, the caller repeats the same operation with the same arguments. The synchronous and asynchronous wrappers drive that loop for the caller. See the OneLite step contract and journal sync sequences.
See the state change in OneLite
Replace the quick-start program with this one to compare rollback with commit. It creates a new database and runs the same update twice. The output shows which value the connection can see after each outcome. It does not show pager I/O, cache hits, or power-loss behavior. The quick start uses Database.OpenDefault for its built-in connection. This example uses Database.Open to show the steps on a connection you open yourself. The Storage.Pager import supplies the journal and sync option enums.
using RecursiveAI.OneLite; using RecursiveAI.OneLite.Storage.Pager; string path = $"pager-{Guid.NewGuid():N}.db"; using var db = Database.Open(path, new DatabaseOptions { JournalMode = JournalMode.Delete, Synchronous = SynchronousMode.Full }); using var connection = db.Connect(); Run("CREATE TABLE entries (id INTEGER PRIMARY KEY, value TEXT)"); Run("INSERT INTO entries VALUES (1, 'before')"); const string lookup = "SELECT value FROM entries WHERE id = 1"; Console.WriteLine($"Mode: {Read("PRAGMA journal_mode")}"); Console.WriteLine($"Before: {Read(lookup)}"); const string update = "UPDATE entries SET value = 'after' WHERE id = 1"; Run("BEGIN"); Run(update); Console.WriteLine($"Inside transaction: {Read(lookup)}"); Run("ROLLBACK"); Console.WriteLine($"After rollback: {Read(lookup)}"); Run("BEGIN"); Run(update); Run("COMMIT"); Console.WriteLine($"After commit: {Read(lookup)}"); void Run(string sql) { using var statement = connection.Prepare(sql); while (statement.Step()) { } } string Read(string sql) { using var statement = connection.Prepare(sql); if (!statement.Step()) throw new InvalidOperationException("No row."); return statement.GetString(0) ?? throw new InvalidOperationException("No value."); }
Mode: delete Before: before Inside transaction: after After rollback: before After commit: after
For application transaction code, see SQLite transactions in C#. To observe reads at the storage boundary, see the custom VFS example.