21. Pattern: Upsert

The Upsert (Update or Insert) pattern allows a client to ensure a resource exists at a target URI or under a specific unique identifier in a single, atomic HTTP operation.

If the resource does not exist, the API creates it; if it already exists, the API updates or replaces it. This eliminates the need for clients to perform “check-then-act” operations (GET followed by POST or PUT), avoiding race conditions and making integration code dramatically simpler and resilient to retries.

21.1. Overview

In RESTful design, creating vs. updating a resource typically uses different HTTP methods: POST to append to a collection (server generates the ID) and PUT or PATCH to modify an existing resource at a known URI.

However, in many integration pipelines—such as data synchronization, ETL jobs, or systems where clients generate primary keys (e.g., UUIDs)—the client knows the desired resource identity in advance and wants an “update if present, insert if missing” contract.

The Upsert pattern can be implemented in two standard ways:

Idempotent PUT with Client-Defined URI (Standard REST): The client sends a PUT request directly to a target resource URI (PUT /customers/{customerId}). The server returns 201 Created if the resource was newly created, or 200 OK (or 204 No Content) if an existing resource was replaced.
POST with Unique or Composite Identifiers (Alternative): The client sends a POST request to a collection or upsert endpoint (POST /inventory-items) containing a natural unique key or composite identifier (e.g., warehouseId + sku). The server inspects the keys and returns 201 Created when creating a new record or 200 OK when updating an existing record.

21.2. When to Use Upsert

Use Upsert when:

Clients generate unique resource identifiers.
- e.g., Clients generate UUIDs locally prior to sending requests, enabling safe offline creation and background synchronization.
You are building data sync, import, or ETL integrations.
- External systems syncing records periodically should not need to check whether each record already exists before sending updates.
You need strict retry safety over unreliable networks.
- Upsert operations using PUT are idempotent; network retries will not create duplicate records or fail with conflict errors.
You want to eliminate “check-then-act” race conditions.
- A client executing GET /resources/123 to check existence before issuing POST or PUT invites concurrency bugs if another client creates the resource in between the two calls.

21.3. When NOT to Use Upsert

Avoid Upsert when:

Resource identifiers must be generated sequentially by the server.
- If the database auto-increments primary keys and the client cannot predict the ID, use standard POST collection creation.
Performing partial updates.
- A standard PUT upsert requires sending the complete resource representation. If a client only intends to update a subset of fields on an existing record without replacing the entire object, use PATCH.
Creation and modification trigger vastly different business side-effects.
- If creating a resource sends a welcome email or triggers payment processing, but updating it merely changes a description, forcing both into a single endpoint can obscure business logic and authorization boundaries.

21.4. What the Pattern Looks Looks Like

Below are detailed HTTP interaction flows showing both the standard PUT approach and the alternative POST composite key approach for created (201) vs. updated (200) scenarios.

Option 1: Idempotent `PUT` with Client-Defined Identifier (Standard)

The client defines the exact URI (/customers/cust_99182).

Scenario A: Resource Does Not Exist (Created → `201 Created`)

Request

PUT /customers/cust_99182 HTTP/1.1
Host: api.example.com
Authorization: Bearer eyJhbGciOi...
Content-Type: application/json

{
  "email": "jane.doe@example.com",
  "fullName": "Jane Doe",
  "tier": "PREMIUM"
}

Response

HTTP/1.1 201 Created
Location: https://api.example.com/customers/cust_99182
Content-Type: application/json

{
  "id": "cust_99182",
  "email": "jane.doe@example.com",
  "fullName": "Jane Doe",
  "tier": "PREMIUM",
  "createdAt": "2026-08-03T14:00:00Z",
  "updatedAt": "2026-08-03T14:00:00Z"
}

Scenario B: Resource Already Exists (Replaced → `200 OK`)

The client issues the exact same request again at a later time with updated data.

Request

PUT /customers/cust_99182 HTTP/1.1
Host: api.example.com
Authorization: Bearer eyJhbGciOi...
Content-Type: application/json

{
  "email": "jane.doe@example.com",
  "fullName": "Jane Doe-Smith",
  "tier": "ENTERPRISE"
}

Response

HTTP/1.1 200 OK
Content-Type: application/json

{
  "id": "cust_99182",
  "email": "jane.doe@example.com",
  "fullName": "Jane Doe-Smith",
  "tier": "ENTERPRISE",
  "createdAt": "2026-08-03T14:00:00Z",
  "updatedAt": "2026-08-03T14:25:00Z"
}

