surrealdb

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SurrealDB - Production-Ready Query Generator

SurrealDB - 生产环境可用的查询生成器

Generate solid, high-quality, production-ready SurrealDB queries and operations using SurrealQL for multi-model database applications including document, graph, and relational patterns.

使用SurrealQL为多模型数据库应用（包括文档型、图型和关系型模式）编写可靠、高质量、可用于生产环境的SurrealDB查询语句与操作。

When to Use This Skill

何时使用此技能

Use this skill when the user wants to:

Write SurrealQL queries (SELECT, CREATE, UPDATE, DELETE, UPSERT)
Design database schemas (SCHEMAFULL/SCHEMALESS tables, field definitions)
Model relationships (record links, graph edges with RELATE, nested data)
Implement authentication (DEFINE ACCESS, SCOPE, permissions, RBAC)
Create indexes for performance optimization
Write custom functions using DEFINE FUNCTION
Build real-time applications with LIVE queries
Implement transactions for data consistency
Migrate from SQL/NoSQL to SurrealDB
Debug or optimize existing SurrealQL

当用户需要以下操作时，可使用此技能：

编写SurrealQL查询语句（SELECT、CREATE、UPDATE、DELETE、UPSERT）
设计数据库模式（SCHEMAFULL/SCHEMALESS表、字段定义）
建模关系（记录链接、使用RELATE的图边、嵌套数据）
实现身份认证（DEFINE ACCESS、SCOPE、权限、RBAC）
创建索引以优化性能
使用DEFINE FUNCTION编写自定义函数
使用LIVE查询构建实时应用
实现事务以保证数据一致性
从SQL/NoSQL迁移至SurrealDB
调试或优化现有SurrealQL语句

SurrealQL Quick Reference

SurrealQL快速参考

Core Statement Syntax

核心语句语法

sql

-- SELECT with graph traversal
SELECT *, ->friends->person AS mutual_friends FROM person:alice;

-- CREATE with specific ID
CREATE person:john SET name = 'John', age = 30;

-- UPDATE with operators
UPDATE person SET age += 1, tags += 'senior' WHERE age >= 65;

-- DELETE with conditions
DELETE person WHERE active = false;

-- UPSERT (create if not exists, update if exists)
UPSERT user:email@example.com SET email = 'email@example.com', visits += 1;

-- RELATE for graph edges
RELATE person:alice->follows->person:bob SET since = time::now();

sql

-- SELECT with graph traversal
SELECT *, ->friends->person AS mutual_friends FROM person:alice;

-- CREATE with specific ID
CREATE person:john SET name = 'John', age = 30;

-- UPDATE with operators
UPDATE person SET age += 1, tags += 'senior' WHERE age >= 65;

-- DELETE with conditions
DELETE person WHERE active = false;

-- UPSERT (create if not exists, update if exists)
UPSERT user:email@example.com SET email = 'email@example.com', visits += 1;

-- RELATE for graph edges
RELATE person:alice->follows->person:bob SET since = time::now();

Data Types

数据类型

sql

-- Basic types
string, int, float, bool, datetime, duration, decimal, uuid

-- Complex types
array, object, record<table>, option<type>

-- Special types
geometry (point, line, polygon), bytes, null, none

sql

-- Basic types
string, int, float, bool, datetime, duration, decimal, uuid

-- Complex types
array, object, record<table>, option<type>

-- Special types
geometry (point, line, polygon), bytes, null, none

Essential Functions

常用函数

sql

-- Time functions
time::now()                    -- Current timestamp
time::floor(datetime, 1d)      -- Floor to day
duration::from::days(7)        -- Create duration

-- String functions
string::is::email($value)      -- Validate email
string::concat($a, ' ', $b)    -- Concatenate
string::split($s, ',')         -- Split to array
string::lowercase($s)          -- Lowercase

-- Array functions
array::len($arr)               -- Array length
array::push($arr, $item)       -- Add to array
array::distinct($arr)          -- Remove duplicates
array::flatten($arr)           -- Flatten nested arrays

-- Crypto functions
crypto::argon2::generate($password)           -- Hash password
crypto::argon2::compare($hash, $password)     -- Verify password

