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, and executing the same statement again 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;

Alternatively, from version 3.1.5, you can supply an explicit edge record ID in the RELATE path (for example ->likes:[person:one, post:one]->) or in an INSERT RELATION statement.

A duplicate explicit ID on INSERT RELATION returns an error unless you add ON DUPLICATE KEY UPDATE. For more, see Explicit edge record IDs.

4. Getting a record, or creating it if it does not exist

When a record is reached through a relation rather than a known ID, such as the account that belongs to a phone number, UPSERT cannot find it by ID. A defined function can return the existing record, or create it and its relation if there is none. A function body runs as one transaction, so the new record and the relation are written together or not at all:

-- One account per phone number
DEFINE INDEX one_account_per_mobile ON identity_has FIELDS out UNIQUE;

DEFINE FUNCTION fn::account_for_mobile($mobile: record<mobile>) -> record<account> {
    LET $existing = $mobile<-identity_has<-account;
    IF $existing {
        RETURN $existing[0];
    };
    UPSERT $mobile;
    LET $account = CREATE ONLY account RETURN VALUE id;
    RELATE $account->identity_has->$mobile;
    RETURN $account;
};

-- Both calls return the same account
fn::account_for_mobile(mobile:176823181);
fn::account_for_mobile(mobile:176823181);

The UNIQUE index covers the case where two calls for the same number run at the same time. Both find no account, but only the first RELATE succeeds. The second fails on the index, and its transaction rolls back, including the account it created, so the caller can retry and receive the existing account.

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";