Using SQLite UPSERT (ON CONFLICT DO UPDATE) for Simpler Insert-or-Update Logic

Problem: the two‑step insert‑or‑update pattern

When an application needs to store a record that may already exist, the classic approach is to first SELECT for a matching key, then either INSERT if the row is absent or UPDATE if it is present. This requires two round‑trips to the database and, under concurrent load, can lead to race conditions where two clients both see no row and both try to insert, causing a uniqueness error.

Thesis: SQLite’s UPSERT eliminates the extra SELECT and makes the operation atomic

Introduced in SQLite 3.24.0 (2018), the ON CONFLICT DO UPDATE clause lets a single INSERT statement either add a new row or modify an existing one when a uniqueness conflict occurs. The whole operation runs inside the same transaction, so concurrent clients cannot produce lost updates or duplicate‑key errors when the isolation level is serializable (the default in SQLite).

How UPSERT works

The syntax builds on a regular INSERT:

INSERT INTO table_name (column_list)
VALUES (value_list)
ON CONFLICT(conflict_target) DO UPDATE SET
    column1 = excluded.column1,
    column2 = excluded.column2,
    ...
WHERE (optional_condition);

• conflict_target is the name of a UNIQUE or PRIMARY KEY column (or index) whose violation triggers the upsert.
• The pseudo‑table excluded holds the values that would have been inserted; you can reference them in the SET clause.
• If you omit the WHERE clause, the update runs for every conflicting row.

Worked example: upserting user email preferences

Assume a table that stores a user’s newsletter preference, with the email address as a unique key.

-- Run in the sqlite3 shell; you need write permission on the database file.
CREATE TABLE IF NOT EXISTS user_prefs (
    email TEXT PRIMARY KEY,
    subscribed INTEGER NOT NULL DEFAULT 0,
    updated_at TEXT NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

First insert a new user:

INSERT INTO user_prefs (email, subscribed)
VALUES ('[contact removed]', 1)
ON CONFLICT(email) DO UPDATE SET
    subscribed = excluded.subscribed,
    updated_at = excluded.updated_at;

If a row with email = '[contact removed]' already exists, the ON CONFLICT clause fires and updates subscribed and updated_at to the values supplied in the INSERT. Running the same statement again with a different subscribed value will toggle the flag without generating an error.

To verify that the operation is performed as a single step, you can inspect the query plan:

EXPLAIN QUERY PLAN
INSERT INTO user_prefs (email, subscribed)
VALUES ('[contact removed]', 0)
ON CONFLICT(email) DO UPDATE SET
    subscribed = excluded.subscribed;

The output will show a single scan/update operation rather than separate SEARCH and INSERT steps.

Performance and concurrency considerations

Because the upsert avoids a preliminary SELECT, it reduces latency and the number of transactions needed. In a high‑concurrency workload, the atomic nature means that two clients attempting to upsert the same key will serialize at the storage engine level: one will insert, the other will update, and neither will encounter a duplicate‑key error.

Nonetheless, the statement still acquires a write lock on the affected table (or index) for the duration of the transaction. If your workload mixes many upserts with long‑running read transactions, you may see increased contention; monitoring the database’s busy_timeout or using PRAGMA journal_mode=WAL can help mitigate this.

Limitations and cautions

• Conflict scope: UPSERT only triggers on violations of declared UNIQUE or PRIMARY KEY constraints. It does not fire for CHECK constraint failures or foreign‑key violations.
• Updating conflict columns: If the DO UPDATE SET clause modifies a column that is part of the conflict index, SQLite may raise an error or produce undefined behavior. Avoid updating the key column(s) themselves.
• Version requirement: The feature is unavailable in SQLite releases prior to 3.24.0. Running the statement on an older version yields a syntax error near ON CONFLICT. You can check the version with:

SELECT sqlite_version();
-- Expected output: a string like '3.45.0' (must be ≥ 3.24.0)

To confirm that your table has the necessary unique index, query the schema:

SELECT sql FROM sqlite_master WHERE type='table' AND name='user_prefs';
-- Look for PRIMARY KEY(email) or a UNIQUE clause.

Actionable closing

If you need an insert‑or‑update pattern in SQLite, prefer ON CONFLICT DO UPDATE over a manual SELECT‑then‑INSERT/UPDATE sequence. Verify your SQLite version, ensure the conflict target is a unique or primary‑key index, and refrain from updating the key columns themselves. After applying the change, you can check correctness by:

1. Running the upsert statement with a new key and confirming a row appears.
2. Running the same statement with modified values and confirming the existing row changed rather than a duplicate appearing.
3. Using EXPLAIN QUERY PLAN to affirm a single‑step execution.

When these checks pass, you have a safer, more efficient way to handle mutable unique records in SQLite.