Data manipulation

This page shows how to read and write records with the methods of the Python SDK that write the SurrealQL statement for you: select(), create(), insert(), update(), upsert(), delete() and relate(). Each method takes a target that names a record or a table, and returns a builder that you can refine with clauses such as .where() and .merge(). To write the statements yourself, see Executing queries.

API references

Targeting records and tables

The first argument of each method is its target. The type of the target decides which records the statement works on:

For a table name, Table("person") and "person" do the same thing. insert() is the one exception: it accepts a plain string only when the name is a simple identifier, so a name such as my-table needs Table("my-table").

To work on a range of records, give RecordID a Range as its id. BoundIncluded and BoundExcluded set whether each end is part of the range, and None leaves an end open. This selects post:1 and post:2:

from surrealdb import BoundExcluded, BoundIncluded, Range, RecordID

posts = db.select(
    RecordID("post", Range(BoundIncluded(1), BoundExcluded(3)))
).execute()

The id in a RecordID must have the same type as the id of the stored record. RecordID("post", 1) and RecordID("post", "1") name two different records, and a record whose id is a UUID needs a uuid.UUID, not a string with the same characters. RecordID.parse("post:1") always reads the id as a string.

import uuid
from surrealdb import RecordID

account_id = uuid.UUID("019d764a-c692-7631-a182-de9d745eb127")

db.select(RecordID("account", account_id)).execute()  # The record
db.select(RecordID("account", str(account_id))).execute()  # None

Running a builder

On a blocking connection, a write that you give its data runs at once and returns the result. That applies to create(target, data), update(target, data), upsert(target, data), insert(table, data) and relate(from_, edge, to, data). Every other call returns a builder, which sends nothing until you run it in one of three ways:

• •

  .execute() runs the statement and returns its result.

• •

  A data clause, such as .content(data) or .merge(data), sets the data and runs the statement.

• •

  Iterating the builder runs the statement and gives you the records one by one.

select() and delete() always return a builder, and so do the writes without data, such as update(target). On an asynchronous connection, every call returns a builder, and await runs it.

from surrealdb import RecordID, Surreal

with Surreal("ws://localhost:8000") as db:
    db.signin({"username": "root", "password": "secret"})
    db.use("surrealdb", "docs")

    # Given its data, a write runs at once
    db.create(RecordID("person", "tobie"), {"name": "Tobie", "age": 35})

    # Without data, a write returns a builder, and a data clause runs it
    db.create(RecordID("person", "jaime")).content(
        {"name": "Jaime", "age": 28}
    )

    # A read returns a builder, and .execute() runs it
    print(db.select(RecordID("person", "tobie")).execute())

{'age': 35, 'id': RecordID(table_name=person, record_id='tobie'), 'name': 'Tobie'}

A builder that is never run sends nothing, and the SDK warns about it, as Executing queries describes. The result of each call depends on its target:

In this table, record is a RecordID or a StringRecordId, and table is a Table or a plain string. A range target returns a list, as a table does, although a type checker sees the same type as for one record. The data clauses give the same shapes as the call they are part of: update(record).merge(data) returns the record or None.

Selecting records

db.select() reads one record, a range of records or every record of a table.

from surrealdb import RecordID, Table

tobie = db.select(RecordID("person", "tobie")).execute()
people = db.select(Table("person")).execute()

print(tobie["name"])
print(len(people))

Tobie
2

fields= names the fields to return, so the server sends only those. A dot reaches into a nested object, as in "address.city". The id field is returned only when you name it.

print(db.select(RecordID("person", "tobie"), fields=["name", "age"]).execute())

{'age': 35, 'name': 'Tobie'}

Modifiers add clauses to the statement. .where() takes a SurrealQL condition, and a dictionary of variables that the condition refers to as $name. .limit() returns at most that number of records, and .start() skips that number of records first:

older = (
    db.select(Table("person"), fields=["name"])
    .where("age > $min_age", {"min_age": 30})
    .limit(10)
    .execute()
)

print(older)

[{'name': 'Tobie'}]

