# 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, while executing the same statement 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#explicit-edge-record-ids).

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