---
title: "Upsert a SQLite row with ON CONFLICT in C#"
url: https://recursiveai.org/onelite/cookbook/upsert-sqlite-row-csharp/
description: "Insert a new counter or add to the existing value with SQLite ON CONFLICT DO UPDATE and RETURNING in C#."
version: "1.0.0"
---

# Upsert a SQLite row with ON CONFLICT in C#

**OneLite** is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/upsert-sqlite-row-csharp/upsert-sqlite-row-csharp.zip)

Use `ON CONFLICT DO UPDATE` to add to a counter if its key already exists.

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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](https://recursiveai.org/onelite/docs/happy-path/#-binds-parameters). `{delta}` is sent separately from the SQL text.

Unzip [the example](https://recursiveai.org/onelite/cookbook/upsert-sqlite-row-csharp/upsert-sqlite-row-csharp.zip), then `dotnet run`.

Prints the counter after each write:

```text
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`.

```csharp
await db.ExecuteAsync($"""
    INSERT INTO hits VALUES ('home', 100)
    ON CONFLICT(page) DO NOTHING
    """);
```

## Related recipes

- [Create a SQLite database in C#](https://recursiveai.org/onelite/cookbook/create-sqlite-database-csharp/)
- [Use SQLite transactions in C#](https://recursiveai.org/onelite/cookbook/sqlite-transactions-csharp/)

## 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](https://www.sqlite.org/lang_upsert.html#description).

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](https://www.sqlite.org/lang_returning.html); 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](https://www.sqlite.org/lang_conflict.html) 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.