Option 2: Alternative `POST` with Composite / Natural Identifiers

When URIs cannot easily encode composite keys, or when using a dedicated upsert endpoint, clients POST a payload containing unique matching fields (e.g., warehouseId + sku).

Scenario A: No Matching Record Found (Created → `201 Created`)

Request

POST /inventory-items HTTP/1.1
Host: api.example.com
Authorization: Bearer eyJhbGciOi...
Content-Type: application/json

{
  "warehouseId": "wh_east_01",
  "sku": "KEYBOARD-MX-01",
  "quantity": 150,
  "reorderLevel": 20
}

Response

HTTP/1.1 201 Created
Location: https://api.example.com/inventory-items/inv_30091
Content-Type: application/json

{
  "id": "inv_30091",
  "warehouseId": "wh_east_01",
  "sku": "KEYBOARD-MX-01",
  "quantity": 150,
  "reorderLevel": 20,
  "status": "CREATED"
}

Scenario B: Matching Record Found via Composite Key (Updated → `200 OK`)

A subsequent request sends the same warehouseId and sku combination.

Request

POST /inventory-items HTTP/1.1
Host: api.example.com
Authorization: Bearer eyJhbGciOi...
Content-Type: application/json

{
  "warehouseId": "wh_east_01",
  "sku": "KEYBOARD-MX-01",
  "quantity": 200,
  "reorderLevel": 25
}

Response

HTTP/1.1 200 OK
Content-Type: application/json

{
  "id": "inv_30091",
  "warehouseId": "wh_east_01",
  "sku": "KEYBOARD-MX-01",
  "quantity": 200,
  "reorderLevel": 25,
  "status": "UPDATED"
}

21.5. Anti-Patterns to Avoid

1. Returning `200 OK` when a resource was newly created

Returning 200 OK regardless of whether the resource was created or updated deprives the client of knowing if a new record lifecycle was initiated. Always return 201 Created with a Location header on creation, and 200 OK (or 204 No Content) on updates.

2. The “Check-Then-Act” client implementation

/* Anti-Pattern Client Flow */
1. GET /customers/cust_99182 -> 404 Not Found
2. POST /customers { "id": "cust_99182", ... }

Forcing clients to execute a GET read before deciding whether to send a POST or PUT creates a race condition in concurrent environments. Design the backend to handle upsert atomically.

3. Partial field wiping during `PUT` upserts

Executing a PUT upsert with an incomplete JSON body (e.g., omitting "tier") should nullify or reset the missing fields on an existing resource according to standard PUT replacement semantics. Do not treat PUT upserts as implicit partial PATCH updates.

4. Non-idempotent side effects inside `PUT` upserts

If a client issues the exact same PUT request three times due to network retries, the final state of the system and the returned object must be identical. Avoid side effects like incrementing counter fields (e.g., "loginCount": "loginCount + 1") inside an upserts request body.

21.6. OpenAPI Example

A complete OpenAPI 3.0.3 specification illustrating how to document both PUT upserts and POST composite key upserts with proper 201 Created and 200 OK response definitions.

openapi: 3.0.3
info:
  title: Upsert Pattern API Example
  version: 1.0.0
servers:
  - url: https://api.example.com

paths:
  /customers/{customerId}:
    put:
      summary: Upsert a customer record
      description: Creates a new customer if 'customerId' does not exist (201), or replaces the existing customer if it does (200).
      tags: [Customers]
      parameters:
        - in: path
          name: customerId
          required: true
          schema:
            type: string
            example: cust_99182
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/CustomerUpsertRequest'
      responses:
        '200':
          description: Customer existed and was successfully updated/replaced.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Customer'
        '201':
          description: Customer did not exist and was successfully created.
          headers:
            Location:
              schema:
                type: string
                example: "https://api.example.com/customers/cust_99182"
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Customer'

  /inventory-items:
    post:
      summary: Upsert inventory item by composite key
      description: Inspects composite key ('warehouseId' + 'sku'). Creates record (201) if absent, or updates record (200) if present.
      tags: [Inventory]
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/InventoryUpsertRequest'
      responses:
        '200':
          description: Item matched composite key and was updated.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/InventoryItem'
        '201':
          description: Item did not match existing keys and was created.
          headers:
            Location:
              schema:
                type: string
                example: "https://api.example.com/inventory-items/inv_30091"
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/InventoryItem'

