Create a SQLite database, insert and query in C#

Open the file with Database.OpenAsync, create a table from a class marked [OneLiteRow], insert with Table<T>().InsertAsync, and query with QueryAsync<T>($"…"), where every {value} is a bound parameter. This is OneLite’s happy-path API, in the core package.

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

Put this in Program.cs and run dotnet run:

using RecursiveAI.OneLite;
 
await using var db = await Database.OpenAsync("books.db");   // WAL mode
var books = db.Table<Book>();
await books.EnsureCreatedAsync();                  // create table if missing
 
await db.WriteAsync(async tx =>                    // one transaction, committed
{
    var t = tx.Table<Book>();
    await t.InsertAsync(new Book { Title = "Dune", Year = 1965 });
    await t.InsertAsync(new Book { Title = "Neuromancer", Year = 1984 });
    await t.InsertAsync(new Book { Title = "Hyperion", Year = 1989 });
});
 
int after = 1980;
foreach (Book b in await db.QueryAsync<Book>(
             $"select * from books where Year > {after} order by Year"))
    Console.WriteLine($"{b.Id}: {b.Title} ({b.Year})");
 
Book? first = await books.GetAsync(1L);
Console.WriteLine($"Key 1 is {first?.Title}");
long count = await db.ScalarAsync<long>($"select count(*) from books");
Console.WriteLine($"{count} books");
Console.WriteLine(await db.ScalarAsync<string>($"pragma journal_mode"));
 
[OneLiteRow]
public class Book
{
    public long Id { get; set; }                   // the key: a property named Id
    public string Title { get; set; } = "";
    public int Year { get; set; }
}

2: Neuromancer (1984)
3: Hyperion (1989)
Key 1 is Dune
3 books
wal

• Database.OpenAsync creates the file in WAL mode (wal), the happy-path default.
• [OneLiteRow] has a source generator write the mapping at build time, with no reflection, so it is trimming- and NativeAOT-safe.
• EnsureCreatedAsync creates a STRICT table named books with a column per property, NOT NULL where the C# type isn’t nullable, and Id as the INTEGER PRIMARY KEY. An Id of 0 is left to the database, which numbers the rows.
• WriteAsync runs its delegate in one write transaction and commits when it returns. If the database is busy it rolls back and runs the delegate again, so the delegate should touch nothing but the database. See transactions.
• In QueryAsync<Book>($"… {after} …"), {after} is bound as ?1, never pasted into the SQL, so user input can’t inject SQL. A plain string is not accepted by the query methods. To run SQL text as written, use ExecuteScriptAsync, which takes no parameters.
• Columns match properties by name, ignoring case. Every async method has a blocking twin without the suffix: Query, Scalar, Write.

Check it with sqlite3

The sqlite3 shell (3.53.4, from sqlite.org) opens the file OneLite wrote and sees the same table and rows, in WAL mode:

sqlite3 books.db ".schema books" "select * from books" "pragma journal_mode" \
  "pragma integrity_check"

CREATE TABLE "books" ("Id" INTEGER PRIMARY KEY, "Title" TEXT NOT NULL, "Year" INTEGER NOT NULL) STRICT;
1|Dune|1965
2|Neuromancer|1984
3|Hyperion|1989
wal
ok

Limits

• A preview: APIs may change between previews.
• Table<T> is thin: create, insert, upsert, get, delete and WhereAsync($"…"). There is no LINQ provider and no change tracking; write SQL for anything else.
• The table name is the class name in lower case with an s; [OneLiteRow(TableName = "…")] sets another.
• Run each program once per file here: a second run inserts the three rows again.

Getting started · The happy path · All recipes