-- Math functions
math::sum($arr)                -- Sum values
math::mean($arr)               -- Average
math::max($arr)                -- Maximum

-- Record functions
record::id($record)            -- Get record ID
record::table($record)         -- Get table name

-- Type functions
type::is::string($val)         -- Type check
type::thing($table, $id)       -- Create record ID

sql

-- Time functions
time::now()                    -- Current timestamp
time::floor(datetime, 1d)      -- Floor to day
duration::from::days(7)        -- Create duration

-- String functions
string::is::email($value)      -- Validate email
string::concat($a, ' ', $b)    -- Concatenate
string::split($s, ',')         -- Split to array
string::lowercase($s)          -- Lowercase

-- Array functions
array::len($arr)               -- Array length
array::push($arr, $item)       -- Add to array
array::distinct($arr)          -- Remove duplicates
array::flatten($arr)           -- Flatten nested arrays

-- Crypto functions
crypto::argon2::generate($password)           -- Hash password
crypto::argon2::compare($hash, $password)     -- Verify password

-- Math functions
math::sum($arr)                -- Sum values
math::mean($arr)               -- Average
math::max($arr)                -- Maximum

-- Record functions
record::id($record)            -- Get record ID
record::table($record)         -- Get table name

-- Type functions
type::is::string($val)         -- Type check
type::thing($table, $id)       -- Create record ID

Instructions for Writing SurrealDB Queries

编写SurrealDB查询的步骤

Step 1: Understand the Data Model

步骤1：理解数据模型

Before writing any SurrealQL:

What is the data structure? (Document, graph, relational, or hybrid?)
What relationships exist? (One-to-many, many-to-many, graph traversals?)
What access patterns? (Read-heavy, write-heavy, real-time?)
What consistency requirements? (Eventual, strong, transactional?)

在编写任何SurrealQL之前：

数据结构是什么？（文档型、图型、关系型，或是混合类型？）
存在哪些关系？（一对多、多对多、图遍历？）
访问模式是什么？（读密集型、写密集型、实时型？）
一致性要求是什么？（最终一致性、强一致性、事务性？）

Step 2: Choose Schema Strategy

步骤2：选择模式策略

SCHEMAFULL - Use when:

Data structure is well-defined
Type safety is critical
Validation rules are needed
Production workloads

SCHEMALESS - Use when:

Rapid prototyping
Evolving data structures
Flexible document storage

sql

-- SCHEMAFULL with validation
DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD email ON user TYPE string ASSERT string::is::email($value);
DEFINE FIELD password ON user TYPE string;
DEFINE FIELD created_at ON user TYPE datetime DEFAULT time::now();
DEFINE FIELD status ON user TYPE string DEFAULT 'active'
    ASSERT $value IN ['active', 'inactive', 'suspended'];

-- SCHEMALESS (flexible)
DEFINE TABLE event SCHEMALESS;

SCHEMAFULL - 适用于以下场景：

数据结构已明确定义
类型安全至关重要
需要验证规则
生产环境负载

SCHEMALESS - 适用于以下场景：

快速原型开发
数据结构不断演变
灵活的文档存储

sql

-- SCHEMAFULL with validation
DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD email ON user TYPE string ASSERT string::is::email($value);
DEFINE FIELD password ON user TYPE string;
DEFINE FIELD created_at ON user TYPE datetime DEFAULT time::now();
DEFINE FIELD status ON user TYPE string DEFAULT 'active'
    ASSERT $value IN ['active', 'inactive', 'suspended'];

-- SCHEMALESS (flexible)
DEFINE TABLE event SCHEMALESS;

Step 3: Design Relationships

步骤3：设计关系

Choose the right relationship model:

Record Links - Simple, direct references:

sql

-- One-to-many via array of record IDs
CREATE user:alice SET
    name = 'Alice',
    friends = [user:bob, user:carol];

-- Fetch with link resolution
SELECT *, friends.* FROM user:alice;

Graph Edges (RELATE) - Complex relationships with metadata:

sql

-- Create relationship with properties
RELATE user:alice->follows->user:bob SET
    since = time::now(),
    notifications = true;

