SurrealDB v3 Patterns¶

surql 1.4.0 introduced the SurrealQL forms required by SurrealDB v3. The v3 engine is stricter than v2 about datetime coercion, count aggregates, record-ID construction, transaction batching, and DDL idempotence. This page documents the patterns the library emits and explains the forms you should reach for in any raw SurrealQL you still hand-write.

All examples run cleanly against the v3 integration CI (surrealdb/surrealdb:v3.0.5, see the v3-integration workflow).

Datetime cast on insert¶

v3 no longer coerces bare ISO-8601 strings into datetime values. Any column typed datetime requires an explicit <datetime> cast at the call site.

Before (v2-only):

CREATE _migration_history SET
  version = '20260102_120000',
  applied_at = '2026-01-02T12:00:00Z';

Now (v3-compatible):

CREATE _migration_history SET
  version = '20260102_120000',
  applied_at = <datetime> $applied_at;

Driven from Python, this is handled automatically by the migration recorder:

await client.execute(
  'CREATE _migration_history SET '
  'version = $version, applied_at = <datetime> $applied_at',
  params={'version': version, 'applied_at': '2026-01-02T12:00:00Z'},
)

`count()` aggregates require `GROUP ALL`¶

v3 rejects count(*) and also rejects a bare SELECT count() FROM table without an explicit grouping. Always append GROUP ALL for full-table counts and use count() (no *).

Before:

SELECT count(*) AS total FROM user;

Now:

SELECT count() AS total FROM user GROUP ALL;

The query builder emits the correct form out of the box:

from surql import Query, count_records

# CRUD helper, used by SurrealDB v3
await count_records('user')  # -> SELECT count() AS count FROM user GROUP ALL

# Builder, explicit
(
  Query()
  .select(['count()'])
  .from_table('user')
  .group_all()
)

count_if(predicate) renders count(<predicate>) for conditional counts (see Query UX helpers).

Record-ID construction: `type::thing(table, id)`¶

The constructor for a record id from a separate table name and id value is type::thing(table, id) on both SurrealDB v2 and v3.

[!WARNING] Earlier versions of this guide claimed type::thing was renamed to type::record in v3. That was wrong. In v3 the two-arg form of type::record(value, type) is a type coercion -- it casts value into record<type>, and type::record('user', 'alice') is interpreted as "coerce 'user' into record<alice>" (which fails). The single-arg form type::record('user:alice') parses a full record-id string. For table+id construction, use type::thing(table, id).

Prefer:

from surql import type_record  # alias kept for source compatibility

ref = type_record('user', 'alice').to_surql()
# -> type::thing('user', 'alice')

type_thing() is the canonical name and is identical:

from surql import type_thing

type_thing('user', 'alice').to_surql()
# -> type::thing('user', 'alice')

See Query UX helpers for the full helper surface, including composition with RecordID, integers, and nested SurrealFn arguments.

Buffered `BEGIN`/`COMMIT` transactions¶

The v3 RPC protocol dispatches each client.execute() call as a separate statement. BEGIN TRANSACTION; ...; COMMIT TRANSACTION; split across three round trips crashes v3 because later statements run outside the transaction scope and the trailing COMMIT has nothing to commit.

DatabaseClient.execute() now batches BEGIN/COMMIT and all statements between them into a single RPC frame. Callers don't have to change anything — the transaction() context manager and the migration executor do the right thing automatically:

from surql import get_client, transaction

async with get_client(config) as client:
  async with transaction(client):
    await client.execute('UPDATE user:alice SET credits -= 10')
    await client.execute('UPDATE user:bob   SET credits += 10')
  # Emitted as a single BEGIN; ...; COMMIT; RPC.

