Getting started with OneLite

Install OneLite 0.2.0-preview.2 for .NET 10, create a SQLite database, write to it and query it, in C# with no native library. OneLite is an embedded SQL database written on the .NET base class library alone that reads and writes SQLite files.

Fixed in 0.2.0-preview.2: upgrade. From the Known issues section of the README at v0.2.0-preview.2:

“0.2.0-preview.1 can silently drop a write (data loss); upgrade to 0.2.0-preview.2. In a rollback-journal mode (journal_mode DELETE, TRUNCATE or PERSIST, the default for Database.Open and OneLiteConnection), an INSERT, UPDATE or DELETE in autocommit mode returned success with no change while another connection held a read, in the same process or another one. It now fails with SQLITE_BUSY (‘database is locked’) as SQLite does; WAL mode was never affected. Remaining differences between several connections on one file are listed in docs/engine.md.

“0.2.0-preview.1 could silently lose a key change; upgrade to 0.2.0-preview.2. A key change could be silently lost: PRAGMA rekey or PRAGMA hexrekey run through a call that reads one row and stops (ExecuteScalar, a reader closed early, Statement.Step once, the happy-path scalar and query methods) answered ok but kept the old key. ExecuteNonQuery and running the statement to completion were not affected. Upgrade to 0.2.0-preview.2, or after a rekey in preview.1 reopen the file with the new key to confirm it.”

Before you use it

• What works. From the README’s Status section: “Preview (0.2.0-preview.2). The storage layer, the SQL engine, RecursiveAI.OneLite.Data and the happy-path API work, as do FTS3/4/5, R*Tree, vec0 (OneLite’s own vector table: it follows sqlite-vec’s SQL API but its files are not interchangeable with sqlite-vec’s, see docs/vec.md), the backup API and SQLCipher-4-compatible encryption.”
• Not yet. In the same section’s words: “Still to come before 1.0: BEGIN CONCURRENT, the Microsoft.Data.Sqlite/EF Core adapter, the onelite command-line tool, and published benchmarks. APIs may change between previews.” So Microsoft.Data.Sqlite and EF Core code can’t run on OneLite yet, and there are no speed figures.
• How compatible. OneLite is measured against one pinned release, SQLite 3.53.4, on query results, result codes and error messages. Its compatibility page has the numbers and every known difference; in summary, sqllogictest “622 files, 7,420,713 records: 5,939,877 pass, 0 wrong answers, 1 explained failure”, and differential testing against sqlite3 “4 seeds, 22 minutes each, about 12,000 generated cases and 0.8 M checks: 0 unexplained differences”.
• Encryption. SQLCipher-4-compatible encryption works, in the README’s words. It has had the project’s own review (not an external audit) and has not been independently audited.

Install

The packages target net10.0, so you need the .NET 10 SDK, and dotnet add package needs --prerelease or the exact version.

dotnet new console -o OneLiteStart && cd OneLiteStart
dotnet add package RecursiveAI.OneLite --version 0.2.0-preview.2

Two packages are on NuGet:

A first program

Put this in Program.cs and run dotnet run:

using RecursiveAI.OneLite;
 
await using var db = await Database.OpenAsync("todo.db");
 
// Script n runs once, when PRAGMA user_version is below n.
await db.MigrateAsync("""
    create table todos
      (id integer primary key, title text not null, done integer not null)
    """);
 
await db.WriteAsync(async tx =>
{
    var todos = tx.Table<Todo>();
    await todos.InsertAsync(new Todo { Title = "Install OneLite", Done = true });
    await todos.InsertAsync(new Todo { Title = "Read the cookbook" });
});
 
bool open = false;
foreach (Todo t in await db.QueryAsync<Todo>(
                       $"select * from todos where done = {open}"))
    Console.WriteLine($"To do: {t.Title}");
Console.WriteLine($"{await db.ScalarAsync<long>($"select count(*) from todos")} rows");
 
[OneLiteRow]
public class Todo
{
    public long Id { get; set; }
    public string Title { get; set; } = "";
    public bool Done { get; set; }
}

To do: Read the cookbook
2 rows

• Database.OpenAsync creates the file if it is missing and opens it with write-ahead logging (WAL), foreign keys on and a 5-second busy timeout. One Database is one connection, and it serializes its own calls.
• MigrateAsync brings the schema up to date, counting with PRAGMA user_version. Append scripts; never edit one that has shipped.
• WriteAsync runs the delegate in one transaction and commits it, or rolls it back if it throws.
• Every {value} in a $"…" query is a bound parameter, never pasted into the SQL.
• [OneLiteRow] has a source generator write the mapping, so it works with trimming and NativeAOT. Booleans are stored as integers.

The file is an ordinary SQLite database in WAL mode. The sqlite3 shell (3.53.4, from sqlite.org) reads it:

sqlite3 todo.db "select * from todos" "pragma user_version" "pragma journal_mode" \
  "pragma integrity_check"

1|Install OneLite|1
2|Read the cookbook|0
1
wal
ok

ADO.NET instead

RecursiveAI.OneLite.Data gives the same database to code written against DbConnection. Add the package and replace Program.cs:

dotnet add package RecursiveAI.OneLite.Data --version 0.2.0-preview.2

using RecursiveAI.OneLite.Data;
 
using var conn = new OneLiteConnection("Data Source=todo.db");
conn.Open();
using var cmd = conn.CreateCommand();
cmd.CommandText = "select title from todos where done = @done";
cmd.Parameters.Add(new OneLiteParameter("@done", true));
Console.WriteLine(cmd.ExecuteScalar());

Install OneLite

todo.db stays in WAL mode: the journal mode is stored in the file. A new file the provider creates starts in SQLite’s rollback-journal default.

See Use SQLite with ADO.NET for a complete program and the provider docs for connection strings, parameter and type rules.

What else is in the box

• Full-text search. FTS5, FTS3 and FTS4 on the documented shadow-table layouts: FTS5, FTS3 and FTS4.
• Spatial and vector. R*Tree and dbstat (R*Tree), and vec0 for exact k-nearest-neighbour search (vec0). SearchAsync on the happy path fuses full-text and vector results (the happy path).
• Backup and blobs. The backup API, VACUUM INTO and incremental blob I/O (backup and blob I/O).
• Clearer errors. Exceptions keep SQLite’s code and message and add the position in the SQL with a caret and suggestions (errors).
• In the browser. Storage backends for browser OPFS (in the browser).

Next

• Cookbook: SQLite without native dependencies, create and query, open an existing file, ADO.NET, transactions.
• Compatibility with SQLite 3.53.4: what was measured and every known gap.
• Overview (the README) and the changelog.

OneLite is not affiliated with or endorsed by the SQLite developers; “SQLite” is a registered trademark of Hipp, Wyrick & Company.