-- Traverse graph
SELECT
    ->follows->user AS following,
    <-follows<-user AS followers
FROM user:alice;

-- Multi-hop traversal
SELECT ->follows->user->follows->user AS friends_of_friends
FROM user:alice;

Embedded Documents - Denormalized data:

sql

CREATE order SET
    customer = { name: 'Alice', email: 'alice@example.com' },
    items = [
        { product: 'Widget', quantity: 2, price: 29.99 },
        { product: 'Gadget', quantity: 1, price: 49.99 }
    ],
    total = 109.97;

选择合适的关系模型：

记录链接 - 简单直接的引用：

sql

-- One-to-many via array of record IDs
CREATE user:alice SET
    name = 'Alice',
    friends = [user:bob, user:carol];

-- Fetch with link resolution
SELECT *, friends.* FROM user:alice;

图边（RELATE） - 带元数据的复杂关系：

sql

-- Create relationship with properties
RELATE user:alice->follows->user:bob SET
    since = time::now(),
    notifications = true;

-- Traverse graph
SELECT
    ->follows->user AS following,
    <-follows<-user AS followers
FROM user:alice;

-- Multi-hop traversal
SELECT ->follows->user->follows->user AS friends_of_friends
FROM user:alice;

嵌入式文档 - 非规范化数据：

sql

CREATE order SET
    customer = { name: 'Alice', email: 'alice@example.com' },
    items = [
        { product: 'Widget', quantity: 2, price: 29.99 },
        { product: 'Gadget', quantity: 1, price: 49.99 }
    ],
    total = 109.97;

Step 4: Implement Authentication

步骤4：实现身份认证

Record-Level Access with DEFINE ACCESS:

sql

-- Define user access
DEFINE ACCESS user_auth ON DATABASE TYPE RECORD
    SIGNUP (
        CREATE user SET
            email = $email,
            password = crypto::argon2::generate($password),
            created_at = time::now()
    )
    SIGNIN (
        SELECT * FROM user
        WHERE email = $email
        AND crypto::argon2::compare(password, $password)
    )
    DURATION FOR TOKEN 24h, FOR SESSION 7d;

-- Define table permissions
DEFINE TABLE post SCHEMAFULL
    PERMISSIONS
        FOR select WHERE published = true OR author = $auth.id
        FOR create WHERE $auth.id != NONE
        FOR update WHERE author = $auth.id
        FOR delete WHERE author = $auth.id;

基于记录的访问控制（DEFINE ACCESS）：

sql

-- Define user access
DEFINE ACCESS user_auth ON DATABASE TYPE RECORD
    SIGNUP (
        CREATE user SET
            email = $email,
            password = crypto::argon2::generate($password),
            created_at = time::now()
    )
    SIGNIN (
        SELECT * FROM user
        WHERE email = $email
        AND crypto::argon2::compare(password, $password)
    )
    DURATION FOR TOKEN 24h, FOR SESSION 7d;

-- Define table permissions
DEFINE TABLE post SCHEMAFULL
    PERMISSIONS
        FOR select WHERE published = true OR author = $auth.id
        FOR create WHERE $auth.id != NONE
        FOR update WHERE author = $auth.id
        FOR delete WHERE author = $auth.id;

Step 5: Optimize with Indexes

步骤5：使用索引优化性能

sql

-- Unique index
DEFINE INDEX unique_email ON user FIELDS email UNIQUE;

-- Composite index
DEFINE INDEX order_lookup ON order FIELDS customer, status;

-- Full-text search index
DEFINE ANALYZER english TOKENIZERS blank FILTERS lowercase, snowball(english);
DEFINE INDEX content_search ON article FIELDS content
    SEARCH ANALYZER english BM25;

-- Verify index usage
EXPLAIN SELECT * FROM user WHERE email = 'test@example.com';

sql

-- Unique index
DEFINE INDEX unique_email ON user FIELDS email UNIQUE;

-- Composite index
DEFINE INDEX order_lookup ON order FIELDS customer, status;

-- Full-text search index
DEFINE ANALYZER english TOKENIZERS blank FILTERS lowercase, snowball(english);
DEFINE INDEX content_search ON article FIELDS content
    SEARCH ANALYZER english BM25;

