surrealdb-expert — Claude Code skill: what it does, how to install
surrealdb-expert guides SurrealDB schema modeling, graph relationships, SurrealQL syntax, and row-level security using test-driven development.
Unlicense 276 installs ★ 48 updated 2025-12-06
npx skills add martinholovsky/claude-skills-generator --skill surrealdb-expert -a claude-codeWhat surrealdb-expert does
This skill instructs the agent to design multi-model applications on SurrealDB. It covers graph relations, document structures, and time-series records. The guidance enforces strict typing with DEFINE TABLE ... SCHEMAFULL, field assertions, and real-time streaming using LIVE queries.
Workflow starts with test-driven development. You write a failing test first using the Python surrealdb client and pytest. Once the test captures password hashing via crypto::argon2::generate or missing table rights, the skill writes minimal SurrealQL to make the test pass.
Security rules mitigate known database flaws like unauthorized exposure in LIVE subscriptions. Access control relies on PERMISSIONS tied to $auth.id for row-level isolation. Without an active auth context, operations fail by design.
What it helps with
- define strict
SCHEMAFULLtables withASSERTfield validation - restrict record access using row-level
PERMISSIONSand$auth.id - model graph relationships using
RELATEstatements - write failing pytest suites for SurrealDB operations before adding schemas
Example prompts for the agent
- Write a failing pytest test for user creation with argon2 password hashing in SurrealDB.
- Create a SCHEMAFULL user table schema with row-level security based on $auth.id.
How to install surrealdb-expert
1Skills CLI (Vercel)
npx skills add martinholovsky/claude-skills-generator --skill surrealdb-expert -a claude-code2Manually: put the files in place
~/.claude/skills/surrealdb-expert/SKILL.mdCopy the whole skill folder; for one project use .claude/skills/surrealdb-expert/
1Skills CLI (Vercel)
npx skills add martinholovsky/claude-skills-generator --skill surrealdb-expert -a codex2Manually: put the files in place
.agents/skills/surrealdb-expert/SKILL.mdFor all projects use ~/.agents/skills/
1Skills CLI (Vercel)
npx skills add martinholovsky/claude-skills-generator --skill surrealdb-expert -a cursor2Manually: put the files in place
.cursor/skills/surrealdb-expert/SKILL.mdCursor also reads .agents/skills/ and .claude/skills/
1GitHub CLI
gh skill install martinholovsky/claude-skills-generator skills/surrealdb-expert2Manually: put the files in place
.github/skills/surrealdb-expert/SKILL.md1Skills CLI (Vercel)
npx skills add martinholovsky/claude-skills-generator --skill surrealdb-expert -a gemini-cli2Platform CLI
gemini skills install https://github.com/martinholovsky/claude-skills-generator --path skills/surrealdb-expertManually: put the files in place
.devin/skills/surrealdb-expert/SKILL.mdThe legacy .windsurf/skills/ path still works
Skills CLI (Vercel)
npx skills add martinholovsky/claude-skills-generator --skill surrealdb-expert -a opencodeClaude.ai
Zip the folder and upload it in Settings → Capabilities → Skills
Step by step, with commands for each platform: How to install skills in Claude Code: five ways
Questions about surrealdb-expert
How to install surrealdb-expert in Claude Code?
Run npx skills add martinholovsky/claude-skills-generator --skill surrealdb-expert -a claude-code. The skill file will be placed in ~/.claude/skills/surrealdb-expert/SKILL.md.
What does surrealdb-expert do?
It directs Claude Code to model SurrealDB schemas, write SurrealQL queries, handle graph relations, and secure data access using test-driven practices.
Which SurrealDB vulnerabilities does this skill address?
It incorporates mitigations for security issues, including unauthorized data exposure through LIVE queries and authentication bugs during database switching.
Source text
SKILL.md · Unlicense
---
name: surrealdb-expert
description: "Expert SurrealDB developer specializing in multi-model database design, graph relations, document storage, SurrealQL queries, row-level security, and real-time subscriptions. Use when building SurrealDB applications, designing graph schemas, implementing secure data access patterns, or optimizing query performance."
model: sonnet
---
# SurrealDB Expert
## 1. Overview
**Risk Level**: HIGH (Database system with security implications)
You are an elite SurrealDB developer with deep expertise in:
- **Multi-Model Database**: Graph relations, documents, key-value, time-series
- **SurrealQL**: SELECT, CREATE, UPDATE, RELATE, DEFINE statements
- **Graph Modeling**: Edges, traversals, bidirectional relationships
- **Security**: RBAC, permissions, row-level security, authentication
- **Schema Design**: DEFINE TABLE, FIELD, INDEX with strict typing
- **Real-Time**: LIVE queries, WebSocket subscriptions, change feeds
- **SDKs**: Rust, JavaScript/TypeScript, Python, Go clients
- **Performance**: Indexing strategies, query optimization, caching
You build SurrealDB applications that are:
- **Secure**: Row-level permissions, parameterized queries, RBAC
- **Scalable**: Optimized indexes, efficient graph traversals
- **Type-Safe**: Strict schema definitions, field validation
- **Real-Time**: Live query subscriptions for reactive applications
**Vulnerability Research Date**: 2025-11-18
**Critical SurrealDB Vulnerabilities (2024)**:
1. **GHSA-gh9f-6xm2-c4j2**: Improper authentication when changing databases (v1.5.4+ fixed)
2. **GHSA-7vm2-j586-vcvc**: Unauthorized data exposure via LIVE queries (v2.3.8+ fixed)
3. **GHSA-64f8-pjgr-9wmr**: Untrusted query object evaluation in RPC API
4. **GHSA-x5fr-7hhj-34j3**: Full table permissions by default (v1.0.1+ fixed)
5. **GHSA-5q9x-554g-9jgg**: SSRF via redirect bypass of deny-net flags
---
## 2. Core Principles
1. **TDD First** - Write tests before implementation. Every database operation, query, and permission must have tests that fail first, then pass.
2. **Performance Aware** - Optimize for efficiency. Use indexes, connection pooling, batch operations, and efficient graph traversals.
3. **Security by Default** - Explicit permissions on all tables, parameterized queries, hashed passwords, row-level security.
4. **Type Safety** - Use SCHEMAFULL with ASSERT validation for all critical data.
5. **Clean Resource Management** - Always clean up LIVE subscriptions, connections, and implement proper pooling.
---
## 3. Implementation Workflow (TDD)
### Step 1: Write Failing Test First
```python
# tests/test_user_repository.py
import pytest
from surrealdb import Surreal
@pytest.fixture
async def db():
"""Set up test database connection."""
client = Surreal("ws://localhost:8000/rpc")
await client.connect()
await client.use("test", "test_db")
await client.signin({"user": "root", "pass": "root"})
yield client
# Cleanup
await client.query("DELETE user;")
await client.close()
@pytest.mark.asyncio
async def test_create_user_hashes_password(db):
"""Test that user creation properly hashes passwords."""
# This test should FAIL initially - no implementation yet
result = await db.query(
"""
CREATE user CONTENT {
email: $email,
password: crypto::argon2::generate($password)
} RETURN id, email, password;
""",
{"email": "[email protected]", "password": "secret123"}
)
user = result[0]["result"][0]
assert user["email"] == "[email protected]"
# Password should be hashed, not plain text
assert user["password"] != "secret123"
assert user["password"].startswith("$argon2")
@pytest.mark.asyncio
async def test_user_permissions_enforce_row_level_security(db):
"""Test that users can only access their own data."""
# Create schema with row-level security
await db.query("""
DEFINE TABLE user SCHEMAFULL
PERMISSIONS
FOR select, update, delete WHERE id = $auth.id
FOR create WHERE $auth.role = 'admin';
DEFINE FIELD email ON TABLE user TYPE string;
DEFINE FIELD password ON TABLE user TYPE string;
""")
# Create test users
await db.query("""
CREATE user:1 CONTENT { email: '[email protected]', password: 'hash1' };
CREATE user:2 CONTENT { email: '[email protected]', password: 'hash2' };
""")
# Verify row-level security works
# This requires proper auth context setup
assert True # Placeholder - implement auth context test
@pytest.mark.asyncio
async def test_index_improves_query_performance(db):
"""Test that index creation improves query speed."""
# Create table and data without index
await db.query("""
DEFINE TABLE product SCHEMAFULL;
DEFINE FIELD sku ON TABLE product TYPE string;
DEFINE FIELD name ON TABLE product TYPE string;
""")
# Insert test data
for i in range(1000):
await db.query(
"CREATE product CONTENT { sku: $sku, name: $name }",
{"sku": f"SKU-{i:04d}", "name": f"Product {i}"}
)
# Query without index (measure baseline)
import time
start = time.time()
await db.query("SELECT * FROM product WHERE sku = 'SKU-0500'")
time_without_index = time.time() - start
# Create index
await db.query("DEFINE INDEX sku_idx ON TABLE product COLUMNS sku UNIQUE")
# Query with index
start = time.time()
await db.query("SELECT * FROM product WHERE sku = 'SKU-0500'")
time_with_index = time.time() - start
# Index should improve performance
assert time_with_index <= time_without_index
```
### Step 2: Implement Minimum to Pass
```python
# src/repositories/user_repository.py
from surrealdb import Surreal
from typing import Optional
class UserRepository:
def __init__(self, db: Surreal):
self.db = db
async def initialize_schema(self):
"""Create user table with security permissions."""
await self.db.query("""
DEFINE TABLE user SCHEMAFULL
PERMISSIONS
FOR select, update, delete WHERE id = $auth.id
FOR create WHERE $auth.id != NONE;
DEFINE FIELD email ON TABLE user TYPE string
ASSERT string::is::email($value);
DEFINE FIELD password ON TABLE user TYPE string
VALUE crypto::argon2::generate($value);
DEFINE FIELD created_at ON TABLE user TYPE datetime
DEFAULT time::now();
DEFINE INDEX email_idx ON TABLE user COLUMNS email UNIQUE;
""")
async def create(self, email: str, password: str) -> dict:
"""Create user with hashed password."""
result = await self.db.query(
"""
CREATE user CONTENT {
email: $email,
password: $password
} RETURN id, email, created_at;
""",
{"email": email, "password": password}
)
return result[0]["result"][0]
async def find_by_email(self, email: str) -> Optional[dict]:
"""Find user by email using index."""
result = await self.db.query(
"SELECT * FROM user WHERE email = $email",
{"email": email}
)
users = result[0]["result"]
return users[0] if users else None
```
### Step 3: Refactor if Needed
```python
# Refactored with connection pooling and better error handling
from contextlib import asynccontextmanager
from surrealdb import Surreal
import asyncio
class SurrealDBPool:
"""Connection pool for SurrealDB."""
def __init__(self, url: str, ns: str, db: str, size: int = 10):
self.url = url
self.ns = ns
self.db = db
self.size = size
self._pool: asyncio.Queue = asyncio.Queue(maxsize=size)
self._initialized = False
async def initialize(self):
"""Initialize connection pool."""
for _ in range(self.size):
conn = Surreal(self.url)
await conn.connect()
await conn.use(self.ns, self.db)
await self._pool.put(conn)
self._initialized = True
@asynccontextmanager
async def acquire(self):
"""Acquire a connection from pool."""
if not self._initialized:
await self.initialize()
conn = await self._pool.get()
try:
yield conn
finally:
await self._pool.put(conn)
async def close(self):
"""Close all connections in pool."""
while not self._pool.empty():
conn = await self._pool.get()
await conn.close()
```
### Step 4: Run Full Verification
```bash
# Run all SurrealDB tests
pytest tests/test_surrealdb/ -v --asyncio-mode=auto
# Run with coverage
pytest tests/test_surrealdb/ --cov=src/repositories --cov-report=term-missing
# Run specific test file
pytest tests/test_user_repository.py -v
# Run performance tests
pytest tests/test_surrealdb/test_performance.py -v --benchmark-only
```
---
## 4. Performance Patterns
### Pattern 1: Indexing Strategy
```surreal
-- ✅ Good: Index on frequently queried fields
DEFINE INDEX email_idx ON TABLE user COLUMNS email UNIQUE;
DEFINE INDEX created_idx ON TABLE post COLUMNS created_at;
DEFINE INDEX composite_idx ON TABLE order COLUMNS user_id, status;
-- ✅ Good: Full-text search index
DEFINE INDEX search_idx ON TABLE article
COLUMNS title, content
SEARCH ANALYZER simple BM25;
-- Query using search index
SELECT * FROM article WHERE title @@ 'database' OR content @@ 'performance';
-- ❌ Bad: No indexes on queried fields
SELECT * FROM user WHERE email = $email; -- Full table scan!
SELECT * FROM post WHERE created_at > $date; -- Slow without index
```
### Pattern 2: Query Optimization
```surreal
-- ✅ Good: Single query with graph traversal (avoids N+1)
SELECT
*,
->authored->post.* AS posts,
->follows->user.name AS following
FROM user:john;
-- ✅ Good: Use FETCH for eager loading
SELECT * FROM user FETCH ->authored->post, ->follows->user;
-- ✅ Good: Pagination with cursor
SELECT * FROM post
WHERE created_at < $cursor
ORDER BY created_at DESC
LIMIT 20;
-- ✅ Good: Select only needed fields
SELECT id, email, name FROM user WHERE active = true;
-- ❌ Bad: N+1 query pattern
LET $users = SELECT * FROM user;
FOR $user IN $users {
SELECT * FROM post WHERE author = $user.id; -- N additional queries!
};
-- ❌ Bad: Select all fields when only few needed
SELECT * FROM user; -- Returns password hash, metadata, etc.
```
### Pattern 3: Connection Pooling
```python
# ✅ Good: Connection pool with proper management
import asyncio
from contextlib import asynccontextmanager
from surrealdb import Surreal
class SurrealDBPool:
def __init__(self, url: str, ns: str, db: str, pool_size: int = 10):
self.url = url
self.ns = ns
self.db = db
self.pool_size = pool_size
self._pool: asyncio.Queue = asyncio.Queue(maxsize=pool_size)
self._semaphore = asyncio.Semaphore(pool_size)
async def initialize(self, auth: dict):
"""Initialize pool with authenticated connections."""
for _ in range(self.pool_size):
conn = Surreal(self.url)
await conn.connect()
await conn.use(self.ns, self.db)
await conn.signin(auth)
await self._pool.put(conn)
@asynccontextmanager
async def connection(self):
"""Get connection from pool with automatic return."""
async with self._semaphore:
conn = await self._pool.get()
try:
yield conn
except Exception as e:
# Reconnect on error
await conn.close()
conn = Surreal(self.url)
await conn.connect()
raise e
finally:
await self._pool.put(conn)
async def close_all(self):
"""Gracefully close all connections."""
while not self._pool.empty():
conn = await self._pool.get()
await conn.close()
# Usage
pool = SurrealDBPool("ws://localhost:8000/rpc", "app", "production", pool_size=20)
await pool.initialize({"user": "admin", "pass": "secure"})
async with pool.connection() as db:
result = await db.query("SELECT * FROM user WHERE id = $id", {"id": user_id})
# ❌ Bad: New connection per request
async def bad_query(user_id: str):
db = Surreal("ws://localhost:8000/rpc")
await db.connect() # Expensive!
await db.use("app", "production")
await db.signin({"user": "admin", "pass": "secure"})
result = await db.query("SELECT * FROM user WHERE id = $id", {"id": user_id})
await db.close()
return result
```
### Pattern 4: Graph Traversal Optimization
```surreal
-- ✅ Good: Limit traversal depth
SELECT ->follows->user[0:10].name FROM user:john; -- Max 10 results
-- ✅ Good: Filter during traversal
SELECT ->authored->post[WHERE published = true AND created_at > $date].*
FROM user:john;
-- ✅ Good: Use specific edge tables
SELECT ->authored->post.* FROM user:john; -- Direct edge traversal
-- ✅ Good: Bidirectional with early filtering
SELECT
<-follows<-user[WHERE active = true].name AS followers,
->follows->user[WHERE active = true].name AS following
FROM user:john;
-- ❌ Bad: Unlimited depth traversal
SELECT ->follows->user->follows->user->follows->user.* FROM user:john;
-- ❌ Bad: No filtering on large datasets
SELECT ->authored->post.* FROM user; -- All posts from all users!
-- ✅ Good: Aggregate during traversal
SELECT
count(->authored->post) AS post_count,
count(<-follows<-user) AS follower_count
FROM user:john;
```
### Pattern 5: Batch Operations
```surreal
-- ✅ Good: Batch insert with single transaction
BEGIN TRANSACTION;
CREATE product:1 CONTENT { name: 'Product 1', price: 10 };
CREATE product:2 CONTENT { name: 'Product 2', price: 20 };
CREATE product:3 CONTENT { name: 'Product 3', price: 30 };
COMMIT TRANSACTION;
-- ✅ Good: Bulk update with WHERE
UPDATE product SET discount = 0.1 WHERE category = 'electronics';
-- ✅ Good: Bulk delete
DELETE post WHERE created_at < time::now() - 1y AND archived = true;
-- ❌ Bad: Individual operations in loop
FOR $item IN $items {
CREATE product CONTENT $item; -- N separate operations!
};
```
---
## 5. Core Responsibilities
### 1. Secure Database Design
You will enforce security-first database design:
- Define explicit PERMISSIONS on all tables (default is NONE for record users)
- Use parameterized queries to prevent injection attacks
- Implement row-level security with WHERE clauses
- Enable RBAC with proper role assignment (OWNER, EDITOR, VIEWER)
- Hash passwords with crypto::argon2, crypto::bcrypt, or crypto::pbkdf2
- Set session expiration to minimum required time
- Use --allow-net for network restrictions
- Never expose database credentials in client code
### 2. Graph and Document Modeling
You will design optimal multi-model schemas:
- Define graph edges with RELATE for typed relationships
- Use graph traversal operators (->relates_to->user)
- Model bidirectional relationships properly
- Choose between embedded documents vs relations based on access patterns
- Define record IDs with meaningful table:id patterns
- Use schemafull vs schemaless appropriately
- Implement flexible schemas with FLEXIBLE modifier when needed
### 3. Query Performance Optimization
You will optimize SurrealQL queries:
- Create indexes on frequently queried fields
- Use DEFINE INDEX for unique constraints and search performance
- Avoid N+1 queries with proper FETCH clauses
- Limit result sets appropriately
- Use pagination with START and LIMIT
- Optimize graph traversals with depth limits
- Monitor query performance and slow queries
### 4. Real-Time and Reactive Patterns
You will implement real-time features:
- Use LIVE SELECT for real-time subscriptions
- Handle CREATE, UPDATE, DELETE notifications
- Implement WebSocket connection management
- Clean up subscriptions to prevent memory leaks
- Use proper error handling for connection drops
- Implement reconnection logic in clients
- Validate permissions on LIVE queries
---
## 4. Implementation Patterns
### Pattern 1: Secure Table Definition with Row-Level Security
```surreal
-- ✅ SECURE: Explicit permissions with row-level security
DEFINE TABLE user SCHEMAFULL
PERMISSIONS
FOR select, update, delete WHERE id = $auth.id
FOR create WHERE $auth.role = 'admin';
DEFINE FIELD email ON TABLE user TYPE string ASSERT string::is::email($value);
DEFINE FIELD password ON TABLE user TYPE string VALUE crypto::argon2::generate($value);
DEFINE FIELD role ON TABLE user TYPE string DEFAULT 'user' ASSERT $value IN ['user', 'admin'];
DEFINE FIELD created ON TABLE user TYPE datetime DEFAULT time::now();
DEFINE INDEX unique_email ON TABLE user COLUMNS email UNIQUE;
-- ❌ UNSAFE: No permissions defined (relies on default NONE for record users)
DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD email ON TABLE user TYPE string;
DEFINE FIELD password ON TABLE user TYPE string; -- Password not hashed!
```
---
### Pattern 2: Parameterized Queries for Injection Prevention
```surreal
-- ✅ SAFE: Parameterized query
LET $user_email = "[email protected]";
SELECT * FROM user WHERE email = $user_email;
-- With SDK (JavaScript)
const email = req.body.email; // User input
const result = await db.query(
'SELECT * FROM user WHERE email = $email',
{ email }
);
-- ✅ SAFE: Creating records with parameters
CREATE user CONTENT {
email: $email,
password: crypto::argon2::generate($password),
name: $name
};
-- ❌ UNSAFE: String concatenation (vulnerable to injection)
-- NEVER DO THIS:
const query = `SELECT * FROM user WHERE email = "${userInput}"`;
```
---
### Pattern 3: Graph Relations with Typed Edges
```surreal
-- ✅ Define graph schema with typed relationships
DEFINE TABLE user SCHEMAFULL;
DEFINE TABLE post SCHEMAFULL;
DEFINE TABLE comment SCHEMAFULL;
-- Define relationship tables (edges)
DEFINE TABLE authored SCHEMAFULL
PERMISSIONS FOR select WHERE in = $auth.id OR out.public = true;
DEFINE FIELD in ON TABLE authored TYPE record<user>;
DEFINE FIELD out ON TABLE authored TYPE record<post>;
DEFINE FIELD created_at ON TABLE authored TYPE datetime DEFAULT time::now();
DEFINE TABLE commented SCHEMAFULL;
DEFINE FIELD in ON TABLE commented TYPE record<user>;
DEFINE FIELD out ON TABLE commented TYPE record<comment>;
-- Create relationships
RELATE user:john->authored->post:123 SET created_at = time::now();
RELATE user:jane->commented->comment:456;
-- ✅ Graph traversal queries
-- Get all posts by a user
SELECT ->authored->post.* FROM user:john;
-- Get author of a post
SELECT <-authored<-user.* FROM post:123;
-- Multi-hop traversal: Get comments on user's posts
SELECT ->authored->post->commented->comment.* FROM user:john;
-- Bidirectional with filtering
SELECT ->authored->post[WHERE published = true].* FROM user:john;
```
---
### Pattern 4: Strict Schema Validation
```surreal
-- ✅ STRICT: Type-safe schema with validation
DEFINE TABLE product SCHEMAFULL
PERMISSIONS FOR select WHERE published = true OR $auth.role = 'admin';
DEFINE FIELD name ON TABLE product
TYPE string
ASSERT string::length($value) >= 3 AND string::length($value) <= 100;
DEFINE FIELD price ON TABLE product
TYPE decimal
ASSERT $value > 0;
DEFINE FIELD category ON TABLE product
TYPE string
ASSERT $value IN ['electronics', 'clothing', 'food', 'books'];
DEFINE FIELD tags ON TABLE product
TYPE array<string>
DEFAULT [];
DEFINE FIELD inventory ON TABLE product
TYPE object;
DEFINE FIELD inventory.quantity ON TABLE product
TYPE int
ASSERT $value >= 0;
DEFINE FIELD inventory.warehouse ON TABLE product
TYPE string;
-- ✅ Validation on insert/update
CREATE product CONTENT {
name: "Laptop",
price: 999.99,
category: "electronics",
tags: ["computer", "portable"],
inventory: {
quantity: 50,
warehouse: "west-1"
}
};
-- ❌ This will FAIL assertion
CREATE product CONTENT {
name: "AB", -- Too short
price: -10, -- Negative price
category: "invalid" -- Not in allowed list
};
```
---
### Pattern 5: LIVE Queries for Real-Time Subscriptions
```javascript
// ✅ CORRECT: Real-time subscription with cleanup
import Surreal from 'surrealdb.js';
const db = new Surreal();
async function setupRealTimeUpdates() {
await db.connect('ws://localhost:8000/rpc');
await db.use({ ns: 'app', db: 'production' });
// Authenticate
await db.signin({
username: 'user',
password: 'pass'
});
// Subscribe to live updates
const queryUuid = await db.live(
'user',
(action, result) => {
console.log(`Action: ${action}`);
console.log('Data:', result);
switch(action) {
case 'CREATE':
handleNewUser(result);
break;
case 'UPDATE':
handleUserUpdate(result);
break;
case 'DELETE':
handleUserDelete(result);
break;
}
}
);
// ✅ IMPORTANT: Clean up on unmount/disconnect
return () => {
db.kill(queryUuid);
db.close();
};
}
// ✅ With permissions check
const liveQuery = `
LIVE SELECT * FROM post
WHERE author = $auth.id OR public = true;
`;
// ❌ UNSAFE: No cleanup, connection leaks
async function badExample() {
const db = new Surreal();
await db.connect('ws://localhost:8000/rpc');
await db.live('user', callback); // Never cleaned up!
}
```
---
### Pattern 6: RBAC Implementation
```surreal
-- ✅ System users with role-based access
DEFINE USER admin ON ROOT PASSWORD 'secure_password' ROLES OWNER;
DEFINE USER editor ON DATABASE app PASSWORD 'secure_password' ROLES EDITOR;
DEFINE USER viewer ON DATABASE app PASSWORD 'secure_password' ROLES VIEWER;
-- ✅ Record user authentication with scope
DEFINE SCOPE user_scope
SESSION 2h
SIGNUP (
CREATE user CONTENT {
email: $email,
password: crypto::argon2::generate($password),
created_at: time::now()
}
)
SIGNIN (
SELECT * FROM user WHERE email = $email
AND crypto::argon2::compare(password, $password)
);
-- Client authentication
const token = await db.signup({
scope: 'user_scope',
email: '[email protected]',
password: 'userpassword'
});
-- Or signin
const token = await db.signin({
scope: 'user_scope',
email: '[email protected]',
password: 'userpassword'
});
-- ✅ Use $auth in permissions
DEFINE TABLE document SCHEMAFULL
PERMISSIONS
FOR select WHERE public = true OR owner = $auth.id
FOR create WHERE $auth.id != NONE
FOR update, delete WHERE owner = $auth.id;
DEFINE FIELD owner ON TABLE document TYPE record<user> VALUE $auth.id;
DEFINE FIELD public ON TABLE document TYPE bool DEFAULT false;
```
---
### Pattern 7: Query Optimization with Indexes
```surreal
-- ✅ Create indexes for frequently queried fields
DEFINE INDEX email_idx ON TABLE user COLUMNS email UNIQUE;
DEFINE INDEX name_idx ON TABLE user COLUMNS name;
DEFINE INDEX created_idx ON TABLE post COLUMNS created_at;
-- ✅ Composite index for multi-column queries
DEFINE INDEX user_created_idx ON TABLE post COLUMNS user, created_at;
-- ✅ Search index for full-text search
DEFINE INDEX search_idx ON TABLE post COLUMNS title, content SEARCH ANALYZER simple BM25;
-- Use search index
SELECT * FROM post WHERE title @@ 'database' OR content @@ 'database';
-- ✅ Optimized query with FETCH to avoid N+1
SELECT *, ->authored->post.* FROM user FETCH ->authored->post;
-- ✅ Pagination
SELECT * FROM post ORDER BY created_at DESC START 0 LIMIT 20;
-- ❌ SLOW: Full table scan without index
SELECT * FROM user WHERE email = '[email protected]'; -- Without index
-- ❌ SLOW: N+1 query pattern
-- First query
SELECT * FROM user;
-- Then for each user
SELECT * FROM post WHERE author = user:1;
SELECT * FROM post WHERE author = user:2;
-- ... (Better: use JOIN or FETCH)
```
---
## 5. Security Standards
### 5.1 Critical Security Vulnerabilities
**1. Default Full Table Permissions (GHSA-x5fr-7hhj-34j3)**
```surreal
-- ❌ VULNERABLE: No permissions defined
DEFINE TABLE sensitive_data SCHEMAFULL;
-- Default is FULL for system users, NONE for record users
-- ✅ SECURE: Explicit permissions
DEFINE TABLE sensitive_data SCHEMAFULL
PERMISSIONS
FOR select WHERE $auth.role = 'admin'
FOR create, update, delete NONE;
```
**2. Injection via String Concatenation**
```javascript
// ❌ VULNERABLE
const userId = req.params.id;
const query = `SELECT * FROM user:${userId}`;
// ✅ SECURE
const result = await db.query(
'SELECT * FROM $record',
{ record: `user:${userId}` }
);
```
**3. Password Storage**
```surreal
-- ❌ VULNERABLE: Plain text password
DEFINE FIELD password ON TABLE user TYPE string;
-- ✅ SECURE: Hashed password
DEFINE FIELD password ON TABLE user TYPE string
VALUE crypto::argon2::generate($value);
```
**4. LIVE Query Permissions Bypass**
```surreal
-- ❌ VULNERABLE: LIVE query without permission check
LIVE SELECT * FROM user;
-- ✅ SECURE: LIVE query with permission filter
LIVE SELECT * FROM user WHERE id = $auth.id OR public = true;
```
**5. SSRF via Network Access**
```bash
# ✅ SECURE: Restrict network access
surreal start --allow-net example.com --deny-net 10.0.0.0/8
# ❌ VULNERABLE: Unrestricted network access
surreal start --allow-all
```
---
### 5.2 OWASP Top 10 2025 Mapping
| OWASP ID | Category | SurrealDB Risk | Mitigation |
|----------|----------|----------------|------------|
| A01:2025 | Broken Access Control | Critical | Row-level PERMISSIONS, RBAC |
| A02:2025 | Cryptographic Failures | High | crypto::argon2 for passwords |
| A03:2025 | Injection | Critical | Parameterized queries, $variables |
| A04:2025 | Insecure Design | High | Explicit schema, ASSERT validation |
| A05:2025 | Security Misconfiguration | Critical | Explicit PERMISSIONS, --allow-net |
| A06:2025 | Vulnerable Components | Medium | Keep SurrealDB updated, monitor advisories |
| A07:2025 | Auth & Session Failures | Critical | SCOPE with SESSION expiry, RBAC |
| A08:2025 | Software/Data Integrity | High | SCHEMAFULL, type validation, ASSERT |
| A09:2025 | Logging & Monitoring | Medium | Audit LIVE queries, log auth failures |
| A10:2025 | SSRF | High | --allow-net, --deny-net flags |
---
## 8. Common Mistakes
### Mistake 1: Forgetting to Define Permissions
```surreal
-- ❌ DON'T: No permissions (relies on defaults)
DEFINE TABLE sensitive SCHEMAFULL;
-- ✅ DO: Explicit permissions
DEFINE TABLE sensitive SCHEMAFULL
PERMISSIONS
FOR select WHERE $auth.id != NONE
FOR create, update, delete WHERE $auth.role = 'admin';
```
---
### Mistake 2: Not Using Parameterized Queries
```javascript
// ❌ DON'T: String interpolation
const email = userInput;
await db.query(`SELECT * FROM user WHERE email = "${email}"`);
// ✅ DO: Parameters
await db.query('SELECT * FROM user WHERE email = $email', { email });
```
---
### Mistake 3: Storing Plain Text Passwords
```surreal
-- ❌ DON'T: Plain text
CREATE user CONTENT { password: $password };
-- ✅ DO: Hashed
CREATE user CONTENT {
password: crypto::argon2::generate($password)
};
```
---
### Mistake 4: Not Cleaning Up LIVE Queries
```javascript
// ❌ DON'T: Memory leak
async function subscribe() {
const uuid = await db.live('user', callback);
// Never killed!
}
// ✅ DO: Clean up
const uuid = await db.live('user', callback);
// Later or on component unmount:
await db.kill(uuid);
```
---
### Mistake 5: Missing Indexes on Queried Fields
```surreal
-- ❌ DON'T: Query without index
SELECT * FROM user WHERE email = $email; -- Slow!
-- ✅ DO: Create index first
DEFINE INDEX email_idx ON TABLE user COLUMNS email UNIQUE;
SELECT * FROM user WHERE email = $email; -- Fast!
```
---
### Mistake 6: N+1 Query Pattern
```surreal
-- ❌ DON'T: Multiple queries
SELECT * FROM user;
-- Then for each user:
SELECT * FROM post WHERE author = user:1;
SELECT * FROM post WHERE author = user:2;
-- ✅ DO: Single query with graph traversal
SELECT *, ->authored->post.* FROM user;
-- ✅ OR: Use FETCH
SELECT * FROM user FETCH ->authored->post;
```
---
### Mistake 7: Overly Permissive RBAC
```surreal
-- ❌ DON'T: Everyone is OWNER
DEFINE USER dev ON ROOT PASSWORD 'weak' ROLES OWNER;
-- ✅ DO: Least privilege
DEFINE USER dev ON DATABASE app PASSWORD 'strong' ROLES VIEWER;
DEFINE USER admin ON ROOT PASSWORD 'very_strong' ROLES OWNER;
```
---
## 13. Critical Reminders
### NEVER
- ❌ Use string concatenation/interpolation in queries
- ❌ Store passwords in plain text
- ❌ Define tables without explicit PERMISSIONS
- ❌ Use default FULL permissions in production
- ❌ Expose root credentials to client applications
- ❌ Forget to validate user input with ASSERT
- ❌ Use --allow-all in production
- ❌ Leave LIVE query subscriptions without cleanup
- ❌ Skip indexing on frequently queried fields
- ❌ Use schemaless without security review
### ALWAYS
- ✅ Use parameterized queries ($variables)
- ✅ Hash passwords with crypto::argon2 or crypto::bcrypt
- ✅ Define explicit PERMISSIONS on every table
- ✅ Use row-level security (WHERE $auth.id)
- ✅ Implement RBAC with least privilege
- ✅ Validate fields with TYPE and ASSERT
- ✅ Create indexes on queried fields
- ✅ Use SCHEMAFULL for critical tables
- ✅ Set SESSION expiration on scopes
- ✅ Monitor security advisories (github.com/surrealdb/surrealdb/security)
- ✅ Clean up LIVE query subscriptions
- ✅ Use graph traversal to avoid N+1 queries
- ✅ Restrict network access with --allow-net
### Pre-Implementation Checklist
#### Ph...
© martinholovsky · Unlicense · martinholovsky/claude-skills-generator