components:
  schemas:
    CustomerUpsertRequest:
      type: object
      required:
        - email
        - fullName
        - tier
      properties:
        email:
          type: string
          format: email
          example: jane.doe@example.com
        fullName:
          type: string
          example: Jane Doe
        tier:
          type: string
          example: PREMIUM

    Customer:
      type: object
      properties:
        id:
          type: string
          example: cust_99182
        email:
          type: string
          example: jane.doe@example.com
        fullName:
          type: string
          example: Jane Doe
        tier:
          type: string
          example: PREMIUM
        createdAt:
          type: string
          format: date-time
        updatedAt:
          type: string
          format: date-time

    InventoryUpsertRequest:
      type: object
      required:
        - warehouseId
        - sku
        - quantity
      properties:
        warehouseId:
          type: string
          example: wh_east_01
        sku:
          type: string
          example: KEYBOARD-MX-01
        quantity:
          type: integer
          example: 150
        reorderLevel:
          type: integer
          example: 20

    InventoryItem:
      type: object
      properties:
        id:
          type: string
          example: inv_30091
        warehouseId:
          type: string
          example: wh_east_01
        sku:
          type: string
          example: KEYBOARD-MX-01
        quantity:
          type: integer
          example: 150
        reorderLevel:
          type: integer
          example: 20
        status:
          type: string
          example: CREATED

21.7. Visualizing Upsert (Mermaid Diagram)

sequenceDiagram
    autonumber
    actor Client
    participant API as API Server / Gateway
    participant DB as Database

    rect rgb(240, 248, 255)
    Note over Client,DB: Option 1: Standard PUT Upsert Flow
    Client->>API: PUT /customers/cust_99182 { email, fullName, tier }
    API->>DB: Query customer by ID "cust_99182"
    alt Record Not Found
        API->>DB: INSERT INTO customers (id, email, ...)
        DB-->>API: Insert Success
        API-->>Client: 201 Created (Location: /customers/cust_99182)
    else Record Found
        API->>DB: UPDATE customers SET email=..., fullName=... WHERE id="cust_99182"
        DB-->>API: Update Success
        API-->>Client: 200 OK (Updated Customer Object)
    end
    end

    rect rgb(255, 250, 205)
    Note over Client,DB: Option 2: POST Composite Key Upsert Flow
    Client->>API: POST /inventory-items { warehouseId: "wh_east", sku: "SKU-1" }
    API->>DB: Query inventory WHERE warehouse_id="wh_east" AND sku="SKU-1"
    alt Composite Key Not Found
        API->>DB: INSERT INTO inventory_items (id, warehouse_id, sku, ...)
        DB-->>API: Insert Success (Generated id: "inv_30091")
        API-->>Client: 201 Created (Location: /inventory-items/inv_30091)
    else Composite Key Found
        API->>DB: UPDATE inventory_items SET quantity=... WHERE id="inv_30091"
        DB-->>API: Update Success
        API-->>Client: 200 OK (Updated Inventory Object)
    end
    end

Pattern: Upsert

21. Pattern: Upsert

21.1. Overview

21.2. When to Use Upsert

21.3. When NOT to Use Upsert

21.4. What the Pattern Looks Looks Like

Option 1: Idempotent PUT with Client-Defined Identifier (Standard)

Scenario A: Resource Does Not Exist (Created → 201 Created)

Request

Response

Scenario B: Resource Already Exists (Replaced → 200 OK)

Request

Response

Option 2: Alternative POST with Composite / Natural Identifiers

Scenario A: No Matching Record Found (Created → 201 Created)

Request

Response

Scenario B: Matching Record Found via Composite Key (Updated → 200 OK)

Request

Response

21.5. Anti-Patterns to Avoid

1. Returning 200 OK when a resource was newly created

2. The “Check-Then-Act” client implementation

3. Partial field wiping during PUT upserts

4. Non-idempotent side effects inside PUT upserts

21.6. OpenAPI Example

21.7. Visualizing Upsert (Mermaid Diagram)

Option 1: Idempotent `PUT` with Client-Defined Identifier (Standard)

Scenario A: Resource Does Not Exist (Created → `201 Created`)

Scenario B: Resource Already Exists (Replaced → `200 OK`)

Option 2: Alternative `POST` with Composite / Natural Identifiers

Scenario A: No Matching Record Found (Created → `201 Created`)

Scenario B: Matching Record Found via Composite Key (Updated → `200 OK`)

1. Returning `200 OK` when a resource was newly created

3. Partial field wiping during `PUT` upserts

4. Non-idempotent side effects inside `PUT` upserts