Idempotent operations

Idempotency is a key concept when building reliable applications, especially APIs, event-driven systems, and distributed architectures.

In simple terms, an operation is idempotent if running it multiple times produces the same result as running it once.

Fun fact: idempotent comes from the Latin word idem which means "same" (while "potent" means to have power). Most readers have seen this word in its abbreviated form at the bottom of academic journals:

• United States v. Martinez-Fuerte, 428 U.S. 543, 545 (1976).

• Id. at 547.

Why idempotency matters

In real-world systems, requests can be retried, messages can be delivered more than once, and network failures can interrupt operations.

However, if your database queries are idempotent then you can safely retry them without corrupting data.

Idempotent patterns in SurrealQL

1. Using UPSERT

UPSERT is the most common way to ensure idempotency.

UPSERT user:123 SET name = "Alice", age = 30;

The above query will create the record if it does not exist, and update it if it does exist. Running it multiple times will result in the same final state, at least for the fields name and age.

2. Deleting records

A DELETE statement will remove the record(s) on first execution, while executing the same statement will have no effect. A DELETE statement returns an empty array by default.

DELETE user:123;

3. Creating relations (RELATE)

The following statement is not idempotent on its own, as multiple relations can be created between records.

RELATE user:123->likes->post:456;

However, it can be made idempotent by first defining a unique index on the in and out fields. Doing so will result in the RELATE statement above always resulting in a single graph edge between the two records.

DEFINE INDEX only_one ON likes FIELDS in, out UNIQUE;

Non-idempotent examples

The following examples will produce different results on every execution, and thus are not idempotent.

UPDATE user:123 SET login_count += 1;
UPDATE user:123 SET tags += "new";

Using idempotency to simplify queries

Instead of writing application logic like this:

if user exists:
  update
else:
  create

You can simplify it with a single idempotent query.

UPSERT user:123 SET name = "Alice", status = "active";