SQLite UPSERT: Making Insert-or-Update Safe Under Concurrency

The concrete problem is a counter or cache row that multiple writers touch at once. The naive pattern is SELECT the row, decide in application code, then INSERT or UPDATE. Under concurrency two writers can both see “missing” and try to INSERT, one fails with a UNIQUE constraint violation, or worse you get duplicate work and retry loops.

The useful takeaway is to let SQLite resolve the conflict in one statement with INSERT … ON CONFLICT DO UPDATE. It removes the read-modify-write race and makes the write idempotent when a UNIQUE or PRIMARY KEY defines the conflict target.

Where SELECT-then-write breaks

SQLite uses database-level locking for writes. A SELECT does not hold a write lock, so two connections can read the same state and then race on the write. The classic workaround is to catch the constraint error and retry with an UPDATE. That works but adds round trips, application complexity, and unpredictable latency under load.

UPSERT, available since SQLite 3.24.0 released June 2018, collapses the logic into a single INSERT. If the conflict target is not violated the row is inserted. If it is violated, the DO UPDATE clause runs instead.

How the syntax maps to intent

The core form is:

INSERT INTO table (col_a, col_b, col_c)
VALUES (?, ?, ?)
ON CONFLICT(conflict_target) DO UPDATE SET
  col_b = excluded.col_b,
  col_c = excluded.col_c;

conflict_target can be a column declared UNIQUE or PRIMARY KEY, a named UNIQUE index, or a partial index expression. The excluded pseudo-table holds the values you just tried to insert. Referencing excluded.col lets you update only from the incoming data and avoid re-reading the existing row.

A requirement is explicit: the table must have a suitable UNIQUE index for the conflict to be detected. Without it ON CONFLICT is ignored and the statement behaves like a normal INSERT, which can create duplicates.

Worked example: idempotent inventory bump

Assume a table that tracks stock per SKU with a natural unique key on sku.

CREATE TABLE inventory (
  sku TEXT PRIMARY KEY,
  qty INTEGER NOT NULL,
  updated_at TEXT NOT NULL
);

An upsert that inserts a new SKU or updates qty and timestamp on conflict:

INSERT INTO inventory (sku, qty, updated_at)
VALUES (:sku, :qty, datetime('now'))
ON CONFLICT(sku) DO UPDATE SET
  qty = excluded.qty,
  updated_at = excluded.updated_at;

Run this in sqlite3 CLI or any client with write permission to the database file. Required checks before use:

• Verify version: SELECT sqlite_version(); should return 3.24.0 or higher.
• Confirm the conflict target is enforced: SELECT sql FROM sqlite_master WHERE type='table' AND name='inventory'; and check for UNIQUE/PRIMARY KEY.

To verify behavior, execute the statement twice with the same :sku but different :qty. Query the row after each execution and confirm the qty reflects the value from the excluded pseudo-table on the second run, with no duplicate rows created.

Trade-offs and limitations

UPSERT still acquires write locks on the affected rows. Under heavy concurrent writes SQLite serializes writers, so throughput can plateau and lock wait times increase. Benchmark under realistic load rather than assuming linear gains.

Another limitation is partial updates. If you reference columns not in the INSERT list, excluded.col will be NULL for them. Design the column list to include everything you might want to update.

Older SQLite builds lack the syntax. On those versions you must emulate UPSERT with triggers or application retries, which reintroduces the race you were trying to avoid.

Rollback is only relevant if you wrap the upsert in an explicit transaction and need atomicity with other statements. A single UPSERT is atomic by itself.

Actionable closing

Replace SELECT-then-INSERT/UPDATE patterns for naturally keyed rows with INSERT … ON CONFLICT DO UPDATE. Ensure a PRIMARY KEY or UNIQUE index exists on the conflict target, confirm sqlite_version() >= 3.24.0, and test the statement under concurrent writers to observe lock behavior. This gives a single, idempotent write path without manual retry logic.