Surreal ORM Lite

Python SurrealDB SDK License

Surreal ORM Lite is a lightweight, Django-style ORM for SurrealDB that uses the official SurrealDB Python SDK. It provides a simple and intuitive interface for database operations with full async support and Pydantic validation.

Why This Project?

This ORM is designed to:

Use the official SurrealDB SDK (surrealdb[pydantic]>=2.0.0,<3.0.0) for maximum compatibility
Stay lightweight with minimal dependencies
Keep up-to-date with SurrealDB and SDK releases
Provide Django-style query syntax that developers love

Requirements

Dependency	Version
Python	3.11+ (tested on 3.11 to 3.15)
SurrealDB	2.6.x / 2.7.x or 3.2.x / 3.3.x, or the SDK's embedded engine
Official SDK	surrealdb[pydantic]>=2.0.0,<3.0.0
Pydantic	>=2.13.4

Note: As of v0.7.0, Surreal ORM Lite targets the SurrealDB Python SDK 2.x (surrealdb[pydantic]>=2.0.0,<3.0.0), which supports the SurrealDB 3.x protocol. It is tested against SurrealDB v2.7.0 and v3.3.4; up to v0.19.0 the pins were v2.6.5 and v3.2.4. The 2.x line behaves identically on 2.7.0, and 3.3.0 differs from 3.2.4 in one server behaviour, listed in the behaviour table. SurrealDB 3.1.x is no longer a supported line as of v0.14.0 — the suite is still run against 3.1.5 as a backward-compatibility check, but a regression there does not block a release.

Installation

pip install surreal-orm-lite

Or with uv:

uv add surreal-orm-lite

Quick Start

1. Configure the Connection

from surreal_orm_lite import SurrealDBConnectionManager

SurrealDBConnectionManager.set_connection(
    url="http://localhost:8000",
    user="root",
    password="root",
    namespace="my_namespace",
    database="my_database",
)

2. Define a Model

from surreal_orm_lite import BaseSurrealModel
from pydantic import Field

class User(BaseSurrealModel):
    id: str | None = None
    name: str = Field(..., max_length=100)
    email: str
    age: int = Field(..., ge=0)

3. CRUD Operations

# Create
user = User(name="Alice", email="alice@example.com", age=30)
await user.save()

# Read
user = await User.objects().get("alice_id")
users = await User.objects().filter(age__gte=18).exec()

# Update
user.age = 31
await user.update()

# Or partial update
await user.merge(age=31)

# Delete
await user.delete()

4. QuerySet Methods

# Filter with Django-style lookups
users = await User.objects().filter(
    age__gte=18,
    name__startswith="A"
).exec()

# Ordering (with -field shorthand for DESC)
users = await User.objects().order_by("name").exec()
users = await User.objects().order_by("-age", "name").exec()

# Pagination
users = await User.objects().limit(10).offset(20).exec()

# Select specific fields
results = await User.objects().select("name", "email").exec()

# Get first result
user = await User.objects().filter(name="Alice").first()

# Get all records
all_users = await User.objects().all()

# Custom query
results = await User.objects().query(
    "SELECT * FROM User WHERE age > $min_age",
    {"min_age": 21}
)

What `select()` accepts

await Book.objects().select("title", "price").exec()        # field names
await Book.objects().select("title", "author.name").exec()  # [{"title": "Dune", "author": {"name": "Ada"}}, …]
await Book.objects().select("title", "author.*").exec()     # every field of the linked author
await Book.objects().select("*", "author.*").exec()         # every field, the author's record included

Each item is *, a field name (an aliased field by its Python name: its column is selected), a dotted path into a nested object or through a record link, or such a path followed by .*. Names are letters, digits and underscores, each part starting with a letter or an underscore, as in order_by() and values(). Anything else — an expression, a function call, AS, {…} or […] — raises ValueError naming the item when select() is called, before any request; write a computed projection with query() and bound parameters. Rows that do not validate as the model (a link read as a record, a required field left out) come back as dicts keyed by Python names. On a nested object that is not a link, "meta.*" gives the object on SurrealDB 3.x and its values as a list on 2.x and the embedded engine.

`limit()` and `offset()`

users = await User.objects().order_by("name").limit(10).offset(20).exec()
User.objects().limit(int(size))   # a size read as text: convert it first
User.objects().limit("10")        # TypeError: limit() takes a non-negative integer, got '10'
User.objects().offset(-1)         # ValueError: offset() takes a non-negative integer, got -1

Each takes a non-negative integer (0 reads no record; an IntEnum member or an integer type such as NumPy's int64 gives its value). A value that is not an integer — a bool, a str such as "10", a float, None — raises TypeError, and a negative one or one above 2**63 - 1 (SurrealQL's largest integer) ValueError, naming the method and the value, when limit() or offset() is called, before any request; the value in place is kept. The same holds wherever the queryset is read: exec(), first(), get(), values(…).annotate(…), cache(), as_of() and a subquery. The server may still refuse a large value when the read runs: SurrealDB 2.x and the embedded engine refuse one above 4294967295 (2**32 - 1), SurrealDB 3.x reads it.

Features