-- Verify index usage
EXPLAIN SELECT * FROM user WHERE email = 'test@example.com';

Step 6: Write Transactions

步骤6：编写事务

sql

BEGIN TRANSACTION;

-- Transfer funds between accounts
LET $amount = 100;
UPDATE account:alice SET balance -= $amount;
UPDATE account:bob SET balance += $amount;
CREATE transaction SET
    from = account:alice,
    to = account:bob,
    amount = $amount,
    timestamp = time::now();

COMMIT TRANSACTION;

sql

BEGIN TRANSACTION;

-- Transfer funds between accounts
LET $amount = 100;
UPDATE account:alice SET balance -= $amount;
UPDATE account:bob SET balance += $amount;
CREATE transaction SET
    from = account:alice,
    to = account:bob,
    amount = $amount,
    timestamp = time::now();

COMMIT TRANSACTION;

Common Query Patterns

常见查询模式

CRUD Operations

CRUD操作

Create with validation:

sql

CREATE user CONTENT {
    email: 'user@example.com',
    name: 'John Doe',
    roles: ['user'],
    metadata: {
        source: 'signup',
        ip: '192.168.1.1'
    }
};

Select with filtering and pagination:

sql

SELECT * FROM user
WHERE status = 'active'
    AND created_at > time::now() - 30d
ORDER BY created_at DESC
LIMIT 20
START 0;

Update with operators:

sql

-- Increment/decrement
UPDATE user:alice SET login_count += 1;

-- Array manipulation
UPDATE user:alice SET tags += 'premium', tags -= 'trial';

-- Conditional update
UPDATE user SET status = 'inactive'
WHERE last_login < time::now() - 90d;

Upsert pattern:

sql

UPSERT user:email@example.com SET
    email = 'email@example.com',
    last_seen = time::now(),
    visits += 1;

带验证的创建：

sql

CREATE user CONTENT {
    email: 'user@example.com',
    name: 'John Doe',
    roles: ['user'],
    metadata: {
        source: 'signup',
        ip: '192.168.1.1'
    }
};

带过滤和分页的查询：

sql

SELECT * FROM user
WHERE status = 'active'
    AND created_at > time::now() - 30d
ORDER BY created_at DESC
LIMIT 20
START 0;

带操作符的更新：

sql

-- Increment/decrement
UPDATE user:alice SET login_count += 1;

-- Array manipulation
UPDATE user:alice SET tags += 'premium', tags -= 'trial';

-- Conditional update
UPDATE user SET status = 'inactive'
WHERE last_login < time::now() - 90d;

Upsert模式：

sql

UPSERT user:email@example.com SET
    email = 'email@example.com',
    last_seen = time::now(),
    visits += 1;

Graph Queries

图查询

Social network - friends of friends:

sql

SELECT
    id,
    name,
    array::distinct(->follows->user->follows->user) AS suggested_friends
FROM user:alice
WHERE suggested_friends != user:alice;

E-commerce - product recommendations:

sql

-- Find products bought by users who bought this product
SELECT
    <-purchased<-user->purchased->product AS related_products,
    count() AS frequency
FROM product:widget123
GROUP BY related_products
ORDER BY frequency DESC
LIMIT 10;

Knowledge graph - recursive traversal:

sql

-- Find all ancestors up to 5 levels
SELECT
    ->parent->(1..5)->category AS ancestors
FROM category:electronics;

社交网络 - 好友的好友：

sql

SELECT
    id,
    name,
    array::distinct(->follows->user->follows->user) AS suggested_friends
FROM user:alice
WHERE suggested_friends != user:alice;

电商 - 商品推荐：

sql

-- Find products bought by users who bought this product
SELECT
    <-purchased<-user->purchased->product AS related_products,
    count() AS frequency
FROM product:widget123
GROUP BY related_products
ORDER BY frequency DESC
LIMIT 10;

知识图谱 - 递归遍历：

sql

-- Find all ancestors up to 5 levels
SELECT
    ->parent->(1..5)->category AS ancestors
FROM category:electronics;

Analytics Queries

分析查询

Aggregations:

sql

