Mutations

Surqlize has a query builder for each SurrealQL write statement: create, insert, update, upsert and delete. Creating graph edges with relate is covered in edges and graph.

Like select, each one is an immutable builder that runs when you await it. A write resolves to an array unless you add .only().

The examples on this page use the tables below, with db created as described in connections.

import { orm, t, table } from "surqlize";

const user = table("user", {
    name: t.object({ first: t.string(), last: t.string() }),
    email: t.string(),
    age: t.number(),
    role: t
        .union([t.literal("admin"), t.literal("moderator"), t.literal("member")])
        .default("member"),
    tags: t.array(t.string()).default([]),
    created: t.date().default(() => new Date()),
    lastSeen: t.option(t.date()),
});

const post = table("post", {
    title: t.string(),
    content: t.string(),
    author: t.record("user"),
    tags: t.array(t.string()).default([]),
    created: t.date().default(() => new Date()),
});

const pageview = table("pageview", {
    count: t.number(),
    lastViewed: t.date(),
});

const db = orm(surreal, user, post, pageview);

How to write data

The statements that write a record share the same methods for supplying data. They differ in what the data replaces and in how strictly the type checker treats missing fields.

On create, only .content() requires the full record. Its input type is every field of the table, except id and computed fields, and except the fields that can be left out: fields with a .default() and option<...> fields. The other methods mirror SurrealQL and leave the rest to the database. Use .content() when you want the compiler to enforce required fields.

A query can use one of these methods only. Calling .content() after .set() throws an OrmError.

With runtime validation turned on, .patch() is not checked, and the other methods are checked as described on that page.

Without runtime validation, a create() that leaves out a required field still succeeds, and the record is stored as written. A later select() of that record throws a TypeParseError, because select() parses each record in full. Runtime validation rejects such a write before it is sent. See what a write returns for how each method types its result.

Create

create() makes a new record with a generated id, or with the id you pass as the second argument. Fields with a .default() that you leave out are filled in, except by .replace() and .patch(). See schema for how defaults work.

import { RecordId } from "surrealdb";

const [alice] = await db.create("user", "alice").content({
    name: { first: "Alice", last: "Smith" },
    email: "alice@example.com",
    age: 30,
});

const hello = await db.create("post").only().content({
    title: "Hello world",
    content: "First post",
    author: new RecordId("user", "alice"),
});

const [ada] = await db.create("user", "ada").set({
    name: { first: "Ada", last: "Lovelace" },
    email: "ada@example.com",
    age: 36,
});

Add .only() to get one object instead of an array.

const grace = await db.create("user", "grace").only().set({
    name: { first: "Grace", last: "Hopper" },
    email: "grace@example.com",
    age: 85,
});
// grace: the user record, not an array

Insert

insert() writes one record or many in a single INSERT statement. Pass the data as an object or an array of objects, or use .fields() and .values() for the tuple form. The data argument is not type-checked at compile time, so turn on runtime validation to check it.

await db.insert("user", {
    name: { first: "Alice", last: "Jones" },
    email: "alice.jones@example.com",
    age: 30,
});

await db.insert("user", [
    { name: { first: "Bob", last: "Jones" }, email: "bob@example.com", age: 25 },
    { name: { first: "Charlie", last: "Brown" }, email: "charlie@example.com", age: 28 },
]);

await db
    .insert("user")
    .fields(["name", "email", "age"])
    .values(
        [{ first: "Dana", last: "White" }, "dana@example.com", 41],
        [{ first: "Eve", last: "Black" }, "eve@example.com", 33],
    );

Handling conflicts

When a record with the same id already exists, insert() rejects with the SDK's AlreadyExistsError. .ignore() skips the record without an error instead, and .onDuplicate() updates it. .onDuplicate() takes the same values as .set(), including the += and -= operators. A query cannot use both.

await db
    .insert("user", {
        id: "ada",
        name: { first: "Ada", last: "Lovelace" },
        email: "ada@example.com",
        age: 36,
    })
    .ignore();

await db
    .insert("user", {
        id: "ada",
        name: { first: "Ada", last: "Lovelace" },
        email: "ada@example.com",
        age: 36,
    })
    .onDuplicate({
        age: { "+=": 1 },
        lastSeen: new Date(),
    });

Update

update() changes one record, selected by id, or every record that matches a where(). Without either, it changes every record in the table. update() does not apply .default() values.

await db.update("user", "ada").set({ age: 37 });

