> Full SurrealDB documentation index: https://surrealdb.com/docs/llms.txt

# Idempotent operations

Make SurrealQL idempotent with UPSERT, safe deletes and unique indexes so retries do not corrupt data.

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.

```surql
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.

```surql
DELETE user:123;
```

### 3. Creating relations (`RELATE`)

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

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

However, it can be made idempotent by first defining a [unique index](/docs/reference/query-language/statements/define/indexes.md#composite-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.

```surql
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`](/docs/reference/query-language/statements/insert.md#insert-relation-tables) 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](/docs/reference/query-language/statements/relate.md#handling-duplicate-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](/docs/reference/query-language/statements/define/function.md) 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:

```surql
-- 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.

```sql
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:

```text
if user exists:
  update
else:
  create
```

You can simplify it with a single idempotent query.

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