SELECT
    status,
    count() AS total,
    math::mean(age) AS avg_age,
    math::min(created_at) AS first_created
FROM user
GROUP BY status;

Time-series analysis:

sql

SELECT
    time::floor(timestamp, 1h) AS hour,
    count() AS events,
    math::sum(value) AS total_value
FROM metrics
WHERE timestamp > time::now() - 24h
GROUP BY hour
ORDER BY hour;

聚合查询：

sql

SELECT
    status,
    count() AS total,
    math::mean(age) AS avg_age,
    math::min(created_at) AS first_created
FROM user
GROUP BY status;

时间序列分析：

sql

SELECT
    time::floor(timestamp, 1h) AS hour,
    count() AS events,
    math::sum(value) AS total_value
FROM metrics
WHERE timestamp > time::now() - 24h
GROUP BY hour
ORDER BY hour;

Subqueries and Computed Fields

子查询与计算字段

Subquery in SELECT:

sql

SELECT
    *,
    (SELECT count() FROM post WHERE author = $parent.id) AS post_count,
    (SELECT VALUE title FROM post WHERE author = $parent.id LIMIT 5) AS recent_posts
FROM user;

LET for complex queries (CTE alternative):

sql

LET $active_users = (SELECT id FROM user WHERE status = 'active');
LET $recent_orders = (SELECT * FROM order WHERE created_at > time::now() - 7d);

SELECT * FROM $recent_orders
WHERE customer IN $active_users.id;

SELECT中的子查询：

sql

SELECT
    *,
    (SELECT count() FROM post WHERE author = $parent.id) AS post_count,
    (SELECT VALUE title FROM post WHERE author = $parent.id LIMIT 5) AS recent_posts
FROM user;

使用LET编写复杂查询（CTE替代方案）：

sql

LET $active_users = (SELECT id FROM user WHERE status = 'active');
LET $recent_orders = (SELECT * FROM order WHERE created_at > time::now() - 7d);

SELECT * FROM $recent_orders
WHERE customer IN $active_users.id;

Schema Design Patterns

模式设计模式

User Profile with Nested Objects

带嵌套对象的用户资料

sql

DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD email ON user TYPE string ASSERT string::is::email($value);
DEFINE FIELD password ON user TYPE string;
DEFINE FIELD profile ON user TYPE object;
DEFINE FIELD profile.name ON user TYPE string;
DEFINE FIELD profile.avatar ON user TYPE option<string>;
DEFINE FIELD profile.bio ON user TYPE option<string>;
DEFINE FIELD settings ON user TYPE object DEFAULT {};
DEFINE FIELD settings.notifications ON user TYPE bool DEFAULT true;
DEFINE FIELD settings.theme ON user TYPE string DEFAULT 'light';
DEFINE FIELD created_at ON user TYPE datetime DEFAULT time::now();
DEFINE FIELD updated_at ON user TYPE datetime VALUE time::now();

DEFINE INDEX unique_email ON user FIELDS email UNIQUE;

sql

DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD email ON user TYPE string ASSERT string::is::email($value);
DEFINE FIELD password ON user TYPE string;
DEFINE FIELD profile ON user TYPE object;
DEFINE FIELD profile.name ON user TYPE string;
DEFINE FIELD profile.avatar ON user TYPE option<string>;
DEFINE FIELD profile.bio ON user TYPE option<string>;
DEFINE FIELD settings ON user TYPE object DEFAULT {};
DEFINE FIELD settings.notifications ON user TYPE bool DEFAULT true;
DEFINE FIELD settings.theme ON user TYPE string DEFAULT 'light';
DEFINE FIELD created_at ON user TYPE datetime DEFAULT time::now();
DEFINE FIELD updated_at ON user TYPE datetime VALUE time::now();

DEFINE INDEX unique_email ON user FIELDS email UNIQUE;

E-commerce Schema

电商模式

sql

-- Products
DEFINE TABLE product SCHEMAFULL;
DEFINE FIELD name ON product TYPE string;
DEFINE FIELD description ON product TYPE string;
DEFINE FIELD price ON product TYPE decimal;
DEFINE FIELD inventory ON product TYPE int DEFAULT 0;
DEFINE FIELD categories ON product TYPE array<record<category>>;
DEFINE FIELD active ON product TYPE bool DEFAULT true;