Feature	Status
Async/await support	✅
Pydantic validation	✅
CRUD operations	✅
QuerySet with filters	✅
Django-style lookups	✅
Custom primary keys	✅
HTTP connections	✅
WebSocket connections	✅
Aggregations	✅
GROUP BY	✅
Model Signals	✅
Raw SurrealQL queries	✅
Q Objects (OR/AND/NOT)	✅
Parameterized filters	✅
Bulk operations	✅
`-field` ordering	✅
Relations & Graph	✅
FETCH clause	✅
Transactions (`tx=`)	✅
upsert / get_or_create	✅
patch / atomic ops	✅
Retry on conflict	✅
Server-side functions	✅
Computed fields	✅
Stored `fn::` calls	✅
JWT / record auth	✅
Model-level auth mixin	✅
Field aliases & DX	✅
Live queries (raw)	✅
Typed live queries	✅
Live auto-resubscribe	✅
Change feeds (CDC)	✅
Typed edges (`insert_relation`)	✅
Custom table name	✅
Rich field types (datetime, date, duration, decimal, uuid, bytes, set, range)	✅
Geospatial fields (nearby, distance, spatial lookups)	✅
Embedded engine (mem://, surrealkv://) & test fixtures	✅
Time travel (`as_of()`, versioned storage)	✅
Subqueries (`Subquery`, a QuerySet as a filter value)	✅
Query cache (`cache()`, TTL, invalidated by ORM writes)	✅
Multiple databases (named connections, `using()`)	✅
Schema introspection (`DatabaseIntrospector`, `generate_models_from_db()`)	✅

Supported Filter Lookups

exact (default)
gt, gte, lt, lte
in, not_in (a list, or a Range bound as one value: see section 24)
contains, not_contains (contains on a range column tests membership)
containsall, containsany
startswith, endswith, like, ilike, match, regex (text: see Text lookups)
isnull
inside, outside, intersects (geometry values: see section 25)
a subquery — Subquery(qs) or the queryset itself — as the value of in, not_in, containsall, containsany (all its values) or exact, gt, gte, lt, lte, contains, not_contains (its first value): see section 28

Filter values on rich fields must be typed: filter(trial__gte=timedelta(days=7)), not "7d". Strings are not converted (see section 24).

Text lookups

SurrealQL has no LIKE or STARTSWITH operator, so each text lookup compiles to a string function, with its value bound:

Lookup	Value	Compiles to
`startswith`	a prefix	`string::starts_with(field, $v)`
`endswith`	a suffix	`string::ends_with(field, $v)`
`like`	an SQL `LIKE` pattern	`string::matches(field, $v)`, the pattern translated to an anchored `(?s)` regex
`ilike`	the same, case-insensitive	the same, with `(?i)` added
`regex`	a regular expression	`string::matches(field, $v)`
`match`	a regular expression	`string::matches(field, $v)`: the same as `regex`

await User.objects().filter(name__startswith="A").exec()
await User.objects().filter(email__ilike="%@EXAMPLE.com").exec()   # case-insensitive
await User.objects().filter(code__like="AB_-%").exec()            # "AB", any one character, "-", anything
await User.objects().filter(name__regex=r"^\d{3}").exec()          # SurrealDB's regex syntax (Rust regex)

Non-string fields. Each condition is guarded by the field's type: filter(name__startswith="A") is ((name).is_string() AND string::starts_with(name, $_f0)). A record whose field is missing (NONE), NULL or not a string never matches, and the query does not fail on it. The negation (~Q(name__startswith="A")) matches it. A dotted path through an array of record links or objects (authors.name) holds an array, so it never matches either, on both lines: the parentheses make SurrealDB 3.x test that array, not each of its elements.
like / ilike patterns. % matches any run of characters (newlines included) and _ exactly one character. Every other character is literal, regex metacharacters included (., *, (, \, …). There is no escape character, so a pattern cannot match a literal % or _ alone: use regex for that. The whole value must match, so like="Al" finds "Al" but not "Alice".
match and regex. Both take a regular expression in SurrealDB's syntax (the Rust regex crate) and match anywhere in the value unless it is anchored (^…$). Prefix it with (?i) to ignore case. Here match is a regex lookup, not the full SurrealDB-ORM's full-text @@ operator: full-text search is planned for v0.34.0, as a separate API.
Values. The value must be a str, otherwise ValueError. A string starting with $ is data, as for every lookup (String filter values and Var()). startswith, endswith, match and regex also take a Var. like and ilike do not: their pattern is translated before it is sent, so Var raises ValueError.

Filtering on the record id

The id column holds a native RecordID, not a string, so the ORM coerces an id lookup before binding it. All of these address the same record:

await User.objects().filter(id="alice").exec()                        # bare identifier
await User.objects().filter(id="User:alice").exec()                   # full table:id form
await User.objects().filter(id=RecordID("User", "alice")).exec()      # explicit
await User.objects().get("alice")                                     # same rule
await User.objects().filter(id__in=["alice", "bob"]).exec()

The value's Python type decides which record is addressed, exactly as it does when a record is saved: filter(id=5) is the integer record id User:5, filter(id="5") is the string one. They are two different records — a model declared id: int needs the former.

A text or collection lookup (contains, startswith, regex, like, containsall, …) on id raises ValueError rather than silently matching nothing, since a record id is not a string:

await User.objects().filter(id__startswith="al").exec()
# ValueError: 'startswith' cannot be applied to the record id column 'id' …

A model that aliases its identity through primary_key keeps that alias as an ordinary column, so filter(code="abc") is a normal string comparison and is unaffected.

String filter values and `Var()`

from surreal_orm_lite import Var

await User.objects().filter(name="$admin").exec()                                  # the string "$admin"
await User.objects().filter(age__gte=Var("min_age")).variables(min_age=21).exec()  # … WHERE age >= $min_age

A string filter value is data, whatever it starts with: filter(name="$admin") compares name with the string "$admin", bound like any other value, wherever a filter applies: filter(), Q, get(), get_or_create(), update_or_create(), bulk_update() by filter, subqueries and live queries. Var(...) is the only way to reference a query variable, whose value variables() gives.

Until v0.28.1 a value starting with $ was read as a variable reference (deprecated since v0.14.3), and "$$admin" was how to write the string "$admin". That escape keeps its meaning, deprecated: a filter value starting with $$ loses one $ ("$$admin" finds "$admin": write "$admin"), with a DeprecationWarning; an item of an in list never had the escape.

A call written for the old reading, such as filter(age__lte="$max_age").variables(max_age=25), now compares age with the string "$max_age": a number sorts below a string, so lt and lte match every number, and gt, gte and exact none. When a string filter value is exactly $ followed by the name of a variable that variables() gives, the ORM keeps it as data and emits a UserWarning that says to write Var("max_age"), once per read, pointing at your line.

Var() also reads the parameters SurrealDB sets for each session, without variables(): $auth (the signed-in record user), $access, $session and $token, in reads and in live queries.

mine = await Doc.objects().filter(owner=Var("auth")).exec()  # … WHERE owner = $auth

The string "$auth" is data: filter(owner="$auth") compares owner with the string "$auth", so a read keeps only the records whose owner is that string, and a live query notifies only for those. A string filter value that is exactly "$auth", "$access", "$session" or "$token" emits a UserWarning naming the Var(...) that reads the parameter, once per read, pointing at your line. Any other string, such as "$x" with no variable x, is data the caller meant: no warning. On an edge's in and out, which hold record ids, such a string is not a record id and raises ValueError; when it names a variable of variables() or one of these parameters, the message names the Var(...) to write.

get_or_create() and update_or_create() look up their criteria and write the exact ones on create. Their lookup binds variables(), as filter() does, so a Var criterion reads the variable there too, and an exact one is written as the variable's value on create: the next identical call finds the record.

qs = Room.objects().variables(t="mine", n=10)
room, created = await qs.get_or_create(title=Var("t"))  # as get_or_create(title="mine")
room, created = await qs.update_or_create(size__gte=Var("n"), title=Var("t"), defaults={"floor": 2})

On the record id and on an edge's in and out, such a criterion is compared as the record id its value names, as the same criterion given that value is: with variables(a="person:alice"), get_or_create(in_=Var("a"), out=bob) finds or relates person:alice. A value that names no record id there raises ValueError before any request. A model instance given to variables() is bound and written as its record id, as the instance given as the criterion is (variables(a=alice)); an unsaved one raises ValueError before any request. The variables() of a subquery criterion share the lookup, as in filter(): one that binds a name of the queryset's variables() to another value raises ValueError. Var("auth") finds the signed-in user's record (get_or_create(owner=Var("auth")) returns it) but cannot be written: with no record to return, it raises SurrealDbError, and so does an exact Var that names no variable of variables().

A string criterion is refused where it would be a trap: update_or_create(owner="$auth", defaults=…) would find and write the record whose owner is the string "$auth", the same record for every record user, and members__contains="$auth" would look for the string. A criterion that is exactly $ followed by the name of one of these parameters or of a variable the lookup binds (of variables(), the queryset's or a subquery criterion's) raises SurrealDbError before any request, whatever its lookup. For a variable, the message names Var("x") as the criterion to write, under any lookup but like/ilike, which take no Var (pass the pattern itself, or use regex). For a parameter, it says to pass the actual value as an exact criterion (the record id, or the value itself), and that Var("auth") works under any other lookup but like/ilike, which take no Var (pass the pattern itself, or use regex): members__contains=Var("auth") is looked up, never written. Any other string, such as "$admin", is looked up and written as it is. On an edge's in and out, such a criterion raises the ValueError above.

variables() refuses these four names with a ValueError: SurrealDB sets them for each session and refuses a query variable of that name. To pass your own value, give it another name, or pass the value itself in the filter: filter(token=Var("device_token")).variables(device_token=t) or filter(token=t), in a read or a live query. Var("token") without variables() reads SurrealDB's $token.

Model instances as filter values

from surrealdb import RecordID


class Order(BaseSurrealModel):
    id: str | None = None
    customer: RecordID          # a record link
    note: str = ""


alice = await Customer.objects().get("alice")
bob = await Customer.objects().get("bob")
order, created = await Order.objects().get_or_create(customer=alice, note="first")
orders = await Order.objects().filter(customer=alice).exec()        # customer = Customer:alice
orders = await Order.objects().filter(customer__in=[alice, bob]).exec()

Since v0.22.0 a saved model instance is accepted wherever a filter takes a value (keyword lookups, Q objects, live filters, each item of in, not_in, containsany and containsall) and is replaced by its record id. get_or_create() and update_or_create() store that record id when they create. An unsaved instance raises ValueError, and so does a single instance given to a list lookup (customer__in=alice: wrap it in a list). A "table:id" string is not converted on an ordinary column, which may legitimately hold such text, and a string never equals a record id: pass the instance or a RecordID. Edge endpoints are the exception (see section 23).

Fields ending in an underscore

class Item(BaseSurrealModel):
    id: str | None = None
    type_: int = 0             # trailing underscore: `type` is a builtin


heavy = await Item.objects().filter(type___gt=1).exec()   # the gt lookup on type_

A lookup on a field whose name ends in _ takes three underscores: the field's own, then the __ separator. type___gt is gt on type_, and in___in is in on an edge's in_. Before v0.22.0 such a key raised "Unsupported lookup".

5. Q Objects (Complex Queries)

from surreal_orm_lite import Q

# OR queries
users = await User.objects().filter(Q(name="Alice") | Q(name="Bob")).exec()

# NOT queries
active = await User.objects().filter(~Q(status="banned")).exec()

# Complex combinations
results = await User.objects().filter(
    Q(age__gte=18) & (Q(role="admin") | Q(role="mod"))
).exec()

# Mix Q objects with keyword filters
results = await User.objects().filter(
    Q(role="admin") | Q(role="mod"),
    age__gte=25
).exec()

Since v0.27.0 a lookup inside a Q takes a subquery as filter() does (see section 28), here with admins = User.objects().filter(role="admin"):

posts = await Post.objects().filter(Q(author__in=Subquery(admins)) | Q(score__gte=9)).exec()

6. Bulk Operations

# Bulk create
users = [User(name="Alice", age=30), User(name="Bob", age=25)]
created = await User.objects().bulk_create(users)

# Bulk update (returns count of updated records)
count = await User.objects().filter(status="pending").bulk_update(status="active")

# Bulk delete (returns count of deleted records)
count = await User.objects().filter(status="inactive").bulk_delete()

7. Relations & Graph

# Create a relation
await user.relate("follows", other_user)

# With data on the edge
await user.relate("purchased", product, data={"quantity": 2, "price": 29.99})

# Get related records (outgoing)
following = await user.get_related("follows", direction="out", model_class=User)

# Get related records (incoming)
followers = await user.get_related("follows", direction="in", model_class=User)

# A target can also be a "table:id" string. Its id is read as a *string* record id — the
# same rule the ORM applies to a model's own id — so these two name the same record:
await user.relate("follows", "User:1")        # -> User:`1`, i.e. User(id="1")
# Pass a RecordID to target an *integer* record id (what a model with `id: int` stores):
from surrealdb import RecordID
await user.relate("follows", RecordID("User", 1))   # -> User:1

# Remove a specific relation
await user.remove_relation("follows", other_user)

# Remove all outgoing relations of a type
await user.remove_all_relations("follows", direction="out")

# Graph traversal
friends_of_friends = await user.traverse("->follows->User->follows->User")

Since v0.22.0 the helpers take the full SurrealDB-ORM's parameters, in its order, by position or by keyword, and every one of them accepts an edge model class (section 23) as well as a table name:

# Edge fields as keyword arguments (the full ORM's spelling); they merge with data=
await user.relate("follows", other_user, since="2025-01-01")
await user.relate("purchased", product, data={"quantity": 2}, price=29.99)

# reverse=True: target -> edge -> self
await table.relate("created", creator, reverse=True)       # creator -> created -> table
await table.relate("created", creator, True)               # same call, the full ORM's positional order
await table.remove_relation("created", creator, reverse=True)

# An edge model class (section 23) wherever a table name goes
following = await user.get_related(Follows, model_class=User)

# Both directions in one query: the out-neighbours, then the in-neighbours
everyone = await user.get_related("follows", direction="both", model_class=User)

# Several edge tables in one statement
await user.remove_all_relations(["follows", "likes", "blocks"], direction="both")

# A bare record id: that id in any table linked through the edge table
await table.remove_relation("has_player", "abc123")

# tx= on the three write helpers
async with SurrealDBConnectionManager.transaction() as tx:
    await user.relate("follows", other_user, tx=tx)
    await user.remove_relation(Follows, "User:carol", tx=tx)
    await user.remove_all_relations(["likes", "blocks"], direction="both", tx=tx)

Direction. reverse=True relates or unlinks target -> edge -> self; the default is self -> edge -> target.
Edge fields. data= and keyword arguments merge; a field given both ways is a TypeError, raised before anything is sent. A field named like a parameter (edge, target, reverse, tx, data or self) cannot be a keyword argument: pass it in data= (data={"reverse": True}).
Bare record id. A string without : is a bare record id. It is compared as a string, so it matches that id in any table linked through the edge table (Player:abc123 and Bot:abc123 alike). To pin the table, or to reach an integer record id ("7" never matches Player:7), pass the instance or a RecordID. Only remove_relation() takes one; an empty or blank string raises ValueError.
Removals are graph-path DELETE statements: they delete exactly the edges get_related() and traverse() see (a plain record that merely carries in/out fields is not an edge and is left alone), a self-loop once, and an edge table that was never created is a no-op on both server lines, in a transaction too. remove_all_relations() takes one edge or a list; an empty list raises ValueError.
direction="both" returns the out-neighbours, then the in-neighbours: a neighbour linked by k edges appears k times, the record itself only through a self-loop (once per direction). The order inside each group is unspecified; it differs between server versions.
tx= follows the transaction's strategy (section 11). On an interactive transaction (WebSocket + SurrealDB 3.x) the statement runs at once: the change is visible to objects(tx=) reads and a rollback undoes it. On a buffered one (2.x, or HTTP) it is queued and applied at commit, and the string relate() returns []. get_related() has no tx=, as in the full ORM.
The source and target are bound as record ids (only edge names, field names and the validated traverse() path are written into the statement), so a node whose id is not an identifier, such as User(id="joe@x.com"), works with every graph helper: relate() with an edge class or a table name, get_related(), the remove_* helpers and traverse().

Coming from the full SurrealDB-ORM? The few remaining differences are listed in Migrating from SurrealDB-ORM (graph API).

8. FETCH Clause

# Resolve record links inline (prevents N+1 queries)
posts = await Post.objects().fetch("author", "tags").exec()
# Generates: SELECT * FROM Post FETCH author, tags;

9. Aggregations

from surreal_orm_lite import Count, Sum, Avg, Min, Max

# Simple aggregations
count = await User.objects().count()
total = await Order.objects().sum("amount")
avg_age = await User.objects().avg("age")
max_price = await Product.objects().max("price")
min_price = await Product.objects().min("price")

# Check existence
has_admins = await User.objects().filter(role="admin").exists()

# GROUP BY with annotations
results = await User.objects().values("status").annotate(count=Count()).exec()
# [{"status": "active", "count": 42}, {"status": "inactive", "count": 8}]

# Raw SurrealQL queries
results = await User.raw_query(
    "SELECT * FROM User WHERE age > $min_age",
    variables={"min_age": 18}
)

10. Model Signals

from surreal_orm_lite import pre_save, post_save, pre_delete, post_delete

@post_save.connect(User)
async def on_user_saved(sender, instance, created, **kwargs):
    """Called after every User save."""
    if created:
        await send_welcome_email(instance.email)
    await invalidate_cache(f"user:{instance.id}")

@pre_delete.connect(User)
async def on_user_deleting(sender, instance, **kwargs):
    """Called before User deletion."""
    await archive_user_data(instance.id)

Available signals:

Signal	When	Extra kwargs
`pre_save`	Before `save()`
`post_save`	After `save()`	`created`
`pre_update`	Before `update()`/`merge()`	`update_fields`
`post_update`	After `update()`/`merge()`	`update_fields`
`pre_delete`	Before `delete()`
`post_delete`	After `delete()`
`around_save`	Wraps `save()`
`around_update`	Wraps `update()`/`merge()`	`update_fields`
`around_delete`	Wraps `delete()`

Around signals use async generators to wrap operations:

from surreal_orm_lite import around_save

@around_save.connect(User)
async def time_user_save(sender, instance, **kwargs):
    import time
    start = time.time()
    yield  # save() executes here
    duration = time.time() - start
    print(f"Save took {duration:.3f}s")

11. Transactions (atomic, all-or-nothing)

from surreal_orm_lite import SurrealDBConnectionManager

# All operations commit together, or none do.
async with SurrealDBConnectionManager.transaction() as tx:
    await User(id="alice", name="Alice").save(tx=tx)
    await Order(id="o1", user="User:alice", total=100).save(tx=tx)

    # v0.9.0: QuerySet reads & bulk ops participate in the transaction.
    actives = await User.objects(tx=tx).filter(status="active").exec()
    await User.objects(tx=tx).filter(role="guest").bulk_update(role="member")
    # Auto-commit on success; auto-rollback if the block raises.

transaction() picks the strategy automatically based on the connection:

WebSocket + SurrealDB 3.x → InteractiveTransaction (native begin()/commit()/cancel()). Reads inside the tx see uncommitted writes; save(tx=) supports auto-generated ids; refresh(tx=) and QuerySet.objects(tx=) reads work.
HTTP, or WebSocket on SurrealDB 2.6.x → BufferedTransaction. Writes are buffered and flushed as one BEGIN TRANSACTION; …; COMMIT TRANSACTION; query at commit; reads inside the tx raise; save(tx=) requires an explicit id. bulk_update/bulk_delete return 0 (the row count is not knowable before commit).

A failed statement fails the whole transaction, on every strategy. After a statement fails inside the block — even when the block catches its error and carries on — the transaction is cancelled when the block ends, nothing of the block is committed, and leaving the block raises SurrealDbError (SurrealDbConflictError for a retryable conflict, so retry_on_conflict still retries the block). A buffered transaction fails as a whole at commit; an interactive one raises the failed statement's error at once, then, when the block ends, "The transaction was cancelled: a statement in it failed; nothing was committed (…)", chained to that error. An explicit tx.commit() after a failure raises the same error. When the block's own exception propagates, that exception wins, as before. Reads the ORM answers itself are not failed statements: a table that does not exist reads as no rows, and get() raises SurrealDbNotFoundError.

async with SurrealDBConnectionManager.transaction() as tx:
    await User(id="alice", name="Alice").save(tx=tx)
    try:
        await Order(id="o2", user="User:alice", total=-1).save(tx=tx)  # refused by a field ASSERT
    except SurrealDbError:
        pass  # caught, but the transaction can no longer commit
# leaving the block raised SurrealDbError: alice was not saved either

12. Upsert & get_or_create / update_or_create

from surreal_orm_lite import BaseSurrealModel, SurrealConfigDict


class User(BaseSurrealModel):
    model_config = SurrealConfigDict(primary_key="id")
    id: str | None = None
    name: str
    email: str


# Insert-or-replace by explicit id (full REPLACE — omitted fields are dropped).
# Use merge() instead if you only want a partial update.
await User(id="alice", name="Alice", email="alice@example.com").upsert()

# Criteria-based, Django-style; returns (instance, created).
# update_or_create: on create, writes criteria + defaults; on update, MERGEs them (a partial
# update — fields outside the criteria/defaults are preserved). Lifecycle signals fire on both
# paths, and the primary key anchors the record identity.
user, created = await User.objects().update_or_create(
    email="alice@example.com", defaults={"name": "Alice"}
)

# get_or_create writes the defaults ONLY when creating; an existing match is returned as-is:
user, created = await User.objects().get_or_create(
    email="bob@example.com", defaults={"name": "Bob"}
)

# Both participate in a transaction via objects(tx=) (interactive on SurrealDB 3.x):
async with SurrealDBConnectionManager.transaction() as tx:
    user, created = await User.objects(tx=tx).get_or_create(email="z@x.io", defaults={"name": "Z"})

If the lookup criteria match more than one record, both methods raise SurrealDbError (the criteria are not unique). Non-exact lookups (e.g. name__contains) drive the lookup but are not written to the record. The lookup binds the queryset's variables(), as filter() does: User.objects().variables(e="bob@example.com").get_or_create(email=Var("e")) finds the user whose email is the variable's value, and writes that value on create (see String filter values and Var()). Without a transaction the behaviour is identical on SurrealDB 2.6.x and 3.x; under objects(tx=) they participate in the transaction on 3.x, while a buffered 2.6.x transaction raises on the lookup (see the behaviour table).

13. Patch & atomic field/array operations

Mutate a record granularly — server-side — without reading and rewriting the whole document.

No signals. patch() and the atomic helpers are low-level primitives and emit no pre_*/post_*/around_* lifecycle signals. If you rely on signals (audit, cache invalidation, …), use merge() / save() instead.

# JSON Patch (RFC 6902) on a single record (native SDK patch()). Requires an explicit id.
await user.patch([
    {"op": "replace", "path": "/age", "value": 26},
    {"op": "add", "path": "/tags/-", "value": "premium"},
    {"op": "remove", "path": "/settings/notifications"},
])

# Ergonomic atomic helpers — each is one atomic UPDATE … SET, safe under concurrency:
await post.atomic_append("tags", "python")     # array::append — duplicates allowed
await post.atomic_set_add("editors", "alice")  # array::add     — added only if absent (set)
await post.atomic_remove("tags", "spam")       # array::complement — removes ALL "spam"
await counter.atomic_increment("views")        # += 1 (default); pass a negative to decrement
await counter.atomic_increment("score", 5)     # += 5

# List-valued variants — apply many in ONE round-trip instead of N:
await post.atomic_append_many("tags", ["python", "orm"])   # array::concat — all, dups allowed
await post.atomic_set_add_many("editors", ["alice", "bob"])  # array::add — only those absent
await post.atomic_remove_many("tags", ["spam", "draft"])     # array::complement — all matches

# Patch a filtered set (or the whole table if unfiltered); returns the affected count.
n = await User.objects().filter(status="trial").patch(
    [{"op": "replace", "path": "/plan", "value": "free"}]
)

# All of the above accept tx= and participate in a transaction:
async with SurrealDBConnectionManager.transaction() as tx:
    await counter.atomic_increment("views", tx=tx)
    await user.patch([{"op": "replace", "path": "/age", "value": 27}], tx=tx)

# atomic_increment accepts a Decimal for exact arithmetic (e.g. money):
from decimal import Decimal

await account.atomic_increment("balance", Decimal("2.25"))

# Optimistic concurrency with a JSON Patch `test` op: if the test fails, the WHOLE patch is
# aborted server-side (no op applies) and a ServerError is raised — RFC 6902 semantics.
await order.patch([
    {"op": "test", "path": "/version", "value": 7},  # only proceed if version is still 7
    {"op": "replace", "path": "/status", "value": "shipped"},
    {"op": "replace", "path": "/version", "value": 8},
])

These atomic ops behave identically on SurrealDB 2.6.x and 3.x by design: they use the version-portable functions array::append / array::add / array::complement (and numeric +=) rather than the bare += / -= array operators, whose semantics differ between server lines. patch() and the atomic helpers emit no signals (use merge() / save() if you need lifecycle hooks). On a non-transactional or interactive (3.x) call the instance is synced with the server's returned row; in a buffered 2.6.x transaction the result is unknown until commit, so refresh() the instance if you need it (same caveat as merge(tx=)).

A failed JSON Patch test op aborts the entire patch and raises the SDK's ServerError (message: Given test operation failed…) — none of the other ops in the list are applied. This gives you compare-and-set / optimistic-concurrency without a transaction.

14. Optimistic concurrency: `retry_on_conflict`

Under SurrealDB's optimistic concurrency, a transaction is rolled back with a retryable conflict when a concurrent writer changed the same data. retry_on_conflict re-runs the whole function (a fresh transaction per attempt) with exponential backoff + jitter, but only on a real conflict — any other error propagates immediately.

from surreal_orm_lite import retry_on_conflict, SurrealDBConnectionManager

@retry_on_conflict(max_retries=3, base_delay=0.05, max_delay=2.0, backoff_factor=2.0)
async def transfer(src_id, dst_id, amount):
    async with SurrealDBConnectionManager.transaction() as tx:
        src = await Account.objects(tx=tx).get(src_id)
        dst = await Account.objects(tx=tx).get(dst_id)
        await src.merge(tx=tx, balance=src.balance - amount)
        await dst.merge(tx=tx, balance=dst.balance + amount)

await transfer("acc:a", "acc:b", 100)  # retries automatically on a version conflict

A conflict is exposed as SurrealDbConflictError (a subclass of SurrealDbError) on both SurrealDB lines, so you can catch it yourself or test any exception with is_conflict_error():

from surreal_orm_lite import SurrealDbConflictError, is_conflict_error

try:
    await transfer("acc:a", "acc:b", 100)
except SurrealDbConflictError:
    ...  # still conflicting after every retry

Total attempts = max_retries + 1; after they are exhausted the conflict is re-raised as SurrealDbConflictError. The numeric parameters are validated at decoration time.
Detection anchors on SurrealDB's own retryable marker ("This transaction can be retried"), so a non-retryable failure (e.g. a duplicate-key error) is not retried.
The exception type is identical on 2.6.x and 3.x; conflicts simply arise more often on 3.x (optimistic MVCC) than on 2.6.x (see the behaviour table), except on change-feed tables, where 2.x refuses concurrent writes that 3.x accepts (see section 22).
The decorator wraps any coroutine, not only a transaction(): a single save(), merge() or delete() is retried the same way (see section 22 for change-feed tables on SurrealDB 2.x). The conflict such a write raises outside a transaction is not converted: on 2.7.0 it is the SDK's InternalError, which is_conflict_error() recognises.

15. Server-side values: `SurrealFunc` + `server_values=`

Some values belong to the server, not to your process: a creation timestamp should come from the DB clock, a password hash from the DB's own crypto. Pass them as server_values= on save() or merge() and the ORM compiles a SET clause where the expression is evaluated by SurrealDB:

from surreal_orm_lite import SurrealFunc

await player.save(server_values={"joined_at": SurrealFunc("time::now()")})
# CREATE $rid SET seat = $_sv_seat, joined_at = time::now();

The instance is synced with the row the server returns, so player.joined_at is a real datetime right after the call — no extra refresh().

User input never goes into the expression. Reference a bound parameter and supply it through extra_vars=, which is bound like any other value:

from surreal_orm_lite import SurrealFunc, SurrealCryptoFunction

await user.save(
    server_values={"password_hash": SurrealFunc.call(SurrealCryptoFunction.ARGON2_GENERATE, "$password")},
    extra_vars={"password": raw_password},   # bound — never interpolated
)

merge() takes the same two arguments and stays a partial update (unlisted fields are untouched); a server_values entry overrides a keyword of the same name:

from surreal_orm_lite import SurrealTimeFunction

await user.merge(plan="pro", server_values={"updated_at": SurrealFunc.call(SurrealTimeFunction.NOW)})

SurrealFunc.call(fn, *args) builds fn(arg, …) from a function name — a plain string or a member of the shipped enums, which give you autocompletion over a curated catalog whose every member is tested against SurrealDB 2.7.0 and 3.3.4:

Enum	Covers
`SurrealTimeFunction`	`time::now`, `time::floor`, `time::unix`, `time::year`, …
`SurrealMathFunction`	`math::abs`, `math::pow`, `math::mean`, `math::fixed`, …
`SurrealStringFunction`	`string::concat`, `string::slug`, `string::replace`, …
`SurrealArrayFunction`	`array::append`, `array::add`, `array::distinct`, …
`SurrealCryptoFunction`	`crypto::argon2::generate` / `::compare`, `crypto::bcrypt::*`, …
`SurrealRandFunction`	`rand`, `rand::uuid::v7`, `rand::ulid`, `rand::enum`, …

Functions whose name differs between the two server lines are deliberately excluded from the catalog (rand::guid is 2.6-only; type::is::* became type::is_* in 3.x) — pass those as a plain string if you target one line. The enums are convenience, not a gate: SurrealFunc accepts any expression.

Security: the SurrealFunc expression is inserted verbatim into the query, so build it only from developer-controlled text. Field values and extra_vars are always bound parameters — that is the injection boundary. SurrealFunc rejects ; as a guard against accidental statement chaining, but that is not a sanitizer.

Both calls behave identically on SurrealDB 2.6.x and 3.x. Inside a transaction the usual v0.9.0 rules apply: on a buffered transaction (HTTP / 2.6.x) the computed value is unknown until commit, so the instance keeps its previous value for that field until you refresh().

16. Computed fields: `Computed[...]` → `DEFINE FIELD … VALUE`

server_values= computes a value for one write. A Computed field attaches the expression to the schema instead, so SurrealDB recomputes it on every write to the table — including writes that never go through the ORM:

from surreal_orm_lite import BaseSurrealModel, Computed, computed

class Player(BaseSurrealModel):
    id: str
    first_name: str
    last_name: str
    full_name: Computed[str] = computed("string::concat(first_name, ' ', last_name)")
    initials: Computed[str] = computed("string::uppercase(string::slice(first_name, 0, 1))")

await Player.define_computed_fields()   # DEFINE FIELD OVERWRITE full_name ON Player VALUE …

player = await Player(id="ada", first_name="Ada", last_name="Lovelace").save()
player.full_name        # "Ada Lovelace" — computed by the server, not by Python

Computed[T] is the annotation and makes the field T | None defaulting to None, so an instance is constructible before the server has ever computed it; computed("<expr>") is the default and carries the expression, which may be a plain string or a SurrealFunc. The two are split — rather than one dual-use name — so that player.full_name resolves to str | None under mypy and pyright, the same shape as SQLAlchemy's Mapped[T] = mapped_column(...).

Applying the schema. define_computed_fields() is idempotent — call it at start-up:

Player.computed_field_ddl()             # the statements, without touching the DB
await Player.define_computed_fields()                  # DEFINE FIELD OVERWRITE … (default)
await Player.define_computed_fields(overwrite=False)   # DEFINE FIELD IF NOT EXISTS …

The default OVERWRITE treats the model as the source of truth, so editing an expression and redeploying takes effect. overwrite=False never disturbs a definition that already exists.

The field is server-owned. It is dropped from every write payload, and naming it in a write raises rather than being silently discarded:

await player.merge(last_name="Byron")       # ✅ full_name recomputes to "Ada Byron"
await player.merge(full_name="whatever")    # ❌ ValueError: full_name is a computed field …

The same guard applies to save(server_values=), patch(), QuerySet.patch(), QuerySet.bulk_update() and the atomic_* helpers. A JSON Patch is checked on its top-level pointer segment, so /full_name and /full_name/0 are both refused. This is genuinely enforced by SurrealDB, not just by the ORM — a client that bypasses ORM-lite entirely and writes full_name directly still gets the expression's result.

Ordering caveat: SurrealDB evaluates computed fields in alphabetical field-name order, not declaration order. A computed field that reads another must sort after it — subtotal → total works, but z_sub → a_total fails at write time.

Security: like SurrealFunc, the expression is inlined verbatim into DDL and cannot reference bound parameters. Build it only from developer-controlled text, never user input.

No TYPE clause is emitted — SurrealDB infers an optional type on both server lines. Behaviour is identical on SurrealDB 2.6.x and 3.x.

17. Stored functions: `call_function()`

Invoke a function declared server-side with DEFINE FUNCTION fn::…. Arguments are bound as query parameters, never formatted into the statement.

from surreal_orm_lite import SurrealDBConnectionManager

# Declare it once (DDL goes through query(); a define_function() helper lands in v0.31.0)
client = await SurrealDBConnectionManager.get_client()
await client.query("""
    DEFINE FUNCTION OVERWRITE fn::acquire_lock($table_id: string, $pod_id: string, $ttl: int) {
        UPSERT type::record("lock:⟨" + $table_id + "⟩") SET holder = $pod_id, ttl = $ttl;
        RETURN { acquired: true, holder: $pod_id };
    };
""", {})

# Positional — SurrealQL's own calling convention
lock = await SurrealDBConnectionManager.call_function(
    "fn::acquire_lock", ["table-1", "pod-a", 30],
)

# The fn:: prefix is optional, and nested namespaces work
total = await SurrealDBConnectionManager.call_function("billing::total", [cart_id])

Named arguments. SurrealQL function arguments are positional, so the ORM reads the function's declared signature (INFO FOR DB, cached) and orders them for you — the mapping's own order is irrelevant:

lock = await SurrealDBConnectionManager.call_function(
    "fn::acquire_lock", params={"pod_id": "pod-a", "ttl": 30, "table_id": "table-1"},
)

Passing both args and params raises ValueError, as does a key that does not match the declared parameters (the error names the ones expected).

Typed results. return_type= accepts anything Pydantic can adapt — a model, a dataclass, a scalar, or a generic like list[Model]:

from pydantic import BaseModel

class LockResult(BaseModel):
    acquired: bool
    holder: str

lock = await SurrealDBConnectionManager.call_function(
    "fn::acquire_lock", ["table-1", "pod-a", 30], return_type=LockResult,
)
lock.acquired  # True

A result that does not fit raises SurrealDbValidationError. Note this is Pydantic validation, not a cast: an int asked to be a str is a mismatch, not a silent str(5).

Inside a transaction. Pass tx= so the function runs inside the transaction — without it the call would execute outside it and silently break atomicity, which matters because stored functions typically mutate state:

async with SurrealDBConnectionManager.transaction() as tx:
    await SurrealDBConnectionManager.call_function("fn::acquire_lock", ["t1", "pod-a", 30], tx=tx)
    await Booking(id="b1", table_id="t1").save(tx=tx)
# both the function's writes and the booking commit together, or neither does

The return value depends on the transaction strategy (the v0.9.0 contract): interactive transactions (WebSocket + SurrealDB 3.x) return the value immediately; buffered ones (SurrealDB 2.6.x or HTTP) queue the call and return None until commit. Combining return_type= with a buffered tx= raises ValueError rather than silently returning None.

From a model. A stored function is not bound to a table, but the shortcut is convenient in model-oriented code:

lock = await GameTable.call_function("fn::acquire_lock", ["t1", "pod-a", 30])

Errors are normalised: a function the server does not know raises SurrealDbNotFoundError, anything else raises SurrealDbError. A result that does not fit return_type raises SurrealDbValidationError — inside an interactive transaction too. The one exception is a buffered transaction, where the call is merely queued: a missing function cannot be detected at call time and surfaces at commit as SurrealDbError.

Portability note — building a record id inside a function is the one fiddly part, and the spelling above is the one verified on both DB lines. The two-argument type::record($table, $id) is 3.x-only (on 2.6.x the second argument means a type, not an id), and type::thing is its 2.6-only inverse. The ⟨…⟩ brackets around the id matter too: without them type::record("lock:" + $id) truncates an id containing a hyphen ("table-1" becomes table) on 3.x and is rejected outright on 2.6.x.

Signature caching is transparent, but two helpers are available if you redefine functions out-of-band: SurrealDBConnectionManager.clear_function_signature_cache() and function_signature_cache_size(). The cache is keyed by URL, namespace and database, so set_url(), set_namespace() and set_database() cannot serve a signature read elsewhere; it is cleared outright by set_connection() and unset_connection(). A signature whose parameter names changed self-heals on its own — but one redefined with the same names in a different order cannot be detected, since params= is a mapping and carries no order to compare against. After such a redefinition, call clear_function_signature_cache(), or params= keeps binding in the stale order.

18. Authentication (`signin` / `signup` / `info` / `invalidate`)

Authenticate the connection as a SurrealDB record user (a DEFINE ACCESS … TYPE RECORD method) or as a different system user. Declare the access method once — DDL goes through query():

client = await SurrealDBConnectionManager.get_client()
await client.query("""
    DEFINE TABLE OVERWRITE app_user SCHEMALESS
      PERMISSIONS FOR select, update WHERE id = $auth.id;
""", {})
await client.query("""
    DEFINE ACCESS OVERWRITE account ON DATABASE TYPE RECORD
      SIGNUP ( CREATE app_user SET email = $email, pass = crypto::argon2::generate($pass) )
      SIGNIN ( SELECT * FROM app_user
               WHERE email = $email AND crypto::argon2::compare(pass, $pass) )
      DURATION FOR TOKEN 15m, FOR SESSION 12h;
""", {})

Then register and authenticate users:

from surreal_orm_lite import AuthTokens, SurrealDBConnectionManager, SurrealDbAuthenticationError

tokens = await SurrealDBConnectionManager.signup(
    access="account", variables={"email": "ada@example.com", "pass": "s3cret"},
)
tokens = await SurrealDBConnectionManager.signin(
    access="account", variables={"email": "ada@example.com", "pass": "s3cret"},
)
tokens.access     # the JWT — hand it to a web client
tokens.refresh    # SurrealDB 3.x only (WITH REFRESH); always None on 2.6.x

namespace/database come from set_connection() for record access. A system user gets neither unless you pass them, because a root user is defined at no level:

await SurrealDBConnectionManager.signin(username="root", password="root")            # root
await SurrealDBConnectionManager.signin(username="u", password="p", namespace="ns")  # NS user

Read back who is signed in, optionally hydrated into a model:

me = await SurrealDBConnectionManager.info()                  # dict | None
me = await SurrealDBConnectionManager.info(return_type=User)  # User | None

Restore an identity on a later request, and log out:

await SurrealDBConnectionManager.authenticate(stored_jwt)
await SurrealDBConnectionManager.invalidate()   # back to the credentials of set_connection()

The identity survives reconnects: get_client() replays the stored token, so a dropped connection or a new event loop comes back as the same user rather than silently reverting to the configured root identity.

Every failure — wrong password, unknown access method, malformed or expired token — raises SurrealDbAuthenticationError on both DB lines (it subclasses SurrealDbError). Match on the type, never on the server's wording, which differs per line.

⚠️ Auth changes the identity of the whole connection. The manager caches one client per event loop and every model shares it, so an auth call affects every subsequent ORM operation — not just the caller's. An application serving concurrent users must not route them all through a single connection-manager identity.

⚠️ info() returns None unless the table grants the record user select on itself. The signin succeeded and $auth is set, but the server returns nothing. Grant something like PERMISSIONS FOR select WHERE id = $auth.id.

Refresh tokens (SurrealDB 3.x only). With DEFINE ACCESS … WITH REFRESH, renew without the password:

tokens = await SurrealDBConnectionManager.signin(access="account", refresh=stored_refresh)
persist(tokens.refresh)   # REQUIRED — see below

⚠️ Refresh tokens rotate. A successful exchange kills the token it spent, immediately and permanently. If you keep the old value and drop the new one, the user is logged out for good — and nothing is raised at the moment you make the mistake.

19. Model-level authentication (`AuthenticatedUserMixin`)

Where section 18 authenticates the connection, this authenticates a model: declare a user model, let the ORM generate its DEFINE ACCESS statement, and get hydrated model instances back instead of bare tokens.

from surreal_orm_lite import AuthenticatedUserMixin, BaseSurrealModel, SurrealConfigDict

class User(AuthenticatedUserMixin, BaseSurrealModel):
    model_config = SurrealConfigDict(access_name="account")

    id: str | None = None
    email: str
    password: str
    name: str = ""

# Once, at start-up — emits DEFINE TABLE … PERMISSIONS + DEFINE ACCESS …
await User.define_access()

result = await User.signup(email="ada@example.com", password="s3cret", name="Ada")
result.user     # a User instance, carrying the id the server generated
result.tokens   # AuthTokens — repr redacts, so no JWT reaches your logs

result = await User.signin(email="ada@example.com", password="s3cret")

# A later request carrying the stored JWT: token → current user
me = await User.authenticate(result.tokens.access)

Each call runs on its own short-lived connection. The process-wide client keeps the identity set_connection() gave it, so a web application can authenticate concurrent users without them trampling each other — the footgun that makes section 18's signin() unsuitable per-request. Pass bind=True to also adopt the token process-wide (handy in a script or notebook, wrong in a concurrent server).

The model must declare either an id field or a primary_key, as every ORM model must. Prefer id here so the server mints the record id during SIGNUP; configure primary_key when identity is a column, and the SIGNUP clause targets type::thing(…) to match.

Configuration

Key	Default	Purpose
`access_name`	`<table>_access`	Name of the `DEFINE ACCESS` method
`identifier_field`	`email`	Field a user signs in with
`password_field`	`password`	Field holding the password hash
`auth_algorithm`	`argon2`	`argon2` / `bcrypt` / `pbkdf2` / `scrypt`
`auth_duration_token`	`15m`	JWT lifetime
`auth_duration_session`	`12h`	Session lifetime
`with_refresh`	`False`	Emit `WITH REFRESH` — SurrealDB 3.x only
`auth_duration_grant`	`30d`	Refresh-grant lifetime

Inspecting the DDL before applying it

access_ddl() is pure — it renders without touching the database, so you can print it, diff it, or feed it to a migration:

for statement in User.access_ddl():
    print(statement)
# DEFINE TABLE OVERWRITE User SCHEMALESS PERMISSIONS FOR select, update WHERE id = $auth.id;
# DEFINE ACCESS OVERWRITE account ON DATABASE TYPE RECORD SIGNUP ( ... ) SIGNIN ( ... ) ...;

The DEFINE TABLE ships by default, and deliberately: without FOR select WHERE id = $auth.id a signin succeeds and $auth is set, yet the server returns no record — so no instance can be built. define_access(with_table=False) opts out when you manage the table yourself.

The password field holds the hash. SurrealDB returns the stored record, so result.user.password is $argon2id$v=19$…, never the plaintext you submitted.

Renewing a session (SurrealDB 3.x only)

class User(AuthenticatedUserMixin, BaseSurrealModel):
    model_config = SurrealConfigDict(access_name="account", with_refresh=True)
    ...

result = await User.refresh(stored_refresh_token)
persist(result.tokens.refresh)   # REQUIRED — refresh tokens rotate

On SurrealDB 2.6.x the WITH REFRESH clause does not parse at all, so define_access() raises a SurrealDbError that says exactly that, and no refresh token ever exists.

20. Field aliases, `server_fields` and `merge(refresh=False)`

Three developer-experience features (v0.18.0) that let a model's Python surface differ from its SurrealDB column surface, and let a caller skip work they do not need. All three behave identically on SurrealDB 2.6.x and 3.x.

Field aliases

Declare the column name with plain Pydantic; the ORM honours it everywhere.

from pydantic import Field
from surreal_orm_lite import BaseSurrealModel

class User(BaseSurrealModel):
    id: str
    password: str = Field(alias="password_hash")   # column is password_hash
    display: str = Field(alias="display_name")

# Build with either name — the Python one in your code, the alias when hydrating a row.
user = User(id="ada", password="secret", display="Ada")
await user.save()                                   # stores password_hash / display_name

# Query in Python names; the ORM emits the columns.
found = await User.objects().filter(password="secret").order_by("-display").exec()
await user.merge(password="rotated")
await User.objects().filter(display="Ada").bulk_update(password="rotated")

The translation covers every boundary the ORM owns:

writes — save, update, upsert, merge, bulk_create, server_values=, and the atomic_* family (atomic_increment, atomic_append, atomic_set_add, atomic_remove and their *_many counterparts);
reads — exec(), refresh(), and the row a write returns;
every QuerySet clause — select, filter (including Q objects, nested and negated), order_by, values, fetch, bulk_update, the sum/avg/min/max helpers and the Count/Sum/Avg/Min/Max objects passed to annotate(). A grouped result comes back keyed by your Python names, not by columns;
DDL the ORM generates — computed_field_ddl(), and AuthenticatedUserMixin's access_ddl() (its SIGNUP/SIGNIN clauses name the column on the left and keep your keyword argument as the bound variable on the right);
signal payloads — update_fields always names the model's Python attributes.

Three classmethods expose the mapping if you need it directly:

Method	Returns
`get_field_aliases()`	`{python_name: column}` for aliased fields (`{}` if none)
`to_db_field(name)`	the column for `name` (unchanged when unaliased)
`to_py_field(column)`	the Python attribute for `column`

Only a plain, symmetric Field(alias=…) is treated as a column rename. A separate validation_alias / serialization_alias, and in particular AliasPath / AliasChoices, describe something other than a renamed column and are left entirely to Pydantic — write payloads follow the ORM's map, not model_dump(by_alias=True), so a row can never land under a name the read paths do not look for.

The map has to be unambiguous, and the ORM checks it the first time it is used: an alias that collides with another field's name, or two fields sharing one column, raises ValueError. Either shape would make the payload's two keys collapse into one and silently drop a field.

patch() pointers follow the same rule. A pointer's top-level segment may name the field or its column — /password and /password_hash both reach password_hash, and so does the from of a move/copy. A whole-document operation ("path": "") has its object value re-keyed. Nested paths translate their root: Q(**{"address.city": …}) and Sum("address.n") address addr.city / addr.n, exactly like filter(**{"address.city": …}).

Result keys stay in your vocabulary too, with one rule for grouped rows: an annotate() alias is your own word and is never renamed. An alias that spells a grouped field — by name or by column — would lose one of the two values in the same row, so it raises ValueError.

`server_fields`

Mark the columns the server owns — filled by a DEFINE FIELD … DEFAULT, an event or a trigger — so the client never volunteers them:

from surreal_orm_lite import SurrealConfigDict

class Post(BaseSurrealModel):
    model_config = SurrealConfigDict(server_fields=["created_at"])
    id: str
    title: str
    created_at: datetime | None = None

post = await Post(id="a", title="First").save()
post.created_at        # populated from the row the server sent back

What it does, precisely:

Creates (save(), bulk_create()) omit the column, which is exactly what lets the server's DEFAULT apply.
Replaces (update(), upsert()) keep it. DEFAULT is a create-time default on both DB lines, so on UPDATE … CONTENT an omitted optional column would be deleted and an omitted required one is a hard server error. The instance's value is carried instead, which for any record loaded or saved through the ORM is the server's own.
After a write, the column is hydrated back onto the instance.
An explicit write still works: merge(created_at=…), bulk_update(created_at=…) and server_values={"created_at": …} all go through, because naming the column is taken as consent (a backfill, an admin correction). get_or_create() / update_or_create() count too: a server field named as a criterion or in defaults is stored on both branches, so get_or_create(owner="bob", region="us") finds the row it created on the next call instead of creating another under the server's default.

That last point is where server_fields differs from a computed field. A Computed[...] field is defined by DEFINE FIELD … VALUE, so the server discards any client write — the ORM raises rather than let an invisible no-op through. get_server_fields() returns the union of both sets, since for payload building they are the same thing. A name in server_fields that the model does not declare raises ValueError naming it.

`merge(refresh=False)`

By default merge() resyncs the instance from the row the UPDATE itself returns — one round-trip, no follow-up read, and a merge that matched no record raises SurrealDbError. For fire-and-forget updates, ask the server not to send that row at all:

await user.merge(last_seen=now, refresh=False)                       # compiled with RETURN NONE
await user.merge(server_values={"seen": SurrealFunc("time::now()")}, refresh=False)

The write still happens and the literal keyword arguments are applied locally.

What you give up: the "no rows came back ⇒ record not found" check is the returned row. Under refresh=False a merge against a record that no longer exists is a silent no-op instead of a SurrealDbError, and a field computed by server_values keeps its stale value until the next refresh(). Keep the default whenever you need to know the write landed.

tx, server_values, extra_vars and refresh are reserved keyword names on merge().

21. Live queries (real-time notifications)

A live query is a server-side subscription: SurrealDB pushes a notification every time a record it watches is created, updated or deleted. It needs a WebSocket connection, and it works on both SurrealDB 2.x (2.6 / 2.7) and 3.x (3.2 / 3.3).

Typed events: `live()`

Since v0.20.0, live() streams model instances, with the same API as the full SurrealDB-ORM. The queryset's filters are applied by the server, and the live query is killed when the block exits — even if the body raised:

from surreal_orm_lite import LiveAction

async with User.objects().filter(role="admin").live() as stream:
    async for event in stream:
        match event.action:
            case LiveAction.CREATE:
                print("new admin:", event.instance.name)
            case LiveAction.UPDATE:
                print("admin updated:", event.instance)
            case LiveAction.DELETE:
                print("admin removed:", event.record_id)

Each item is a ModelChangeEvent:

Attribute	Type	Meaning
`action`	`LiveAction`	`CREATE`, `UPDATE` or `DELETE`
`instance`	your model	the record after the change; for `DELETE`, its last state
`record_id`	`str`	the affected record, e.g. `"user:abc"`
`changed_fields`	`list[str]`	diff mode only: the top-level fields that changed, as Python field names
`raw`	`dict / list`	the payload as received: a dict, or the patch list in diff mode (a 2.x diff `DELETE` is a dict)
`validation_error`	`ValidationError \| None`	set when the record does not fit the model; `None` otherwise

A record that does not validate against the model does not end the stream — one bad row written by another client, or a fetch()-resolved link on a field typed str, would otherwise stop your async for and kill the subscription. The event carries the error in validation_error and an instance built without validation from the record as received, the way exec() falls back to plain rows. Check event.validation_error before trusting event.instance; post_live_change handlers are not called for such events, so they only ever see validated instances. A warning naming the failing fields (never their values) is logged once per stream.

What the server filters. filter() (keyword lookups, Q objects, Var references with variables()) and fetch() are honoured, with field aliases translated as everywhere else. The filter is evaluated against each change, which has two consequences worth knowing:

a record that enters the filter through an update is reported as UPDATE, not CREATE;
a record that leaves it reports nothing — not even a DELETE — and a later DELETE of that record is not reported either, because it no longer matched.

Filter values are written into the statement. SurrealDB 2.x does not carry bound parameters into a live query: LIVE SELECT … WHERE status = $s is accepted, then $s evaluates to NONE at notification time and the filter matches nothing, silently. So for live queries only, the ORM writes each value as a SurrealQL literal — the same approach as the full SurrealDB-ORM — and the filter behaves identically on both lines. The encoder is closed: None, bool, int, float, str, Decimal, datetime (written in UTC), UUID, RecordID, and lists or dicts of these; since v0.23.0 also timedelta and Duration (1h30m), date (d'2026-02-01T00:00:00Z'), sets (sorted arrays) and Range (9..17, 65..), see section 24. Anything else, a string containing a NUL character, an integer beyond 64 bits, a Decimal SurrealDB would round or a zero, negative or oversized duration raises TypeError when you call live(), before anything is sent. A Var must be supplied through variables() — an unbound reference would read as NONE on every notification, so age > $min_age would match every record; it raises SurrealDbError instead. SurrealDB's own parameters need no variables(): Var("auth") (and $access, $session, $token) stays a reference the server reads on every notification, so filter(owner=Var("auth")) streams the signed-in record user's own records, on both lines; the string "$auth" is data, written as '$auth' (see String filter values and Var()). Every other query keeps binding parameters.

Diff mode

live(diff=True) asks the server for patches instead of whole records (LIVE SELECT DIFF):

async with Product.objects().live(diff=True) as stream:
    async for event in stream:
        if event.action == LiveAction.UPDATE:
            print(event.record_id, "changed", event.changed_fields)  # ["price", "stock"]

A CREATE still carries the whole record, so event.instance is complete. An UPDATE carries only patches: event.instance holds just the id, changed_fields lists what changed, and event.raw is the patch list. The format is JSON Patch extended by SurrealDB: a changed string arrives as {"op": "change", "value": "<diff-match-patch text>"} rather than a replace. A DELETE gives an instance holding the id on 3.x, and its last state on 2.x (see the behaviour table).

The `post_live_change` signal

Handlers connected to post_live_change run for every event a live() stream yields — changes made by any client, unlike post_save and friends, which fire for this process's own writes:

from surreal_orm_lite import LiveAction, post_live_change

@post_live_change.connect(Player)
async def on_player_change(sender, instance, action, record_id, changed_fields, **kwargs):
    if action == LiveAction.CREATE:
        await broadcast({"type": "player_joined", "name": instance.name})

Handlers run on one background task per stream, in event order — a slow handler for a CREATE never lets the DELETE that follows overtake it — and never stall the iteration. One that raises is logged and does not interrupt anything. Leaving the block normally waits for the pending handlers (up to stream.signal_drain_timeout, 5 s by default) so the last event's side effects are not lost; leaving it on an exception cancels them. The signal fires only while you iterate a live() stream.

Surviving a dropped connection

Since v0.21.0 a live() or watch() stream survives a WebSocket that drops — a network failure, a server restart, a proxy timing out. The ORM notices within milliseconds, opens a new connection (signed in, session restored), sends the same live query again and carries on: an async for parked on the stream simply receives the next event later. stream.live_id changes.

async def catch_up(old_id, new_id):
    # A live query only reports what happens while it runs: re-read what the outage hid.
    await refresh_dashboard(await Order.objects().filter(status="open").all())

async with Order.objects().filter(status="open").live(on_reconnect=catch_up) as stream:
    async for event in stream:
        ...

on_reconnect(old_id, new_id) — sync or async, called with both UUIDs once the new subscription is already buffering, so nothing written after it is missed while it runs. An exception in it is logged, never raised.
The outage itself is not replayed. Changes made while the connection was down are lost to a live query. Catch up in on_reconnect, or pair the stream with a change feed (below), which can be resumed exactly from a cursor.
Backoff: reconnect_delay (0.5 s) doubling up to reconnect_max_delay (30 s), at most reconnect_max_attempts (10; None retries forever) — class attributes on LiveModelStream and LiveStream, overridable per stream. Only connection failures are retried. When the attempts run out, or anything else goes wrong (the table was removed, the session token was refused), the error is raised from the iteration and is_active turns False.
Never under another identity. A live query runs with the permissions of whoever opened it, and a new connection comes back as the identity the manager replays (the stored session token, else the configured user). If the stream was opened under an identity a new connection cannot restore (signin(..., store=False)), or what a new connection would restore is no longer that identity (another stored token, other connection settings, a refused session replay), the stream ends with SurrealDbAuthenticationError instead of resubscribing as somebody else. The comparison is on the settings themselves, so changing them and changing them back — or signin() then invalidate() — counts as no change. It is per connection, so another event loop's identity changes do not leak in. Identity changes made directly on the SDK client are not seen by the ORM.
auto_resubscribe=False ends the stream on a drop with SurrealDbConnectionError, as does any secondary reader from subscribe_live(uuid), which is tied to one uuid. Before v0.21.0 they all waited forever.
Teardowns you ask for still end the stream. kill(), leaving the block, close_connection(), reconnect(), set_url(reconnect=True) — none of them triggers a resubscribe. stop() during a reconnect cancels it.
A silent network loss with no TCP reset is noticed by the WebSocket keepalive, within about 40 s.

The connection manager benefits too: get_client() now replaces a dropped WebSocket client instead of handing back a dead one, and concurrent callers share one new connection. A request that was already in flight when the socket dropped still fails — with the SDK's own KeyError — because retrying arbitrary queries is planned for v0.40.0.

One exception: an identity the manager does not store. signin(), signup() and authenticate() with store=False change the connection's own identity (that is the SDK's behaviour) without telling the manager to restore it. If that connection drops, a new one could only come back as the configured user — a silent change of privileges. So every call on that event loop raises SurrealDbAuthenticationError until the application chooses: sign in or authenticate again, invalidate(), or reconnect()/close_connection() to continue as the configured user. Live streams on that connection end the same way. To mint tokens for other users without touching the shared connection, use the model-level auth (section 19), which runs on its own short-lived connections.

Migrating from SurrealDB-ORM

live(), ModelChangeEvent, LiveModelStream, LiveAction and post_live_change have the same names, fields and arguments as in the full ORM — change the import from surreal_orm to surreal_orm_lite. The differences:

auto_resubscribe= and on_reconnect= are accepted with the same defaults (v0.21.0). on_reconnect receives UUIDs rather than str, like lite's live_id; str(old_id) works with both.
Lite refuses a clause a live query cannot honour (order_by(), limit(), …) where the full ORM ignores it, and honours fetch().
record_id is filled in diff mode too.

Raw envelopes: `watch()`

watch() takes the same filters and diff= but yields the server's raw envelope, a plain dict, instead of an event:

async with User.objects().filter(role="admin").watch() as stream:
    async for notif in stream:
        print(notif["action"], notif["result"])
        if notif["action"] == LiveAction.DELETE:
            break
# the live query is killed here, even if the body raised

Key	Type	Meaning
`action`	`str`	`CREATE`, `UPDATE` or `DELETE` — compare against `LiveAction`
`id`	`UUID`	the live query's own uuid
`record`	`RecordID`	the affected record
`result`	`dict`	the record after the change; for `DELETE`, its last content; patches with `diff=True`
`session`	`UUID`	SurrealDB 3.x only — absent on 2.x

Values keep their SDK types (RecordID, Datetime) rather than being coerced. With diff=True the raw payload differs by line — see the behaviour table; live(diff=True) normalises it.

LiveAction is a StrEnum, so it compares directly against the raw string and you never need to quote a magic value.

The explicit form

When the subscription must outlive one block — read in one task, stopped from another — start the stream yourself, hand the stream to the reader, and kill it by uuid from anywhere:

from surreal_orm_lite import SurrealDBConnectionManager

stream = await User.objects().filter(role="admin").watch().start()  # or .live().start()

async def reader() -> None:
    async for notif in stream:  # ends when the live query is killed
        handle(notif)

task = asyncio.create_task(reader())
...
await SurrealDBConnectionManager.kill(stream.live_id)

A started stream buffers every notification until it is read, so always read it rather than opening a second reader on its uuid and leaving the stream's own buffer to grow. SurrealDBConnectionManager.subscribe_live(live_id) adds another reader to the same live query — useful to fan one subscription out to several consumers, each of which must keep reading.

await User.objects().live(), the v0.19.0 way to get the uuid, still works but is deprecated (DeprecationWarning). The returned stream also behaves as a coroutine for that path, so asyncio.create_task(qs.live()) and asyncio.run(qs.live()) keep working too. Like a coroutine it can be awaited once, and an awaited stream refuses to start() (and the reverse): the two would be separate subscriptions, and leaving the block would kill only one of them.

subscribe_live() is not a coroutine: call it without await. Buffering starts the moment you call it rather than at the first iteration, so a write you make in between is still reported. Each reader on a uuid receives every notification.

A read that times out loses nothing, so polling with a deadline is safe:

stream = SurrealDBConnectionManager.subscribe_live(live_id)
while True:
    try:
        notif = await asyncio.wait_for(anext(stream), timeout=1.0)
    except TimeoutError:
        continue  # a quiet second; the stream is still alive
    handle(notif)

await stream.aclose() stops one reader and drops its buffer without killing the live query.

kill() is idempotent — an unknown or already-killed uuid is a no-op — and it ends every open stream on that uuid, so the async for above terminates rather than waiting forever.

What a live query will refuse

A non-WebSocket connection. Live queries cannot run over HTTP; live(), watch() and subscribe_live() say so instead of letting the SDK raise a bare NotImplementedError.
A clause it cannot honour. SurrealDB's LIVE SELECT has no ORDER BY, LIMIT or START, and a typed stream needs whole records, so select(), limit(), offset(), order_by(), values() and annotate() are rejected by name rather than silently ignored.
A transaction. A live query is a connection-level subscription with no transaction to join, so objects(tx=tx).live() is refused rather than quietly run outside it.
A filter value with no SurrealQL literal form (see above) — TypeError at the call.
A table that does not exist, on SurrealDB 3.x. You get a SurrealDbNotFoundError naming the table. On 2.x the same call succeeds and simply never notifies. Define the table first if you need the two lines to behave alike.

The stream handles

live() returns a LiveModelStream and watch() a LiveStream, both exported from the package root. They share these members:

Member	Type	Meaning
`live_id`	`UUID \| None`	the live query's uuid; `None` before start and after `stop()`
`table`	`str`	the table being watched
`is_active`	`bool`	whether the subscription is running
`start()`	`async` → self	open the subscription; a no-op on a running stream
`stop()`	`async` → `None`	kill the subscription; safe before start and to repeat

__aexit__ calls stop(), so the async with form needs none of these. They are there for the cases where you hold the stream yourself.

Lifecycle and limitations

A reader parked on async for only wakes when something wakes it, so it is worth knowing exactly what ends a stream.

kill() ends it, and so does leaving a live() or watch() block. This works identically on both server lines.
Closing the connection ends it. close_connection() releases every reader on its event loop before the socket goes, and close_all_connections() releases every reader on every loop, so an ordinary shutdown does not leave a wedged task behind. Leaving a watch() block afterwards does not reconnect: with the connection gone there is nothing left to kill.
A timed-out read does not end it. Cancelling a pending read loses nothing; the next read picks up where it left off.
A WebSocket that drops on its own does not end it (v0.21.0): the stream resubscribes — see Surviving a dropped connection. With auto_resubscribe=False, and for subscribe_live() readers, it raises SurrealDbConnectionError instead.
Subscriptions are per event loop. A uuid belongs to the connection that created it, on the loop that created it. Killing it from another loop is not supported.
A failed kill() releases the readers but keeps the uuid alive. When the server refuses the kill for any reason other than an unknown uuid, the error is raised, the readers are woken, and the subscription is still running — so you can subscribe to it again or retry stop(). A kill caught by a dropped connection is not a refusal: the subscription died with the socket, so kill() records it as ended and returns normally.
The buffer is unbounded. A stream you stop reading but never kill keeps accumulating notifications. Use the async with forms, or pair every uuid you start with a kill().

22. Change feeds (`changes()`)

A change feed is SurrealDB's durable log of a table's writes, kept for a retention period you choose. Unlike a live query, nothing is missed while nobody reads: a consumer that stops and restarts resumes exactly where it left off. It suits event streaming, replication and audit.

DEFINE TABLE order SCHEMALESS CHANGEFEED 7d;   -- only writes made after this are recorded

stream = Order.objects().changes(since=load_cursor())   # None = from now
async for event in stream:
    await publish(event.action, event.instance)          # ModelChangeEvent, as for live()
    save_cursor(stream.cursor)                           # after processing

stream.cursor is an int: pass it back as since= to resume. It moves past a transaction only once that transaction's last event has been yielded, so saving it after each event gives at-least-once delivery: nothing is ever skipped, and a transaction cut short by a crash is delivered again in full.
since= takes a cursor, a datetime or ISO-8601 string (naive means UTC), or None for changes made after the stream starts. A datetime is exact on SurrealDB 3.x and at-least-once on 2.x, whose server also returns some earlier changes.
What an event says. A change feed records writes, not their kind: a creation arrives as UPDATE, a deletion as DELETE. With CHANGEFEED … INCLUDE ORIGINAL on the table, updates also carry changed_fields, and on SurrealDB 3.3 a deletion carries the last state of the record (elsewhere the instance holds only its id).
poll_interval (0.1 s) is the pause when the feed has nothing new; batch_size (100) the entries read per poll. stop() ends the iteration at once when it is waiting between polls or in a retry backoff, or as soon as a request already sent returns; start() fixes the starting position before the first read.
A read under asyncio.timeout() or asyncio.wait_for() times out as asked and loses nothing: the stream keeps going, and the next read starts from the cursor. Before v0.22.2 a read cancelled just as the server answered could be retried as a dropped connection instead, and never time out.
Works over HTTP as well as WebSocket. A dropped connection is retried with the same backoff as live streams, and nothing is lost, because the next read starts from the cursor.
Refused: any queryset clause — SHOW CHANGES has no WHERE, so filter() would silently stream the whole table — and a table that is missing or has no change feed (on the table or on its database), with the DEFINE TABLE to run.
Writing to a change-feed table on SurrealDB 2.x: wrap the write in retry_on_conflict. On 2.x, concurrent writes to a CHANGEFEED table conflict with each other, even on different records. Measured on 2.7.0 with 8 writers sharing one connection: 2.5 to 4 % of save() and merge() calls were refused with a retryable conflict, in every second of the run. A single writer was refused far more rarely: at most once per cycle of the server's change-feed garbage collection (--changefeed-gc-interval, 10 s by default), and not on every cycle. The same 8-writer load refused nothing on a table without a change feed, nor on 3.3.4 (save(), merge() and delete()). The ORM does not retry these writes for you: apply retry_on_conflict yourself (see section 14). A refused write stores nothing, so replaying it is safe for the data; the pre_save and around_save handlers run again on each attempt. Outside a transaction the error is the SDK's own InternalError ("… This transaction can be retried"), which is_conflict_error() recognises; once the retries run out, retry_on_conflict raises SurrealDbConflictError.
```
from surreal_orm_lite import retry_on_conflict

@retry_on_conflict()
async def save_order(order: Order) -> Order:
    return await order.save()

await save_order(Order(id="o1", total=42))
```

Why the cursor is not the server's versionstamp. SurrealDB 2.x and 3.x number their feeds differently: resuming a 2.x feed with the stamp it returned yields nothing at all, and asking 3.x for changes since a datetime always answers empty. ChangeModelStream normalises both (see the behaviour table), so the same code resumes correctly on either line.

23. Typed edges (`BaseSurrealEdge`)

Since v0.22.0 a graph edge can be a model: its data validated by Pydantic, created with SurrealDB's INSERT RELATION (the official SDK's insert_relation()), and read, filtered and watched like any other record, with the same behaviour on SurrealDB 2.x and 3.x. The string form of section 7 (relate("follows", …)) is unchanged.

