surrealdb

What this skill does

# SurrealDB - Production-Ready Query Generator

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

## 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 Quick Reference

### 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 protected] SET email = '[email protected]', 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
```

### 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
```

## Instructions for Writing SurrealDB Queries

### Step 1: Understand the Data Model

Before writing any SurrealQL:

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

### Step 2: Choose Schema Strategy

**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;
```

### Step 3: Design Relationships

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: '[email protected]' },
    items = [
        { product: 'Widget', quantity: 2, price: 29.99 },
        { product: 'Gadget', quantity: 1, price: 49.99 }
    ],
    total = 109.97;
```

### Step 4: Implement Authentication

**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;
```

### Step 5: Optimize with Indexes

```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 = '[email protected]';
```

### Step 6: Write Transactions

```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

**Create with validation:**
```sql
CREATE user CONTENT {
    email: '[email protected]',
    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 protected] SET
    email = '[email protected]',
    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