SurrealDB Upserts and Schema Type Validation: Why Your Update Created a Duplicate

An Upsert That Inserts, and an Insert That Fails

Two complaints come up constantly when teams adopt SurrealDB: an upsert that was supposed to update a record quietly creates a second one, and a write that looks perfectly reasonable gets rejected with a type error. Both behaviors are usually correct from the database's point of view. They hinge on two things you control: whether you address the record by its full record ID, and whether your field definitions match the shape of the data you send.

The takeaway: in SurrealQL, an upsert-style write only updates when you target a specific record ID such as person:alice. Without that ID, you get a new record every time. And once you define field types with DEFINE FIELD, the server enforces them on every write — there is no silent coercion.

How Upsert Actually Works in SurrealQL

SurrealQL has an UPSERT statement (available in the 1.x line; check the changelog for your exact version, since statement availability has evolved). Its behavior is straightforward: if the record ID you name exists, its content is merged or replaced according to the clause you use; if it does not exist, the record is created. The record ID is the entire decision mechanism — there is no separate "match on this column" logic.

-- Run in the SurrealDB SQL shell (surreal sql) or via the HTTP /sql endpoint.
-- Permissions: a user/scope with write access to the target namespace, database and table.
-- Placeholders: person:alice is a concrete record ID; swap in your own table and ID.

-- Creates the record if missing, updates it if present.
UPSERT person:alice CONTENT {
    name: 'Alice',
    age: 30
};

-- Re-running with the same ID updates in place; no duplicate is created.
UPSERT person:alice MERGE {
    age: 31
};

Expected check: after both statements, SELECT * FROM person:alice; should return exactly one record with age: 31. If you instead run INSERT INTO person { name: 'Alice' }; without an ID, SurrealDB generates a random ID and creates a brand-new record — repeat that statement and you get duplicates by design.

Schema Enforcement with DEFINE FIELD

Type validation is not automatic; it appears when you declare a schema. In SurrealQL you do this with DEFINE TABLE and DEFINE FIELD, where each field gets a TYPE clause. From that point on, inserts and updates that violate the declared type are rejected with an error instead of being coerced.

-- Define a schemaless-by-default table, then constrain fields.
DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD name ON TABLE person TYPE string;
DEFINE FIELD age  ON TABLE person TYPE int;

-- Accepted: types match.
UPSERT person:bob CONTENT { name: 'Bob', age: 35 };

-- Rejected: 'thirty-five' is a string, not an int.
UPSERT person:bob CONTENT { name: 'Bob', age: 'thirty-five' };

Expected check: the second upsert should fail with a field-type error mentioning the age field, and SELECT * FROM person:bob; should still show the original integer value. Note that SCHEMAFULL also rejects writes containing fields you have not defined, which is a separate (and often surprising) constraint from type checking.

The Trade-offs

• ID-driven semantics: because upsert matches only on the record ID, "natural key" upserts (e.g., by email) require you to either use the natural key inside the ID (person:['[contact removed]']) or use a unique index plus an explicit UPDATE ... WHERE. There is no ON CONFLICT (column) equivalent.
• Error messages: validation errors name the field but can be terse about nested structures. Keeping field definitions close to your application models reduces debugging time.
• Version drift: SurrealQL statements and type names have changed across releases. The examples above assume a 1.x server; verify against the documentation for your deployed version before relying on a specific clause such as MERGE on UPSERT.

A Five-Minute Verification

1. Start the SQL shell against a throwaway namespace and database.
2. Define the person table and typed fields as above.
3. Run the same UPSERT person:alice ... twice and confirm SELECT * FROM person; returns one row.
4. Run an ID-less INSERT INTO person twice and confirm it creates two rows with random IDs.
5. Attempt a type-mismatched write and confirm it errors rather than coercing the value.

If all five checks pass, your deployment enforces the semantics described here and you can build idempotent writes on top of explicit record IDs. If any check fails, the version or permission configuration of your server is the first place to look.