Embedded engines (mem://, file://, surrealkv://) remain transactional-in-process and skip the wrapper entirely (see CHANGES 1.3.1).

`IF NOT EXISTS` on DDL¶

v3 treats repeat DDL as an error unless the statement is idempotent. surql emits DEFINE TABLE ... IF NOT EXISTS, DEFINE INDEX ... IF NOT EXISTS, and DEFINE FIELD ... IF NOT EXISTS whenever the generator's if_not_exists=True flag is set (the default for the migration history table and the recommended setting for schema generation).

from surql.schema.table import generate_table_sql

sql = generate_table_sql(user_schema, if_not_exists=True)
# DEFINE TABLE IF NOT EXISTS user SCHEMAFULL;
# DEFINE FIELD IF NOT EXISTS email ON user TYPE string ...;
# DEFINE INDEX IF NOT EXISTS email_idx ON TABLE user COLUMNS email UNIQUE;

The _migration_history bootstrap in ensure_migration_table() uses this form unconditionally so surql migrate up is safe to run repeatedly on a schema-already-bootstrapped database.

Graph-depth literals¶

v3 rejects grouped graph-depth syntax such as ->follows{1..3}->user. Expand to literal hop lists:

from surql import traverse

# surql unrolls depth ranges into literal hop unions that v3 accepts.
await traverse('user:alice', '->follows->user', depth=(1, 3), client=client)

Full-text index renamed `SEARCH` -> `FULLTEXT`¶

SurrealDB 3.0 renamed the full-text index keyword. The v1/v2 form is a parse error on v3 (Unexpected token, expected Eof at SEARCH):

DEFINE INDEX idx ON TABLE t COLUMNS content SEARCH ANALYZER ascii BM25;  -- parse error on v3

v3 spells it FULLTEXT:

DEFINE INDEX idx ON TABLE t COLUMNS content FULLTEXT ANALYZER ascii BM25 HIGHLIGHTS;

surql emits the FULLTEXT keyword from IndexType.SEARCH / search_index / bm25_index and the migration diff; the INFO FOR TABLE index parser recognises both spellings (so live databases created under either version round-trip). COLUMNS and FIELDS are interchangeable in this statement.

The analyzer is defined separately with a DEFINE ANALYZER statement, which must run before the index that references it. Define it in code rather than hand-authoring SurrealQL:

from surql.schema import (
  bm25_index,
  generate_schema_sql,
  standard_analyzer,
  string_field,
  table_schema,
)

# class tokenizer + lowercase + ascii filters; add .with_filter(snowball('english'))
# for stemming.
analyzer = standard_analyzer('text_en')

memory = table_schema(
  'memory',
  fields=[string_field('content')],
  indexes=[bm25_index('content_bm25', ['content'], 'text_en')],
)

# Analyzers render before the tables that reference them.
sql = generate_schema_sql(tables={'memory': memory}, analyzers={'text_en': analyzer})
# DEFINE ANALYZER text_en TOKENIZERS class FILTERS lowercase,ascii;
#
# DEFINE TABLE memory SCHEMAFULL;
# DEFINE FIELD content ON TABLE memory TYPE string;
# DEFINE INDEX content_bm25 ON TABLE memory COLUMNS content FULLTEXT ANALYZER text_en BM25;

Bare BM25 uses the engine defaults (k1 = 1.2, b = 0.75); a search_index(...) without an analyzer renders the historical ascii default (define + name one explicitly for real lexical recall).

Run the lexical query with the builder or the fulltext_search_query helper:

from surql import fulltext_search_query

# SELECT *, search::score(1) AS score FROM memory
#   WHERE content @1@ 'insider buying' LIMIT 100
query = fulltext_search_query('memory', 'content', 1, 'insider buying').limit(100)

`search::score` and scan ordering¶

The v3 streaming executor's full-text scan yields matching rows already in BM25 relevance order, but in 3.0.x it does not plumb the per-row score through to search::score(<ref>), which returns 0 there. So rank by the scan's natural order rather than ORDER BY search::score(...). This is sufficient for Reciprocal Rank Fusion (RRF), which fuses ranks, not raw scores:

from surql import fulltext_search_query, vector_search_query

# Sparse leg: rows come back in relevance order — take the order, not the score.
sparse = fulltext_search_query('memory', 'content', 1, 'insider buying').limit(100)

# Dense leg: vector KNN.
dense = vector_search_query('memory', 'embedding', query_vector, k=100, distance='COSINE')

# Fuse the two returned orders by rank (RRF) client-side.

search::score(<ref>) is still projected via Query.search_score(ref, alias) / the score_alias argument so the column exists for callers (and for future engine versions that populate it); just don't depend on its magnitude on 3.0.x.

v3 integration CI¶

The v3-integration.yml workflow spins up surrealdb/surrealdb:v3.0.5 and runs the integration suite on every push. The same suite runs nightly against the latest surrealdb/surrealdb:latest image to flag upstream drift early. Opt into the local v3 container via:

export SURQL_PRE_PUSH_INTEGRATION=1
docker run -d -p 8000:8000 --name surrealdb \
  surrealdb/surrealdb:v3.0.5 start --user root --pass root memory

Wire the pre-push hook once per clone so the same checks run before every git push:

git config core.hooksPath .githooks

See CONTRIBUTING.md for the full pre-push setup.

SurrealDB v3 Patterns¶

Datetime cast on insert¶

count() aggregates require GROUP ALL¶

Record-ID construction: type::thing(table, id)¶

Buffered BEGIN/COMMIT transactions¶

IF NOT EXISTS on DDL¶