await db
    .update("user")
    .where((user) => user.age.lt(18))
    .set({ role: "member" });

await db.update("user", "ada").merge({ email: "ada@example.org" });

.content() replaces the whole record, and .patch() applies JSON Patch operations. .unset() removes fields.

await db.update("user", "ada").content({
    name: { first: "Ada", last: "Lovelace" },
    email: "ada@example.com",
    age: 37,
    role: "member",
    tags: [],
    created: new Date(),
});

await db.update("user", "ada").patch([
    { op: "replace", path: "/age", value: 38 },
    { op: "remove", path: "/lastSeen" },
]);

await db.update("user", "ada").set({ age: 39 }).unset(["lastSeen"]);

SurrealQL does not allow SET and UNSET in the same statement, so when .unset() follows .set(), Surqlize writes the unset fields as field = NONE inside the SET clause.

Operators

The += and -= operators change a value in the database, so you do not read it first. They work on numbers and arrays.

await db.update("user", "ada").set({
    age: { "+=": 1 },
    tags: { "+=": ["developer"] },
});

await db.update("user", "ada").set({
    tags: { "-=": ["developer"] },
});

Dotted keys in set()

.set() accepts dotted keys such as "name.first". A dotted key changes one field of a nested object and leaves the other fields of that object as they are.

await db.update("user", "ada").set({ "name.first": "Augusta" });

await db.create("user", "byron").set({
    name: { first: "George", last: "Byron" },
    email: "byron@example.com",
    age: 36,
});

On create() and relate(), the parent object must be set in full in the same call, as the name object is in the second query. A dotted key without its parent in the same call is a type error. At runtime, .set() throws an OrmError at the call, before anything is sent.

On update() and upsert(), a dotted key is accepted even when the parent object is missing from the stored record. The query then stores a partial object. Reading the record back with the full schema throws a TypeParseError, so set the whole object to repair it.

Upsert

upsert() creates the record if it does not exist and updates it if it does. It takes the same data methods and where() as update(), except that where() takes a callback only. Like update(), it does not apply .default() values.

await db.upsert("pageview", "homepage").set({
    count: { "+=": 1 },
    lastViewed: new Date(),
});

await db
    .upsert("user")
    .where((user) => user.email.eq("alice@example.com"))
    .set({ lastSeen: new Date() });

When no record matches the where() condition, upsert() creates one.

Delete

delete() removes a record by id, or every record that matches a where(). Chained where() calls are combined with AND, and clearWhere() removes them all. Without .return(), delete() resolves to an empty array, or to undefined with .only(). With .return(), it returns the deleted rows.

await db.delete("user", "alice");

await db.delete("user").where((user) => user.age.lt(13));

With .only() and .return("before"), delete() gives back the deleted record as a single object. The record is read as it was before the delete, so only id is guaranteed to be present, as described in what a write returns.

const deleted = await db.delete("user", "grace").only().return("before");
// deleted: the user record that was removed, with id always present

A projection reads the deleted record as $before. Its fields are typed from the projection, as with select.

const removed = await db
    .delete(hello.id)
    .return((post) => ({ title: post.title, author: post.author }));

Choosing what a write returns

create, update, upsert and insert return the record after the change by default. delete returns no rows by default: an empty array, or undefined with .only(). .return() takes a mode or a projection.

await db.update("user", "ada").set({ age: 38 }).return("none");

const before = await db.update("user", "ada").set({ age: 39 }).return("before");

const projected = await db
    .update("user", "ada")
    .set({ age: 40 })
    .return((user) => ({ name: user.name, age: user.age }));

Every write builder also takes .timeout(), which adds the TIMEOUT clause that the server enforces. To limit how long the client waits, see cancellation, timeouts and retries.

What a write returns

A write returns the fields it knows are present, and the result type follows what was written.

A field whose value may be undefined, either an optional key or a value typed T | undefined, counts as not written, so it is typed as optional. A .set() that passes a field as undefined does not apply the .default() of that field, because the key was supplied.

.unset() removes the unset fields from the result type. A .return() projection can read only the fields that the result is typed with. After .onDuplicate(), an insert may return a record that was updated instead of inserted, so only id is typed as present. A table linked to a class returns instances of its class.

const [babbage] = await db.create("user", "babbage").set({
    name: { first: "Charles", last: "Babbage" },
});
// babbage: id, name, role, tags, created and lastSeen
// email and age are not typed as present, because the write did not set them

Next steps