DEFINE INDEX product_search ON product FIELDS name, description
    SEARCH ANALYZER blank BM25;

-- Orders
DEFINE TABLE order SCHEMAFULL;
DEFINE FIELD customer ON order TYPE record<user>;
DEFINE FIELD items ON order TYPE array;
DEFINE FIELD items.*.product ON order TYPE record<product>;
DEFINE FIELD items.*.quantity ON order TYPE int;
DEFINE FIELD items.*.price ON order TYPE decimal;
DEFINE FIELD status ON order TYPE string DEFAULT 'pending'
    ASSERT $value IN ['pending', 'processing', 'shipped', 'delivered', 'cancelled'];
DEFINE FIELD total ON order TYPE decimal;
DEFINE FIELD created_at ON order TYPE datetime DEFAULT time::now();

DEFINE INDEX order_customer ON order FIELDS customer;
DEFINE INDEX order_status ON order FIELDS status, created_at;

sql

-- Products
DEFINE TABLE product SCHEMAFULL;
DEFINE FIELD name ON product TYPE string;
DEFINE FIELD description ON product TYPE string;
DEFINE FIELD price ON product TYPE decimal;
DEFINE FIELD inventory ON product TYPE int DEFAULT 0;
DEFINE FIELD categories ON product TYPE array<record<category>>;
DEFINE FIELD active ON product TYPE bool DEFAULT true;

DEFINE INDEX product_search ON product FIELDS name, description
    SEARCH ANALYZER blank BM25;

-- Orders
DEFINE TABLE order SCHEMAFULL;
DEFINE FIELD customer ON order TYPE record<user>;
DEFINE FIELD items ON order TYPE array;
DEFINE FIELD items.*.product ON order TYPE record<product>;
DEFINE FIELD items.*.quantity ON order TYPE int;
DEFINE FIELD items.*.price ON order TYPE decimal;
DEFINE FIELD status ON order TYPE string DEFAULT 'pending'
    ASSERT $value IN ['pending', 'processing', 'shipped', 'delivered', 'cancelled'];
DEFINE FIELD total ON order TYPE decimal;
DEFINE FIELD created_at ON order TYPE datetime DEFAULT time::now();

DEFINE INDEX order_customer ON order FIELDS customer;
DEFINE INDEX order_status ON order FIELDS status, created_at;

Graph Relationship Schema

图关系模式

sql

-- Define edge table
DEFINE TABLE follows SCHEMAFULL TYPE RELATION;
DEFINE FIELD in ON follows TYPE record<user>;
DEFINE FIELD out ON follows TYPE record<user>;
DEFINE FIELD since ON follows TYPE datetime DEFAULT time::now();
DEFINE FIELD notifications ON follows TYPE bool DEFAULT true;

-- Prevent duplicate follows
DEFINE INDEX unique_follow ON follows FIELDS in, out UNIQUE;

sql

-- Define edge table
DEFINE TABLE follows SCHEMAFULL TYPE RELATION;
DEFINE FIELD in ON follows TYPE record<user>;
DEFINE FIELD out ON follows TYPE record<user>;
DEFINE FIELD since ON follows TYPE datetime DEFAULT time::now();
DEFINE FIELD notifications ON follows TYPE bool DEFAULT true;

-- Prevent duplicate follows
DEFINE INDEX unique_follow ON follows FIELDS in, out UNIQUE;

Custom Functions

自定义函数

sql

-- Calculate user engagement score
DEFINE FUNCTION fn::engagement_score($user_id: record<user>) {
    LET $posts = (SELECT count() FROM post WHERE author = $user_id);
    LET $comments = (SELECT count() FROM comment WHERE author = $user_id);
    LET $likes_received = (SELECT count() FROM like WHERE post.author = $user_id);

    RETURN ($posts * 5) + ($comments * 2) + $likes_received;
};

-- Usage
SELECT *, fn::engagement_score(id) AS score FROM user;

-- Validate and normalize email
DEFINE FUNCTION fn::normalize_email($email: string) {
    IF !string::is::email($email) {
        THROW "Invalid email format";
    };
    RETURN string::lowercase(string::trim($email));
};

