Use ON CONFLICT DO UPDATE to add to a counter if its key already exists.
dotnet add package RecursiveAI.OneLiteRequires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.
using RecursiveAI.OneLite; await using var db = await Database.OpenAsync(":memory:"); await db.ExecuteAsync($""" CREATE TABLE hits ( page TEXT NOT NULL UNIQUE, total INTEGER NOT NULL ) """); foreach (var delta in new[] { 2, 3 }) { var total = await db.ScalarAsync<long>($""" INSERT INTO hits VALUES ('home', {delta}) ON CONFLICT(page) DO UPDATE SET total = hits.total + excluded.total RETURNING total """); Console.WriteLine(total); }
OneLite binds interpolated values as SQL parameters. {delta} is sent separately from the SQL text.
Unzip the example, then dotnet run.
Prints the counter after each write:
2 5
The first write creates the home counter with a total of 2. The second finds the same page and adds 3 without inserting another row. If the conflict target doesn’t match a unique constraint or index, OneLite throws OneLiteException.
Variations
Keep the old total if the page already exists. Append this to the program; home stays at 5.
await db.ExecuteAsync($""" INSERT INTO hits VALUES ('home', 100) ON CONFLICT(page) DO NOTHING """);
Related recipes
The short answer
Use INSERT … ON CONFLICT(page) DO UPDATE with a unique page column. Put hits.total + excluded.total on the right of SET to add the proposed increment to the current counter. RETURNING total gives C# the value from the insert or update without another query.
How does SQLite ON CONFLICT DO UPDATE work?
The conflict target names the key that identifies a row. In this table, page is NOT NULL UNIQUE: there can only be one counter per page, and every counter must have a page. You can use a primary key instead. If uniqueness is defined on a pair of columns, identify that pair rather than just one of its columns. An upsert does not handle failures of NOT NULL, CHECK, or foreign key constraints. See the SQLite UPSERT reference.
In the update expression, hits.total is the total already in the table. excluded.total is the value the attempted insert would have written.
How do you return the value after a SQLite upsert?
Use RETURNING and read it as a query result. The example targets one page, so ScalarAsync<long> reads the one returned value. That value isn’t a flag indicating which branch ran: the first 2 is the inserted total and the second 5 is the updated total because the program starts from an empty table and writes the same key twice. A DO NOTHING conflict doesn’t modify a row and returns no row; don’t interpret a scalar default value as the counter in that case.
What is the difference between SQLite upsert and INSERT OR REPLACE?
DO UPDATE changes the columns in its SET clause. If the table also has a note or creation timestamp, an upsert that only sets total leaves it alone. SQLite’s REPLACE conflict algorithm deletes pre-existing rows that cause a uniqueness conflict before inserting the replacement. It is not an update of selected columns. For a running counter with other data attached to the row, choose DO UPDATE; choose DO NOTHING when the existing row should win unchanged.