The CRUD builder has no modifier for ORDER BY. To sort the records, write the statement with query().

.fetch() replaces each record link at the field paths that you name with the record that the link points to. When a post holds its author as a link such as person:tobie, this returns the post with the whole person record in its author field:

post = db.select(RecordID("post", 1)).fetch("author").execute()

.version() reads the records as they were at a point in time. It needs a datastore that keeps versions, such as SurrealKV with versioning (surrealkv+versioned://), and other datastores raise ConfigurationError.

from datetime import datetime, timezone

people = (
    db.select(Table("person"))
    .version(datetime(2026, 1, 1, tzinfo=timezone.utc))
    .execute()
)

A modifier that the statement does not take raises SurrealError when you call it, for example .limit() on a create() builder. The CRUD builder reference lists which statements take each modifier.

Creating records

db.create() creates one record and returns it. A RecordID target sets the id, and a table target lets the server generate one:

alice = db.create(Table("person"), {"name": "Alice", "age": 30})

print(alice)

{'age': 30, 'id': RecordID(table_name=person, record_id='ulx1dgodvo1yyo4ftwv2'), 'name': 'Alice'}

Creating a record that already exists raises AlreadyExistsError. To create a record or change it when it exists, use upsert().

Inserting records

db.insert() inserts one record or a list of records into a table, and returns a list. A record without an id gets a generated one.

posts = db.insert(Table("post"), [
    {"id": 1, "title": "Hello", "author": RecordID("person", "tobie")},
    {"id": 2, "title": "Graphs", "author": RecordID("person", "jaime")},
])

print(len(posts))

2

To insert edge records, pass relation=True, or call .relation() on the builder. The SDK then sends INSERT RELATION, and each record needs an in and an out field that name the two records it connects:

db.insert(
    Table("likes"),
    [
        {"in": RecordID("person", "jaime"), "out": RecordID("post", 1)},
        {"in": RecordID("person", "tobie"), "out": RecordID("post", 2)},
    ],
    relation=True,
)

Replacing records

db.update(target, data) replaces the whole content of each record with data, so the fields that data leaves out are removed. The record keeps its id.

jaime = db.update(RecordID("person", "jaime"), {"name": "Jaime"})

print(jaime)

{'id': RecordID(table_name=person, record_id='jaime'), 'name': 'Jaime'}

The age field is gone, because data did not name it. db.update(target).replace(data) replaces the content as well, with the SurrealQL REPLACE clause, which raises an error when data changes a READONLY field. An update of a record that does not exist returns None and creates nothing.

Merging data into records

.merge(data) changes the fields that data names and keeps every other field. A nested object in data merges at every level, and an array replaces the stored array.

jaime = db.update(RecordID("person", "jaime")).merge({"age": 29})

print(jaime)

{
    'age': 29,
    'id': RecordID(table_name=person, record_id='jaime'),
    'name': 'Jaime',
}

With a table target, the update changes every record of the table. .where() limits it to the records that match:

seniors = (
    db.update(Table("person"))
    .where("age > $min_age", {"min_age": 30})
    .merge({"senior": True})
)

print([person["name"] for person in seniors])

['Tobie']

Applying JSON Patch operations

.patch() applies a list of JSON Patch operations. Each operation is a dictionary with an op, a path and, for most operations, a value. The operations are add, remove, replace, move, copy and test.

tobie = db.update(RecordID("person", "tobie")).patch([
    {"op": "replace", "path": "/age", "value": 36},
    {"op": "add", "path": "/tags", "value": ["admin"]},
])

print(tobie["age"], tobie["tags"])

36 ['admin']

Upserting records

db.upsert() creates the record when it does not exist, and replaces its content when it does. It takes the same data clauses as update(), so upsert(target).merge(data) creates the record or merges data into it.

db.upsert(RecordID("person", "ana"), {"name": "Ana", "age": 41})
ana = db.upsert(RecordID("person", "ana")).merge({"active": True})

print(ana["name"], ana["age"], ana["active"])

Ana 41 True

Deleting records

db.delete() deletes a record, a range of records or the records of a table, and returns what it deleted. A record target returns the deleted record, or None when it did not exist.

deleted = db.delete(RecordID("person", "ana")).execute()

print(deleted["name"])

Ana

A table target deletes every record of the table, so add .where() to delete only some of them: db.delete(Table("person")).where("age < $min_age", {"min_age": 18}).execute(). Against SurrealDB 3.0 or later, deleting from a table that does not exist raises NotFoundError.

Relating records

db.relate() creates an edge record from one record to another, the same as the SurrealQL RELATE statement. The edge argument is the name of the edge table, a Table, or a RecordID to choose the id of the edge. The ends must be RecordID or StringRecordId values: a plain string raises TypeError, because it is a table name.

edge = db.relate(
    RecordID("person", "tobie"),
    "wrote",
    RecordID("post", 1),
    {"written_at": "2026-11-04"},
)

print(edge["in"], edge["out"])

person:tobie post:1

Without data, relate() returns a builder, which you run with .execute() or .content(data). Either end can be a list of records, which relates every pair and returns a list of edges:

edges = db.relate(
    [RecordID("person", "tobie"), RecordID("person", "jaime")],
    "bookmarked",
    RecordID("post", 2),
).execute()

print(len(edges))

2

To read the records at the other end of the edges, use a graph query, such as SELECT VALUE ->wrote->post.title FROM ONLY person:tobie, with query().

Choosing what a write returns

.output() sets what a write returns, with the SurrealQL RETURN clause. It takes one of these modes, or a list of field paths:

On a blocking connection, a data clause runs the statement, so call .output() before it:

diff = db.update(RecordID("person", "tobie")).output("diff").merge({"age": 37})

print(diff)

[{'op': 'replace', 'path': '/age', 'value': 37}]

On an asynchronous connection, nothing runs until await, so the order of the calls does not matter: await db.update(target, data).output("diff") works as well. "none" saves the server from sending back records that you do not need, for example in a large insert().

Setting a statement timeout

.timeout() makes the server stop the statement when it takes longer than the duration that you give, with the SurrealQL TIMEOUT clause. It takes a Duration, a datetime.timedelta, a duration string such as "5s", or a whole number of seconds as an int. A statement that takes too long raises QueryError, and its is_timed_out property is True:

from surrealdb import QueryError

try:
    people = db.select(Table("person")).timeout("2s").execute()
except QueryError as e:
    if e.is_timed_out:
        print("The statement took longer than", e.timeout)

This is a limit on the server. How long the client waits for a reply is a separate setting, which you change with db.timeout().

Mapping records onto models

into= maps each record onto a class: a dataclass, a pydantic BaseModel, or any class that takes the fields as keyword arguments. A record target gives one instance or None, and a table target gives a list. select(), create(), insert(), update(), upsert() and delete() take into= as a keyword argument.

from dataclasses import dataclass

@dataclass
class Person:
    id: RecordID
    name: str

tobie = db.select(
    RecordID("person", "tobie"),
    fields=["id", "name"],
    into=Person,
).execute()

print(tobie)

Person(id=RecordID(table_name=person, record_id='tobie'), name='Tobie')

A record with a field that the class does not accept raises UnexpectedResponseError, which names the fields of the record and of the class. Select only the fields that the class declares, as fields= does above, or add the other fields to the class. The id field is returned only when you name it, so a class with an id field needs "id" in fields=.

Reading a large table record by record

Iterating a builder runs its statement and gives you one record at a time. Against SurrealDB 3.3.0 or later over a WebSocket, the records arrive as the server produces them, so the SDK does not hold the whole table in memory.

for person in db.select(Table("person"), fields=["name"]):
    print(person["name"])

.rows(into=Person) does the same and maps each record onto a model. Streaming results explains how to stop early, and what happens against older servers and other connection types.

Learn more

• •

  Surreal API reference for the signatures of the methods on this page

• •

  Builders for every data clause and modifier

• •

  RecordID and Table for the target types

• •

  Executing queries for statements that you write yourself

• •

  SurrealQL statements for the statements that these methods send