ThisCalculator

Everyday

Unique Index for Safe API Retries

Updated · Standard formula, shown below

This guide explains how adding a unique index to your database table makes an API endpoint safe to retry, preventing duplicate records when a client sends the same request more than once. You will learn the concept, the exact steps to implement it, and common pitfalls to avoid.

What You’ll Need

  • A database table that stores the data your API endpoint writes to (e.g., orders, payments, or messages).
  • Access to run SQL commands on that database (e.g., via a migration tool, database console, or ORM).
  • An API endpoint that currently inserts a new row on each request, and you want to make it idempotent.
  • A unique identifier that can be sent by the client with each request (e.g., an Idempotency-Key header or a client-generated UUID).

Step-by-Step

  1. Choose a unique key. Decide what will uniquely identify a single logical operation. This is often a client-generated UUID (like an order ID) or an idempotency key that the client sends in a header. The key must be the same for retries of the same operation, and different for distinct operations.
  2. Add a column to your table to store this key. If you don’t already have one, add a column (e.g., idempotency_key or request_id) to the table that the API writes to. It should be a string type (VARCHAR) with a sufficient length (e.g., 36 characters for a UUID).
  3. Create a unique index on that column. Run a SQL command like: CREATE UNIQUE INDEX idx_unique_idempotency_key ON your_table (idempotency_key); This enforces that no two rows can have the same value in that column.
  4. Modify your API endpoint to use the unique key. In your endpoint’s logic, extract the idempotency key from the request (e.g., from a header or request body). If it is missing, you can either reject the request or generate one (but then retries won’t work unless the client sends the same generated key).
  5. Attempt to insert the record, catching duplicate key errors. In your database code, perform an INSERT that includes the idempotency key. When a retry occurs, the insert will fail with a unique constraint violation (e.g., error code 1062 in MySQL, or a unique violation in PostgreSQL). Catch this error.
  6. On duplicate key error, fetch and return the existing record. In the catch block, query the database for the row with that idempotency key, and return it as the response. This way, the client receives the same result as the original request, without creating a duplicate.
  7. Return a consistent response. Ensure that both the successful insert and the duplicate-key path return the same HTTP status code (e.g., 200 or 201) and the same body (the created resource). Some APIs use 200 for both to indicate the operation succeeded, even if it was a retry.
  8. Test with retries. Send the same request twice (with the same idempotency key) and verify that only one row is created, and that both responses are identical.

Common Mistakes

  • Using a key that is not unique per operation. If the key is the same for two different operations (e.g., a user ID), the unique index will block legitimate inserts. The key must be unique per logical operation.
  • Not handling the duplicate key error properly. If you don’t catch the error, the API will return a 500 error on retries, which defeats the purpose. You must catch it and return the existing record.
  • Forgetting to make the column NOT NULL. If the idempotency key column is nullable, multiple rows with NULL will be allowed (since NULLs are not equal in most databases), so retries without a key will still create duplicates. Make the column NOT NULL.
  • Using a non-unique index. A regular (non-unique) index does not prevent duplicates; it only speeds up lookups. You must use a UNIQUE index.
  • Assuming the client always sends the key. If the client omits the key, your endpoint might create a new row each time. Decide how to handle missing keys (e.g., reject the request or generate a key, but then retries won’t be safe).

Tips

  • Use a UUID or a random string as the idempotency key. This avoids collisions and is easy to generate on the client.
  • Store the key in a separate column, not in the primary key. This allows you to keep an auto-increment primary key if you need one, while still enforcing uniqueness.
  • Consider adding a timestamp column to track when the record was created, which can help with debugging or cleanup.
  • If using an ORM, look for built-in support for idempotency (e.g., Django’s get_or_create or Rails’ find_or_create_by) that can simplify the logic.
  • Document the idempotency key requirement in your API documentation so clients know to send it.

Troubleshooting

  • Retry still creates a duplicate. Check that the unique index actually exists (run SHOW INDEX FROM your_table in MySQL or \d your_table in PostgreSQL). Also verify that the column is NOT NULL and that you are actually sending the same key on retries.
  • Getting a 500 error on retry. This usually means the duplicate key error is not being caught. Check your error handling code to ensure it catches the specific database error for unique violations.
  • Unique index blocks legitimate inserts. This happens if the key is not unique per operation. Double-check how you generate the key. For example, if you use a timestamp with second precision, two operations in the same second will collide. Use a UUID or a high-resolution timestamp.
  • NULL values causing duplicates. If the key column is nullable, multiple rows with NULL are allowed. Make the column NOT NULL and ensure your code always sets a value.
  • Race condition between checking and inserting. If you first SELECT to see if the key exists, then INSERT, two concurrent requests could both see no existing row and both insert. The unique index is what prevents this, so rely on the insert failing rather than a pre-check.

Frequently Asked Questions

  1. Can I use a unique index on a composite of two columns? Yes, you can create a unique index on multiple columns, e.g., (user_id, idempotency_key), which would allow the same key for different users.
  2. What if my API uses a different idempotency key for the same operation? Then the unique index won’t help, because the keys differ. The client must send the same key for retries.
  3. Do I need to remove the unique index later? No, you can keep it permanently. It ensures data integrity and is a small overhead on inserts.
  4. Is a unique index enough to make an endpoint idempotent? Yes, for the common case of preventing duplicate inserts. But if your endpoint also updates other data (e.g., a counter), you may need additional logic (like conditional updates) to be fully idempotent.
  5. What if the client sends the same key but a different payload? The unique index will prevent the second insert, and you will return the original record. This is usually the desired behavior, but you may want to return an error if the payload differs.
  6. Can I use this pattern with a NoSQL database? Yes, many NoSQL databases support unique constraints or you can use a deterministic key as the primary key (e.g., in DynamoDB, use the idempotency key as the partition key).

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.