from pydantic import Field, ValidationError
from surrealdb import RecordID
from surreal_orm_lite import BaseSurrealEdge, BaseSurrealModel, SurrealConfigDict, SurrealDbError


class User(BaseSurrealModel):
    id: str | None = None
    name: str


class Product(BaseSurrealModel):
    id: str | None = None
    name: str


class Purchased(BaseSurrealEdge):
    model_config = SurrealConfigDict(table_name="purchased")

    quantity: int = Field(ge=1)
    price: float


class Follows(BaseSurrealEdge):
    model_config = SurrealConfigDict(table_name="follows")

    weight: int = 1

BaseSurrealEdge declares the two endpoints, so a subclass declares only the edge's data:

in_ (stored in the column in, a Python keyword) and out hold a RecordID. Each accepts a saved model instance, a RecordID or a "table:id" string whose id is read as a string ("User:1" is User(id="1"), as for relate()). For an id with other characters (joe@x.com) or an integer id, pass the instance or a RecordID. Anything else, an unsaved instance or "nocolon", is a ValidationError.
table_name maps the model to its edge table, here the lower-case purchased and follows that relate("purchased", …) writes (see Custom table name). Without it the table is the class name.
A subclass may not redeclare in_ or out (TypeError at the class statement).

Creating edges

alice = await User(id="alice", name="Alice").save()
bob = await User(id="bob", name="Bob").save()
carol = await User(id="carol", name="Carol").save()
widget = await Product(id="widget", name="Widget").save()
gadget = await Product(id="gadget", name="Gadget").save()

# relate() with an edge class: validated, created, returned typed
edge = await alice.relate(Purchased, widget, quantity=2, price=9.5)
assert isinstance(edge, Purchased) and edge.in_ == RecordID("User", "alice")
edge = await alice.relate(Purchased, gadget, data={"quantity": 1, "price": 4.0})  # data= works too

# save() on an edge instance
edge = await Purchased(
    in_=RecordID("User", "bob"), out=RecordID("Product", "widget"), quantity=1, price=9.5
).save()
print(edge.id)                                   # purchased:… (the server's RecordID)

# bulk_create(): the whole batch in one insert_relation() round-trip
edges = await Purchased.objects().bulk_create([
    Purchased.model_validate({"in": alice, "out": widget, "quantity": 3, "price": 9.0}),
    Purchased.model_validate({"in": bob, "out": gadget, "quantity": 1, "price": 4.0}),
])

try:
    await alice.relate(Purchased, widget, quantity=0, price=9.5)
except ValidationError:
    pass                                         # checked before anything is sent: nothing written

The edge is a real relation: ->purchased->Product, traverse() and get_related(Purchased, …) see it. A CREATE or UPSERT carrying in/out would store a plain record that no traversal sees, which is why upsert() is refused on an edge (below).
edge.id is the server's RecordID; computed fields and server_fields are read back from the row, as on any create. save() and relate(Edge, …) fire the save signals (pre_save, around_save, post_save with created=True); bulk_create() fires none, as for nodes.
relate(Edge, …) checks the edge fields before anything is sent: an endpoint (in, in_, out) or a computed field raises ValueError, a field the model does not declare SurrealDbError (unless the model sets extra="allow"). reverse=True and tx= work as in section 7: alice.relate(Follows, bob, reverse=True) creates bob -> follows -> alice.
Purchased(in_=alice, …) works at run time, but a type checker expects a RecordID there: Purchased.model_validate({"in": alice, …}) or relate() is the type-checker-clean form with model instances.

Reading edges

mine = await Purchased.objects().filter(in_=alice).order_by("-quantity").exec()
same = await Purchased.objects().filter(in_="User:alice").exec()             # a "table:id" string
same = await Purchased.objects().filter(in_=RecordID("User", "alice")).exec()
both = await Purchased.objects().filter(in___in=[alice, "User:bob"]).exec()  # the in lookup on in_
products = await alice.get_related(Purchased, model_class=Product)

An edge model reads like any model (get(), filter(), Q, count(), aggregations). On the endpoints a filter value may be a model instance, a RecordID or a "table:id" string, read by the rule the fields validate with, so filter(in_="User:alice") finds alice's edges (a string never equals a record id in SurrealQL, so it would otherwise match nothing). A text lookup on an endpoint (in___contains, out__startswith, …) raises ValueError. in___in is the in lookup on the field in_ (see Fields ending in an underscore).

Endpoints are immutable

SurrealDB ignores a change to an existing edge's endpoints on both lines: an UPDATE writes the other fields and leaves in/out as they were. The ORM refuses the change instead of letting it pass silently:

edge = await alice.relate(Purchased, widget, quantity=1, price=9.5)

try:
    edge.out = RecordID("Product", "gadget")      # a frozen field
except ValidationError:
    pass
try:
    await edge.merge(out=gadget)                  # refused before anything is sent
except ValueError as exc:
    print(exc)  # merge(): `in`/`out` are the endpoints of edge `Purchased`; … delete the edge and relate again.

await edge.merge(quantity=3)                      # the edge's data changes as usual

# To move an edge, delete it and relate again
await edge.delete()
edge = await alice.relate(Purchased, gadget, quantity=3, price=4.0)

ValueError, before any call, for every write that names an endpoint: merge() (and its server_values=), a patch() or objects().patch() pointer on /in or /out, the atomic_* helpers, objects().bulk_update(), and update_or_create(defaults=…).
update(), a full replace, sends the endpoints unchanged: SurrealDB 2.x rejects a replace without them on a TYPE RELATION … SCHEMAFULL table.
upsert() and save(server_values=…) raise SurrealDbError: an UPSERT stores a plain record that no traversal sees, and INSERT RELATION cannot evaluate SET expressions (put a DEFINE FIELD … DEFAULT or VALUE on the edge table instead).
edge.model_copy(update={"out": …}) is not checked, because Pydantic skips validation there. That is legitimate for a copy meant as a new edge (model_copy(update={"id": None, "out": other}), then save()); on a copy that kept its id, update() or merge() would send an endpoint change the server silently ignores.

Explicit ids

a, w = RecordID("User", "alice"), RecordID("Product", "widget")
await Purchased(id="p1", in_=a, out=w, quantity=1, price=9.5).save()
try:
    await Purchased(id="p1", in_=a, out=w, quantity=5, price=9.5).save()
except SurrealDbError as exc:
    print(exc)  # … Database record `purchased:p1` already exists

An edge with an id goes through a guarded INSERT RELATION: an id that already exists raises SurrealDbError ("already exists") on both lines, and the stored edge is unchanged. A raw INSERT RELATION does not do that everywhere: SurrealDB 2.7.0 and 3.1.5 silently keep the stored edge and return it as if it had been inserted (see the behaviour table). bulk_create() takes the same path, so one duplicate in a batch, stored already or repeated in the batch, writes nothing. With other endpoints under an existing id, 2.7.0 and 3.1.5 report a field mismatch ("Found … which does not match the existing field value") instead of "already exists": still a SurrealDbError, still nothing changed. An id that is a RecordID of another table raises ValueError before anything is sent (SurrealDB 2.x would silently move it into the edge table).

Idempotent edges

get_or_create() is the convenient idempotent relate: it looks the pair up and creates the edge only when there is none. Instances, RecordIDs and "table:id" strings converge on one row:

follow, created = await Follows.objects().get_or_create(in_=alice, out=bob)                # created: True
follow, created = await Follows.objects().get_or_create(in_="User:alice", out="User:bob")  # False, same edge

Between its lookup and its create, a concurrent caller can create the same edge. SurrealDB's own mechanisms close that window on the server, and through the ORM both raise SurrealDbError on 2.x and 3.x alike.

A deterministic array id, one id per pair: a second save() with that id raises "already exists" (the guard above).

a, c = RecordID("User", "alice"), RecordID("User", "carol")
await Follows(id=RecordID("follows", [a, c]), in_=a, out=c).save()
try:
    await Follows(id=RecordID("follows", [a, c]), in_=a, out=c, weight=2).save()
except SurrealDbError as exc:
    print(exc)  # … Database record `follows:[User:alice, User:carol]` already exists

For a symmetric relation (a friendship, where alice–carol and carol–alice are one edge), sort the pair first (sorted([a, c], key=str)) and use that order for the id and for in_/out. A raw RELATE that names an existing edge id silently overwrites it on both lines, so create these edges with save() or bulk_create(), which go through the guard. Such an edge is then updated and deleted like any other, update(tx=) and delete(tx=) included: the ORM binds the record id as a query parameter, never writes it into the statement.

A UNIQUE index on in, out: the server refuses a second edge for the same ordered pair, whatever its id.

from surreal_orm_lite import SurrealDBConnectionManager


class Likes(BaseSurrealEdge):
    model_config = SurrealConfigDict(table_name="likes")


client = await SurrealDBConnectionManager.get_client()
await client.query("DEFINE INDEX likes_pair ON likes FIELDS in, out UNIQUE;", {})

await alice.relate(Likes, bob)
try:
    await alice.relate(Likes, bob)
except SurrealDbError as exc:
    print(exc)  # … Database index `likes_pair` already contains [User:alice, User:bob], …
await bob.relate(Likes, alice)                    # the reverse pair is another edge: allowed

The index is directional, so the reverse pair is allowed. A second string relate("likes", …) for the same pair fails as well, with the SDK's ServerError (the string form does not translate server errors).

Refused writes and permissions

SurrealDB answers an INSERT RELATION that the edge table's PERMISSIONS FOR create refuse (or an edge the user may not select afterwards) with fewer rows and no error, on both lines. The ORM raises SurrealDbError instead: "Can't save data, no record returned: …" for one edge (save(), relate(Edge, …); post_save does not fire) and "Only k of n edges came back: …" for bulk_create(). Outside a transaction such a batch is not atomic: the edges the server accepted stay written. Inside an interactive transaction the error rolls the whole transaction back; a buffered transaction cannot detect the refusal, since nothing comes back before commit.

Transactions

async with SurrealDBConnectionManager.transaction() as tx:
    edge = await bob.relate(Follows, carol, tx=tx)
    await Purchased.objects(tx=tx).bulk_create([
        Purchased.model_validate({"in": carol, "out": gadget, "quantity": 2, "price": 4.0}),
    ])
print(edge.id)  # a RecordID on an interactive transaction, None on a buffered one

Edges follow the strategy of every write (section 11). On an interactive transaction (WebSocket + SurrealDB 3.x) the row comes back at once: edge.id is set, objects(tx=) reads see the edge, and a rollback removes it. On a buffered one (2.x, or HTTP) the edge is queued and written at commit, then rolled back with the batch if the commit fails. An auto id is allowed there, unlike a node's buffered save(), so edge.id stays None; bulk_create() returns its inputs, and a duplicate explicit id fails the commit. post_save fires after the commit. The graph helpers of section 7 take tx= too.

Live queries and change feeds

async with Purchased.objects().filter(in_=alice).live() as stream:
    await alice.relate(Purchased, gadget, quantity=4, price=4.0)
    async for event in stream:
        print(event.action, event.instance.out, event.instance.quantity)  # CREATE Product:gadget 4
        break

live(), watch() and changes() work on edge models unchanged, and the typed instance is an edge. A live filter on an endpoint takes the same values as filter(): an instance, a RecordID or a "table:id" string. SurrealDB 2.x adds "__": true to the edge record of a CREATE or UPDATE notification and of a change-feed update entry; the typed instance drops it, so it is the same on both lines, while event.raw keeps the payload as the server sent it.

A `TYPE RELATION` table, defined by hand

class Reviewed(BaseSurrealEdge):
    model_config = SurrealConfigDict(table_name="reviewed")

    stars: int = Field(ge=1, le=5)


await client.query("DEFINE TABLE reviewed TYPE RELATION IN User OUT Product;", {})
await alice.relate(Reviewed, widget, stars=5)
try:
    await alice.relate(Reviewed, bob, stars=1)    # out must be a Product
except SurrealDbError as exc:
    print(exc)

Typed edges work with whatever table exists: one created by the first edge, a TYPE ANY table, or a TYPE RELATION [IN … OUT …] [ENFORCED] table you define. The server's refusals come back as ORM errors on both lines: an endpoint from the wrong table raises SurrealDbError; with ENFORCED, an endpoint record that does not exist raises SurrealDbNotFoundError; a TYPE NORMAL table raises SurrealDbError. A helper that writes this DDL from the model (the full ORM's table_type=TableType.RELATION, relation_in, relation_out and enforced keys, on BaseSurrealEdge) is planned for v0.33.0.

Migrating from SurrealDB-ORM (graph API)

The graph helpers of section 7 have the full ORM's method names, parameter order and meaning (reverse=, keyword edge fields, tx=, lists of edges, direction="both", a record id without its table as the remove_relation() target): change the import from surreal_orm to surreal_orm_lite. What differs:

The string relate() returns a list[dict], the created rows ([] inside a buffered transaction), where the full ORM returns one dict. Changing it would break lite's v0.6.0 API.
data= is lite-only. The full ORM's **edge_data works too, and the two merge (a field in both is a TypeError). An edge field named like a parameter (data, tx, reverse, edge, target or self) must go through data=.
Typed edge models (BaseSurrealEdge, relate(EdgeModel, …)) are lite-only. The full ORM declares a relation table with table_type=TableType.RELATION (plus relation_in, relation_out and enforced), which is DDL only; lite gets those keys, with those names, on BaseSurrealEdge in v0.33.0.
table_name is validated as an identifier when the class is defined, because it is written into SurrealQL.
upsert() and save(server_values=…) are refused on an edge (an UPSERT stores a plain record that no traversal sees).
Lite's save() keeps create semantics, so an existing id raises "already exists", where the full ORM's save() upserts.
Positional calls are identical to the full ORM: relate(edge, target, reverse, tx), remove_relation(edge, target, reverse, tx), remove_all_relations(edge, direction, tx), get_related(edge, direction, model_class). Only the names of the first two parameters differ, edge/target in lite and relation/to in the full ORM, because renaming them would break existing v0.6.0 keyword calls. data= is keyword-only (lite-only).
get_related() without model_class returns the neighbours' record ids (RecordID), where the full ORM returns the records as dicts (SELECT VALUE out.*). Pass model_class to get instances. Changing it would break lite's v0.6.0 API.

24. Rich field types

Since v0.23.0 a model can declare timedelta, date and set[X]/frozenset[X] fields, and the ORM's own Duration and Range[T]: every write stores them as SurrealDB's native values and every read returns the declared type, with the same behaviour on SurrealDB 2.x and 3.x (one exception: TYPE set<…> columns, see Sets). datetime, Decimal, UUID and bytes fields keep working as before.

from datetime import UTC, date, datetime, timedelta
from decimal import Decimal
from uuid import UUID

from surreal_orm_lite import (
    BaseSurrealModel,
    BoundExcluded,
    BoundIncluded,
    Duration,
    Range,
    SurrealConfigDict,
)


class Subscription(BaseSurrealModel):
    model_config = SurrealConfigDict(table_name="subscription")

    id: str | None = None
    started_at: datetime
    renews_on: date
    trial: timedelta
    grace: Duration | None = None
    price: Decimal
    token: UUID
    active_hours: Range[int]
    period: Range[datetime] | None = None
    logo: bytes | None = None
    tags: set[str] = set()


await Subscription(
    id="acme",
    started_at=datetime(2026, 1, 2, 9, 30, tzinfo=UTC),
    renews_on=date(2026, 2, 1),
    trial=timedelta(days=14),
    grace=Duration.parse("1h30m1ns"),                         # nanoseconds kept
    price=Decimal("9.99"),
    token=UUID("0190c3d1-5c3b-7cc6-8f8a-2d2b1f0e9a11"),
    active_hours=Range(BoundIncluded(9), BoundExcluded(17)),  # 9..17: 9 included, 17 excluded
    period=Range(BoundIncluded(datetime(2026, 1, 1, tzinfo=UTC)), None),  # open end
    logo=b"\x89PNG",
    tags={"pro", "annual"},
).save()

sub = await Subscription.objects().get("acme")
sub.trial         # timedelta(days=14): a duration on the server
sub.renews_on     # date(2026, 2, 1): a datetime at midnight UTC on the server
sub.tags          # {'annual', 'pro'}: an array on the server
sub.active_hours  # Range(begin=BoundIncluded(value=9), end=BoundExcluded(value=17))

Annotation	Sent as	Stored as	Read back as
`datetime`	as before (a naive value is taken as UTC)	`datetime` (UTC)	an aware UTC `datetime`, to the microsecond
`Decimal`	as before; a value written with an exponent goes out in fixed-point notation	`decimal`	`Decimal`
`UUID`	as before	`uuid`	`UUID`
`bytes`	as before	`bytes`	`bytes`
`timedelta`	a duration	`duration`	`timedelta`, floored to the microsecond
`date` (subclasses included)	a `datetime` at midnight UTC	`datetime`	`date`
`set[X]`, `frozenset[X]`	an array, sorted when its items can be ordered	`array`	the set or frozenset (see Sets)
`Duration`	a duration	`duration`	`Duration`, to the nanosecond
`Range[T]`, `Range`	a range; an open side as SurrealDB's null bound	`range`	`Range`, each bound value validated as `T`

