HTTP 409 Conflict

Express + PostgreSQL: map unique violations to 409

PostgreSQL reports unique-constraint violations with SQLSTATE 23505. Turn that into a 409 with a problem-details body, not a 500.

app.post('/api/users', async (req, res, next) => {
  try {
    const user = await db.users.insert(req.body);
    res.status(201).location(`/api/users/${user.id}`).json(user);
  } catch (err) {
    if (err.code === '23505') { // unique_violation
      return res.status(409).type('application/problem+json').json({
        type: 'https://api.example.com/problems/duplicate-email',
        title: 'Email already registered',
        status: 409,
      });
    }
    next(err);
  }
});

Optimistic concurrency with a version number

Update only if the version still matches. If zero rows changed, someone else got there first: return 409 with the current version.

-- SQL
UPDATE docs SET title = $1, version = version + 1
WHERE id = $2 AND version = $3;   -- 0 rows updated -> 409

-- HTTP
PATCH /api/docs/42
{ "title": "New title", "version": 7 }

HTTP/1.1 409 Conflict
Content-Type: application/problem+json

{ "title": "Version conflict", "status": 409,
  "detail": "Document is at version 8; reload and reapply your change." }

Client: resolve, don't blindly retry

The same request will conflict again. Fetch the latest state, merge or ask the user, then resubmit. Kubernetes controllers written with client-go can wrap updates in retry.RetryOnConflict, which re-reads and retries.