-- Pagination helper
DEFINE FUNCTION fn::paginate($table: string, $page: int, $per_page: int) {
    LET $offset = ($page - 1) * $per_page;
    RETURN (SELECT * FROM type::table($table) LIMIT $per_page START $offset);
};

sql

-- Calculate user engagement score
DEFINE FUNCTION fn::engagement_score($user_id: record<user>) {
    LET $posts = (SELECT count() FROM post WHERE author = $user_id);
    LET $comments = (SELECT count() FROM comment WHERE author = $user_id);
    LET $likes_received = (SELECT count() FROM like WHERE post.author = $user_id);

    RETURN ($posts * 5) + ($comments * 2) + $likes_received;
};

-- Usage
SELECT *, fn::engagement_score(id) AS score FROM user;

-- Validate and normalize email
DEFINE FUNCTION fn::normalize_email($email: string) {
    IF !string::is::email($email) {
        THROW "Invalid email format";
    };
    RETURN string::lowercase(string::trim($email));
};

-- Pagination helper
DEFINE FUNCTION fn::paginate($table: string, $page: int, $per_page: int) {
    LET $offset = ($page - 1) * $per_page;
    RETURN (SELECT * FROM type::table($table) LIMIT $per_page START $offset);
};

Real-Time (LIVE Queries)

实时查询（LIVE Queries）

sql

-- Subscribe to changes on a table
LIVE SELECT * FROM post WHERE published = true;

-- Subscribe to specific record changes
LIVE SELECT * FROM user:alice;

-- Subscribe with graph traversal
LIVE SELECT *, ->comments->comment AS comments FROM post;

-- Kill a live query
KILL $live_query_id;

sql

-- Subscribe to changes on a table
LIVE SELECT * FROM post WHERE published = true;

-- Subscribe to specific record changes
LIVE SELECT * FROM user:alice;

-- Subscribe with graph traversal
LIVE SELECT *, ->comments->comment AS comments FROM post;

-- Kill a live query
KILL $live_query_id;

Performance Best Practices

性能最佳实践

1. Use Specific Record IDs Instead of Scans

1. 使用特定记录ID而非全表扫描

sql

-- FAST: Direct ID access
SELECT * FROM user:alice;

-- SLOW: Table scan
SELECT * FROM user WHERE id = 'alice';

sql

-- FAST: Direct ID access
SELECT * FROM user:alice;

-- SLOW: Table scan
SELECT * FROM user WHERE id = 'alice';

2. Select Only Needed Fields

2. 仅选择所需字段

sql

-- BETTER: Specific fields
SELECT name, email FROM user;

-- AVOID: All fields when not needed
SELECT * FROM user;

sql

-- BETTER: Specific fields
SELECT name, email FROM user;

-- AVOID: All fields when not needed
SELECT * FROM user;

3. Use Indexes Effectively

3. 有效使用索引

sql

-- Create index for common queries
DEFINE INDEX active_users ON user FIELDS status, created_at;

-- Query uses index
SELECT * FROM user
WHERE status = 'active'
ORDER BY created_at DESC;

sql

-- Create index for common queries
DEFINE INDEX active_users ON user FIELDS status, created_at;

-- Query uses index
SELECT * FROM user
WHERE status = 'active'
ORDER BY created_at DESC;

4. Batch Operations

4. 批量操作

sql

-- BETTER: Single batch insert
INSERT INTO log [
    { level: 'info', message: 'Start' },
    { level: 'info', message: 'Processing' },
    { level: 'info', message: 'Complete' }
];

-- AVOID: Multiple separate inserts

sql

-- BETTER: Single batch insert
INSERT INTO log [
    { level: 'info', message: 'Start' },
    { level: 'info', message: 'Processing' },
    { level: 'info', message: 'Complete' }
];

-- AVOID: Multiple separate inserts

5. Use Transactions for Related Operations

5. 对相关操作使用事务

sql

BEGIN TRANSACTION;
-- Multiple related operations
COMMIT TRANSACTION;

sql

BEGIN TRANSACTION;
-- Multiple related operations
COMMIT TRANSACTION;