Optional fields (ttl: timedelta | None = None) behave the same. Values nested in containers (list[timedelta], dict[str, date], a sub-model's fields) are sent the same way at any depth. On reads, a timedelta is converted from the SDK's duration at any depth too: a top-level field, list[timedelta], dict[str, timedelta], timedelta | str or a sub-model's timedelta field all read a timedelta, floored to the microsecond (declare Duration to keep the nanoseconds). Your validators (any mode) and model_post_init still apply once to the value read, even when they change it in place. A naive datetime is written as UTC and read back as an aware UTC datetime. The instance keeps it naive until a call applies the row the server returns (see After a write that returns the row): after save() (since #261 with an explicit id too), refresh(), merge() (default refresh=True), upsert(), patch() or an atomic_* helper, the field holds that aware UTC datetime, so compare it with aware values. update(), and save(tx=) in a buffered transaction, leave it naive.

`Duration`

from datetime import timedelta

import surrealdb
from surreal_orm_lite import Duration, InvalidDurationError

d = Duration.parse("1h30m1ns")
d.elapsed                 # 5400000000001: nanoseconds
d.to_string()             # '1h30m1ns': every unit, lossless (str(d) too)
d.to_compact()            # [5400, 1]: [seconds, nanoseconds]
d.get_seconds_and_nano()  # (5400, 1)
d.to_timedelta()          # timedelta(seconds=5400): floored to the microsecond
Duration.parse(90, 5)     # Duration(elapsed=90000000005): an int is seconds, then nanoseconds
Duration.from_timedelta(timedelta(days=7))            # Duration(elapsed=604800000000000)
Duration.parse(" 1h30m ") == Duration.parse("1h30m")  # True: surrounding whitespace is ignored

try:
    Duration.parse("1h 30m")             # "1.5h" is refused too
except InvalidDurationError as exc:      # a ValueError and a surrealdb.InvalidDurationError
    print(exc)

Duration(5) == surrealdb.Duration(5)     # True
surrealdb.Duration(5) == Duration(5)     # False: keep the ORM value on the left

timedelta stops at microseconds: declare a field Duration to keep the server's nanoseconds. Duration is the ORM's own class, not the SDK's surrealdb.Duration, but it mirrors the SDK 2.0.0 class: the same members under the same names and parameters (elapsed, parse(value, nanoseconds=0) with an int in seconds, get_seconds_and_nano(), to_compact(), to_string() and the unit properties seconds, minutes, hours, days, …), with exact results: get_seconds_and_nano() uses integer arithmetic, to_compact() keeps the nanoseconds (SDK 2.0.0 returns [seconds]) and to_string() writes every unit (SDK 2.0.0 writes "1h" for 1h30m1ns). parse(str) reads the server's syntax strictly: surrounding whitespace is accepted, "1h 30m" and "1.5h" are refused. A refused value raises InvalidDurationError, which is both a ValueError and a surrealdb.InvalidDurationError; a wrong argument type raises TypeError. Duration adds MAX_NANOSECONDS (the largest SurrealDB duration), from_timedelta(), to_timedelta(), hashing and ordering.

As a field, Duration accepts a Duration, the SDK's Duration, a non-negative timedelta or a SurrealQL duration string ("1h30m"), and refuses a bare number, which does not say its unit. In JSON it is its SurrealQL text: model_dump_json() writes "1h30m1ns", which model_validate_json() reads back.

Equality with the SDK's values is one-way. Duration(5) == surrealdb.Duration(5) is True, but surrealdb.Duration(5) == Duration(5) is False: the SDK's __eq__ answers False for any other type, so Python never asks the ORM's. The same holds for Range and the SDK's Range. Keep the ORM value on the left, or compare elapsed.

`Range[T]`

from pydantic import TypeAdapter

from surreal_orm_lite import BoundExcluded, BoundIncluded, Range

str(Range(BoundIncluded(9), BoundExcluded(17)))  # '9..17'
str(Range(BoundExcluded(1), BoundIncluded(5)))   # '1>..=5'
str(Range(begin=BoundIncluded(65)))              # '65..': None is an open side
str(Range())                                     # '..'

hours = TypeAdapter(Range[int])
hours.validate_python(range(9, 17))  # Range(begin=BoundIncluded(value=9), end=BoundExcluded(value=17))
hours.validate_json('"9..17"')       # the same range: in JSON a Range is its SurrealQL text

A Range has two bounds, each BoundIncluded(value) or BoundExcluded(value) (the SDK's classes, exported by the ORM) or None for an open side; Range(1, 5) raises TypeError. The SDK's own spellings of an open side, a bare Bound(), surrealdb.cbor.undefined and a CBOR null, are accepted too and stored as None. Whatever its spelling, an open side is sent as SurrealDB's null bound, the form its protocol defines.

As a field, Range[int], Range[datetime], Range[timedelta]… validate each bound value as T (a bare Range keeps them as given) and accept the SDK's Range, a builtin range with step 1 (range(9, 17) is 9..17) or the SurrealQL text str() writes ("9..17", "d'2026-01-01T00:00:00Z'..", "1m..1h"). A Range[timedelta] turns the SDK's duration bounds into timedeltas (floored to the microsecond) and a Range[date] accepts midnight datetime bounds. In JSON a Range is that text, which model_validate_json() reads back: every bound is written exactly, a zero duration as 0ns and a Decimal with all its digits, even where a live query filter refuses the value. A Range is not hashable, like the SDK's.

Filters

class Slot(BaseSurrealModel):
    model_config = SurrealConfigDict(table_name="slot")

    id: str | None = None
    hour: int


for hour in (8, 9, 12, 17):
    await Slot(id=f"h{hour}", hour=hour).save()

# in / not_in with a Range: bound as one value (hour IN $_f0), never expanded
working = await Slot.objects().filter(hour__in=Range(BoundIncluded(9), BoundExcluded(17))).exec()  # 9, 12
late = await Slot.objects().filter(hour__not_in=Range(end=BoundExcluded(17))).exec()              # 17

# Filter values are sent like field values
trials = await Subscription.objects().filter(trial__gte=timedelta(days=7), renews_on=date(2026, 2, 1)).exec()
cheap = await Subscription.objects().filter(price__lte=Decimal("9.99"), tags__containsall={"pro"}).exec()
open_at_noon = await Subscription.objects().filter(active_hours__contains=12).exec()  # 12 in 9..17
graced = await Subscription.objects().filter(grace__lte=Duration.parse("2h")).exec()

Filter values on rich fields must be typed: they are sent exactly like field values, and strings are not converted. filter(trial__gte="7d") compares the stored duration with a string, which does not give the result you want: pass timedelta(days=7).

in and not_in take a Range, the ORM's or the SDK's, and bind it as one value; a builtin range keeps its meaning, the list of its integers. On the record-id column id and on an edge's in/out columns a Range raises ValueError, since a record id never falls inside a value range. contains on a range column tests membership (active_hours__contains=12). Live queries (section 21) write these values into the statement as SurrealQL literals (1w, d'2026-02-01T00:00:00Z', ['pro'], 9..17), with the same results on both lines.

Precision and `Decimal`

datetime: to the microsecond. The SDK truncates the server's nanoseconds, so a stored d'2026-01-02T03:04:05.123456789Z' reads …05.123456. update() and upsert() rewrite the whole record, but a datetime you did not change (it still floors to the stored value) keeps the stored nanoseconds, on both lines and the embedded engine: a record whose READONLY datetimes are top-level fields can be updated after get() or save(), even when they hold nanoseconds (#261). A datetime you changed is written as you set it, and a READONLY field still refuses it. So that the statement stays small whatever the data, only the top-level datetimes are sure to be kept, up to 32 per record. Below the top level (in a sub-model, a list or a tuple, down to three levels) a datetime is kept only while the values rebuilt to reach it fit in what the fields before it left of those 32, so it depends on the data: an update() that passes in a test can fail once a list of timestamped items before the sub-model grows. A datetime not kept, and one in a set (its items have no position to match a stored value by), is written as sent: a READONLY field holding one may refuse update(), so change such a record with merge(). SurrealDB 2.x also refuses, for now, update() and upsert() of an existing record that has a field with a non-optional TYPE and a VALUE clause the model does not write (a Computed field, such as a generated updated: Computed[datetime]): change it with merge() there (#267).
timedelta: floored to the microsecond on read (1h30m1ns reads timedelta(seconds=5400)).
Duration: exact, to the nanosecond.
Decimal: a value whose str() writes no exponent is sent as before. A value written with an exponent (Decimal("1E-7"), Decimal("1E+3")) goes out in fixed-point notation (0.0000001, 1000), cut after the 29th fractional digit: the server keeps at most 28 fractional digits, rounding half away from zero as before, so the stored value is the same. A finite value whose integer part, rounded, exceeds 2**96 - 1 (the largest SurrealDB decimal) is refused before sending. On reads, SurrealDB 2.x keeps trailing zeros (Decimal("12.50") reads 12.50) where 3.x normalises them (12.5); the values are equal. NaN and ±Infinity are stored as floats, as before: declare Annotated[Decimal, Field(allow_inf_nan=True)] to read them back.

Sets

set[X]/frozenset[X] fields are stored as arrays. On SurrealDB 3.3 a TYPE set<…> column stores the array as a native set, which the field reads back as the declared set (see Values the SDK cannot read), but 3.1 refuses an array in it; to support 3.1, declare such columns array<…>, optionally with VALUE $value.distinct():

DEFINE FIELD tags ON subscription TYPE array<string> VALUE $value.distinct();

On a schemaless or array<…> column the field reads back as the declared set or frozenset, on both lines. The array is sorted when its items can be ordered, so a set is written the same way every time.

Values refused before sending

Every value the ORM sends (a field, a filter value, a call_function() argument, an auth variable) goes through the same check. A value SurrealDB would reject is refused before anything is sent, with a plain ValueError that says why, never re-wrapped in a SurrealDbError:

Value	Raised
A negative duration (a `timedelta`, the SDK's `Duration`)	`ValueError`
A duration of `2**64` seconds or more	`ValueError`
A zero duration, if neither the SDK nor the ORM reads one back (never with SDK 2.0.0)	`ValueError`
A finite `Decimal` whose integer part, rounded, exceeds `2**96 - 1`	`ValueError`
An `int` outside the signed 64-bit range	`ValueError` (use `Decimal`)
A `Range` side that is neither `BoundIncluded`/`BoundExcluded` nor an open side	`ValueError`
`id__in=Range(…)`, `id__not_in=Range(…)`, or the same on an edge's `in`/`out`	`ValueError`
`atomic_increment(field, <a duration>)`	`TypeError` (use `merge()`)
In a live filter, a negative or oversized duration, or a range bound with no literal form	`TypeError` from `live()`
A `Duration` or `Range` field given a value it does not accept	Pydantic's `ValidationError`

A zero duration (timedelta(0), Duration(0)) is written, filtered and read back like any other, on both lines: the surrealdb SDK 2.0.0 cannot read one, but the clients the ORM opens do (see Values the SDK cannot read); in the one case where nothing reads it back, a live filter refuses it with TypeError too. Since v0.24.3 the ORM stores zero durations, so a record it writes can hold one that a reader outside its clients cannot read: an AsyncSurreal client the application builds itself, or another service on the SDK 2.0.0. Filter "longer than zero" with __gt=Duration(0) or __gte=Duration(1). atomic_increment() adds numbers only: SurrealDB 2.x ignores += with a duration, so the ORM refuses it on both lines; set the new value with merge():

nonzero = await Subscription.objects().filter(trial__gte=Duration(1)).exec()  # longer than zero
await sub.merge(trial=sub.trial + timedelta(days=7))                          # not atomic_increment()

After a write that returns the row

save() (with or without an id, an edge's too), refresh(), merge(), upsert(), patch() and the atomic_* helpers apply the row the server returns to the instance. After save() the instance therefore holds what the record holds: the server_fields and computed fields, the sub-fields a DEFAULT filled (prefs.at DEFAULT time::now()), and the values the server coerced or rewrote (VALUE string::lowercase($value)), so a following update() writes them back unchanged (#261). An id you gave stays as you gave it; an auto id is the server's RecordID. A buffered transaction (HTTP, SurrealDB 2.x, the embedded engine) returns no row before commit: there save(tx=) leaves the instance as it was, and refresh() after the commit reads the row.

The top-level timedelta, Duration and geometry fields (since v0.24.0, see section 25) are converted on the way, and every other field gets the value a read gives (#261; before, only a field holding a sub-model, a geometry or a nested timedelta did): the row goes through the model's validation, the model's own validators included. A sub-model field holds its model instance, not the row's dict, with the ORM's durations, timedeltas, ranges and geometries inside; an enum field holds the enum, not its str or int; a tuple, set, date or Range field holds its declared type, not the SDK's list, midnight datetime or Range; a float field stored in a decimal column holds a float, not a Decimal; a list[Duration] holds the ORM's durations. When the row as a whole does not validate, each field is validated on its own, except one that the model's own validators could reshape (a field_validator on that field, a model_validator, a model_post_init): that one, like a value that does not validate, is applied as received, so these calls never fail on it. The id keeps the server's RecordID. A column the model does not declare goes where a read puts it: as the SDK reads it into model_extra under extra="allow", nowhere otherwise. So a column holding a Pydantic @computed_field (model_dump() writes it) or named like a method (json) never replaces that attribute.

Writing such an instance back is safe, sub-models included: a following update() replaces the record with these values, as after get(). So a float field stored in a decimal column writes the float's rounding (0.12345678901234567890 becomes 0.1234567890123457: declare Decimal to keep every digit), and under extra="allow" the columns in model_extra are written back, not removed. An enum declared (str, Enum) or (int, Enum) holds the member, as after get(): it compares equal to its value, but str() and f-strings give Color.RED, not red (a StrEnum or an IntEnum gives the value), so use .value to build a key, a URL or a log line from it.

Values the SDK cannot read

The surrealdb SDK 2.0.0 cannot decode a zero duration, a SurrealDB 3.x native set (a TYPE set<…> column on SurrealDB 3.3, see Sets) or a degenerate geometry (see Geometries the SDK cannot read): one such value fails the whole response it is in. The clients the ORM opens read them anyway, through a hook around the SDK's own CBOR decoder that handles only these values; everything else reads exactly as the SDK reads it:

a zero duration reads as zero: timedelta(0) or Duration(0) in a typed field, the SDK's Duration(0) in an untyped one;
a native set reads as a Python set, which set[X] and frozenset[X] fields validate, or as a list when its items are unhashable (record ids, objects, nested sets) or two of them are equal in Python, like 1 and true: every item the server holds comes back.

Written back through the ORM (merge(v=row.v), or update(), which rewrites the whole row, the untouched fields included), a zero duration stays a duration, but a native set is stored as an array, like every set the ORM writes, unless its column is TYPE set<…> (see Sets), and a degenerate geometry as a plain object (see Geometries the SDK cannot read). In an untyped field, update() flattens the SDK's Duration(0) into an object, like any SDK value there (#222).

The hook is installed only while the installed SDK cannot decode these values, and only on the ORM's own clients (SurrealDBConnectionManager.get_client(), ephemeral_client()), the embedded engine's included (see Embedded engine and test fixtures): there a zero duration and a degenerate geometry read as on a server, and <set>[…] is stored as an array, as on 2.x. An AsyncSurreal client the application builds itself keeps the SDK's behaviour and fails on them. A later SDK reads zero durations and native sets itself (#205), and the hook then leaves them to it.

Authentication

Rich values in the fields of an AuthenticatedUserMixin model's signup(), its defaults included (trial: timedelta = timedelta(days=7)), and in the variables= of SurrealDBConnectionManager.signin()/signup() are sent like any other write: a date reaches the SIGNUP clause as a midnight UTC datetime and a timedelta as a duration, on both lines. A refused value raises ValueError before anything is sent.

Coming from the full SurrealDB-ORM

Plain datetime, Decimal and UUID annotations work as they do there. The full ORM has no timedelta or Range fields and its Duration is a string: timedelta, Duration and Range[T] fields are lite additions. The full ORM also validates the rows its writes return; so does lite, every field but the id since #261, with top-level durations converted (see After a write that returns the row).

25. Geospatial fields

Since v0.24.0 a model can declare geometry fields: every write stores them as SurrealDB's native geometries, every read returns the ORM's geometry classes, and nearby(), GeoDistance and the inside, outside and intersects lookups query them, with the same results and the same distances on SurrealDB 2.x and 3.x. The names are the full SurrealDB-ORM's (GeoField, PointField, nearby(), GeoDistance); what differs is listed at the end of this section.

Declaring geometry fields

from surreal_orm_lite import (
    BaseSurrealModel,
    Computed,
    GeoField,
    GeometryPoint,
    LineStringField,
    PointField,
    PolygonField,
    SurrealConfigDict,
    computed,
)


class Restaurant(BaseSurrealModel):
    model_config = SurrealConfigDict(table_name="restaurant")

    id: str | None = None
    name: str
    location: PointField = None                # GeometryPoint or None, default None: the full ORM's form
    delivery_area: GeoField["polygon"] = None  # the same form, with a kind name (see Type checkers)
    entrance: GeometryPoint | None = None      # a class annotation (required without "| None = None")


class Zone(BaseSurrealModel):
    model_config = SurrealConfigDict(table_name="zone")

    id: str | None = None
    name: str
    area: PolygonField = None
    centroid: Computed[GeometryPoint] = computed("IF area.is_polygon() THEN geo::centroid(area) END")


class Route(BaseSurrealModel):
    model_config = SurrealConfigDict(table_name="route")

    id: str | None = None
    name: str
    path: LineStringField = None
    stops: list[GeometryPoint] = []  # inside containers: the classes, not the aliases


await Zone.define_computed_fields()  # the centroid's DEFINE FIELD (see Computed geometry fields)

Annotation	Holds	`GeoField` kind
`PointField`	`GeometryPoint`	`"point"`
`LineStringField`	`GeometryLine`	`"line"` (or `"linestring"`)
`PolygonField`	`GeometryPolygon`	`"polygon"`
`MultiPointField`	`GeometryMultiPoint`	`"multipoint"`
`MultiLineStringField`	`GeometryMultiLine`	`"multiline"` (or `"multilinestring"`)
`MultiPolygonField`	`GeometryMultiPolygon`	`"multipolygon"`
`GeometryCollectionField`	`GeometryCollection`	`"collection"` (or `"geometrycollection"`)
`GeometryField`	any of them (a `Geometry`)	`"feature"` (or `"geometry"`)

GeoField["kind"] and the aliases are nullable with default None, as in the full ORM: the field is not required and model_construct() sets None. The kind is one case-insensitive string; several kinds are separated by a pipe (GeoField["point|polygon"] holds a point or a polygon), and an unknown kind raises ValueError where the class is defined. A class annotation (entrance: GeometryPoint) is required unless written GeometryPoint | None = None, and Geometry holds any kind. At nested positions (list[GeometryPoint], dict[str, GeometryPolygon], a sub-model's field) use the classes: an alias inside a container validates normally, but Pydantic warns at class definition that its default has no effect there. Geometry fields work on edge models (BaseSurrealEdge) and with field aliases (Field(default=None, alias="loc")). is_geo_field() and get_geo_info(), from surreal_orm_lite.geo, tell whether an annotation is a geometry field and which kinds it holds (get_geo_info(PolygonField) is "polygon").

Geometry values and GeoJSON

from surreal_orm_lite import Geometry, GeometryLine, GeometryPolygon

times_square = GeometryPoint(-73.9855, 40.7580)  # longitude, latitude: the GeoJSON order
midtown = GeometryPolygon.parse_coordinates(
    [[(-74.0, 40.745), (-73.97, 40.745), (-73.97, 40.765), (-74.0, 40.765), (-74.0, 40.745)]]
)
broadway = GeometryLine(GeometryPoint(-73.9897, 40.74), GeometryPoint(-73.9845, 40.76))

times_square.to_geojson()  # {'type': 'Point', 'coordinates': [-73.9855, 40.758]}
Geometry.from_geojson({"type": "Point", "coordinates": [-73.9855, 40.758]}) == times_square  # True
str(times_square)          # "{type: 'Point', coordinates: [-73.9855, 40.758]}": its SurrealQL text

await Restaurant(id="pizza", name="Corner Pizza", location=GeometryPoint(-73.9877, 40.7547)).save()
await Restaurant(id="burgers", name="Park Burgers", location=(-73.9881, 40.7417)).save()  # a pair
await Restaurant(
    id="deli", name="Village Deli", location={"type": "Point", "coordinates": [-73.9969, 40.729]}
).save()  # a GeoJSON object
await Restaurant(id="bistro", name="Paris Bistro", location=GeometryPoint(2.3522, 48.8566)).save()
await Restaurant(id="truck", name="Food Truck").save()  # no location
await Zone(id="midtown", name="Midtown", area=midtown).save()
await Route(id="broadway", name="Broadway", path=broadway, stops=[times_square]).save()

deli = await Restaurant.objects().get("deli")
deli.location                             # GeometryPoint(longitude=-73.9969, latitude=40.729)
deli.model_dump()["location"]             # the same GeometryPoint
deli.model_dump(mode="json")["location"]  # {'type': 'Point', 'coordinates': [-73.9969, 40.729]}

The seven classes, GeometryPoint, GeometryLine, GeometryPolygon, GeometryMultiPoint, GeometryMultiLine, GeometryMultiPolygon and GeometryCollection, subclass Geometry and have the names, constructor signatures, attributes and methods of the SDK's classes (surrealdb.data): GeometryPoint(longitude, latitude), parse_coordinates(), get_coordinates(), geometry_points, … They are the ORM's own classes because Pydantic's model_dump() turns the SDK's dataclasses into plain dicts; the ORM converts them to the SDK's classes when it sends them, and SurrealDB stores native geometries. They are immutable (setting an attribute raises AttributeError, and an attribute holding several items is a tuple) and hashable; copy, pickle and Pydantic's copies keep them, so a geometry works as a field default; repr() is the SDK's format.

Coordinates are real numbers, stored as float. A bool, a string, None or a Decimal raises TypeError (convert a Decimal with float()). A non-finite coordinate, a polygon ring of fewer than 4 points or whose last point is not its first, and a GeometryCollection nested more than 32 levels deep raise InvalidGeometryError, which is both a ValueError and a surrealdb.InvalidGeometryError. GeoJSON goes both ways: to_geojson() and __geo_interface__ (read by shapely and similar libraries) give the GeoJSON dict, and Geometry.from_geojson() reads one (GeometryPolygon.from_geojson() requires a polygon); a 3D position, an unknown type or a Feature raises InvalidGeometryError.

A geometry field accepts the ORM value, the SDK's, a GeoJSON object and, for points, a (longitude, latitude) pair; anything else, or a refused value, raises Pydantic's ValidationError. model_dump() returns the ORM value and model_dump(mode="json") its GeoJSON object, which model_validate_json() reads back.

Equality with the SDK's values is one-way, as for Duration: GeometryPoint(1, 2) == surrealdb.data.GeometryPoint(1.0, 2.0) is True, the reverse is False. Keep the ORM value on the left.

`nearby()`

near = await Restaurant.objects().nearby("location", times_square, max_distance=2_000).exec()
sorted(r.name for r in near)  # ['Corner Pizza', 'Park Burgers']: within 2 km of Times Square

nearby(field, point, max_distance) keeps the records whose field is a point within max_distance metres of point, as SurrealDB's geo::distance measures it. The point is a GeometryPoint (the ORM's or the SDK's), a (longitude, latitude) pair or a GeoJSON Point; it and the distance are bound as query variables, never written into the statement. A .is_point() guard leaves out the records whose field is not a point (missing, NULL, a polygon, a string, a GeoJSON object), on both lines. Several calls are AND-ed, and the condition joins filter() wherever that applies: exec(), first(), get() without an id, count() and the other aggregations, values()/annotate() groups, bulk_update(), bulk_delete(), patch(), and live()/watch(). all() ignores it, as it ignores filter(), and changes() refuses it. A field the model declares as a geometry that holds no point (PolygonField, GeoField["line"]) raises ValueError, and so does a negative or non-finite distance.

Distance: GeoDistance

from surreal_orm_lite import GeoDistance

closest = await (
    Restaurant.objects()
    .nearby("location", times_square, max_distance=5_000)
    .annotate(dist=GeoDistance("location", times_square))
    .order_by("dist")
    .limit(3)
    .exec()
)
[(r.name, round(r.dist)) for r in closest]  # [('Corner Pizza', 411), ('Park Burgers', 1826), ('Village Deli', 3365)]

farthest_first = await Restaurant.objects().annotate(dist=GeoDistance("location", times_square)).order_by("-dist").exec()
farthest_first[0].name                             # 'Paris Bistro'
farthest_first[-1].name, farthest_first[-1].dist  # ('Food Truck', None): no location, last

annotate(<alias>=GeoDistance(field, point)) gives each instance the distance as an attribute named after the alias (r.dist): metres as a float, or None when the record's field is not a point. order_by("dist") sorts the nearest first, after the records without a point; order_by("-dist") sorts the farthest first and the records without a point last. The attribute is never written back: model_dump(), update() and upsert() ignore it. Rows that do not validate (a select() that omits a required field) come back as dicts carrying the alias.

The alias is a projection, not a column: filter(dist__lt=500) filters a stored column named dist, so bound the distance with nearby(). Choose an alias no record stores: on an extra="allow" model, a stored, undeclared column of that name is replaced by the distance in the results and missing from the instance, so a later update() or upsert() removes it. An alias that names a declared field or its column, id, in or out, starts with _ or model_, or names a model attribute (save, objects, …) raises ValueError. A GeoDistance cannot be combined with aggregations (Count(), …) or values(), and live(), watch() and changes() refuse annotate().

Spatial lookups

inside = await Restaurant.objects().filter(location__inside=midtown).exec()    # Corner Pizza
outside = await Restaurant.objects().filter(location__outside=midtown).exec()  # the four others
zones = await Zone.objects().filter(area__contains=times_square).exec()         # Midtown
crossing = await Route.objects().filter(path__intersects=midtown).exec()        # Broadway

inside, outside and intersects compare a field with a geometry through SurrealQL's INSIDE, OUTSIDE and INTERSECTS; the existing contains lookup tells whether a polygon field contains a point (area__contains=times_square). The value of a spatial lookup is an ORM or SDK geometry, a GeoJSON object or a Var, bound as one value; the lookups work in filter(), in Q objects (~Q(...) included) and in live queries. A list, a tuple, a string or None raises ValueError (use __in for membership in a list), and so does a spatial lookup on the record id or on an edge's in/out.

outside keeps SurrealQL's meaning: it is true for every record whose field is not inside the geometry, including a record whose field is missing, NULL, a string or a GeoJSON object stored as a plain object. That is why Food Truck, which has no location, is among outside; ~Q(location__inside=midtown) returns the same records.

Geometries in live queries

import asyncio

async with Restaurant.objects().nearby("location", times_square, 2_000).live() as stream:
    await Restaurant(id="crepes", name="Seine Creperie", location=GeometryPoint(2.35, 48.86)).save()
    await Restaurant(id="cart", name="Hot Dog Cart", location=GeometryPoint(-73.9851, 40.7589)).save()
    async with asyncio.timeout(5):
        async for event in stream:
            print(event.instance.name)  # Hot Dog Cart: the Paris write is not notified
            break

nearby(), the spatial lookups and geometry filter values work in live() and watch() (section 21), where the ORM writes each value into the statement as a GeoJSON object literal ({type: 'Point', coordinates: [-73.9855, 40.758]}), with the same notifications on both lines. Typed live streams and change feeds (section 22) deliver instances holding the ORM's classes.

Filter values, keyword writes and assignments

same_spot = await Restaurant.objects().filter(location=GeometryPoint(-73.9877, 40.7547)).exec()  # Corner Pizza

pizza = await Restaurant.objects().get("pizza")
await pizza.merge(location=GeometryPoint(-73.9876, 40.7548))  # a geometry object, not a pair
pizza.entrance = GeometryPoint(-73.9877, 40.7546)             # the same for an assignment
await pizza.update()

Give geometry objects wherever the field does not validate the value. The values of the other lookups (exact, contains, …) are bound as given, and keyword writes (merge(), bulk_update(), the atomic_* helpers) are not validated against the field (#220, #221): a (longitude, latitude) pair or a GeoJSON dict there is sent as an array or an object, which matches nothing or is stored as such. Models do not validate assignments, so pizza.entrance = (-73.9877, 40.7546) keeps the tuple, which Pydantic warns about when it serialises it and which is stored as an array. A geometry filter value matches a stored geometry exactly.

Decimal coordinates of SDK geometries

The ORM's classes refuse a Decimal coordinate, and so does a geometry field given an SDK geometry that holds one. An SDK geometry (surrealdb.data.GeometryPoint, …) given as a filter value, a raw_query() variable or a keyword write is only checked for what SurrealDB refuses (below), and SurrealDB converts its Decimal coordinates to floats. A Decimal coordinate written with an exponent goes out in fixed-point notation, as a top-level Decimal does (see Precision and Decimal): before v0.24.0, SurrealDB 2.x refused Decimal("1E-7") there, and both lines stored a long one (Decimal("1.234567890123456789012345678901E-7")) with a wrong value; both lines now store the value. An SDK geometry SurrealDB would refuse raises ValueError before anything is sent: a line of fewer than 2 points, a polygon without a ring, a ring of fewer than 4 points once closed, a coordinate that is a bool, a string, None or an integer beyond 64 bits, or a member that is not a geometry of the expected kind.

Computed geometry fields

await Zone(id="unmapped", name="Unmapped").save()

(await Zone.objects().get("midtown")).centroid   # a GeometryPoint at (-73.985, 40.755), computed by SurrealDB
(await Zone.objects().get("unmapped")).centroid  # None: the guard skips a zone without an area

Zone.centroid is a computed field (section 16) read back as an ORM GeometryPoint. Guard geometry functions with a type check: geo::centroid() raises on a record without a polygon, so computed("geo::centroid(area)") would make every write of a zone without an area fail, while IF area.is_polygon() THEN … END leaves its centroid None.

Migrating stored GeoJSON

for restaurant in await Restaurant.objects().all():
    await restaurant.update()  # a location read from a GeoJSON object is written back as a native point

SurrealDB keeps a GeoJSON dict as a plain object, not a geometry: the full SurrealDB-ORM stores its geometry fields that way, and so does any client that writes GeoJSON dicts. nearby() skips such objects, geo::* functions and geometry<…> columns refuse them, and no SurrealQL cast turns them into geometries. A geometry field reads them (a GeoJSON object validates, in a read and in the row a write returns), and update() writes the instance back with a native geometry, so loading and updating every record migrates a table.

Untyped fields

A field declared Any or dict, or an undeclared column of an extra="allow" model, is not a geometry field. A GeoJSON dict written there stays a plain object (see Migrating stored GeoJSON); an ORM geometry written there is stored as a native geometry but reads back as the SDK's class (surrealdb.data.GeometryPoint, …), which a later write of the instance stores as a plain object (#222). Declare geometry fields with the annotations above. The results of raw_query(), query() and call_function() are untyped too: they hold the SDK's classes, which compare equal to the ORM's with the ORM value on the left.

Type checkers

mypy sees each alias as its class or None (PointField is GeometryPoint | None). Without the Pydantic mypy plugin, mypy only knows a default written in the class body: write location: PointField = None (or = Field(default=None, alias="loc")) in code that builds the model without that field; at runtime the default is there either way. Type checkers do not understand GeoField["point"] (mypy: "GeoField" expects no type arguments), as in the full ORM: in type-checked code, use the aliases or the classes.

Geometries the SDK cannot read

A degenerate geometry written by another client (a line of fewer than 2 points, alone or inside another geometry, or a polygon ring of fewer than 4 points) cannot be decoded by the surrealdb SDK 2.0.0, nor by its 3.0 pre-releases. The clients the ORM opens read it as its GeoJSON dict, such as {"type": "LineString", "coordinates": [[1.0, 2.0]]}; a multi-geometry or a collection holding one reads as GeoJSON as a whole (see Values the SDK cannot read for where this applies):

an untyped field (Any, dict), raw_query() and live notifications give that dict;
a geometry field refuses it with a ValidationError naming the field, since the ORM's geometry classes refuse degenerate values: get() raises it, and filter().exec() and raw_query() return such rows as dicts, as for any row that does not validate.

The ORM never writes one: its classes refuse such values, and so does the check of the SDK geometries it sends (see Decimal coordinates of SDK geometries). Written back, the dict an untyped field holds is stored as a plain object, not a geometry, on both lines, like any GeoJSON dict (see Migrating stored GeoJSON): merge(v=row.v) does it, and so does update() after a change to another field, since it rewrites the whole row.

Coming from the full SurrealDB-ORM

Field values are geometry objects, not GeoJSON dicts. location.to_geojson() gives the dict, model_dump(mode="json") writes GeoJSON, and a GeoJSON dict (or a (longitude, latitude) pair for points) is accepted as input, so full-ORM-shaped data keeps loading. SurrealDB keeps a GeoJSON dict as a plain object, which geo::* functions and geometry<…> columns refuse; lite stores a native geometry (see Migrating stored GeoJSON).
get_geo_info() returns SurrealDB's kind names: "line" for LineStringField (the full ORM: "linestring"), "point|polygon" for a union; GeoField["linestring"] is still accepted.
nearby() keeps its name and argument order. The point and the distance are bound, never written into the statement; a .is_point() guard skips the records whose field is not a point; several calls are AND-ed (the full ORM keeps the last); a field declared as a geometry that holds no point raises ValueError; the point may also be an ORM or SDK GeometryPoint or a GeoJSON point.
GeoDistance: its value reaches each instance as an attribute named after the alias (the full ORM computes it but does not attach it), None when the record's field is not a point; .point is an ORM GeometryPoint (the full ORM keeps the tuple); the aliases that would hide a column, the id, an edge endpoint or a model attribute are refused; it cannot be combined with aggregations or values().
Lite additions: the eight value classes, MultiLineStringField, MultiPolygonField, GeometryCollectionField, GeometryField, InvalidGeometryError and the inside, outside and intersects lookups.
Aliases inside containers (list[PointField]) make Pydantic warn at class definition; use the classes there (list[GeometryPoint]).
Imports: from surreal_orm_lite import GeoField, PointField, GeoDistance, … (the full ORM: surreal_orm, surreal_orm.fields, surreal_orm.geo); is_geo_field and get_geo_info come from surreal_orm_lite.geo (the full ORM: surreal_orm.fields). There is no surreal_orm_lite.fields module.

26. Embedded engine and test fixtures

Since v0.25.0 the ORM also runs on the official SDK's in-process SurrealDB engine: no server and no Docker, the database lives inside your Python process. It suits tests, notebooks and small tools, and the same models and queries run unchanged against a server. The surreal_orm_lite.testing module adds the test helpers: embedded_database() (an isolated database per block) and the full SurrealDB-ORM's SurrealFixture / @fixture.

In-process databases

from surreal_orm_lite import BaseSurrealModel, SurrealDBConnectionManager

SurrealDBConnectionManager.set_connection(url="mem://", namespace="app", database="app")

class Note(BaseSurrealModel):
    id: str | None = None
    title: str
    done: bool = False

await Note(id="docs", title="Write the docs").save()
assert await Note.objects().count() == 1
assert SurrealDBConnectionManager.is_embedded()

Same models, no server: the first ORM call opens the engine. user and password are optional on an embedded URL and never sent — the engine has no system user on a fresh database, and its anonymous session is the in-process owner (see Identity on the embedded engine). They are kept, so one configuration switches between a server and mem:// by its URL alone. SurrealDBConnectionManager.is_embedded() tells code that must run on both kinds of connection which one is configured.

Embedded URL	surreal-orm-lite (official SDK 2.0.0)	Full SurrealDB-ORM
`mem://`, `memory://`	✅ in memory, gone when the connection closes	❌ (HTTP/WebSocket only)
`surrealkv://<path>`	✅ on disk (SurrealKV)	❌
`surrealkv+versioned://<path>`	✅ on disk, keeps history: time travel with `as_of()` (section 27)	❌
`file://<path>`	✅ a SurrealKV store, not the RocksDB store `file://` means for the SurrealDB server	❌
`rocksdb://<path>`	❌ not yet supported by the official SDK (#233): the ORM raises the SDK's refusal	❌

A bare memory (no ://) is refused by SDK 2.0.0 and accepted by the SDK 3.0 line; write memory://. The engine is SurrealDB core 2.3.10, bundled with SDK 2.0.0 — not the 2.x and 3.x server lines the ORM is tested against (see below). surreal-orm-lite keeps no list of embedded schemes: the SDK decides, so rocksdb:// will work with no ORM change once a released SDK accepts it (#233).

A URL the installed SDK refuses is accepted by set_connection() and reported by the first ORM call, before anything is opened, with a SurrealDbConnectionError that quotes the SDK:

from surreal_orm_lite import SurrealDbConnectionError

SurrealDBConnectionManager.set_connection(url="rocksdb://app.db", namespace="app", database="app")
try:
    await Note.objects().count()
except SurrealDbConnectionError as error:
    print(error)
# The installed surrealdb SDK cannot open 'rocksdb://app.db': 'rocksdb' is not a valid UrlScheme.
# The URL schemes it accepts are: file, http, https, mem, memory, surrealkv, surrealkv+versioned,
# ws, wss. Embedded RocksDB (rocksdb://) is not supported by the official SDK yet: see
# https://github.com/EulogySnowfall/SurrealDB-ORM-lite/issues/233.

# Back to mem://: the same in-memory database as above, which only a close discards.
SurrealDBConnectionManager.set_connection(url="mem://", namespace="app", database="app")

One engine per process

A server client is bound to the event loop it connected on, so the manager keeps one per loop (see Connections and event loops). The engine is not: the manager keeps one engine for the whole process, opened on the first call and handed to every event loop and thread, so they all see the same database. Concurrent first calls open exactly one engine.

import asyncio

async def count_notes() -> int:
    return await Note.objects().count()

# Another event loop, on another thread: the same engine, the same data.
assert await asyncio.to_thread(lambda: asyncio.run(count_notes())) == 1

await SurrealDBConnectionManager.close_connection()  # the in-memory database is discarded
assert await Note.objects().count() == 0              # a new, empty one

Namespace and database are a selection on that engine. After a change (set_connection(), set_namespace(), set_database()), the next call resets the shared session on the same engine — nothing is lost — and selects the new pair; a stored session ends (see Identity). A new URL closes the old engine and opens the new one.
Closing — close_connection() (from any loop), close_all_connections(), unset_connection(), reconnect() and the exit of async with SurrealDBConnectionManager(): — closes the engine: a mem:// database is discarded with it, and the next call opens a new, empty one; a surrealkv:// or file:// database reopens with its data.
validate_connection() opens the engine if needed and never discards it, so the set_*(..., reconnect=True) setters keep a mem:// database.
Server clients are untouched while an embedded URL is configured, and are used again when a server URL is configured back. close_connection(), reconnect() and the exit of async with SurrealDBConnectionManager(): close only the engine then: a server client keeps its live queries and its identity, even one signed in with store=False, and is_connected() stays True on an event loop that keeps one. An event loop whose server connection dropped while signed in with store=False (section 21) still raises once the server URL is set back with set_url(), or after an embedded_database() block: closing the engine, or setting an identity on it (signin(), signup(), authenticate(), invalidate(), model auth with bind=True), does not end that state. set_connection() does: a new configuration is a choice of identity. close_all_connections() and unset_connection() close the server clients too.

With the surrealdb SDK 2.x, closing does not stop an in-process engine: a client you obtained before close_connection() (or inside an embedded_database() block) is still a running database. Do not use it after the close, and drop it (del client) before reopening the same surrealkv:// path: two engines on one path miss each other's writes. Never open one path from two processes.

What the embedded engine does not do

Each of these raises a clear ORM error up front, never an SDK AttributeError or an engine error:

Live queries (live(), watch(), subscribe_live()): the engine has no notification channel. Read the table's change feed instead (Model.objects().changes(), section 22), which works from now and from a cursor.
changes(since=<datetime>): the SDK 2.x engine keeps no timestamps for change-feed positions; pass a cursor (ChangeModelStream.cursor) or since=None.
Model auth needs bind=True: the engine has a single session per database, so AuthenticatedUserMixin calls with bind=False (a session of their own) and ephemeral_client() are refused.
rocksdb://: refused by the SDK (#233), see above.

And two differences that raise nothing:

Transactions are buffered: the SDK refuses interactive transactions on the engine, so transaction() picks the buffered strategy, as on HTTP (section 11).
Parse errors are a bare RuntimeError with SDK 2.0.0 (a server raises the SDK's error classes). The DDL helpers (define_computed_fields(), define_access()) raise their usual SurrealDbError.

from surreal_orm_lite import SurrealDbError

try:
    async with Note.objects().live() as stream:
        pass
except SurrealDbError as error:
    print(error)
# Live queries need a WebSocket server connection (ws:// or wss://): the embedded engine ('mem://')
# has no notification channel to deliver them. Read the table's change feed with
# Model.objects().changes() instead, or connect to a server.

Its SurrealQL is the 2.x line

SDK 2.0.0 bundles the SurrealDB 2.3.10 engine: write 2.x SurrealQL in raw queries and computed fields (type::is::string, not 3.x's type::is_string; no DEFINE FIELD … COMPUTED), and expect the 2.x column of the behaviour table — a table that does not exist reads as []. client.version() reports the SDK's version (surrealdb-2.0.0), not the engine's. The SDK 3.0 line bundles a 3.x engine (#205).

Identity on the embedded engine

The anonymous session is the in-process owner: full access (DDL, writes), and info() returns None.
signin() / signup() / authenticate() (section 18) work with the engine's record access methods and DEFINE USERs, and record users and table PERMISSIONS apply as on a server. A sign-in changes the session of the whole process: every event loop and thread shares the engine's single session.
invalidate() returns to the in-process owner: a configured user, if any, is never signed in.
A namespace/database change returns the session to the owner and ends the stored session: a token belongs to the database it was issued for, and replaying it would move the session back there.
Model auth with bind=True (section 19) signs that single session in, one call at a time: concurrent bind=True calls, and the connection-level signin() / signup() / authenticate() / invalidate() that hold the same session, are serialised (each bind=True call reads back its own record and adopts its own token) and the last one wins. A failed bind=True puts the previous session back, with one exception, as on a server: when signup() or signin() succeeded but the record does not fit the model, the session stays signed in as that record (the error says so). refresh() cannot succeed on the engine: its SurrealQL does not parse WITH REFRESH.

assert await SurrealDBConnectionManager.info() is None  # anonymous: the in-process owner

Test fixtures

embedded_database() runs a block against an isolated in-memory database (mem:// by default, namespace and database test), then puts the previous connection back: its settings, stored session and engine. Blocks nest. It yields the block's SDK client for raw SurrealQL and DDL, valid inside the block only. Pass surrealkv://<path> for an on-disk database (with pytest, under tmp_path).

SurrealFixture and @fixture declare model instances as test data: load() saves a fresh copy of each, in declaration order, yields them, and deletes them on exit. They work on any connection, a server or the engine.

from surreal_orm_lite.testing import SurrealFixture, embedded_database, fixture

class User(BaseSurrealModel):
    id: str | None = None
    name: str
    role: str = "player"

@fixture
class UserFixtures(SurrealFixture):
    alice = User(name="Alice", role="admin")
    bob = User(id="bob", name="Bob", role="player")  # an explicit id is kept

async with embedded_database():  # a fresh, empty in-memory database
    async with UserFixtures.load() as fixtures:
        assert fixtures.alice.get_id() is not None
        admins = await User.objects().filter(role="admin").exec()
        assert [admin.name for admin in admins] == ["Alice"]
    assert await User.objects().count() == 0  # alice and bob are deleted
# the in-memory database is gone; the previous connection is back

With pytest and pytest-asyncio, one fresh in-memory database per test:

import pytest_asyncio
from surreal_orm_lite.testing import embedded_database

@pytest_asyncio.fixture
async def surreal_db():
    async with embedded_database():
        yield

@pytest_asyncio.fixture
async def users(surreal_db):
    async with UserFixtures.load() as fixtures:
        yield fixtures

surreal_db yields nothing, so no test keeps a client past its block; a test that needs raw SurrealQL calls await SurrealDBConnectionManager.get_client(). It works whatever loop scope pytest-asyncio gives the fixture and the test: there is one engine per process.

A fresh engine raises a retryable write conflict when several writes run concurrently on a table that does not exist yet: define the tables your test writes to concurrently first (DEFINE TABLE), or run those writes in a function decorated with @retry_on_conflict() (section 14).

import asyncio

async with embedded_database() as client:
    await client.query("DEFINE TABLE User SCHEMALESS;")  # before the concurrent first writes
    await asyncio.gather(*(User(name=f"user {n}").save() for n in range(20)))
    assert await User.objects().count() == 20

Coming from the full SurrealDB-ORM

The full ORM has no embedded engine: it connects over HTTP/WebSocket only (see the embedded URL table).
surreal_orm_lite.testing mirrors surreal_orm.testing for SurrealFixture and fixture: the same names, the same declarations, load() as an async context manager.
load() keeps an explicit id where the full ORM resets it to None, so a fixture can name its records (and an edge its endpoints). It also cleans up when a save fails or is cancelled mid-load: the instances already saved are deleted before the error propagates.
@fixture raises TypeError on a class that is not a SurrealFixture subclass (the full ORM accepts any class), and load() raises ValueError on an undecorated subclass of a fixture that declares instances of its own (the full ORM loads only the parent's).
load() is typed to yield the fixture class, so fixtures.alice has its model's type (the full ORM yields Any).
Lite additions: SurrealDBConnectionManager.is_embedded() and embedded_database().
ModelFactory and Faker come in v0.39.0.

27. Time travel (`as_of()`)

Since v0.26.0 a QuerySet can read a model's records as they were at a moment: as_of() adds SurrealQL's VERSION clause to every read of the queryset. The store must keep history (see Stores that keep history): the embedded engine's surrealkv+versioned:// keeps it on disk, with no server.

import tempfile

from surreal_orm_lite import BaseSurrealModel, SurrealDBConnectionManager, SurrealDbNotFoundError


class Product(BaseSurrealModel):
    id: str | None = None
    name: str
    price: float


path = tempfile.mkdtemp()
SurrealDBConnectionManager.set_connection(
    url=f"surrealkv+versioned://{path}/shop", namespace="shop", database="shop"
)
client = await SurrealDBConnectionManager.get_client()

lamp = Product(id="lamp", name="Lamp", price=40.0)
desk = Product(id="desk", name="Desk", price=250.0)
await lamp.save()
await desk.save()
opening = await client.query("RETURN time::now();")  # a moment, from the database's clock
await lamp.merge(price=30.0)
sale = await client.query("RETURN time::now();")
await desk.delete()

then = await Product.objects().as_of(opening).order_by("name").exec()
assert [(product.name, product.price) for product in then] == [("Desk", 250.0), ("Lamp", 40.0)]
assert await Product.objects().as_of(sale).count() == 2
assert await Product.objects().count() == 1

# A record deleted since is still found at that moment; one created later is not.
assert (await Product.objects().as_of(sale).get("desk")).price == 250.0
try:
    await Product.objects().get("desk")
except SurrealDbNotFoundError:
    print("no desk now")

Stores that keep history

Where the read runs	`as_of()`
Embedded `surrealkv+versioned://<path>`	✅ (SDK 2.0.0 ignores `?versioned=true` on `surrealkv://`)
Embedded `mem://`, `memory://`, `surrealkv://`, `file://`	`SurrealDbError` before any request
SurrealDB 3.x started on `surrealkv://<path>?versioned=true` or `rocksdb://<path>?versioned=true`	✅
SurrealDB 3.x in memory, or on `surrealkv://` or `rocksdb://` without `versioned=true`	`SurrealDbError`: the server refuses `VERSION` (the SDK's error is its `__cause__`)
SurrealDB 2.x started on `surrealkv+versioned://<path>`	✅
SurrealDB 2.x on RocksDB	`SurrealDbError`: the server refuses `VERSION`
SurrealDB 2.x in memory or on plain `surrealkv://`	no error, and the ORM cannot tell (see below)

surreal start --user root --pass root "surrealkv:///var/lib/surrealdb?versioned=true"  # SurrealDB 3.x
surreal start --user root --pass root "surrealkv+versioned:///var/lib/surrealdb"       # SurrealDB 2.x

The moment

as_of() takes a moment, "how long ago", a server expression, or None:

from datetime import UTC, datetime, timedelta

from surreal_orm_lite import Duration, SurrealFunc

Product.objects().as_of(datetime(2026, 1, 1, 9, 30, tzinfo=UTC))      # an aware datetime, any time zone
Product.objects().as_of(datetime(2026, 1, 1, 9, 30))                  # a naive one is UTC
Product.objects().as_of("2026-01-01T09:30:00+02:00")                  # ISO-8601, parsed by the ORM
Product.objects().as_of("2026-01-01")                                 # a date alone: midnight UTC
Product.objects().as_of(timedelta(hours=1))                           # an hour before the database's clock
Product.objects().as_of(Duration.parse("1h"))                         # the ORM's Duration too
Product.objects().as_of(SurrealFunc("time::floor(time::now(), 1d)"))  # evaluated by the database
Product.objects().variables(cutoff=opening).as_of(SurrealFunc("$cutoff"))
Product.objects().as_of(opening).as_of(None)                          # None: the current state again

assert await Product.objects().as_of(timedelta(0)).count() == 1                     # now
assert await Product.objects().as_of(SurrealFunc("time::now() - 1h")).count() == 0  # before the shop

A naive datetime or ISO-8601 string is UTC, as for every datetime the ORM sends; a date alone is that day's midnight UTC, while a date object is refused (TypeError), and so are numbers, bool and bytes. A string that is not ISO-8601 raises ValueError.
A timedelta or an ORM Duration (>= 0) means that long before the database's clock, and a SurrealFunc is an expression the database evaluates to a datetime: both are resolved by the database just before each read (one RETURN request). A SurrealFunc is written into that RETURN as is: build it in your code, never from user input, and pass user values through variables() (bound), as $cutoff above. _as_of is a reserved variables() name: the moment is bound under it.
The surrealdb SDK's own Duration (surrealdb.Duration) is accepted at runtime only: the annotation names the ORM's types, so a type checker flags it. Duration(sdk_duration.elapsed) converts it.
Called again, the last moment wins. A moment after the last write reads the current state; a moment before the table existed reads no records.

The moment must lie between 1970-01-01T00:00:00Z and 2262-04-11T23:47:16.854775Z, the instants SurrealDB versions can address (nanoseconds since 1970 in a signed 64-bit integer). SurrealDB 2.x reads an earlier moment as the current state, without an error, and every line fails after the later one, so the ORM refuses both, on every line: an absolute moment at the call (ValueError), a duration or an expression once the database has resolved it, before the read is sent (SurrealDbError).

try:
    Product.objects().as_of(datetime(1900, 1, 1))
except ValueError as error:
    print(error)
# as_of() takes a moment from 1970-01-01T00:00:00+00:00 to 2262-04-11T23:47:16.854775+00:00, the
# instants SurrealDB versions can address (SurrealDB 2.x reads an earlier moment as the current
# state), got 1900-01-01T00:00:00+00:00

Reads

Every read of the queryset honours the moment, with filter(), Q, nearby(), order_by(), limit(), offset(), select() and variables(), on edge models too:

Read	Under `as_of()`
`exec()`, `first()`, `get()` with filters	the records as they were, as snapshots (a row that does not validate comes back as a dict)
`get("id")`	the record as it was: found if deleted since, `SurrealDbNotFoundError` if created later
`all()`	every record as it was, as snapshots (`all()` still ignores `filter()`)
`exists()`, `count()`, `sum()`, `avg()`, `min()`, `max()`	the values at that moment
`values(…).annotate(…)`	the groups at that moment, as dicts
`annotate(d=GeoDistance(…))`	snapshots carrying their distances

A plain SELECT … VERSION is wrong on both server lines, so the ORM never sends one on its own. SurrealDB 3.x's table reads return the records deleted before the moment, in their last state (so filtered counts, sums and groups count them too); SurrealDB 2.x and the embedded engine miscount an unfiltered count(). Every time-travel table read therefore also reads each record by its id at the moment, which is right on every versioned store:

SELECT * FROM Product WHERE (SELECT VALUE id FROM ONLY $parent.id VERSION $_as_of) != NONE VERSION $_as_of;

It costs about one extra point read per record scanned; a read by id (get("lamp")) needs no check. The moment is always a bound parameter.

Snapshots

The instances a time-travel read returns are snapshots, read-only: save(), update(), merge(), patch(), upsert(), delete(), the atomic_*() methods, relate(), remove_relation() and remove_all_relations() raise SurrealDbError before any signal or request, and bulk_create() refuses a snapshot in its list. To restore a past state, build a new instance from the snapshot:

from surreal_orm_lite import SurrealDbError

old_lamp = await Product.objects().as_of(opening).get("lamp")
try:
    await old_lamp.save()
except SurrealDbError as error:
    print(str(error).split(":")[0])
# Product.save() cannot run on an instance read with as_of()

# The record exists: update() writes the past state back.
await Product(**old_lamp.model_dump()).update()
assert (await Product.objects().get("lamp")).price == 40.0

# It was deleted since: save() creates it again (an "undelete").
old_desk = await Product.objects().as_of(sale).get("desk")
await Product(**old_desk.model_dump()).save()
assert await Product.objects().count() == 2

# refresh() turns a snapshot back into the current record: its writes work again.
await old_lamp.refresh()
await old_lamp.merge(price=35.0)

# A snapshot is never == the current instance, even with the same fields.
current_desk = await Product.objects().get("desk")
assert old_desk != current_desk and old_desk.model_dump() == current_desk.model_dump()

Auth models: on a model mixing AuthenticatedUserMixin, refresh is the mixin's token-exchange classmethod; call await BaseSurrealModel.refresh(snapshot).
Equality: Pydantic compares private attributes, and the snapshot's moment is one: compare model_dump()s.
server_fields: save() leaves them out, so an undelete by save() lets the server's DEFAULT fill them again; update() and upsert() write them back. await Product(**snapshot.model_dump()).upsert() restores the past state exactly, whether the record still exists or not (not on edge models, whose upsert() always raises). Computed fields are recomputed by the server in every case.
Copies: model_copy() and pickle keep the snapshot; an instance pickled by v0.25.x unpickles as a current record.
Nested records: a record model nested in a snapshot's fields is a snapshot too, such as a link fetch() resolves into a model-typed field (author: Author | RecordID | None, see Record links): it holds the linked record as it was. Its own refresh() makes it the current record; the snapshot's refresh() re-reads that record only.
Graph reads stay available: get_related() and traverse() read the current graph.
When the record was deleted since, refresh() raises its usual SurrealDbError and the instance stays a snapshot.

What a time-travel queryset refuses

A time-travel read is read-only and cannot be streamed: bulk_create(), bulk_update(), bulk_delete(), patch(), update_or_create(), get_or_create(), delete_table(), query(), live(), watch() and changes() raise SurrealDbError on a queryset with as_of(), before any request. History cannot join a transaction: Model.objects(tx=...).as_of(...) raises at the call. On the embedded engine, a URL that keeps no history raises before any request, naming surrealkv+versioned://.

SurrealDB 2.x without versioned storage

A SurrealDB 2.x server in memory or on plain SurrealKV answers a time-travel read without an error: it returns only the records last written at or before the moment, in their current state, and nothing tells the ORM which storage the server runs, so it cannot tell. Start 2.x with surrealkv+versioned:// for time travel. A 3.x server without versioned storage refuses the read (SurrealDbError), and so does 2.x on RocksDB.

Record links

fetch() resolves links as they were on SurrealDB 3.x. SurrealDB 2.x (servers, and the embedded engine of the surrealdb SDK 2.x) resolves them to their current state, so on 2.x the reads that return the fetched records (exec(), first(), get() without an id) raise SurrealDbError; the other reads ignore fetch(), as without as_of(). Read the linked records with their own as_of() query. On 3.x, a link fetched into a model-typed field is a snapshot too, read-only. A dotted path through a link (author.name) in filter(), select(), order_by() or values() reads the linked record as it was on 3.x and as it is now on 2.x.

Writing history at a past moment

The ORM writes now. A raw query can write a record at a past moment, one statement per client.query() call:

await client.query("CREATE Product:stool SET name = 'Stool', price = 15.0 VERSION d'2020-01-01T00:00:00Z';")
# SurrealDB 2.x and the embedded engine: stored at that moment, readable with as_of()
assert await Product.objects().as_of(datetime(2021, 1, 1, tzinfo=UTC)).count() == 1

SurrealDB 3.x accepts the statement, but the record is not readable at that moment (the table did not exist then), and a CREATE of an id that exists raises.

Coming from the full SurrealDB-ORM

The full ORM has no time travel (no VERSION support): as_of(), snapshots and their errors are lite additions.
as_of(), not version(): the name is temporal SQL's AS OF. SurrealQL's keyword is VERSION and the JavaScript SDK has select().version(), but on the Python SDK's connection version() returns the server's version.
A snapshot is never == the current instance, even with identical fields: compare model_dump()s.

28. Subqueries (`Subquery`)

Since v0.27.0 a QuerySet can filter another: Subquery(qs), or the queryset itself, is the value of a lookup in filter() or Q. The ORM sends one request: the server runs the inner queryset inside the outer statement, so both read the same state (one consistent read), and the inner queryset's values are bound like any filter value.

from surrealdb import RecordID

from surreal_orm_lite import BaseSurrealModel, Subquery


class Person(BaseSurrealModel):
    id: str | None = None
    name: str
    active: bool = True
    age: int = 0


class Post(BaseSurrealModel):
    id: str | None = None
    title: str
    author: RecordID | None = None  # a record link to a Person
    score: int = 0


people = [("ada", "Ada", True, 36), ("bob", "Bob", True, 50), ("cy", "Cy", False, 20)]
for key, name, is_active, age in people:
    await Person(id=key, name=name, active=is_active, age=age).save()
for key, title, author, score in [
    ("p1", "one", "ada", 5),
    ("p2", "two", "bob", 8),
    ("p3", "three", "cy", 2),
    ("p4", "four", "ada", 9),
    ("p5", "five", None, 1),  # no author
]:
    link = RecordID("Person", author) if author else None
    await Post(id=key, title=title, author=link, score=score).save()

active = Person.objects().filter(active=True)
posts = await Post.objects().filter(author__in=Subquery(active)).exec()  # p1, p2, p4
same = await Post.objects().filter(author__in=active).exec()  # p1, p2, p4 (a QuerySet is a subquery)

What a subquery selects

The inner queryset gives the outer filter one list of values: without select(), the ids of its records (SELECT VALUE id); with select("field"), the values of that one field (an aliased field compiles its column). Here the subquery gives the record links the posts hold:

top_authors = Post.objects().filter(score__gt=8).select("author")
writers = await Person.objects().filter(id__in=top_authors).exec()  # ada

filter(), Q, nearby(), order_by(), limit(), offset() and variables() apply to the inner queryset, and fetch() on the inner queryset is ignored, as in the full ORM. select() of several fields, values() and annotate() raise ValueError: a list of objects would match nothing.

Values are compared as stored, never converted: to match records, select id or a record link (a "table:key" string never equals a record id). A record without the selected field gives NONE, which in matches with a record whose field is missing, as a list holding None does: Post.objects().filter(author__in=Post.objects().select("author")) returns p5 too, and author__not_in=active keeps it.

Lookups

Lookup	Compares the field with
`in`, `not_in`	all the subquery's values: equal to one of them, or to none
`containsall`, `containsany`	all its values: the array field holds all of them, or any
`exact` (no suffix), `gt`, `gte`, `lt`, `lte`	its first value
`contains`, `not_contains`	its first value: the array field holds it, or lacks it

Order the subquery to choose its first value. A first-value lookup on an empty subquery matches no record, and ~Q(...) of it every record: SurrealDB alone would compare with array::first([]), which is NONE, and score > NONE holds for every record, so the ORM guards the comparison ($_sq0 != [] AND …). For in, an empty subquery is an empty list: in matches no record, not_in every record. isnull, like, ilike, startswith, endswith, match, regex, inside, outside and intersects raise ValueError with a subquery.

from surreal_orm_lite import Q

others = await Post.objects().filter(author__not_in=active).exec()  # p3, p5 (p5 has no author)
how_many = await Post.objects().filter(author__not_in=active).count()  # 2

best = Post.objects().select("score").order_by("-score").limit(1)
top = await Post.objects().filter(score=Subquery(best)).exec()  # p4 (the top score, 9)

nothing = Post.objects().filter(score__gt=100).select("score")
above = await Post.objects().filter(score__gt=Subquery(nothing)).exec()  # []
not_above = await Post.objects().filter(~Q(score__gt=Subquery(nothing))).exec()  # p1, p2, p3, p4, p5

Ordered and limited subqueries

order_by(), limit() and offset() choose the values a subquery gives:

oldest = Person.objects().filter(active=True).order_by("-age").limit(1)
theirs = await Post.objects().filter(author__in=oldest).exec()  # p2 (Bob is the oldest active person)

A statement with subqueries is one block, whose LETs bind each subquery once, innermost first:

{ LET $_sq0 = (SELECT id AS _sqv, age FROM Person WHERE active = $_f0 ORDER BY age DESC LIMIT 1)._sqv; (SELECT * FROM Post WHERE author IN $_sq0) };

Why a LET: SurrealDB 3.2 and later can return wrong rows for an inline sub-SELECT with ORDER BY and LIMIT, which gives its value to the first outer record only (surrealdb/surrealdb#7470). Bound once, the subquery is right on every line, and the server runs it once instead of once per record.
Why _sqv: an ordered subquery selects its column as _sqv, with its order fields, and reads ._sqv back: SurrealDB 2.x refuses to order a sub-SELECT of one column by another field.
Reserved names: $_sq0, $_sq1, … (_sq<N>) are the block's LETs and hide a variables() value of the same name; a subquery can order by _sqv only when it selects _sqv itself (ValueError otherwise).
limit() and offset() of a subquery take a non-negative integer, as everywhere (TypeError or ValueError when they are called).

Where subqueries apply

Every read honours a subquery (exec(), first(), get() with filters, exists(), count(), sum(), avg(), min(), max(), values(…).annotate(…), annotate(GeoDistance)), and so do the writes by filter (bulk_update(), bulk_delete(), QuerySet.patch()) and the lookups of get_or_create() and update_or_create(). Subqueries nest, and combine with Q (&, |, ~):

from surreal_orm_lite import Count

wrote_a_top_post = Person.objects().filter(id__in=top_authors)
by_writers = await Post.objects().filter(author__in=wrote_a_top_post).exec()  # p1, p4

inactive = Person.objects().filter(active=False)
mixed = await Post.objects().filter(Q(author__in=inactive) | Q(score__gte=9)).exec()  # p3, p4

total = await Post.objects().filter(author__in=active).sum("score")  # 22
per_author = Post.objects().filter(author__in=active).values("author").annotate(n=Count())
groups = await per_author.exec()  # ada: 2, bob: 1

updated = await Post.objects().filter(author__in=inactive).bulk_update(score=0)  # 1
post, created = await Post.objects().get_or_create(author__in=active, title="one")  # p1, False

Transactions: in Model.objects(tx=tx) the statement runs in the transaction. An interactive transaction (SurrealDB 3.x over WebSocket) reads its own uncommitted writes in the subquery too; a buffered one (2.x, or HTTP) still cannot read, and applies writes by filter at commit. Build the inner queryset with the same objects(tx=), or with none.
Live queries: live() and watch() write the sub-SELECT inline in the LIVE SELECT, its values as literals, and the server evaluates it again on every change: a post created once its author is active notifies, even when the author became active after the live query started. Before it starts, a live query reads each subquery table (one request): SurrealDB 3.x would accept a live query whose subquery table does not exist and never notify, so the ORM raises SurrealDbNotFoundError there; 2.x reads that table as empty.
Time travel: under as_of() every subquery reads at the same moment (the ORM gives it the moment's VERSION and the per-record check of section 27). Without versioned storage the read raises section 27's SurrealDbError; SurrealDB 3.1.5 raises its own ThrownError ("Failed to scan record") instead.
changes() refuses filter(), so subqueries too.
A subquery table that does not exist reads as empty, as the ORM reads any missing table. SurrealDB 2.x and the embedded engine read it so in the same request. SurrealDB 3.x raises: the ORM then asks the server whether that table exists (a table probe) and, once the server confirms it does not, sends the statement again with that subquery empty: three requests (statement, table probe, re-send) for one missing table, and only then. Any other error, even one whose text quotes "The table '…' does not exist", is the server's own: it is relayed, never read as empty. On 3.x a write by filter inside a transaction fails with the server's error (SurrealDbError), as any failed write in a transaction does.

What a subquery cannot do

Each of these raises when the outer queryset compiles, before any request (live() and watch() at the call):

a lookup other than the eleven above (isnull, like, startswith, inside, …): ValueError;
a queryset that is its own subquery, directly or through another one: ValueError;
an inner values(), annotate() or select() of several fields: ValueError;
an inner as_of(): SurrealDbError (call as_of() on the outer queryset);
an inner objects(tx=) whose transaction is not the outer queryset's: SurrealDbError;
an inner variables() binding a name of the statement to another value: ValueError (the two values must have the same type and be equal: 1 and True, or 1 and 1.0, differ);
an inner order_by() on _sqv (above): ValueError;
a subquery inside a list (author__in=[Subquery(qs)]): ValueError, give it as the whole value;
Q(...).to_sql() called on its own with a subquery lookup: ValueError (only a QuerySet binds a subquery);
a subquery written as data: bulk_update(score=Subquery(qs)) raises ValueError, and an exact criterion or a defaults= value of get_or_create() or update_or_create() raises SurrealDbError (use a lookup such as author__in, and pass the value to write in defaults=);
Subquery(x) for anything but a QuerySet: TypeError.

annotate(x=Subquery(...)) (a subquery's value on each row), correlated subqueries and Exists are not available yet.

Coming from the full SurrealDB-ORM

The same Subquery(qs), imported from surreal_orm_lite; a QuerySet given as the value is accepted too (author__in=active).
No select() means the record ids (SELECT VALUE id): the full ORM writes SELECT *, which in never matches. select() of several fields is refused instead of matching nothing.
One statement: a block whose LETs bind the subqueries, where the full ORM sends a LET prelude, two statements, whose rows the official SDK's query() cannot read (it returns the first statement's result only).
A first-value lookup on an empty subquery matches nothing: the full ORM compares with array::first() of it, NONE, so gt matches every record.
isnull, the text lookups and the spatial lookups are refused with a subquery.
annotate(x=Subquery(...)) is not available yet.
live() keeps the sub-SELECT inline, in both.
A subquery table that does not exist reads as empty, like any missing table in the lite; the full ORM raises TableNotFoundError on SurrealDB 3.x.
Subquery() raises TypeError for anything but a QuerySet, and has no public to_surql().
The inner queryset's variables() are merged into the statement (a clash raises ValueError) and its nearby() applies, where the full ORM's subquery drops both.
The inner fetch() is ignored, as in the full ORM, while values() and annotate() are refused.

29. Query cache (`cache()`, `QueryCache`)

Since v0.28.0 a QuerySet can serve its reads from a client-side cache: with cache(ttl), the rows a read returned stay in this process's memory, and the same read is answered from there, without a request, until its TTL runs out or an ORM write changes what it reads. Invalidation is built in: every ORM write removes the cached reads it can change, with nothing to set up. The cache behaves the same on SurrealDB 2.x, 3.x and the embedded engine.

from surreal_orm_lite import BaseSurrealModel, QueryCache


class Product(BaseSurrealModel):
    id: str | None = None
    name: str
    category: str
    price: int


for key, name, category, price in [("p1", "Dune", "book", 12), ("p2", "Lamp", "home", 30), ("p3", "Emma", "book", 9)]:
    await Product(id=key, name=name, category=category, price=price).save()

books = await Product.objects().filter(category="book").order_by("name").cache(ttl=30).exec()  # Dune, Emma
again = await Product.objects().filter(category="book").order_by("name").cache(ttl=30).exec()  # Dune, Emma
hits = QueryCache.stats()["hits"]  # 1 (the second read was answered from memory, without a request)

await Product(id="p4", name="Atlas", category="book", price=40).save()  # an ORM write to Product
fresh = await Product.objects().filter(category="book").order_by("name").cache(ttl=30).exec()  # Atlas, Dune, Emma

What is cached

Every read of a queryset with cache(): exec(), first(), get() by filters or by id (get(<id>)), all(), count(), sum(), avg(), min(), max(), exists(), values(…).annotate(…) and annotate(GeoDistance). A read is answered from the cache when an earlier read sent the same statement with the same variables, on the same connection and with the same identity, within the TTL, and no ORM write that can change its result has completed since.

Rows are cached, not instances: each hit builds new instances from a copy of the cached rows, so changing a result never changes the cache.
Empty results are cached too: [], a count of 0, a missing record (get(<id>) raises SurrealDbNotFoundError on the hit as on the first read). Errors never are.
Not cached: reads inside a transaction (objects(tx=), which on SurrealDB 3.x may see its uncommitted writes), refresh(), get_related(), traverse(), raw_query() and call_function().
Refused with cache(): query() runs your own SurrealQL and raises SurrealDbError (cache its result yourself, see Configuration and statistics); live(), watch() and changes() name cache() among the clauses their usual error refuses; as_of() raises SurrealDbError in either order (a time-travel read is never cached).
Writes on a cached queryset (bulk_update(), bulk_delete(), QuerySet.patch(), delete_table(), get_or_create()…) run as without cache(): they never read the cache, and they invalidate it.
A Subquery is read inside the outer statement: the outer queryset's cache() applies, the inner one's is ignored, as its fetch() is.
An internal failure of the cache (a value it cannot copy, say) never fails a read: the read goes to the database.
It works the same on the embedded engine (mem://, surrealkv://).

Time to live

cache(ttl) takes the TTL in seconds (an int or a float) or a timedelta. It is the oldest result this read accepts: an entry stored by a read with a longer TTL is not served to it once older than its own, and a result it stores lives for its TTL. cache() without a TTL uses QueryCache's default when the read runs: 60 seconds unless configured. A TTL of 0 means never cached: every read goes to the database. Negative or non-finite values raise ValueError, anything but a number or a timedelta (True included) TypeError.

from datetime import timedelta

n = await Product.objects().filter(category="book").cache(timedelta(minutes=5)).count()  # 3

Invalidation

Every ORM write removes the cached reads it can change, before its post_* signals fire: save(), update(), merge(), upsert(), patch(), delete(), the atomic operations, relate(), remove_relation(), remove_all_relations(), a typed edge's save(), bulk_create(), bulk_update(), bulk_delete(), QuerySet.patch(), delete_table(), get_or_create() and update_or_create() when they write, signup(), and the DDL helpers define_computed_fields() and define_access().

A write removes the reads of the tables it writes, the reads whose subqueries read them included.
A read that follows links is removed by any ORM write: fetch(), a dotted path such as filter(**{"author.name": …}) or order_by("author.name") (in a subquery too), and a select() item that is not a plain field, such as "author.*" (the projection reads the linked record).
A read of an edge model (BaseSurrealEdge) is removed by any delete: deleting a record deletes the edges that point at it or from it.
Inside transaction() the tables written are invalidated at commit (a failed commit too, nothing on rollback); until then, reads outside the transaction see the committed state. delete_table() runs at once, outside objects(tx=), and invalidates at once.
SurrealDBConnectionManager.signup() writes into a table the ORM does not map: it empties the cache.
While a write runs, cached reads of its tables go to the database and store nothing: a read answered before the write completed is returned to its caller, never stored.

What the cache cannot see — the TTL bounds it, and QueryCache.invalidate(Model) removes a table's entries after such a change:

writes by another process or client;
raw SurrealQL: raw_query(), QuerySet.query(), call_function(), Transaction.add();
a write whose task is cancelled, or that fails client-side, after its request was sent: the server may apply it after the invalidation;
writes made by an access method's own SIGNIN/SIGNUP clauses beyond the record they create;
what the server derives on its own: DEFINE EVENT, REFERENCE … ON DELETE (3.x), views (DEFINE TABLE … AS SELECT), PERMISSIONS that read another table;
values computed when the read runs: a DEFINE PARAM read with Var(), <future>/COMPUTED fields (over another table, the graph such as count(<-likes), or the time);
a table of relations read through a model that is not a BaseSurrealEdge.

lamp = await Product.objects().cache().get("p2")  # Lamp (price 30, now cached)
await Product.raw_query("UPDATE Product:p2 SET price = 35")  # raw SurrealQL: the cache is not told
stale = (await Product.objects().cache().get("p2")).price  # 30
QueryCache.invalidate(Product)  # removes Product's entries (and every read that follows links)
price = (await Product.objects().cache().get("p2")).price  # 35

One cache per connection and identity

Cached results never cross identities. Each connection the manager opens has its own cache scope, and every identity change on it starts a new one: after a sign-in, a sign-up, authenticate() or invalidate(), the previous identity's results leave memory. The connection of another event loop, a reconnect and an embedded_database() block start empty for the same reason, and closing a connection (close_connection(), close_all_connections(), or the manager dropping a dead one) drops its results.

A session changed behind the ORM's back — raw_query("USE …"), or signin()/use() called on the SDK client itself — is not seen: call QueryCache.clear() after it.
Cached rows stay in this process's memory until they expire, are evicted, invalidated or cleared, or their connection closes. configure(enabled=False) only hides them; QueryCache.clear() drops them.
Entries you store with QueryCache.set() are not scoped to a connection or an identity: put whatever tells your callers apart in the key.

Configuration and statistics

QueryCache is one store for the whole process, shared by its event loops and threads, with the full SurrealDB-ORM's three settings: QueryCache.configure(default_ttl=60, max_size=1000, enabled=True).

Every call sets all three settings: an argument you leave out takes its default, as in the full ORM. configure() never clears the cache.
default_ttl is the TTL of a cache() read without one (seconds or a timedelta; 0: never cached). max_size is the most entries kept: past it, the least recently used entry is evicted, and lowering it evicts at once.
enabled=False hides the entries without removing them: cache() reads go to the database (calling cache() logs a warning), QueryCache.get() returns None, QueryCache.set() stores nothing and nothing is counted, while ORM writes, expiry and closed connections keep removing entries. enabled=True serves again the entries still valid. clear() is the way to drop the rows from memory; it also resets hits and misses.
stats() returns entries (unexpired entries, hidden ones included), tables (the distinct tables they are tagged with), max_size, default_ttl (seconds), enabled, and hits and misses (the cache() reads and get() calls answered from the cache, and those that were not, since start-up or clear()).
invalidate() takes a model class or a table name and returns how many entries it removed.

QueryCache.configure(default_ttl=timedelta(seconds=30), max_size=500)  # enabled=True: its default
ttl = QueryCache.stats()["default_ttl"]  # 30.0

QueryCache.configure(enabled=False)  # hides the entries; default_ttl and max_size take their defaults
QueryCache.configure()  # enabled again: the entries still valid are served
QueryCache.clear()  # drops every entry from memory and resets hits and misses

To cache your own results, of raw_query() for instance, build a key with make_key() and use get() and set(). The key comes from the statement, its variables as the SDK sends them and the table; the entry is tagged with the table, so an ORM write to it removes the entry. From the moment the write starts, its pre_*, around_* and post_* handlers included, get() returns None for the entry (a miss), and what set() stores for that table meanwhile leaves with the write:

async def total_price() -> int:
    statement = "SELECT math::sum(price) AS total FROM Product GROUP ALL"
    key = QueryCache.make_key(statement, {}, "Product")
    rows = QueryCache.get(key)  # a copy, or None: missing, expired, or the cache is disabled
    if rows is None:
        rows = await Product.raw_query(statement)
        QueryCache.set(key, rows, "Product", ttl=60)
    return rows[0]["total"]


total = await total_price()  # 96 (the database answers)
cached_total = await total_price()  # 96 (the cache answers)

Coming from the full SurrealDB-ORM

The same names: cache(ttl=…) on a QuerySet, and QueryCache with configure(), make_key(), get(), set(), invalidate(), clear() and stats(), imported from surreal_orm_lite.
Every read is cached, not only exec() (with first() and get() by filters): get(<id>), all(), count(), the aggregations, exists() and values()+annotate() too.
Invalidation is built in and needs no configure() call. It covers bulk_*(), QuerySet.patch(), the atomic operations, relations and transactions (at commit), where the full ORM listens to post_save, post_update and post_delete. Reads through links (fetch(), dotted paths) and edge reads after a delete are invalidated too.
Rows are cached, and each hit builds new instances: the full ORM caches the instances.
Keys come from the CBOR bytes the SDK sends, with the keys of mappings sorted as the full ORM's sort_keys=True does: RecordID("user", 1) and "user:1" never share an entry, where the full ORM's JSON with default=str writes them alike.
One scope per connection and identity: a sign-in never reads another identity's results.
live(), watch(), changes() and query() refuse cache(), which the full ORM ignores there; as_of() reads are not cached (the full ORM has no time travel).
ttl may be a float or a timedelta; 0 is never cached, as in the full ORM; negative or non-finite values are refused; it is the oldest result a read accepts.
LRU eviction instead of FIFO.
invalidate() takes a table name too, and stats() adds hits and misses.

30. Multiple databases (`add_connection()`, `using()`)

Since v0.29.0 one process can work with several databases at once. Beside the default connection (set_connection()), add_connection() registers named connections — another database on the same server, another server, or the embedded engine — and each model reads and writes through its own. Each connection keeps its own clients, session, live streams, transactions and cached reads, and decides on its own server line: a SurrealDB 2.x server and a 3.x server work side by side. The examples of this section run on the embedded engine (mem://), with nothing to start.

from surreal_orm_lite import BaseSurrealModel, SurrealConfigDict, SurrealDBConnectionManager

# Any URL works: ws://…/rpc or http://… (with user= and password=), mem://, surrealkv://<path>
SurrealDBConnectionManager.set_connection(url="mem://", namespace="shop", database="main")   # "default"
SurrealDBConnectionManager.add_connection("analytics", url="mem://", namespace="shop", database="analytics")

class Customer(BaseSurrealModel):                       # no connection key: "default"
    id: str | None = None
    name: str

class PageView(BaseSurrealModel):
    model_config = SurrealConfigDict(connection="analytics")
    id: str | None = None
    path: str

await Customer(id="ada", name="Ada").save()             # shop/main
await PageView(id="v1", path="/pricing").save()         # shop/analytics
names = SurrealDBConnectionManager.list_connections()   # ['default', 'analytics']

add_connection(name, *, url, user=None, password=None, namespace, database) opens nothing: each connection opens its own clients on first use, as the default one does. Its arguments are checked before anything is registered — a non-empty name, URL, namespace and database, and a user and password for a server URL (optional on the embedded engine) — and a URL the installed SDK refuses raises SurrealDbConnectionError. add_connection("default", …) configures the default connection, as set_connection() does, with these checks.
Registering a name again with the same settings changes nothing; with other settings, see What each connection keeps for itself.
get_config(name) returns the settings as a frozen ConnectionConfig (url, user, password, namespace, database; the password is kept out of its repr), and None for a name that is not registered or for the default connection while it is not configured. list_connections() returns the registered names, 'default' first while it is configured.
remove_connection(name) unregisters a name and closes what it holds; a name that is not registered is a no-op, and remove_connection("default") is unset_connection().
get_client(name), close_connection(name) and transaction(name) act on a named connection, and without a name on the current one (Which connection a manager method uses). For a name that is not registered, get_client(name) and transaction(name) raise ValueError, while close_connection(name) closes nothing.

Routing a model

A model's connection key — model_config = SurrealConfigDict(connection="analytics"), the full SurrealDB-ORM's key — sends every database call of the model to that connection: writes, reads and every QuerySet, relations, raw_query(), call_function(), model auth, live queries, change feeds and as_of(). A model without the key, or with None, uses the default connection.

where = PageView.get_connection_name()                  # analytics

get_connection_name() is the connection the model's calls use right now: the name of the using() block around the call, else the connection key, else "default". It is resolved at every database call, so a QuerySet built outside a using() block and run inside it uses the block's connection; a live stream or a change feed resolves it once, when it starts.
The key is checked when the class is defined (TypeError for a value that is not a string, ValueError for an empty one). Whether the name is registered is checked at the model's first database call (ValueError), so models can be declared before add_connection() runs.
Subclasses inherit the key and may set their own; edge models (BaseSurrealEdge) and AuthenticatedUserMixin models take it too. table_name and connection are independent: two connections can each hold a table of the same name.

Redirecting a block: `using()`

using(name), the full SurrealDB-ORM's, runs a block of code against a named connection: inside it every model, whatever its connection key, and every manager method that talks to a database use that connection, with the exceptions of Which connection a manager method uses.

async with SurrealDBConnectionManager.using("analytics"):
    elsewhere = await Customer.objects().count()        # 0
here = await Customer.objects().count()                 # 1

The name is checked when the block is entered: one that is not registered raises ValueError before the block runs. get_active_connection_name() returns the block's name, None outside any block.
Blocks nest, and the outer connection is back after the inner block, on an exception too.
It is async-safe: the name lives in a contextvars.ContextVar, so concurrent tasks each see their own block. A task created inside the block keeps its connection for its whole life, even after the block exits; asyncio.to_thread() carries it, a new threading.Thread does not.
Live streams and change feeds started inside the block keep its connection after it.
signin() inside using(name) signs in that connection only.
With the test helpers, embedded_database() inside using(name) isolates that connection, and embedded_database(connection=name) does so outside any block; inside a using() block, naming another connection raises ValueError (the block's models would reach their real database).

Transactions and other operations across connections

SurrealDB runs a statement, and a transaction, in one database. So the ORM refuses, before sending the statement, every operation that would mix two connections in one statement or one transaction:

from surreal_orm_lite import SurrealDbError

async with SurrealDBConnectionManager.transaction("analytics") as tx:
    await PageView(id="v2", path="/").save(tx=tx)

try:
    async with SurrealDBConnectionManager.transaction() as tx:      # the default connection
        await PageView(id="v3", path="/x").save(tx=tx)
except SurrealDbError as exc:
    refused = type(exc).__name__                        # SurrealDbError

A transaction is one connection's. transaction(name), or transaction() inside using(name), opens it on that connection, with that connection's strategy (interactive on a 3.x server over WebSocket, buffered otherwise). A model of another connection given that tx= raises SurrealDbError, before any request and any signal: its writes, objects(tx=), refresh(tx=), call_function(tx=), and the manager's call_function(tx=). Transactions of two connections are independent: nothing commits across connections.
Subqueries: a Subquery, or a QuerySet used as a filter value, whose model runs on another connection than the statement around it raises SurrealDbError: SurrealDB would read it in the outer statement's database. Read it through its own connection first and filter on its values.
Relations: relate(), remove_relation(), remove_all_relations() and get_related() raise SurrealDbError when the source, a target model instance, the edge model and model_class are not all on one connection. A RecordID or "table:id" target is not checked.
Links: following a record link — fetch(), or a dotted path in filter(), Q, select(), order_by(), values() or an aggregation — through a field typed with a model of another connection raises SurrealDbError: SurrealDB resolves a link in the statement's database.
An on-disk embedded store (surrealkv://, file://) is open under one name at a time in a process: a second name raises SurrealDbConnectionError until the first one closes it (close_connection(), remove_connection()). Two mem:// names are two separate databases.
Inside a using() block every model runs on the block's connection, so none of these refusals apply there.

What the ORM cannot see, and you keep on one connection yourself:

a link followed through a field whose annotation names no model (RecordID, str, dict);
graph arrows: select("->likes->Post"), traverse();
an edge saved with endpoint ids of another database;
raw SurrealQL: raw_query(), QuerySet.query(), call_function(), Transaction.add().

What each connection keeps for itself

Its clients: one per event loop, as the default connection's (Connections and event loops); on the embedded engine, one for the whole process. close_connection(name) closes one connection's client on the running loop, close_all_connections() every connection's.
Its session: signin(), signup(), authenticate(), invalidate(), info() and clear_session() act on the current connection, so sign in inside using(name). A stored token is replayed on its own connection only, and an identity lost on one connection does not block another. An AuthenticatedUserMixin model signs in on its own connection.
Its live streams and change feeds, bound to the connection when they start: close_connection(name) ends that connection's live streams only.
Its transactions, with the strategy of its server: one process holds interactive transactions on a 3.x connection beside buffered ones on a 2.x connection.
Its stored-function signatures (call_function()).
The ORM's cached reads (cache()): a cached result is never served to another connection. An ORM write removes the cached reads of its table on every connection: more than needed, never a stale result.
Registering a name again with other settings, or removing it with remove_connection(), ends what the ORM holds on the old settings: a client still opening is closed unused (the embedded engine is closed only when its URL changed or the name was removed; otherwise it is kept and its session is selected again at the next call) and its caller gets a retryable SurrealDbConnectionError (retry the call), live streams end, a change feed ends at its next read (SurrealDbConnectionError: its cursor belongs to the old database, start a new one), and an open transaction() is cancelled when its block ends, with nothing committed (SurrealDbConnectionError: open it again). After remove_connection(), a call already under way on the name opens no new client on the old settings: one still waiting for the embedded engine while another thread held it raises ValueError (Connection not been set ('<name>').). A client you obtained yourself with get_client() before the change is yours: on WebSocket its socket is closed and its next request fails; an HTTP client keeps the namespace and database it was opened on, so call get_client() again; an embedded client is the connection's one in-process session: it follows a new namespace or database at the ORM's next call, but after a new URL or remove_connection() it is the previous engine (with SDK 2.x still running), so stop using it, as after close_connection(). set_connection() and the set_*() setters do none of this: clients opened on the previous settings keep serving, as in earlier versions.

Entries you cache yourself with QueryCache.make_key() and set() are not scoped to a connection: the key is built from the table, the statement and the variables only. When the same statement runs on two connections, put the connection's name among the variables (keep the table name as it is, so the ORM's writes still invalidate the entry).

from surreal_orm_lite import QueryCache

statement = "SELECT count() AS n FROM page_view GROUP ALL;"
key_main = QueryCache.make_key(statement, {"connection": "default"}, "page_view")
key_analytics = QueryCache.make_key(statement, {"connection": "analytics"}, "page_view")
distinct = key_main != key_analytics                   # True

Which connection a manager method uses

A manager method acts on the current connection — the name it is given, else the innermost using() block's, else the default one — except where this table says otherwise:

Method	Acts on
`add_connection()`, `remove_connection()`, `get_config()`, `list_connections()`, `using()`, `get_active_connection_name()`	the registry, by the name given
`set_connection()`, `unset_connection()`	always the default connection
`close_all_connections()`	every connection, on every event loop
`get_client(name)`, `close_connection(name)`, `transaction(name)`	name, else the current connection
`subscribe_live(uuid)`, `kill(uuid)`	the connection whose live stream started uuid on this event loop, else the current connection
every other method: the session methods (`signin()`, `info()` …), the getters and setters (`get_url()`, `set_database()` …), `reconnect()`, `validate_connection()`, `ephemeral_client()`, `call_function()`, `is_connected()`, `is_embedded()`, `async with SurrealDBConnectionManager():`	the current connection

A coroutine method (get_client(), signin(), close_connection() …) resolves the connection when it is awaited, and a context manager (transaction(), ephemeral_client()) when it is opened by async with (strictly, when it is called), so both act on the connection of the using() block they are written in.

A manager method you replace by hand, in a test for instance, is inherited by every connection, so it intercepts the calls of every connection. Save what the class body holds, vars(SurrealDBConnectionManager)[name], and put that back: pytest's monkeypatch.setattr() and unittest.mock.patch.object() already do. Saving SurrealDBConnectionManager.<name> instead, which worked while there was one connection, saves a method bound to SurrealDBConnectionManager: put back, it is what every connection inherits, and it acts on the current connection, so a model with a connection key reads and writes the current connection's database, with no error.

async def offline():
    raise ConnectionError("no database in this test")

saved = vars(SurrealDBConnectionManager)["get_client"]
SurrealDBConnectionManager.get_client = offline         # every connection calls offline()
try:
    await PageView.objects().count()                    # raises ConnectionError
except ConnectionError:
    pass
finally:
    SurrealDBConnectionManager.get_client = saved       # the classmethod again
views = await PageView.objects().count()                # 2

Coming from the full SurrealDB-ORM

Same names: add_connection(), remove_connection(), get_config(), list_connections(), get_active_connection_name(), using(), ConnectionConfig, SurrealConfigDict(connection=), get_connection_name(), get_client(name), close_connection(name).
ConnectionConfig has the same fields in the same order, built by position or by keyword, without protocol (the official SDK speaks CBOR only); user and password may be None (the embedded engine needs neither).
close_connection() without a name: closing an embedded mem:// client discards its database, so close_connection() without a name stays on the current connection; close_all_connections() closes every one (full ORM: close_connection() closes every connection).
list_connections() lists 'default' first while it is configured (full ORM: registration order).
Registering a name again with other settings closes its open clients and ends what the ORM holds on the old settings (full ORM: drops the clients without closing them). set_connection() keeps its open clients, as in earlier lite versions.
The getters, the setters and reconnect() follow using() (full ORM: the default connection).
transaction(name), embedded_database(connection=) and the errors for operations across connections are lite additions.
add_connection() checks its arguments before registering anything (a non-empty name, URL, namespace and database; a user and password for a server URL, optional on the embedded engine), and a model's connection key must be a non-empty string, checked when the class is defined (full ORM: no checks). ConnectionConfig keeps the password out of its repr.
get_client(name), close_connection(name) and transaction(name) accept name in code, but inspect.signature() does not show it, so FastAPI's Depends(SurrealDBConnectionManager.get_client) adds no ?name= query parameter: a request never chooses the connection (full ORM: name is visible and becomes a query parameter).

31. Schema introspection (`DatabaseIntrospector`, `generate_models_from_db()`)

Since v0.30.0 the ORM reads the schema of a database — its tables with their fields, indexes and events, and its accesses, analyzers, functions and params — as immutable typed objects, and generates BaseSurrealModel classes from it, as the full SurrealDB-ORM does. It only reads: it sends INFO FOR DB and INFO FOR TABLE, never a statement that reads a record or changes the schema, so it needs a session allowed to run INFO: a system user (Permissions). Kinds, field paths and the generated types are the same on SurrealDB 2.x, 3.x and the embedded engine; expressions (DEFAULT, VALUE, ASSERT …) are kept as each line renders them (SurrealDB 2.x and 3.x). The examples of this section run on the embedded engine (mem://), with nothing to start.

Reading the schema

from surreal_orm_lite import DatabaseIntrospector, SurrealDBConnectionManager

SurrealDBConnectionManager.set_connection(url="mem://", namespace="shop", database="main")
client = await SurrealDBConnectionManager.get_client()
await client.query(
    "DEFINE TABLE person SCHEMAFULL;"
    "DEFINE FIELD name ON person TYPE string;"
    "DEFINE FIELD age ON person TYPE option<int> DEFAULT 18;"
    "DEFINE FIELD address ON person TYPE option<object>;"
    "DEFINE FIELD address.city ON person TYPE string;"
    "DEFINE INDEX person_name ON person FIELDS name UNIQUE;",
    {},
)

schema = await DatabaseIntrospector().introspect()
person = schema.tables["person"]
person.schema_mode                    # 'SCHEMAFULL'
person.fields["age"].field_type       # 'int'
person.fields["age"].nullable         # True: option<int> on SurrealDB 2.x, none | int on 3.x
person.fields["age"].default          # '18': SurrealQL text
person.fields["address.city"].path    # ('address', 'city')
person.indexes["person_name"].unique  # True
schema.issues                         # (): everything was read and parsed

introspect() sends INFO FOR DB;, then one INFO FOR TABLE per table in codepoint order of the names (each escaped as an identifier), on the connection's client and outside any transaction. No record is read, nothing is defined or written.
schema.tables holds one TableState per table — mode, type ("normal", "relation", "any"), relation ends, view query, change feed, permissions and comment — with its fields, indexes and events. The database's own definitions are in schema.accesses, schema.analyzers, schema.functions (keyed "fn::name") and schema.params (keyed without $). The sections the ORM does not model (apis, configs, models, and on SurrealDB 3.x buckets, modules and sequences) are kept in schema.other as SurrealDB returns them. The users section is never read.
A field is keyed by its path, with * for array items ("address.city", "tags.*"), and path holds the segments, unescaped. field_type is its kind without the optional wrapper ("any" without TYPE), and nullable says whether it may be NONE.
Expressions are SurrealQL text, as the server renders them: default, value, computed, assertion, an event's when and then, a view's query, permission rules ('FULL', 'NONE' or 'WHERE <expr>'). Every definition also keeps the server's statement in raw.
The state is immutable: frozen dataclasses, read-only maps that iterate in sorted key order, and tuples. Printing or logging a state shows names and structure only: definition texts stay in their attributes, out of repr() and str().
Nothing is guessed. A definition that cannot be parsed exactly, a section of an unexpected shape or an INFO FOR TABLE the server answers with an error becomes a SchemaIssue in schema.issues (its kind, name, table, message and statement) and one WARNING on the surreal_orm_lite.introspection logger; introspect() does not raise for them. An issue's message never quotes the definition, which may hold a secret.
Errors: SurrealDbAuthenticationError when the session may not run INFO FOR DB, or is refused an INFO FOR TABLE after INFO FOR DB succeeded (it lost its rights meanwhile); SurrealDbError when INFO FOR DB fails otherwise; and SurrealDbNotFoundError for a tables= name the database does not define. A connection lost during the read is not an issue: the transport's error (or the ORM's) propagates unchanged, so generate_models_from_db() never replaces a file with models of tables it could not read.

SurrealDBConnectionManager.add_connection("archive", url="mem://", namespace="shop", database="archive")

just_person = await DatabaseIntrospector(tables=["person"]).introspect()  # INFO FOR TABLE `person` only
no_table = await DatabaseIntrospector(tables=[]).introspect()             # INFO FOR DB only: no_table.tables is empty
archive = await DatabaseIntrospector("archive").introspect()              # a named connection
async with SurrealDBConnectionManager.using("archive"):
    archive = await DatabaseIntrospector().introspect()                   # the block's connection
archive.connection                                                        # 'archive'

connection= is the name of a connection, the one given to add_connection(), or None for the current one: the innermost using() block's, else "default". It is resolved when introspect() runs, and schema.connection records it. A client object, such as await SurrealDBConnectionManager.get_client(), raises TypeError: it does not carry the connection's name, which routing and the generated models' connection= key need.
tables= reads those tables only: None, the default, reads every table, and an empty list reads none (the database-level sections are read all the same). A str raises TypeError: write tables=["person"].

Generating models

from surreal_orm_lite import ModelCodeGenerator, generate_models_from_db

source = ModelCodeGenerator().generate(schema)          # the same text as: await generate_models_from_db()
print(source)
await generate_models_from_db("models_generated.py")    # writes the file, replacing it if it exists (as the full ORM)
try:
    await generate_models_from_db("models_generated.py", overwrite=False)
except FileExistsError as exc:
    print(exc)  # 'models_generated.py' already exists: overwrite=False refuses to replace it.

It prints:

"""Models generated by surreal_orm_lite.generate_models_from_db() from a SurrealDB schema.

Review them before use. They describe tables that already exist: do not call
define_computed_fields() on them, it would replace the database's field definitions.
"""

from pydantic import BaseModel
from surreal_orm_lite import BaseSurrealModel, SurrealConfigDict


class PersonAddress(BaseModel):
    city: str


class Person(BaseSurrealModel):
    # Indexes: person_name (UNIQUE)
    model_config = SurrealConfigDict(table_name="person")

    id: str | None = None
    address: PersonAddress | None = None
    age: int | None = 18
    name: str

ModelCodeGenerator().generate(schema) is pure: the same schema always gives the same source. generate_models_from_db(output_path=None, connection=None, *, tables=None, overwrite=True) reads the schema (connection and tables as for DatabaseIntrospector), generates the models and returns their source; with an output_path it also writes the file, UTF-8 with \n newlines, creating its parent directories.
An existing file is replaced, as the full ORM replaces it, and atomically: the source goes to a temporary file in the same directory, which os.replace() then moves over the file. A failure leaves the previous file and no temporary file, and a symbolic link is replaced by the file, its target never written. overwrite=False refuses an existing path with FileExistsError before reading anything, and creates the file exclusively.
One class per table: a BaseSurrealModel, or a BaseSurrealEdge for a TYPE RELATION table (which declares no id, in_ or out: the base class provides them). Its model_config holds the table_name, connection= when the schema was read from another connection than "default", then server_fields and extra="allow" (below). A model declares id: str | None = None first, then one line per field, in codepoint order of the columns.
A class is named after its table in PascalCase, without singularisation (game_tables → GameTables); a name that is taken — a Python keyword, a name the module imports, another table's class — gets Model appended (range → RangeModel). The table's comment, relation ends, view query, change feed (# Changefeed: 1d), permissions (# Permissions: FOR select WHERE …, FOR create, update FULL, FOR delete NONE, left out when all four are NONE, a table's default), indexes and events are comments of its class, and the module's header lists every table or field left out, with the reason.
The source is safe to import: names, comments and expressions read from the database reach it only as escaped string literals, single-line ASCII comments and identifiers the generator builds, so a schema cannot inject code into it. It holds no timestamp, server address, namespace or database name, and no from __future__ import annotations.

How SurrealDB kinds become fields

SurrealDB kind	Generated annotation
`any`, or no `TYPE`	`Any = None`
`bool`, `int`, `float`, `string`, `bytes`	`bool`, `int`, `float`, `str`, `bytes`
`number`	`int`, `float` or `Decimal`: their union, so each value keeps its type
`decimal`, `datetime`, `uuid`	`Decimal`, `datetime`, `UUID`
`duration`	`Duration`, nanosecond-exact (section 24)
`range`	`Range`
`record`, `record<…>`	`RecordID`
`geometry<point>`, or another single geometry kind	`GeometryPoint` …; optional: `PointField = None` … (section 25)
`geometry`, `geometry<feature>`	`Geometry`; optional: `GeometryField = None`
`geometry<…>` with several kinds	the union of their classes
`object` with declared fields (`address.city`)	a sub-model (below)
`object` without declared fields, an object literal (`{ a: int }`)	`dict[str, Any]`
`array`, `array<T>`, `array<T, N>`	`list[Any]`, `list[T]`
`set`, `set<T>`, `set<T, N>`	`set[T]` when `T` is hashable (`str`, `int`, `float`, `bool`, `bytes`, `Decimal`, `UUID`, `datetime`, `Duration`, a `Literal`), else `list[T]`; `set` alone: `list[Any]`
`[A, B]` (a tuple)	`tuple[A, B]`
string, integer and boolean literals (`'a'`, `1`, `true`)	`Literal[…]`, values in the kind's order
float and decimal literals (`1.5f`, `1.5dec`)	`float`, `Decimal`
`null`	`None`
a union of kinds	the union of their annotations, `None` last; `Any` when one of them is `Any`
`regex`, `function`, `file`, any other kind	`Any = None`

An optional kind, option<T> (rendered none | T by SurrealDB 3.x), gives T | None = None; a single geometry kind gives its field alias instead (option<geometry<point>> → PointField = None).
Sub-models: an object field with declared fields (address with address.city) becomes a BaseModel named after its class and column (PersonAddress), written before the class; an array<object> whose items have declared fields (lines.*.sku) becomes list[<Class><Column>Item]. The sub-model keeps undeclared keys (extra="allow") when the object is FLEXIBLE or the table SCHEMALESS. A nested model writes its keys by attribute name, so when a nested column cannot be an attribute name as it is (first-name, datetime), its parent is a dict[str, Any] instead. A nested field whose parent is not defined makes that parent dict[str, Any] | None = None, noted in the header. Item definitions (tags.*) add no attribute: the array's kind carries the item type.
DEFAULT: a constant the model can hold exactly becomes the Python default, so the database stores the same value whether you set the field or not, and stores yours when you do — DEFAULT 18 → = 18, 'active' → = "active", true → = True, 0.5f on a float → = 0.5, 1.5dec → = Decimal("1.5"), NONE → = None, [] → = Field(default_factory=list) (set for a set), [1, 2] → = Field(default_factory=lambda: [1, 2]), {} → = Field(default_factory=dict) or the sub-model's class, 1h30m → = Field(default_factory=lambda: Duration.parse("1h30m")), d'2030-01-01T00:00:00Z' → = datetime.fromisoformat("2030-01-01T00:00:00Z"), u'…' → = UUID("…"). The value is copied when the models are generated: generate them again after changing a DEFAULT. A {} whose sub-model needs a value makes the field required, because SurrealDB refuses a create that leaves it to that default. Any other default — an expression such as time::now(), a record id, math::INF, a non-empty object, a datetime with nanoseconds, a literal of another type — makes the field T | None = None and lists its attribute in server_fields (section 20): omitted on create, so that the server's default applies, and read back after save(). A value you set on such a field before save() is not written: set it with merge() once the record exists.
VALUE and COMPUTED: COMPUTED e (SurrealDB 3.x), and a VALUE e that reads neither $value nor $input, become Computed[T] = computed("e") (section 16): never written, read back after save(). When computed() refuses the expression (a ;, even inside a string, or a block { … }) or the column needs an alias, the field takes the server form instead: T | None = None, listed in server_fields. A VALUE that reads $value (string::lowercase($value)) is an ordinary field: you write the value and the server transforms it.
SCHEMALESS tables get extra="allow": the fields the table does not declare are read into model_extra and written back.
Aliases: a column that cannot be an attribute name as it is gets one, with Field(alias=<column>) (section 20): first-name → first_name, class → class_, _private → private, 9lives → f_9lives. So does a column named like a type or a name the module uses (datetime, computed, list, str, Field, Any), like a model attribute (save, objects, json), like a class of the module, or l, O or I (ambiguous names for linters): it gets a _ suffix (datetime_, with alias="datetime"), since the attribute would otherwise shadow that name in the annotations after it.
Skipped tables, listed in the module's header: a table whose name is not an identifier (order-items: table_name refuses it), and a table named like a SurrealQL keyword, in any case, which the ORM's statements cannot use as a table name (select, Select, create, only, none …). They are in schema.tables all the same.

What the generator does not do

It reads no record: a SCHEMALESS table's model holds the fields the table declares (with extra="allow"), and an object-literal kind ({ a: int }) stays a dict[str, Any].
A field's ASSERT, PERMISSIONS, REFERENCE (the field is a RecordID) and comment are not generated, and a table's permissions and change feed are only comments of its class: the database still applies them. READONLY is a trailing # READONLY comment: no field option exists for it.
update() may fail on an instance holding a READONLY datetime below the top level, in a sub-model (address.at), a tuple, an array (array<datetime>, items.*.at) or a set, after get() as after save(): Python keeps microseconds, SurrealDB nanoseconds, and the server refuses the changed value. update() keeps the top-level READONLY datetimes to the nanosecond, up to 32 per record; below the top level it keeps one only while the fields before it leave room, which depends on the data (see Precision and Decimal). The generated class says so, as does a class with more than 32 top-level fields that may hold a datetime: change such a record with merge().
array<T, N> becomes list[T]: the length is left to the server, which checks it differently on each line (SurrealDB 2.x accepts at most N items, 3.x exactly N).
null | T becomes a required T | None, but the ORM sends None as NONE, which null | T refuses: give such a field a value, or declare it option<T>, which accepts NONE.
Never call define_computed_fields() on a generated model: it would replace the database's field definitions with DEFINE FIELD OVERWRITE … VALUE …, without their TYPE, ASSERT or PERMISSIONS (the module's docstring says so).
Long lines: the model_config line is wrapped the way ruff format keeps it — one keyword per line once it exceeds 128 columns, and the server_fields names one per line once their own line does. Other long lines — long names, a long Literal union — are left on one line for your formatter. When every line fits in 128 columns, the module passes ruff check and ruff format --check at that line length.

SurrealDB 2.x and 3.x

Each line renders the same definition its own way. The reader normalises what the ORM types — kinds, nullability, field paths, flags and names — to one form, the 3.x one where both lines accept it: option<int> (2.x) and none | int (3.x) both give field_type == "int" and nullable is True; array items, tags[*] (2.x) and tags.* (3.x), both give the field "tags.*"; FLEXIBLE TYPE object (2.x) and TYPE object FLEXIBLE (3.x) both give flexible is True. The same schema gives the same state and the same generated types on every line.
Expressions are kept as each line renders them: VALUE IF b THEN 1 ELSE 2 END on 2.x is VALUE (IF b THEN 1 ELSE 2 END) on 3.x, a block { … } is one line on 3.x and several on 2.x, and math::INF (2.x) is math::INFINITY (3.x). So value, default, raw … and a generated computed("…") may differ between the lines; the annotations do not.
A definition only one line has is read and typed on that line: on 3.x COMPUTED (FieldState.computed), REFERENCE (reference, on_delete), FULLTEXT indexes, the file kind, WITH REFRESH and BEARER accesses, and DEFINE SEQUENCE (kept raw in schema.other["sequences"]); on 2.x and the embedded engine MTREE indexes (IndexState.mtree), SEARCH ANALYZER indexes and geometry<feature>.
DDL that a transaction() block has not committed yet is invisible: introspection reads outside the transaction, so it sees the committed schema, inside the block too.
A database removed (REMOVE DATABASE) while the connection uses it: on 3.x introspection raises SurrealDbError (the database does not exist) until the database is defined again; 2.x answers an empty schema.
The embedded engine reads like a 2.x server (ORM behaviour: the embedded engine).

Permissions

Introspection needs a system user: root, a namespace user or a database user, any role. A record user or an anonymous session gets SurrealDbAuthenticationError ("this session may not run INFO FOR DB"); on the embedded engine the anonymous session owns the database and may read it.

The users section is never read. On SurrealDB before 3.2.2 (every 2.x release, 3.x before 3.2.2, and the embedded engine of SDK 2.0.0), INFO FOR DB shows user password hashes to every role that may run INFO (SurrealDB's INFO documentation); introspection never reads, stores, logs or writes them.

Coming from the full SurrealDB-ORM

Same names and call shapes: DatabaseIntrospector(connection).introspect(), ModelCodeGenerator().generate(state), generate_models_from_db(output_path, connection), and the state classes SchemaState, TableState, FieldState, IndexState, EventState, AccessState and AnalyzerState, built by position or by keyword with the full ORM's arguments. Lite attributes (raw, path, table, comment, …) come after them and are keyword-only. FunctionState, ParamState, SchemaIssue and tables= are lite additions.
State is immutable: frozen dataclasses with read-only maps, and every definition keeps the server's statement in raw (full ORM: mutable dataclasses, no statement).
Sequences are tuples: compare index.fields, analyzer.tokenizers, analyzer.filters, table.relation_in and table.relation_out with tuples (index.fields == ("email",)); lists are accepted when constructing, and so is the full ORM's pipe-joined relation_in="person | company". Relation ends are () where the full ORM has None (not a relation table, or any table).
Permission values are 'FULL', 'NONE' or 'WHERE <expr>' (full ORM: the bare condition).
default is SurrealQL text: '18', "'active'", 'time::now()', with default_always for DEFAULT ALWAYS (full ORM: a Python value, where 'active' and time::now() both become strings). A FieldState built with a default that is not text raises TypeError.
Typed defaults and server_fields: a constant DEFAULT becomes a typed Python default (Field(default_factory=list), Duration.parse(…), datetime.fromisoformat(…), UUID(…)), and any other DEFAULT becomes a server_fields entry (full ORM: Field(default=…) holding the SurrealQL text as a Python string, such as '[]' or 'time::now()', and no server_fields).
nullable means "may be NONE": it is true for an optional kind (option<T>, rendered none | T by SurrealDB 3.x); null | T is not nullable, since it refuses NONE (full ORM: nullable). field_type is "any" for a field without TYPE, as in the full ORM.
Accesses are database-level: they are in schema.accesses, and TableState has no access attribute; find a table's with [a for a in schema.accesses.values() if a.table == "user"]. table, signup_fields and signin_where are read from SIGNUP (CREATE <table> SET …) (also CREATE type::thing('<table>', …) SET … and CREATE type::record('<table>', …) SET …, the forms define_access() uses for a primary_key) and SIGNIN (SELECT … FROM <table> WHERE …). The full ORM reads the first form only, and another shape leaves them empty, with field names unescaped; durations are kept as rendered, 'NONE' included.
encrypted is not provided: the lite has no Encrypted type, so a hashing VALUE (crypto::argon2::generate($value)) stays in FieldState.value and the field is generated as a writable field.
APIs and the GraphQL config are not typed: their definitions are in SchemaState.other["apis"] and SchemaState.other["configs"], as SurrealDB returns them (full ORM: ApiState, GraphQLConfigState).
SchemaState.diff() and schema_diff() come with v0.37.0 (migrations), as does applied_migrations; the inspectdb command comes with v0.38.0.
A refused or failed INFO FOR DB raises SurrealDbAuthenticationError or SurrealDbError, and a definition that cannot be read or parsed becomes a SchemaIssue in schema.issues and a logged warning (full ORM: an empty state, and definitions dropped with a debug log).
on_delete keeps 'THEN <expr>' for REFERENCE ON DELETE THEN … (full ORM: 'THEN' alone).
connection= is a name: the name a connection was added with (add_connection()), else the current one (using(), then "default"). A client object is refused with a TypeError, because a client does not carry the ORM's connection name, which routing, reconnects and the generated models' connection= key need (full ORM: an HTTPConnection).
Generated models use comments where the full ORM writes SurrealConfigDict(permissions=…, changefeed=…, view_query=…, relation_in=…, relation_out=…, enforced=…) (# Permissions: …, # Changefeed: …, # View: AS …, # IN … OUT … ENFORCED), and the base class where it writes table_type= (BaseSurrealEdge for a relation table): those keys arrive in the lite's SurrealConfigDict with v0.31.0–v0.33.0. READONLY is a comment too (full ORM: Field(readonly=True)).
RecordID instead of ForeignKey/ReferencesField; no VectorField or Encrypted; computed() instead of Computed(); Duration, UUID, Decimal and the geometry classes instead of str, dict and float; nested fields become sub-models (full ORM: dotted attribute lines).
No singularisation: game_tables becomes GameTables (full ORM: a trailing s is stripped, so status becomes Statu).
overwrite=False added: generate_models_from_db(path) replaces an existing file as the full ORM does, here atomically; overwrite=False refuses it with FileExistsError.

Configuration Options

Custom Primary Key

from surreal_orm_lite import BaseSurrealModel, SurrealConfigDict

class Product(BaseSurrealModel):
    model_config = SurrealConfigDict(primary_key="sku")

    sku: str
    name: str
    price: float

Custom table name (`table_name`)

from surreal_orm_lite import BaseSurrealModel, SurrealConfigDict

class OrderLine(BaseSurrealModel):
    model_config = SurrealConfigDict(table_name="order_line")

    id: str | None = None
    sku: str
    quantity: int = 1

line = await OrderLine(sku="A-1", quantity=2).save()          # stored in order_line
lines = await OrderLine.objects().filter(sku="A-1").exec()

By default a model's table is its class name. table_name (v0.22.0, the full SurrealDB-ORM's key) overrides it, and every path follows: writes and reads, every QuerySet clause, live queries and change feeds, relate() targets, and the DDL the ORM generates (computed_field_ddl(), access_ddl()). A subclass inherits it unless it sets its own. The name is written into SurrealQL, so it is checked when the class is defined: a value that is not an identifier (letters, digits and underscores, not starting with a digit) raises ValueError, a non-string TypeError. Every SurrealConfigDict key is optional for type checkers since v0.22.0, so SurrealConfigDict(table_name="order_line") alone type-checks.

Deprecated: the undocumented _table_name: ClassVar[str] = "order_line" class attribute still works and emits a DeprecationWarning when the class is defined; it will be removed in a future minor. It now applies to every path: before v0.22.0 only the objects() QuerySet used it, so save() and the other instance methods wrote to the class-name table. When both are set, table_name wins.

Context Manager

async with SurrealDBConnectionManager():
    users = await User.objects().all()
# Connection automatically closed

async with SurrealDBConnectionManager(): uses the current connection (the default, or the using() block's) and closes that connection's client on exit.

Connections and event loops

The manager caches one client per event loop. A SurrealDB WebSocket client is bound to the loop it connected on, so reusing it from another loop is not merely wrong — it fails with got Future attached to a different loop, or hangs. Keying the cache by loop means each one gets its own connection:

# Each asyncio.run() creates and closes its own loop. Both calls work.
asyncio.run(work())
asyncio.run(work())

# Two loops alive at once (threads, multi-loop servers) each keep their own client;
# neither evicts the other.

An entry whose loop has been closed is dropped the next time a client is requested. The stale client is not closed — that would have to be awaited on a loop that is already gone — and its socket is released when the loop is finalised.

close_connection() closes the running loop's client of the current connection only (the default, or the one a using() block names).
close_all_connections() tears down every cached client of every connection; unset_connection() does that for the default connection only, remove_connection(name) for a named one.
is_connected() answers for the loop asking; called outside a loop, it reports whether any loop still holds a client.

Each named connection keeps its own per-loop clients in the same way (section 30).

Long-lived single-loop applications are unaffected: one loop, one connection, as before.

An embedded URL is the exception: the engine runs in this process and is not bound to a loop, so the manager keeps one client for the whole process (section 26). While it is configured, close_connection() closes that client only, not the loop's server client: is_connected() stays True on a loop that keeps one.

Compatibility

As of v0.7.0, Surreal ORM Lite uses surrealdb[pydantic]>=2.0.0,<3.0.0 (SurrealDB 3.x protocol) and is tested against both major SurrealDB release lines.

Compatibility advantage over the full ORM: ORM-lite runs on both SurrealDB 2.x (2.6 / 2.7) and 3.x (3.2 / 3.3), while the full SurrealDB-ORM (custom SDK) targets 3.x only. Lite stays usable on existing 2.6.x deployments without forcing a server upgrade.

SurrealDB Version	SDK Version	Status
3.3.4	2.0	✅ Tested
2.7.0	2.0	✅ Tested
Embedded engine (SDK 2.0.0: SurrealDB 2.3.10 core)	2.0	✅ Tested (in-process)
3.2.4	2.0	✅ Compatible (tested up to v0.19.0)
2.6.5	2.0	✅ Compatible (tested up to v0.19.0)
3.3.x / 3.2.x / 2.7.x / 2.6.x	2.0	✅ Compatible
3.1.5	2.0	⚠️ Backward-compat only
< 2.6 or > 3.3	—	⚠️ Not guaranteed

ORM behaviour: SurrealDB 2.x vs 3.x

Surreal ORM Lite runs on both lines; some capabilities differ because they rely on server features introduced in SurrealDB 3.x. On 2.x the ORM degrades gracefully. Capabilities not listed behave the same on both lines. The 2.x column was measured on 2.6.5 and holds unchanged on 2.7.0. The 3.x column was measured on 3.2.4 and holds on 3.3.0, except where a row says otherwise. The full suite and every divergence below were re-verified on both new versions. Rows added in v0.20.0 were measured directly on 2.7.0 and 3.3.0; rows added in v0.21.0 on 2.7.0 and 3.3.2, re-verified on 3.3.3, with 3.1.5 where it differs; rows added in v0.22.0 on 2.7.0 and 3.3.3, with 3.1.5 where it differs; rows added in v0.23.0 on 2.7.0 and 3.3.4, with 3.1.5 where it differs; rows added in v0.24.0 on 2.7.0 and 3.3.4, identical on 3.1.5; rows added in v0.26.0 on 2.7.0 and 3.3.4, in memory and on versioned storage, identical on 3.1.5; rows added in v0.27.0 on 2.7.0 and 3.3.4, with 3.1.5 where it differs; rows added in v0.28.0 on 2.7.0 and 3.3.4, identical on 3.1.5; the rows added in v0.28.1 on 2.7.0 and 3.3.4, identical on 3.1.5; rows added in v0.29.0 on 2.7.0 and 3.3.4 in one process, identical on 3.1.5. The row added in v0.29.2 was measured on 2.7.0, 3.3.4 and the embedded engine, identical on 3.1.5. Rows added in v0.30.0 were measured on 2.7.0, 3.3.4 and the embedded engine, with 3.1.5 where it differs. The row added in v0.30.3 was measured on 2.7.0 and 3.3.4, over WebSocket and HTTP, and on the embedded engine, identical on 3.1.5. The row added in v0.30.7 was measured on 2.7.0, 3.3.4 and the embedded engine, identical on 3.1.5: its parser refuses a statement that nests six objects, and update() nests at most four. The SDK's in-process engine has a table of its own: ORM behaviour: the embedded engine.

ORM capability	SurrealDB 2.6.x / 2.7.x	SurrealDB 3.2.x / 3.3.x	Since
Transaction strategy auto-selected by `transaction()`	buffered batch (`BEGIN…COMMIT`)	native interactive on WebSocket	v0.9.0
Reads inside a transaction (`objects(tx=)`)	raise (buffered cannot read)	see uncommitted writes	v0.9.0
`save(tx=)` with an auto-generated id	raises — explicit id required	supported	v0.9.0
`refresh(tx=)` inside a transaction	raises	works	v0.9.0
`bulk_update` / `bulk_delete` / `QuerySet.patch` row count inside a tx	returns `0` (not knowable pre-commit)	real count	v0.9.0
"Already exists" error on create	normalised to `SurrealDbError`	normalised to `SurrealDbError`	v0.7.0
Cleanup on a missing target (`delete_table`)	native no-op	ORM makes it a silent no-op	v0.7.0
Aggregation over an empty set (`NaN` / `±inf`)	returns `0.0` / `None`	ORM normalises to `0.0` / `None`	v0.7.0
Namespace/db selection (`use()` ordering)	lenient (auto-creates)	strict — ORM signs in before `use()`	v0.7.0
Writing a field a `SCHEMAFULL` table does not define (`CREATE`, `INSERT`, `INSERT RELATION`)	silently dropped: the record is stored without it	rejected ("Found field …, but no such field exists"): nothing is stored	v0.7.0
`upsert()` / `update_or_create()` / `get_or_create()`	same on both lines	same on both lines	v0.10.0
`patch()` / `atomic_append` / `atomic_set_add` / `atomic_remove` / `atomic_increment`	same on both lines (portable `array::*` fns chosen over divergent `+=`/`-=`)	same on both lines	v0.11.0
`retry_on_conflict` / `SurrealDbConflictError` (retryable conflict)	same type + decorator; conflicts rarer (engine serialises more), except on `CHANGEFEED` tables (section 22)	same type + decorator; conflicts are the normal optimistic-MVCC failure	v0.12.0
`SurrealFunc` / `server_values=` / `extra_vars=` on `save`/`merge`	same on both lines (compiled to portable `CREATE`/`UPDATE … SET`)	same on both lines	v0.13.0
Shipped function-name enums (`SurrealTimeFunction`, `SurrealCryptoFunction`, …)	every catalogued member verified on 2.7.0	every catalogued member verified on 3.3.4	v0.13.0
`server_values` inside a transaction — when the instance sees the computed value	only after commit (buffered; `refresh()` to read it)	immediately (interactive returns the row)	v0.13.0
`merge(server_values=)` on a missing record / never-created table	server returns no rows → ORM raises `SurrealDbError`	server raises `NotFound` for a missing table → ORM raises the same error	v0.13.0
Computed fields (`Computed[...]` → `DEFINE FIELD … VALUE`)	same on both lines (DDL, recompute triggers, precedence over client data)	same on both lines	v0.14.0
DDL run inside a transaction, then rolled back	definition rolled back with the transaction	same on both lines	v0.14.0
Invalid computed-field expression — raw SDK exception	`InternalError`	`ValidationError`	v0.14.0
Invalid computed-field expression — through the ORM	`SurrealDbError` (normalised)	`SurrealDbError` (normalised)	v0.14.0
Issue #156 correctness fixes (`Var`/`$$`, `first()`, `*_or_create` strictness, `created` on upsert, quoted ids, one-hop `get_related`)	same on both lines (each fix reproduced and verified on 2.6.5)	same on both lines (verified on 3.2.4)	v0.14.3
Record-id lookups (`filter(id=…)`, `id__in`, `get(…)`, `*_or_create(id=…)`) coerced to `RecordID`	same on both lines (int/str typing verified on 2.6.5)	same on both lines (verified on 3.2.4)	v0.14.4
Per-event-loop client cache (`get_client`, `close_connection`, `close_all_connections`)	same on both lines (loop binding is an asyncio/SDK property, not a server one)	same on both lines (verified on 3.2.4)	v0.14.5
`call_function()` — the call itself (`args`, `params`, `return_type`, nested `fn::a::b`)	same on both lines (bare call form chosen so it is portable)	same on both lines (verified on 3.2.4)	v0.15.0
`call_function(tx=)` — return value	`None` (buffered: queued until commit)	the function's value (interactive returns it immediately)	v0.15.0
`call_function(tx=, return_type=)`	raises `ValueError` (no value to coerce yet)	coerces the returned value	v0.15.0
Missing function called inside a transaction	surfaces at COMMIT as `SurrealDbError` (buffered: the call is only queued)	raises `SurrealDbNotFoundError` at call time	v0.15.0
A statement that fails inside `transaction()`, its error caught in the block	leaving the block raises `SurrealDbError`, nothing of the block committed (the buffered batch fails as a whole)	same on both lines; over WebSocket the statement raises at once, then the block's end cancels the transaction and raises `SurrealDbError`	v0.30.3
Declared parameter name that collides with a reserved word, as echoed by `INFO FOR DB`	quoted: $`by` — parser accepts it	bare: `$by`	v0.15.0
Auth methods (`signin`, `signup`, `authenticate`, `invalidate`, `info`)	same on both lines (native SDK primitives, verified on 2.6.5)	same on both lines (verified on 3.2.4)	v0.16.0
Auth failure — raw SDK exception for a wrong password	`InternalError`	`NotFoundError`	v0.16.0
Auth failure — through the ORM	`SurrealDbAuthenticationError` (normalised)	`SurrealDbAuthenticationError` (normalised)	v0.16.0
`DEFINE ACCESS … WITH REFRESH` and `AuthTokens.refresh`	not supported — the clause does not parse; `refresh` is always `None`	supported; `refresh` populated	v0.16.0
`signin(access=…, refresh=…)` renewal	raises `SurrealDbAuthenticationError` (no such access method is definable)	returns a fresh, rotated token pair; the spent one is rejected	v0.16.0
Session token replayed on reconnect (`get_client`, `reconnect`)	same on both lines	same on both lines	v0.16.0
`info()` when the record's table denies it `select` on itself	returns `None` (no error)	returns `None` (no error)	v0.16.0
Signing in as a system user while a record session is open	permissions change, `$auth` still points at the record — only `invalidate()` clears it	3.2.x: same as 2.x. 3.3.0: the identity is cleared and `info()` returns `None`; `invalidate()` stays the portable log-out	v0.16.0
Duplicate signin identifier in the record table	signin fails (`No record was returned`)	signin succeeds, picking one record	v0.16.0
Model auth (`signup`/`signin`/`authenticate`, `access_ddl`/`define_access`)	same on both lines (verified on 2.6.5)	same on both lines (verified on 3.2.4)	v0.17.0
Model auth session isolation (each call on its own ephemeral connection)	same on both lines (an SDK/connection property, not a server one)	same on both lines (verified on 3.2.4)	v0.17.0
Hydrated instance's password field after `signup`/`signin`	holds the stored hash, never the plaintext	same on both lines	v0.17.0
Model config `with_refresh=True` → `define_access()`	raises `SurrealDbError` (clause does not parse; message names the 3.x requirement)	applies, and `AuthResult.tokens.refresh` is populated	v0.17.0
`Model.refresh(token)` renewal	unavailable — no refresh token can exist	returns a fresh, rotated pair; the spent token is rejected	v0.17.0
Field aliases (`Field(alias=…)`) across writes, hydration and every QuerySet clause	same on both lines (pure Pydantic + client-side name translation)	same on both lines (verified on 3.2.4)	v0.18.0
`server_fields` — excluded from creates, kept on replaces, hydrated back	same on both lines; a `DEFAULT` is a create-time default on 2.6.5	same on both lines; a `DEFAULT` is a create-time default on 3.2.4	v0.18.0
A server column omitted from a REPLACE (`UPDATE`/`UPSERT … CONTENT`)	an optional column is deleted; a required one raises `Found NONE for field …`	same on both lines — which is why the ORM keeps it in replace payloads	v0.18.0
`merge(refresh=False)` — skipped resync, `RETURN NONE`, forfeited missing-record check	same on both lines	same on both lines (verified on 3.2.4)	v0.18.0
`merge()` on a table that was never created	server returns no rows → ORM raises `SurrealDbError` ("no record found")	server raises `NotFoundError` → normalised to the same `SurrealDbError`	v0.18.0
Live queries (`live()`, `subscribe_live()`, `kill()`) — the subscription itself	supported over WebSocket (verified on 2.6.5)	supported over WebSocket (verified on 3.2.4)	v0.19.0
`live()` on a table that does not exist	succeeds; the stream simply never fires	server raises `NotFound` → ORM raises `SurrealDbNotFoundError`	v0.19.0
Server notification after `kill()`	none is sent	a final envelope whose action is `KILLED`	v0.19.0
End of a `subscribe_live()` stream after `kill()`	ORM pushes its own sentinel so the `async for` ends	ends on the sentinel or the `KILLED` envelope, whichever lands first	v0.19.0
`session` key in a live notification envelope	absent	present (the SDK session uuid)	v0.19.0
Live queries over HTTP	refused by the ORM with a message naming the WebSocket requirement	same on both lines	v0.19.0
Filtered live query (`live()`/`watch()` with `filter()`, `Q`, `Var`)	bound parameters never reach a `LIVE SELECT` (they read as `NONE`, the filter matches nothing); the ORM inlines each value as a literal, so it works	parameters would work; the ORM inlines too, for one code path — same result on both lines	v0.20.0
A record entering / leaving a live filter	entering → `UPDATE`; leaving → nothing (nor its later `DELETE`)	same on both lines	v0.20.0
`fetch()` inside a live query	links resolved in every notification	same on both lines	v0.20.0
Raw diff payload (`watch(diff=True)`): root patch path	`"/"`	`""`	v0.20.0
Raw diff payload (`watch(diff=True)`): `DELETE`	the whole last record (a dict, not a patch list)	`[{op: replace, path: "", value: None}]`	v0.20.0
Typed diff `DELETE` (`live(diff=True)`): `event.instance`	the last state of the record	an instance holding only the `id`	v0.20.0
Diff patch format	extended JSON Patch: a changed string is an `op: change` with diff-match-patch text	same on both lines	v0.20.0
A WebSocket that drops (detection, server-side cleanup)	the SDK's receive task ends within milliseconds; the server removes the session's live queries	same on both lines	v0.21.0
Live stream after a drop (`auto_resubscribe=True`)	resubscribed on a new connection; `live_id` changes, `on_reconnect` runs	same on both lines	v0.21.0
A request in flight when the socket drops	fails with the SDK's `KeyError` (not normalised; the next call reconnects)	same on both lines	v0.21.0
Change-feed entry shape	`{versionstamp, changes}`; a create is reported as `update`	same on both lines	v0.21.0
Change-feed `SINCE <n>` — what `n` is	the counter, `versionstamp >> 16` (the raw stamp silently returns nothing); `ChangeModelStream.cursor` normalises it	the versionstamp, inclusive; `cursor` normalises it	v0.21.0
`changes(since=<datetime>)`	the server also returns some earlier changes → at-least-once	the server returns nothing → the ORM converts to a versionstamp → exact	v0.21.0
`changes(since=None)` — finding "now"	the ORM searches the counter (no history replayed)	the ORM reads the server clock's millisecond, then skips the entries already stamped in it (no history replayed); before v0.22.2 the entries of that millisecond were replayed	v0.21.0
`INCLUDE ORIGINAL` delete in a change feed	the id only → an instance holding only the `id`	3.3.x: carries `original` → the last state of the record; 3.1.5: as 2.x	v0.21.0
`SHOW CHANGES` on a table without a change feed	returns `[]`; `changes()` raises `SurrealDbError` instead	same on both lines	v0.21.0
Write to a `CHANGEFEED` table (`save()`, `merge()`, `delete()`)	measured on 2.7.0: 8 concurrent writers conflict, even on different records (2.5–4 % refused); one writer, rarely, at a change-feed GC (10 s); wrap in `retry_on_conflict`	measured on 3.3.4: no conflict observed, with 8 concurrent writers	v0.21.0
Typed edges (`relate(Edge, …)`, edge `save()` and `bulk_create()`) through `insert_relation()`	same on both lines: a real relation that graph traversal sees, `id` hydrated from the returned row	same on both lines	v0.22.0
Explicit edge id that already exists: raw `INSERT RELATION`, then through the ORM (edge `save()`, `bulk_create()`)	raw: 2.7.0 silently keeps the old edge and returns it. ORM: `SurrealDbError` ("already exists"), nothing written	raw: 3.3.3 raises `AlreadyExistsError`; 3.1.5 silently keeps the old edge, although the SurrealDB docs announce a record-exists error from 3.1.5. ORM: as 2.x, on 3.1.5 and 3.3.3	v0.22.0
Explicit edge id given as a `RecordID` of another table	raw: silently moved into the edge table. ORM: `ValueError` before anything is sent	raw: raises. ORM: `ValueError` before anything is sent	v0.22.0
`TYPE RELATION … ENFORCED` with an endpoint record that does not exist	raw: `InternalError` ("The record … does not exist"). ORM: `SurrealDbNotFoundError`	raw: `NotFoundError`. ORM: `SurrealDbNotFoundError`	v0.22.0
Changing `in`/`out` of an existing edge (`UPDATE … CONTENT`/`MERGE`/`SET`, `PATCH`)	silently ignored by the server (the other fields are written); the ORM refuses it (`ValueError`, frozen fields)	same on both lines	v0.22.0
`UPSERT` / `CREATE` carrying `in`/`out` on an edge table	stores a plain record that no graph traversal sees; the ORM refuses `upsert()` on an edge, and `save()` uses `INSERT RELATION`	same on both lines	v0.22.0
Replacing an edge without `in`/`out` on a `TYPE RELATION … SCHEMAFULL` table	raises `Found NONE for field in …`; the ORM's `update()` always sends the endpoints	keeps the endpoints; the ORM sends them anyway	v0.22.0
Edge record in a `CREATE`/`UPDATE` live notification or a change-feed update entry	`raw` carries `"__": true`, the typed `instance` drops it; `DELETE` notifications and change-feed delete entries carry no marker	no marker; the typed `instance` is identical	v0.22.0
`table_name`, model instances as filter values, `"table:id"` strings on edge endpoints in filters	same on both lines	same on both lines	v0.22.0
Edge write refused by the table's `PERMISSIONS` (record user)	fewer rows come back and no error (a batch keeps its accepted rows); the ORM raises `SurrealDbError`	same on both lines	v0.22.0
`remove_relation` / `remove_all_relations` on an edge table that was never created, with or without `tx=`	native no-op (graph-path `DELETE`)	native no-op (graph-path `DELETE`); before v0.22.0 it raised "table does not exist", which the ORM suppressed outside a transaction	v0.22.0
`remove_relation` / `remove_all_relations` under `tx=` when the source record's own table does not exist	no-op	the statement fails, and the transaction with it (outside a transaction the ORM keeps it a silent no-op on both lines)	v0.22.0
`get_related(direction="both")`	out-neighbours, then in-neighbours; the order inside each group differs between versions	same on both lines	v0.22.0
`UNIQUE` index on an edge table's `in, out`: a second edge for the same ordered pair	raises ("index … already contains"), through `insert_relation()` and `RELATE`; the reverse pair is allowed	same on both lines	v0.22.0
Rich field types (`datetime`, `date`, `timedelta`/`Duration`, `Decimal`, `UUID`, `bytes`, `Range`, `set[X]`)	same on both lines: stored natively (sets as arrays) and read back as the declared type; a value the server would reject is refused with `ValueError` before anything is sent	same on both lines (verified on 3.3.4 and 3.1.5)	v0.23.0
`TYPE set<…>` columns (a `set[X]` or `frozenset[X]` field)	an array: the ORM reads back the declared set	3.1: writing the array is refused. 3.3: stored as a native set, read back as the declared set (the SDK 2.0.0 alone cannot read it, section 24). Declare `array<…>` columns on 3.1.x	v0.23.0
Reading a `Decimal`	trailing zeros kept (`Decimal("12.50")` reads `12.50`)	normalised (`12.5`); the values are equal	pre-existing (documented in v0.23.0)
Geometry fields, `nearby()`, `GeoDistance`, the `inside` / `outside` / `intersects` lookups	same on both lines: stored as native geometries, read back as the declared class, distances in metres identical	same on both lines (verified on 3.3.4 and 3.1.5); an SDK geometry SurrealDB refuses raises `ValueError` before anything is sent	v0.24.0
A `Decimal` coordinate written with an exponent in an SDK geometry	raw SDK value: refused, or a wrong value for a long one. ORM: sent in fixed point, stored with its value	raw SDK value: stored, a long one with a wrong value. ORM: sent in fixed point, stored with its value	v0.24.0
A GeoJSON dict written to an untyped field (`Any`, `dict`)	stored as a plain object, not a geometry: `nearby()` skips it, `geo::*` functions and `geometry<…>` columns refuse it; declare a geometry field	same on both lines	pre-existing (documented in v0.24.0)
Time travel (`as_of()`) on a server without versioned storage	no error, and the ORM cannot tell: in memory or on plain SurrealKV, only records last written at or before the moment, in their current state. RocksDB: `SurrealDbError`	`SurrealDbError` (the server refuses `VERSION`)	v0.26.0
Time travel on versioned storage: records deleted since, counts and aggregates	right (the ORM's per-record check also corrects the server's unfiltered `count()`)	right (the ORM's per-record check hides the records deleted before the moment that the server's table reads return)	v0.26.0
`fetch()` and dotted paths through record links under `as_of()`	`fetch()` refused (`SurrealDbError`) by `exec()`, `first()`, `get()` without an id; dotted paths in `filter()`, `select()`, `order_by()`, `values()` read the current value	as they were at the moment	v0.26.0
Writing history with a raw `CREATE … VERSION`	stored at that moment, readable with `as_of()`	accepted, not readable at that moment (the table did not exist then)	v0.26.0
`as_of()` moment before 1970-01-01 or after 2262-04-11T23:47:16.854775Z	refused by the ORM before any read (`ValueError`; `SurrealDbError` for a duration or expression resolving there): the server reads an earlier moment as the current state	refused by the ORM before any read, same errors: the server would fail with an internal error	v0.26.0
Subqueries (`Subquery`, or a QuerySet as a filter value)	one statement for every read, count, aggregation, write by filter and live query; reads inside a transaction raise (buffered)	the same, and reads inside an interactive transaction (`objects(tx=)`) see its uncommitted writes	v0.27.0
A subquery with `order_by()` and `limit()`	right; the ORM also selects the order fields, without which 2.x refuses to order by a field other than the one compared	right: the ORM binds each subquery once, in a `LET`; 3.2 and later can return wrong rows for such an inline sub-SELECT (surrealdb#7470)	v0.27.0
A subquery reading a table that does not exist	read as empty, like any missing table	read as empty in three requests (statement, table probe, re-send); other errors, even ones quoting "does not exist", are relayed; transaction writes fail; `live()` raises on start	v0.27.0
Subqueries under `as_of()`	each subquery reads at the same moment: the ORM adds `VERSION` to it (the server would read the current state)	the same (the server applies the outer `VERSION` to an inline sub-SELECT only); without versioned storage `SurrealDbError`, but 3.1.5 raises the server's own `ThrownError`	v0.27.0
Query cache (`cache()`, `QueryCache`)	same on both lines: client-side, per connection and identity; writes in a transaction invalidate at commit; reads in one are never cached (on 2.x they raise: buffered)	the same; reads inside an interactive transaction are never cached (they see its uncommitted writes)	v0.28.0
`select("meta.*")` on a nested object that is not a record link	its values, as a list; through a link (`select("author.*")`), the linked record	the object; through a link, the linked record	pre-existing (documented in v0.28.1)
`filter(f="$x")`: a string filter value starting with `$`	same on both lines: compared with the string `"$x"`, bound like any value, in a live query too; `Var("x")` reads the variable `$x`, `Var("auth")` SurrealDB's `$auth`	the same; on an edge's `in`/`out` (record ids) such a string raises `ValueError`, on both lines; `$access`, `$session`, `$token` read like `$auth`	v0.28.1
`limit(n)` or `offset(n)` above `4294967295` (`2**32 - 1`)	the server refuses the read: `Found <n> but the LIMIT clause must evaluate to a positive integer` (`START` likewise)	read; above `2**63 - 1` the ORM raises `ValueError` when `limit()` or `offset()` is called, on both lines	pre-existing (documented in v0.28.1)
Several connections in one process (`add_connection()`, `using()`)	each connection follows its own server: a 2.x connection's transactions are buffered, whatever the others are	each 3.x WebSocket connection gets interactive transactions, beside 2.x connections in the same process	v0.29.0
Text lookups (`startswith`, `endswith`, `like`, `ilike`, `match`, `regex`)	same on both lines: string functions guarded by `(field).is_string()` (`type::is::string` is 2.x-only); a `NONE`, `NULL`, non-string or array path value never matches	the same (`type::is_string` is 3.x-only; `a.b.is_string()` tests each element of an array path); until v0.29.1: operators SurrealQL lacks (`LIKE`…), a parse error on both lines	v0.29.2
Schema introspection (`DatabaseIntrospector`, `generate_models_from_db()`)	renders `option<T>`, array items as `tags[*]`, `FLEXIBLE` before `TYPE`, IF expressions bare: kinds and field paths are normalised to the 3.x forms	renders optional kinds with `none` first, items as `tags.*`, `FLEXIBLE` after `TYPE`, IF expressions in parentheses: same state and model types; expressions kept as rendered	v0.30.0
Definitions only one line has (schema introspection)	`MTREE` and `SEARCH ANALYZER` indexes, `geometry<feature>`: read and typed	`COMPUTED`, `REFERENCE`, `FULLTEXT`, kind `file`, `WITH REFRESH`, `BEARER`: read and typed; `DEFINE SEQUENCE`: kept raw	v0.30.0
`INFO` (schema introspection) by a record user or an anonymous session	refused: `SurrealDbAuthenticationError` (the SDK raises `InternalError`)	refused: `SurrealDbAuthenticationError` (the SDK raises `NotAllowedError`)	v0.30.0
`array<T, N>` read by schema introspection	at most N items; the generated `list[T]` leaves the check to the server	exactly N items; the generated `list[T]` leaves the check to the server	v0.30.0
Schema introspection of a database removed while the connection is open	empty schema	`SurrealDbError` (the database does not exist) until the database is defined again	v0.30.0
DDL not yet committed in a transaction, seen by schema introspection	invisible	invisible	v0.30.0
A regex with a backslash pair before a `/` (schema introspection)	`/a\\/b/` accepted (a backslash, then an escaped slash), `/\\/` refused: such a definition is a `SchemaIssue`, not read	`/\\/` accepted (an escaped backslash, then the closing slash), `/a\\/b/` refused: such a definition is a `SchemaIssue`, not read	v0.30.0
`save()` applies the returned row (explicit id, edges); `update()`/`upsert()` keep an unchanged stored `datetime` (nanoseconds)	stored row and read types (enums, sets…) after `save()`; a top-level `READONLY` datetime updates, a nested one may not; refused if a typed `VALUE` field is left out (#267)	as on 2.x (verified on 3.3.4 and 3.1.5), but a typed `VALUE` field left out is recomputed; `save(tx=)` in an interactive transaction applies the row too, a buffered one does not	v0.30.7
Full suite and every divergence in this table, re-run on SurrealDB 2.7.0 and 3.3.0	2.7.0 identical to 2.6.5 (same passes, same 3.x-only skips)	3.3.0 identical to 3.2.4, except the system-user signin row	v0.19.1

Note on record IDs: A record loaded from the database has its id field set to a native surrealdb.RecordID object, not a plain string. Use model.get_raw_id() to obtain the bare identifier string (e.g. "alice"), or compare directly with model.id == RecordID("User", "alice"). In-memory instances you construct yourself retain whatever value you assign.

ORM behaviour: the embedded engine

The official SDK's in-process engine (section 26) is a line of its own: SurrealDB core 2.3.10, bundled with SDK 2.0.0. Rows measured with SDK 2.0.0 on mem:// (the time-travel row on surrealkv+versioned://), and on the 2.7.0 and 3.3.4 servers. Capabilities not listed behave as on a 2.x server.

ORM capability	Embedded engine (SDK 2.0.0)	Servers (2.x and 3.x)	Since
Connection and credentials (`set_connection()`)	URL, namespace and database; `user` and `password` optional, kept and never sent	every setting required; the configured user signs in on each new connection	v0.25.0
URLs the SDK refuses (`rocksdb://`, a typo)	`SurrealDbConnectionError` quoting the SDK (naming #233 for `rocksdb://`), before anything is opened	n/a	v0.25.0
Clients	one per connection for the whole process, shared by every event loop and thread	one per connection and event loop	v0.25.0
Namespace/database change	the next call resets the session to the owner and selects the new pair on the same engine (no data lost); the stored session ends	taken by the next connection	v0.25.0
Closing (`close_connection()`, `unset_connection()`, `reconnect()`)	`mem://` discarded, `surrealkv://` reopened with its data; with SDK 2.x a client kept after the close is still a running engine; `close_connection()` and `reconnect()` leave the server clients open (`is_connected()` stays true on a loop with one)	the connection closes	v0.25.0
SurrealQL line	2.x (SurrealDB 2.3.10 core); `client.version()` reports the SDK (`surrealdb-2.0.0`)	the server's	v0.25.0
`transaction()`	buffered	interactive on WebSocket and SurrealDB 3.x, else buffered	v0.25.0
Concurrent first writes to a table that does not exist yet	retryable conflict, a few % per batch of 20: define the table first	rare on 2.x, none measured on 3.x	v0.25.0
Live queries (`live()`, `watch()`, `subscribe_live()`)	`SurrealDbError` before any request: use change feeds	WebSocket only	v0.25.0
Change feeds (`changes()`)	from now and from a cursor; `since=<datetime>` refused (`SurrealDbError`)	from now, a cursor or a datetime	v0.25.0
Model auth (`AuthenticatedUserMixin`)	`bind=True` only, on the shared session, one call at a time with connection-level auth calls (the last one wins); a failure restores the previous session, except a record that does not fit the model after a successful `signup()` or `signin()`: the session stays signed in as it, as on a server (`refresh()` cannot succeed: no `WITH REFRESH`)	a session of its own; `bind=True` also adopts the token	v0.25.0
`ephemeral_client()`	`SurrealDbError`: the engine has a single session per database	an anonymous throwaway client	v0.25.0
Anonymous session	the in-process owner (full access); `info()` returns `None`	not used by the ORM (the configured user signs in); an anonymous `info()` raises `SurrealDbAuthenticationError`	v0.25.0
`invalidate()`	back to the in-process owner	signs in again as the configured user	v0.25.0
Reading a table that does not exist	`[]`, raw queries included (as on 2.x)	the ORM reads `[]`; a raw query on 3.x raises `NotFoundError`	v0.25.0
Parse errors	bare `RuntimeError` (the DDL helpers raise `SurrealDbError`)	the SDK's error classes	v0.25.0
Time travel (`as_of()`)	`surrealkv+versioned://` only (`?versioned=true` is ignored by SDK 2.0.0); any other URL → `SurrealDbError` before any request; `fetch()` refused (2.x core)	versioned storage required (see the 2.x/3.x table)	v0.26.0
Several connections (`add_connection()`)	`mem://`: each name is a database of its own; an on-disk store (`surrealkv://`, `file://`) is open under one name at a time — a second name raises `SurrealDbConnectionError`	each name opens its own clients; two databases on one server are isolated	v0.29.0
Schema introspection (`DatabaseIntrospector`)	reads like a 2.x server (core 2.3.10): same state and same generated model types as on the servers; the anonymous owner session may run `INFO`; `COUNT` indexes cannot be defined	need a system user (root, namespace or database user)	v0.30.0
Everything else	CRUD, QuerySet, aggregations, relations, typed edges, rich and geo fields, upsert/patch, server values, computed fields, stored functions, signals: the same as on a 2.x server	unchanged	v0.25.0

Contributing

Contributions are welcome! Please:

Fork the repository
Create a feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m "Add amazing feature")
Push to the branch (git push origin feature/amazing-feature)
Open a Pull Request

Roadmap

Version	Theme	Status
v0.2.x – v0.7.0	Core ORM → SDK 2.0 / SurrealDB 3.x migration	✅ Released
v0.8.0	Transactions ORM (`tx=`)	✅ Released
v0.9.0	Transactions — QuerySet & interactive (3.x)	✅ Released
v0.10.0	upsert / update_or_create / get_or_create	✅ Released
v0.11.0	patch / atomic field & array ops	✅ Released
v0.12.0	retry_on_conflict & optimistic concurrency	✅ Released
v0.13.0	SurrealFunc & server-side values	✅ Released
v0.14.0	Computed fields (`DEFINE FIELD … VALUE`)	✅ Released
v0.14.3 – v0.14.5	Correctness: `$`-values, record-id lookups, loops	✅ Released
v0.15.0	`call_function()` — custom `fn::` stored functions	✅ Released
v0.16.0	Connection auth (`signin`/`signup`/`info`)	✅ Released
v0.17.0	Model auth (`AuthenticatedUserMixin`)	✅ Released
v0.18.0	Field aliases, `server_fields`, `merge(refresh=)`	✅ Released
v0.19.0	Live queries (base): `live()` / `kill()`, raw notifs	✅ Released
v0.20.0	Typed live queries: `LiveModelStream`, diff, filters	✅ Released
v0.21.0	Auto-resubscribe & change feeds (`changes()`)	✅ Released
v0.22.0	Tier 1 — Native typed relations	✅ Released
v0.23.0	Rich field types	✅ Released
v0.24.0	Geospatial fields	✅ Released
v0.25.0	Embedded test engine	✅ Released
v0.26.0	Time travel (versioned storage)	✅ Released
v0.27.0	Subqueries	✅ Released
v0.28.0	Query cache	✅ Released
v0.29.0	Tier 2 — Multi-database	✅ Released
v0.30.0	Tier 3 — Schema introspection	✅ Released
v0.31.0 – v0.39.0	Tier 3 — Advanced (search, DDL, migrations, CLI)	📋 Planned
v0.40.0	Beta Phase (API freeze, hardening)	📋 Planned
v2.0.0	Production / GA (aligned with SDK 2.0)	📋 Planned

Every roadmap feature is implementable with the official SDK 2.0 (native methods or query() SurrealQL) — no custom SDK. GA is numbered v2.0.0 to mirror SDK 2.0; the 1.x line is intentionally skipped.

See docs/ROADMAP.md for full details.

SurrealDB-ORM-lite vs SurrealDB-ORM

This project prioritizes stability and compatibility with the official SurrealDB Python SDK. The full SurrealDB-ORM uses a custom SDK for advanced features.

Both projects target the same feature set; the difference is how (official SDK vs custom SDK) and server support. Everything below is on the lite roadmap via the official SDK 2.0 — only the custom-SDK internals stay exclusive to the full ORM; the official SDK's in-process engine (section 26) is lite-only.

Feature	ORM-lite (official SDK)	ORM (custom SDK)
Supported SurrealDB	2.6/2.7 + 3.2/3.3	3.x only
CRUD & QuerySet	✅	✅
Aggregations & GROUP BY	✅	✅
Model Signals	✅	✅
Bulk Operations	✅	✅
Q Objects (OR/AND/NOT)	✅	✅
Parameterized Filters	✅	✅
Relations & Graph	✅ (reverse, keyword edge fields, tx= on every write helper: v0.22.0)	✅
FETCH clause	✅	✅
Transactions (tx=)	✅ v0.8 (core), v0.9 QS	✅
Interactive tx (3.x native)	✅ v0.9	✅
upsert / update_or_create	✅ v0.10.0	✅
Atomic field/array operations	✅ v0.11.0	✅
Retry on conflict	✅ v0.12.0	✅
SurrealFunc & server values	✅ v0.13.0	✅
Computed fields	✅ v0.14.0	✅
`call_function()` (`fn::`)	✅ v0.15.0	✅
JWT Authentication	✅ v0.16.0 (connection)	✅
Model auth mixin	✅ v0.17.0	✅
Field Aliases & DX	✅ v0.18.0	✅
Live queries (raw notifs)	✅ v0.19.0	✅
Typed live queries	✅ v0.20.0	✅
Live auto-resubscribe / CDC	✅ v0.21.0	✅
Native typed relations	✅ v0.22.0 (typed edge models; relation DDL in v0.33.0)	✅ DDL only (TableType.RELATION)
Rich field types	✅ v0.23.0	✅
Geospatial Fields	✅ v0.24.0	✅
Embedded engine	✅ v0.25.0 (mem://, surrealkv://; rocksdb:// #233)	❌ (HTTP/WebSocket only)
Time travel (`VERSION`)	✅ v0.26.0 (`as_of()`)	❌
Subqueries	✅ v0.27.0 (`Subquery`, a QuerySet in `filter()`)	✅
Query Cache	✅ v0.28.0 (`cache()`, `QueryCache`)	✅
Multi-database	✅ v0.29.0 (`add_connection()`, `using()`)	✅
Schema Introspection	✅ v0.30.0 (`DatabaseIntrospector`, `generate_models_from_db()`)	✅
DEFINE EVENT	v0.31.0	✅
Materialized views	v0.32.0	✅
Full-Text Search	v0.34.0	✅
Vector Search (KNN/HNSW)	v0.35.0	✅
Hybrid Search (RRF)	v0.36.0	✅
Migrations & CLI	v0.37 – v0.38	✅
Test Fixtures & Factories	✅ v0.25.0 SurrealFixture; factories v0.39.0	✅
Retry, Logging, Metrics	v0.40.0	✅
Connection Pool	post-GA (tentative)	✅
Custom SDK / CBOR Protocol	❌ never	✅

Choose ORM-lite if you want the official SDK, minimal dependencies, support for SurrealDB 2.x (2.6 / 2.7) and 3.x (3.2 / 3.3), and a full feature roadmap built entirely on the official SDK.

Choose ORM if you need the custom-SDK internals (CBOR protocol, native connection pool) or those features available today rather than on the roadmap.

SurrealDB-ORM GitHub: github.com/EulogySnowfall/SurrealDB-ORM
SurrealDB-ORM PyPI: surrealdb-orm

License

MIT License - see LICENSE for details.

Author

Yannick Croteau GitHub: @EulogySnowfall

SurrealDB - The database
surrealdb.py - Official Python SDK
SurrealDB-ORM - Full-featured ORM with custom SDK

Metadata

Release files for surreal-orm-lite 0.30.8

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for surreal-orm-lite 0.30.8
File	Size	Uploaded	Provenance
surreal_orm_lite-0.30.8.tar.gz	605.3 kB	Oct 19, 2026	With provenance	Details

Built distribution (wheel)

Table of built distributions (wheels) for surreal-orm-lite 0.30.8
File	Interpreter	ABI	Platform	Reset
surreal_orm_lite-0.30.8-py3-none-any.whl 468.2 kB Oct 19, 2026 With provenance	Python 3	none	any	Details

Total release size: 1.1 MB

Release files / surreal_orm_lite-0.30.8.tar.gz

Download URL	surreal_orm_lite-0.30.8.tar.gz
Size	605.3 kB
Tags	Source
SHA-256 checksum How to use checksums	`5dfac6e56ef760027c87d3042ed8bde5c5e8ffe3c26e140918b66e22ffe21583`
BLAKE2b-256 checksum How to use checksums	`755ee54f42e1d48027b0648435f0ba6e5452079519a3b64a575ed3afafc842a2`
Upload date	Oct 19, 2026
Uploaded using Trusted Publishing? What is trusted publishing?	Yes
Uploaded via	`twine/7.0.0 CPython/3.13.14`

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Identity

Publishing platform: GitHub Actions
Publishing repository: github.com/EulogySnowfall/SurrealDB-ORM-lite
Publishing commit: github.com/EulogySnowfall/SurrealDB-ORM-lite/tree/f98dab1c07c84cf61a4e2531731824e8b95b410c

Workflow

Publishing configuration: github.com/EulogySnowfall/SurrealDB-ORM-lite/blob/f98dab1c07c84cf61a4e2531731824e8b95b410c/.github/workflows/publish.yml
Publishing logs: github.com/EulogySnowfall/SurrealDB-ORM-lite/actions/runs/39330415515/attempts/1

Artifact

Subject: surreal_orm_lite-0.30.8.tar.gz
SHA-256 checksum: 5dfac6e56ef760027c87d3042ed8bde5c5e8ffe3c26e140918b66e22ffe21583 Verifying attestations

Release files / surreal_orm_lite-0.30.8-py3-none-any.whl

Download URL	surreal_orm_lite-0.30.8-py3-none-any.whl
Size	468.2 kB
Tags	Python 3
SHA-256 checksum How to use checksums	`4bc5893de8993d934470bbe01c0dea29f3489bf8258fdb4b51a5ef13f9522486`
BLAKE2b-256 checksum How to use checksums	`de9c013bd1c327d5ae83a3cbce5b4b08b37bb1d4036f0b9a35a2951f6e2d29fa`
Upload date	Oct 19, 2026
Uploaded using Trusted Publishing? What is trusted publishing?	Yes
Uploaded via	`twine/7.0.0 CPython/3.13.14`

Provenance

Identity

Publishing platform: GitHub Actions
Publishing repository: github.com/EulogySnowfall/SurrealDB-ORM-lite
Publishing commit: github.com/EulogySnowfall/SurrealDB-ORM-lite/tree/f98dab1c07c84cf61a4e2531731824e8b95b410c

Workflow

Publishing configuration: github.com/EulogySnowfall/SurrealDB-ORM-lite/blob/f98dab1c07c84cf61a4e2531731824e8b95b410c/.github/workflows/publish.yml
Publishing logs: github.com/EulogySnowfall/SurrealDB-ORM-lite/actions/runs/39330415515/attempts/1

Artifact

Subject: surreal_orm_lite-0.30.8-py3-none-any.whl
SHA-256 checksum: 4bc5893de8993d934470bbe01c0dea29f3489bf8258fdb4b51a5ef13f9522486 Verifying attestations

Security for surreal-orm-lite 0.30.8

Report project as malware

Provenance

Full provenance

All 2 files in this release have been uploaded with provenance metadata.

Attestation for surreal_orm_lite-0.30.8.tar.gz

Identity

Publishing platform: GitHub Actions
Publishing repository: github.com/EulogySnowfall/SurrealDB-ORM-lite
Publishing commit: github.com/EulogySnowfall/SurrealDB-ORM-lite/tree/f98dab1c07c84cf61a4e2531731824e8b95b410c

Workflow

Publishing configuration: github.com/EulogySnowfall/SurrealDB-ORM-lite/blob/f98dab1c07c84cf61a4e2531731824e8b95b410c/.github/workflows/publish.yml
Publishing logs: github.com/EulogySnowfall/SurrealDB-ORM-lite/actions/runs/39330415515/attempts/1

Artifact

Subject: surreal_orm_lite-0.30.8.tar.gz
SHA-256 checksum: 5dfac6e56ef760027c87d3042ed8bde5c5e8ffe3c26e140918b66e22ffe21583 Verifying attestations

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.