A database-backed API

The lost update

Ada and Lin both open campaign 7, budget 500. Ada changes the name and saves. Lin, looking at an older copy, changes the budget and saves the whole record. Lin's save writes back the old name. Ada's change has vanished, and nobody got an error. That's a lost update, and with many users it happens every day.

Optimistic concurrency with versions

Think of a shared recipe card with a version number in the corner. Before you write your changes on it, you check the number is still the one you read. If it's gone up, someone has changed the card, and you read it again first.

In a database, give each row a version number that goes up on every change. A client says which version it started from, and the update only applies if that's still the current one:

UPDATE campaigns SET budget = ?, version = version + 1
WHERE id = ? AND version = ?

If someone else saved in between, the version no longer matches and no rows change. The API reports a conflict, and the client reloads and tries again. It's called optimistic because it assumes conflicts are rare and only checks at save time, instead of locking the record while someone edits it.

ETags and If-Match

HTTP has headers for exactly this. A GET response carries an ETag, a short tag for this version of the resource, such as "7-3". To update, the client sends it back in an If-Match header, which means "only do this if it's still that version":

• If it matches the current ETag, the update goes ahead, and the response carries the new ETag.
• If it doesn't, the answer is 412 Precondition Failed: "someone changed this; reload first".
• If an API requires If-Match and it's missing, the answer is 428 Precondition Required.

ETags help reads too. A client sends If-None-Match with the ETag it has, meaning "only send it if it's changed". If nothing changed, the server answers 304 Not Modified with no body, which saves bandwidth.

row = {"version": 4}
client_version = 3
print("updated" if client_version == row["version"] else "conflict")

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE campaigns (id INTEGER PRIMARY KEY, name TEXT, budget REAL, version INTEGER NOT NULL DEFAULT 1)")
db.execute("INSERT INTO campaigns (name, budget) VALUES ('Spring', 500)")

def update_budget(cid, budget, expected_version):
    cur = db.execute(
        "UPDATE campaigns SET budget = ?, version = version + 1 WHERE id = ? AND version = ?",
        (budget, cid, expected_version),
    )
    return cur.rowcount == 1

ada_saw = lin_saw = 1                       # both loaded version 1
print("Ada saves:", update_budget(1, 650, ada_saw))
print("Lin saves:", update_budget(1, 400, lin_saw))
print(db.execute("SELECT budget, version FROM campaigns").fetchone())

Practical details

• Timestamps: store created_at and updated_at in UTC as ISO 8601 text (2026-10-30T14:05:00Z). Convert to local time only when showing it to a person.
• Transactions: an operation that touches several rows, such as creating an order and lowering stock, goes in one transaction. It's like a bank transfer: both halves happen, or neither does. A failure halfway leaves nothing behind.
• Soft delete: many APIs set deleted_at instead of removing the row, like moving a file to the trash. Mistakes can be undone and history survives. Every query then has to skip deleted rows, so it's a trade-off.
• Return the new state: after a create or update, return the full resource, with its new ETag. That saves the client a second request.

Mapping database errors to useful answers

When a unique constraint fails, the database raises an error. Left alone, that becomes a 500, telling the client your server broke. It didn't. They tried to use an email address that's already taken. Catch that specific error and return a 409 with a clear message. The constraint is still what keeps your data correct, since checking first and inserting after leaves a gap that two requests can slip through together.

Never build column names from a request

Placeholders protect values, but SQL doesn't allow placeholders for column names. So code that loops over a request's keys and pastes them into UPDATE is open to injection through the key itself. That's why PATCH builds its update from an allowlist of known columns.

Store time in UTC

Save every time in UTC, and convert to local time only for display. Local times have gaps and repeats when the clocks change, so one wall-clock time can mean two different moments. And times from two servers in different zones can't be compared safely. This is easy to do from the start, and very painful to fix later.