Make POST /upload Idempotent in Flask with PostgreSQL: Use UNIQUE and ON CONFLICT RETURNING to avoid duplicates and return 201/200 correctly

Learn how to make a Flask upload endpoint idempotent with PostgreSQL: enforce a UNIQUE key and use INSERT ON CONFLICT DO NOTHING RETURNING to prevent duplicates

When a mobile client retries a POST during a flaky network, your neat one-liner insert can quietly multiply rows. For an upload endpoint that writes to PostgreSQL, that means duplicate content, noisy analytics, and cleanup work later. The goal is simple: make the route idempotent, return 201 Created the first time, 200 OK on legitimate retries, and keep the solution small and idiomatic.

Reproducing the issue

The following route accepts JSON and inserts a record. It works, but repeated POSTs with the same payload create duplicates.

from flask import Flask, request, jsonify

svc = Flask(__name__)

@svc.post("/upload")
def push_photo():
    data = request.get_json()
    dbh.execute(
        "INSERT INTO photos (user_id, filename, uploaded_at) VALUES (%s, %s, NOW())",
        (data["user_id"], data["filename"]),
    )
    return jsonify({"status": "ok"}), 201

What actually goes wrong

Clients sometimes retry POST /upload when the network stutters. Without a uniqueness guarantee in the database, each retry is a fresh insert, so duplicates accumulate. Adding a UNIQUE constraint alone prevents the duplicate but surfaces a raw SQL error to the client. Wrapping the insert with ON CONFLICT DO NOTHING hides the error, but now the handler can’t distinguish “created” from “already existed,” making it impossible to return 201 the first time and 200 for retries.

The minimal and idiomatic fix

The simplest reliable approach is to enforce uniqueness in the table and use an upsert that reports whether an insert really happened. PostgreSQL makes this straightforward with INSERT … ON CONFLICT … DO NOTHING RETURNING. The unique key ensures duplicates can’t exist; the RETURNING clause tells you if a new row was created; and the status code is a direct mapping from that signal.

First, ensure the pair you care about is unique at the database level so duplicates physically can’t happen:

ALTER TABLE photos
ADD CONSTRAINT photos_user_filename_key UNIQUE (user_id, filename);

Then use a single INSERT with ON CONFLICT DO NOTHING RETURNING to detect if the insert succeeded. If the statement returns a row, it’s a new insert and you return 201. If it returns nothing, the row already existed and you return 200.

Final route

from flask import Flask, request, jsonify

svc = Flask(__name__)

@svc.post("/upload")
def push_photo():
    body = request.get_json()

    sql = (
        "INSERT INTO photos (user_id, filename, uploaded_at) "
        "VALUES (%s, %s, NOW()) "
        "ON CONFLICT (user_id, filename) DO NOTHING "
        "RETURNING 1"
    )

    result = dbh.execute(sql, (body["user_id"], body["filename"]))
    created = result.fetchone() is not None

    status_code = 201 if created else 200
    return jsonify({"status": "ok"}), status_code

Why this matters

This pattern keeps idempotency where it belongs: at the boundary between your application and the database. The uniqueness constraint guarantees correctness under retries and races. The upsert with RETURNING gives you a precise “created vs. existed” signal without extra infrastructure. Mapping that signal to HTTP semantics makes the API predictable for clients. While one could catch a database error and switch to 200 on duplicates, relying on a clean RETURNING path is more direct and avoids plumbing through error inspection.

Wrap-up

For idempotent POST uploads in Flask backed by PostgreSQL, lean on the database. Enforce a UNIQUE key for the natural identity, use INSERT … ON CONFLICT … DO NOTHING RETURNING to learn whether a row was created, and translate that to 201 for the first time and 200 for retries. The result is a small, robust endpoint that behaves well under network retries without adding queues, caches, or external services.