Common Anti-patterns to Avoid

需避免的常见反模式

1. Missing Field Existence Checks

1. 缺失字段存在性检查

sql

-- WRONG: Field might not exist
SELECT * FROM user WHERE profile.name = 'John';

-- CORRECT: Check existence first (in application logic)
-- Or use SCHEMAFULL tables with proper defaults

sql

-- WRONG: Field might not exist
SELECT * FROM user WHERE profile.name = 'John';

-- CORRECT: Check existence first (in application logic)
-- Or use SCHEMAFULL tables with proper defaults

2. N+1 Query Problem

2. N+1查询问题

sql

-- WRONG: Fetching related data in loops
-- (in application: for each user, fetch their posts)

-- CORRECT: Use record links and fetch in one query
SELECT *, posts.* FROM user FETCH posts;

sql

-- WRONG: Fetching related data in loops
-- (in application: for each user, fetch their posts)

-- CORRECT: Use record links and fetch in one query
SELECT *, posts.* FROM user FETCH posts;

3. Not Using Appropriate Relationship Model

3. 未使用合适的关系模型

sql

-- WRONG: String IDs for relationships
CREATE user SET friend_ids = ['alice', 'bob'];

-- CORRECT: Record links
CREATE user SET friends = [user:alice, user:bob];

sql

-- WRONG: String IDs for relationships
CREATE user SET friend_ids = ['alice', 'bob'];

-- CORRECT: Record links
CREATE user SET friends = [user:alice, user:bob];

4. Over-fetching with Graph Traversals

4. 图遍历过度获取数据

sql

-- WRONG: Unbounded traversal
SELECT ->*->* FROM user:alice;

-- CORRECT: Bounded traversal
SELECT ->(1..3)->follows->user FROM user:alice;

sql

-- WRONG: Unbounded traversal
SELECT ->*->* FROM user:alice;

-- CORRECT: Bounded traversal
SELECT ->(1..3)->follows->user FROM user:alice;

Debugging and Testing

调试与测试

Explain Query Execution

解释查询执行计划

sql

EXPLAIN SELECT * FROM user WHERE email = 'test@example.com';
EXPLAIN FULL SELECT * FROM user WHERE email = 'test@example.com';

sql

EXPLAIN SELECT * FROM user WHERE email = 'test@example.com';
EXPLAIN FULL SELECT * FROM user WHERE email = 'test@example.com';

Check Table Info

查看表信息

sql

INFO FOR TABLE user;
INFO FOR DB;
INFO FOR NS;

sql

INFO FOR TABLE user;
INFO FOR DB;
INFO FOR NS;

Test Queries with Parameters

使用参数测试查询

sql

LET $email = 'test@example.com';
SELECT * FROM user WHERE email = $email;

sql

LET $email = 'test@example.com';
SELECT * FROM user WHERE email = $email;

Output Format

输出格式

When generating SurrealDB queries, always provide:

Complete SurrealQL statements with proper syntax
Schema definitions when creating tables/fields
Index recommendations for query patterns
Example data for testing
Explanation of design decisions
Performance considerations if relevant

生成SurrealDB查询时，需始终提供：

完整的SurrealQL语句，语法正确
模式定义（当创建表/字段时）
索引建议（针对查询模式）
测试用示例数据
设计决策的说明
相关性能注意事项

Reference Files

参考文档

SurrealQL Syntax Reference - Complete language reference
Schema Patterns - Common schema designs
Graph Relationships - Relationship modeling patterns
Security & Auth - Authentication and permissions

SurrealQL语法参考 - 完整语言参考
模式设计模式 - 常见模式设计
图关系 - 关系建模模式
安全与认证 - 身份认证与权限

Version Compatibility

版本兼容性

SurrealDB 2.x: Latest features, GraphQL support, improved performance
SurrealDB 1.x: Legacy version, use
```
scope
```
instead of
```
access
```

Always verify target SurrealDB version before generating queries.

SurrealDB 2.x：最新功能、GraphQL支持、性能提升
SurrealDB 1.x：旧版本，使用
```
scope
```
替代
```
access
```

生成查询前，请务必确认目标SurrealDB版本。