Use SQLite UPSERT to make enqueue retries explicit

By Batu · English technical notes

Also published in our Blogger archive.

Let the UPSERT name the retry mutation

The first execution inserts r1 with zero retries. The second conflicts on request_key and executes DO UPDATE, which increments retries. Because payload is not named in the update list, the final row retains first instead of replacing it with retry.

That makes duplicate behavior explicit in one statement and keeps bound values outside SQL syntax. Repeated buggy submissions also increment here; this example has no retry cap, payload validation, or running-job decision.

Example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    connection = sqlite3.connect(Path(directory) / "queue.db")
    connection.execute("CREATE TABLE job(request_key TEXT PRIMARY KEY, payload TEXT, retries INTEGER)")
    query = """INSERT INTO job VALUES (?, ?, 0)
               ON CONFLICT(request_key) DO UPDATE SET retries = retries + 1"""
    connection.execute(query, ("r1", "first"))
    connection.execute(query, ("r1", "retry"))
    row = connection.execute("SELECT payload, retries FROM job").fetchone()
    assert row == ("first", 1)
    print("rows=1 payload=first retries=1")

Expected stdout:

rows=1 payload=first retries=1

Sources

- SQLite UPSERT

- sqlite3.Connection.execute

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.