Open an existing SQLite .db file in C#

Pass the path to Database.OpenAsync and query it. OneLite reads the SQLite file format directly, so a database made by the sqlite3 shell, another app or another language opens as it is. Database.OpenAsync opens it in WAL mode, the happy-path default, and that switch stays with the file (why).

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

Make a database with the sqlite3 shell (3.53.4, from sqlite.org), or use a .db file you already have:

sqlite3 shop.db "create table products
    (id integer primary key, name text not null, price real);
  insert into products (name, price)
    values ('Pen', 1.5), ('Notebook', 4.25), ('Ink', 12);" "pragma journal_mode"

delete

delete is SQLite’s default, a rollback-journal mode.

Put this in Program.cs and run dotnet run:

using RecursiveAI.OneLite;
 
// OpenAsync opens in WAL mode, so it switches this rollback-journal file to WAL.
await using var db = await Database.OpenAsync("shop.db");
Console.WriteLine(await db.ScalarAsync<string>($"pragma journal_mode=wal")); // says so
 
Console.WriteLine(await db.ScalarAsync<string>($"pragma integrity_check"));
foreach (Product p in await db.QueryAsync<Product>(
             $"select id, name, price from products order by price"))
    Console.WriteLine($"{p.Id} {p.Name,-9} {p.Price,6:0.00}");
 
// Write to it as well: one row
await db.ExecuteAsync(
    $"insert into products (name, price) values ({"Stapler"}, {7.8})");
 
[OneLiteRow]
public record Product(long Id, string Name, double Price);

wal
ok
1 Pen         1.50
2 Notebook    4.25
3 Ink        12.00

OneLite’s own PRAGMA integrity_check checks the file sqlite3 wrote and returns ok, and the query reads its rows. Records map by constructor parameter, matched to columns by name, ignoring case.

Why WAL

• It is the happy-path default. In the words of the happy-path docs, Database.OpenAsync and Database.OpenDefault “open (creating) a database with write-ahead logging, foreign_keys=ON and a 5 s busy timeout”. pragma journal_mode=wal states it and prints wal. Database.Open and the ADO.NET provider keep SQLite’s rollback-journal default.
• The switch is permanent. From the same page: “opening an existing rollback-journal file with OpenAsync/OpenDefault switches that file to WAL for good (it is a property of the file, as PRAGMA journal_mode=WAL is in SQLite), so other programs that open it, sqlite3 included, see a WAL database and need write access to the directory for the -wal and -shm files; pass DatabaseOptions to keep the file’s journal mode.” sqlite3 3.53.4 reads and writes it as usual, as the check below shows. SQLite older than 3.7.0 can’t open a WAL file at all.
• Two more files while it is open. In WAL mode the database keeps shop.db-wal and shop.db-shm beside shop.db while a connection has it open. They are part of the database: copy or move all three together, or close every connection first.

Check it with sqlite3

Back in sqlite3, the row OneLite wrote is there, the file is in WAL mode and it checks clean:

sqlite3 shop.db "select * from products" "pragma journal_mode" "pragma integrity_check"

1|Pen|1.5
2|Notebook|4.25
3|Ink|12.0
4|Stapler|7.8
wal
ok

Limits

• A preview: APIs may change between previews.
• Native extensions (load_extension) are not supported. OneLite has FTS3/4/5, R*Tree, dbstat and vec0; in the compatibility page’s words, “Geopoly, sessions and changesets, carray and the shell’s miscellaneous extensions are not implemented.”
• vec0 is OneLite’s own, not sqlite-vec: in the words of docs/vec.md, “A vec0 table written by OneLite can’t be used by sqlite-vec, and one written by sqlite-vec can’t be used by OneLite.”
• Encrypted (SQLCipher) files need their key: see encryption.
• Sharing the file live with sqlite3 processes was measured in WAL mode (“10,000 interleaved commits, consistent both ways”).

Getting started · Compatibility · All recipes