Recursive AI
LibraryC#AvailablePreview

onelite

An embedded SQL database for .NET, written in C#, that reads and writes SQLite files, with no native library to ship: trimming, NativeAOT and the browser included.

Known issue: In 0.2.0-preview.1, in rollback-journal mode (the default for Database.Open and ADO.NET OneLiteConnection), a write outside an explicit transaction can be silently dropped while another connection (in any process) holds a read. Use WAL mode until 0.2.0-preview.2. From the Known issues section of the README on main (commit a2ee8f7) (the docs on this site describe the 0.2.0-preview.1 tag): “Known issue in 0.2.0-preview.1 (data loss): a write outside an explicit transaction can be silently dropped in rollback-journal mode. It happens when all of these hold: the database is in a rollback-journal mode (journal_mode DELETE, TRUNCATE or PERSIST), which is the default for Database.Open and for RecursiveAI.OneLite.Data’s OneLiteConnection; another connection holds a read (a SELECT that has stepped but not finished or been reset, an open DbDataReader, or an open read transaction), in the same process or in another process such as sqlite3; and an INSERT, UPDATE or DELETE runs in autocommit mode (no BEGIN/BeginTransaction) on a different connection. The statement then completes without an error, reports 0 changes (ExecuteNonQuery returns 0), and nothing is stored; SQLite returns SQLITE_BUSY. Inside an explicit transaction the write is not lost: COMMIT fails with ‘database is locked’ and can be retried, as in SQLite. WAL mode is not affected. Workaround: use WAL (PRAGMA journal_mode=WAL once on the file; the default of Database.OpenAsync and Database.OpenDefault), or wrap writes in an explicit transaction and handle ‘database is locked’, or make sure no other connection holds a read while you write; check that a write reports the expected number of changes. A fix is coming in 0.2.0-preview.2.” Every sample on this site uses WAL mode.

dotnet add package RecursiveAI.OneLite --prerelease
Getting started Docs Cookbook GitHub NuGet

A quick look

Open a file, write in a transaction, query with bound parameters. The result is an ordinary SQLite database that the sqlite3 shell opens and checks clean.

Getting started
Program.csC#
using RecursiveAI.OneLite;
 
await using var db = await Database.OpenAsync("books.db");
await db.Table<Book>().EnsureCreatedAsync();
 
await db.WriteAsync(async tx =>
    await tx.Table<Book>().InsertAsync(new() { Title = "Dune" }));
 
// {year} is a bound parameter, never pasted into the SQL
var books = await db.QueryAsync<Book>(
    $"select * from books where Year > {year}");
What it does

SQLite files, both ways

Database, rollback-journal, WAL and shared-memory files, read and written in the SQLite format. Files sqlite3 writes open in OneLite, and files OneLite writes open in sqlite3.

Open an existing .db file

The SQL engine

Joins, subqueries, recursive CTEs, aggregates, window functions, views, triggers, upsert, RETURNING, STRICT and WITHOUT ROWID tables, ALTER TABLE, ATTACH, JSON and math functions, transactions and savepoints.

Transactions in C#

No native library

Written in C# on the base class library alone. A publish ships one managed assembly and no e_sqlite3 binary per platform, and it builds for trimming, NativeAOT and the browser.

SQLite without native dependencies

The happy path

$"…" queries whose holes are bound parameters, WriteAsync transactions with retry, Table<T>, source-generated mapping with no reflection, and MigrateAsync.

Create, insert and query

ADO.NET with real async

RecursiveAI.OneLite.Data: DbConnection, DbCommand, DbDataReader, DbDataSource and batches, with async calls that await the storage layer.

ADO.NET and parameters

Search

FTS5, FTS3 and FTS4 on the documented shadow-table layouts, R*Tree, and vec0 for exact nearest-neighbour search. SearchAsync fuses full-text and vector results.

Backup and blobs

The backup API, VACUUM INTO, incremental blob I/O and :memory: databases.

SQLCipher-4-compatible encryption

Encrypted database, journal and WAL with SQLCipher 4’s format. It has had the project’s own review (not an external audit) and has not been independently audited.

Clearer errors

SQLite’s result codes and messages, unchanged, plus the position in the SQL with a caret, the offending name and suggestions.

How compatible

Measured against SQLite 3.53.4

OneLite aims to behave like one pinned SQLite release, the 3.53.4 sqlite3 shell from sqlite.org: the same query results, result codes and error messages. These are the project’s measurements for 0.2.0-preview.1, quoted from its compatibility page, which lists every known difference.

  • sqllogictest, SQL engine (all groups): “622 files, 7,420,713 records: 5,939,877 pass, 0 wrong answers, 1 explained failure; 1,480,834 records are written for other database engines and skipped”
  • Differential testing against sqlite3: “4 seeds, 22 minutes each, about 12,000 generated cases and 0.8 M checks: 0 unexplained differences”
  • Files: files written by sqlite3 (“every page type, overflow, freelists, auto-vacuum modes, encodings, page sizes, WAL states, hot journals”) read by OneLite: “all identical”; files written by OneLite “pass sqlite3’s PRAGMA integrity_check and give the same .dump”: “all pass”
  • Live beside sqlite3 in WAL mode: “10,000 interleaved commits, consistent both ways”
  • FTS: FTS5, “25-minute run: 19 of 647 cases differ, all in the window snippet chooses or a malformed-query quirk of the shell”; FTS3 and FTS4, “25-minute run: 264 cases, 0 differing”
  • Durability: process kills and injected faults, judged by sqlite3: “0 corruptions and 0 lost acknowledged commits in every campaign so far”

Known gaps include the last bit of text-to-REAL conversions with 20 or more significant digits, the order of ties inside a window, and row order before a runtime error when plans differ. In the page’s words, “Native extensions (load_extension) are not supported” and “Geopoly, sessions and changesets, carray and the shell’s miscellaneous extensions are not implemented.”

The full compatibility page

Status and packages

Go further

GitHub NuGet NuGet: OneLite.Data Getting started All docs Cookbook Compatibility Changelog

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