# SurrealDB Core Examples

> Connection patterns, CRUD operations, TypeScript typing, and RecordId usage. See [SKILL.md](../SKILL.md) for core concepts.

**Graph patterns:** See [graph-relations.md](graph-relations.md). **Schema & auth:** See [schema-auth.md](schema-auth.md). **Live queries:** See [live-queries.md](live-queries.md).

---

## Pattern 1: Connection Setup

### Good Example -- Production Connection

```typescript
import Surreal from "surrealdb";

async function connectDatabase(): Promise<Surreal> {
  const url = process.env.SURREALDB_URL;
  if (!url) {
    throw new Error("SURREALDB_URL environment variable is required");
  }

  const namespace = process.env.SURREALDB_NAMESPACE;
  const database = process.env.SURREALDB_DATABASE;
  if (!namespace || !database) {
    throw new Error(
      "SURREALDB_NAMESPACE and SURREALDB_DATABASE environment variables are required",
    );
  }

  const db = new Surreal();

  try {
    await db.connect(url, {
      namespace,
      database,
    });

    // Authenticate (use DEFINE ACCESS for app users, root only for admin)
    await db.signin({
      username: process.env.SURREALDB_USER ?? "root",
      password: process.env.SURREALDB_PASS ?? "",
    });

    return db;
  } catch (error) {
    await db.close();
    throw error;
  }
}

export { connectDatabase };
```

**Why good:** Environment variables for all config, namespace/database set at connection time, error handling closes connection on failure, typed return

### Good Example -- Connection State Monitoring

```typescript
const db = new Surreal();

const unsubConnected = db.subscribe("connected", () => {
  console.log("SurrealDB connected");
});

const unsubDisconnected = db.subscribe("disconnected", () => {
  console.warn("SurrealDB disconnected");
});

const unsubError = db.subscribe("error", (error) => {
  console.error("SurrealDB error:", error);
});

// Cleanup subscriptions when done
function cleanup(): void {
  unsubConnected();
  unsubDisconnected();
  unsubError();
}

export { cleanup };
```

**Why good:** SDK v2 event subscriptions via `subscribe()`, returns unsubscribe functions for cleanup, different log levels for different events

### Good Example -- Graceful Shutdown

```typescript
async function disconnectDatabase(db: Surreal): Promise<void> {
  await db.close();
  console.log("SurrealDB connection closed");
}

process.on("SIGINT", async () => {
  await disconnectDatabase(db);
  process.exit(0);
});

process.on("SIGTERM", async () => {
  await disconnectDatabase(db);
  process.exit(0);
});

export { disconnectDatabase };
```

### Bad Example -- Missing Namespace/Database

```typescript
// BAD: No namespace or database selected
const db = new Surreal();
await db.connect("http://localhost:8000");
await db.signin({ username: "root", password: "root" });

// This query will fail silently or error -- no namespace/database context
const users = await db.query("SELECT * FROM user");
```

**Why bad:** No `namespace`/`database` in connect options or `use()` call, queries fail without context, uses `localhost` (IPv6 issues on Node.js 18+)

---

## Pattern 2: CRUD Operations

### Good Example -- Create Records

```typescript
import { RecordId, Table } from "surrealdb";

interface User {
  id: RecordId;
  name: string;
  email: string;
  role: "admin" | "user" | "moderator";
  created_at: string;
}

// Create with auto-generated ID
const newUser = await db.create<User>(new Table("user"), {
  name: "Alice",
  email: "alice@example.com",
  role: "user",
});
// Returns: { id: RecordId { table: "user", id: "abc123..." }, name: "Alice", ... }

// Create with specific ID
const admin = await db.create<User>(new RecordId("user", "admin-alice"), {
  name: "Alice",
  email: "alice@example.com",
  role: "admin",
});

// Bulk insert with conflict handling (SurrealQL)
const BATCH_SIZE = 100;
const users = generateUsers(BATCH_SIZE);
await db.query(
  `INSERT INTO user $users ON DUPLICATE KEY UPDATE
    name = $input.name,
    email = $input.email`,
  { users },
);
```

**Why good:** TypeScript generics for return type, `Table` for auto-ID creation, `RecordId` for specific IDs, `INSERT ... ON DUPLICATE KEY UPDATE` for idempotent bulk operations, named constant for batch size

### Good Example -- Select and Query

```typescript
const PAGE_SIZE = 20;

// Select by ID (fastest -- direct record lookup)
const user = await db.select<User>(new RecordId("user", "alice"));

// Select all from table
const allUsers = await db.select<User[]>(new Table("user"));

// Parameterized query with pagination
async function getUsers(
  page: number = 1,
  role?: string,
): Promise<{ data: User[]; total: number }> {
  const offset = (page - 1) * PAGE_SIZE;

  const [data, countResult] = await db.query<[User[], [{ count: number }]]>(
    `SELECT * FROM user
      WHERE ($role = NONE OR role = $role)
      ORDER BY created_at DESC
      LIMIT $limit
      START $offset;
     SELECT count() AS count FROM user
      WHERE ($role = NONE OR role = $role)
      GROUP ALL;`,
    { role: role ?? null, limit: PAGE_SIZE, offset },
  );

  return {
    data,
    total: countResult[0]?.count ?? 0,
  };
}

export { getUsers };
```

**Why good:** Direct `select()` for known IDs (fastest path), parameterized queries prevent injection, parallel count query, `NONE` check for optional filters, named constant for page size

### Good Example -- Update and Merge

```typescript
// Full replacement (v2 chainable) -- all fields must be provided
await db.update(new RecordId("user", "alice")).content({
  name: "Alice B.",
  email: "alice-b@example.com",
  role: "admin",
  created_at: "2025-01-01T00:00:00Z",
});

// Partial update (merge) -- only specified fields change
await db.merge(new RecordId("user", "alice"), {
  role: "moderator",
});
// Or via chainable: await db.update(new RecordId("user", "alice")).merge({ role: "moderator" });

// Conditional update via SurrealQL with index workaround for large tables
await db.query(
  `UPDATE (SELECT id FROM user WHERE role = $old_role) SET role = $new_role`,
  { old_role: "moderator", new_role: "user" },
);
```

**Why good:** `update().content()` for full replacement (v2 chainable API), `merge()` for partial updates, subquery pattern for bulk conditional updates (workaround for UPDATE WHERE not using indexes)

### Good Example -- Delete

```typescript
// Delete by ID
await db.delete(new RecordId("user", "alice"));

// Bulk delete via SurrealQL with subquery (index-aware)
const DAYS_INACTIVE = 90;
await db.query(
  `DELETE (SELECT id FROM user WHERE last_active < time::now() - $days + "d")`,
  { days: DAYS_INACTIVE },
);
```

**Why good:** Direct delete by `RecordId`, subquery pattern for bulk deletes to use indexes, named constant for threshold

### Bad Example -- String Interpolation

```typescript
// BAD: Injection risk with every line
const email = userInput.email;
const users = await db.query(`SELECT * FROM user WHERE email = '${email}'`);
await db.query(`DELETE user:${userInput.id}`);
await db.query(
  `UPDATE user SET name = '${userInput.name}' WHERE id = user:${userInput.id}`,
);
```

**Why bad:** Every line is vulnerable to SurrealQL injection, attacker can escape string and execute arbitrary statements (DROP TABLE, DEFINE ACCESS)

---

## Pattern 3: TypeScript Integration

### Good Example -- Typed Query Results

```typescript
import type { RecordId } from "surrealdb";

// Define interfaces matching your SurrealDB schema
interface Post {
  id: RecordId;
  title: string;
  content: string;
  author: RecordId; // Record link to user table
  tags: string[];
  published: boolean;
  created_at: string;
}

interface PostWithAuthor {
  id: RecordId;
  title: string;
  author_name: string;
  author_email: string;
}

// Type-safe query with result typing
const posts = await db.query<[PostWithAuthor[]]>(
  `SELECT
    id, title,
    author.name AS author_name,
    author.email AS author_email
  FROM post
  WHERE published = true
  ORDER BY created_at DESC
  LIMIT $limit`,
  { limit: 10 },
);

// Multi-statement with tuple typing
const [recentPosts, topAuthors] = await db.query<
  [Post[], { author: RecordId; post_count: number }[]]
>(
  `SELECT * FROM post WHERE published = true ORDER BY created_at DESC LIMIT 10;
   SELECT author, count() AS post_count FROM post GROUP BY author ORDER BY post_count DESC LIMIT 5;`,
);
```

**Why good:** Separate interfaces for different query shapes, `RecordId` type for record links, tuple typing for multi-statement queries, dot notation traverses record links in SELECT

### Good Example -- RecordId Handling

```typescript
import { RecordId, Table } from "surrealdb";

// Create RecordId instances
const userId = new RecordId("user", "alice");
const postId = new RecordId("post", "first-post");

// RecordId properties
console.log(userId.table); // Table { name: "user" }
console.log(userId.id); // "alice"

// Use in queries as parameters
const result = await db.query<[Post[]]>(
  `SELECT * FROM post WHERE author = $author`,
  { author: userId },
);

// Create dynamic RecordId from user input (safely)
function toRecordId(table: string, id: string): RecordId {
  return new RecordId(table, id);
}

// Array-based IDs for composite keys
const weatherId = new RecordId("weather", ["London", "2025-01-15T08:00:00Z"]);
```

**Why good:** `RecordId` class for type-safe record references, access `.table` and `.id` properties, parameterized use in queries, array-based IDs for composite keys

---

## Pattern 4: Range Queries with Record IDs

### Good Example -- ID Range Scanning

```typescript
// Numeric ID ranges -- no table scan needed
const batch = await db.query<[User[]]>(`SELECT * FROM user:1..=1000`);

// Array-based ID ranges for time-series data
const londonWeather = await db.query<[WeatherReading[]]>(
  `SELECT * FROM weather:['London', NONE]..=['London', time::now()]`,
);

// Parameterized range query
const readings = await db.query<[WeatherReading[]]>(
  `SELECT * FROM weather:[$city, $start]..=[$city, $end]`,
  {
    city: "London",
    start: "2025-01-01T00:00:00Z",
    end: "2025-01-31T23:59:59Z",
  },
);
```

**Why good:** Record ID ranges avoid table scans entirely, array-based IDs enable efficient time-series queries partitioned by key, parameterized for safety

### Bad Example -- Full Table Scan for Range Data

```typescript
// BAD: Full table scan when record ID ranges would be more efficient
const readings = await db.query<[WeatherReading[]]>(
  `SELECT * FROM weather WHERE city = $city AND timestamp >= $start AND timestamp <= $end`,
  { city: "London", start: "2025-01-01", end: "2025-01-31" },
);
// Requires indexes on city + timestamp to be efficient
// With array-based IDs, the range scan is free
```

**Why bad:** Requires composite index to avoid full table scan, array-based record IDs provide free range scanning by design

---

## Pattern 5: Upsert and Idempotent Operations

### Good Example -- Upsert Pattern

```typescript
// Upsert by specific record ID -- create if missing, update if exists
await db.query(
  `UPSERT user:alice SET
    name = "Alice",
    email = "alice@example.com",
    role = "admin",
    updated_at = time::now()`,
);

// Insert with conflict resolution
await db.query(
  `INSERT INTO user {
    id: user:alice,
    name: "Alice",
    email: "alice@example.com"
  } ON DUPLICATE KEY UPDATE
    name = $input.name,
    updated_at = time::now()`,
);
```

**Why good:** `UPSERT` for single-record idempotency, `INSERT ... ON DUPLICATE KEY UPDATE` for bulk operations with conflict handling, `time::now()` for automatic timestamps

---

_For graph patterns, see [graph-relations.md](graph-relations.md). For schema definitions, see [schema-auth.md](schema-auth.md). For live queries, see [live-queries.md](live-queries.md)._

# SurrealDB Graph & Relations Examples

> Record links, RELATE edges, graph traversal patterns, and relationship modeling. See [SKILL.md](../SKILL.md) for core concepts.

**Core patterns:** See [core.md](core.md). **Schema & auth:** See [schema-auth.md](schema-auth.md). **Live queries:** See [live-queries.md](live-queries.md).

---

## Pattern 1: Record Links (Simple Pointers)

Record links are fields that store a `RecordId` pointing directly to another record. SurrealDB fetches linked records from disk via dot notation -- no JOIN required.

### Good Example -- Record Link Fields

```surql
-- Create records with link fields
CREATE person:alice SET
  name = "Alice",
  company = company:acme,
  manager = person:bob,
  skills = [skill:typescript, skill:surrealdb];

CREATE company:acme SET
  name = "Acme Corp",
  founded = d"2020-01-01";

-- Traverse links with dot notation (automatic remote fetch)
SELECT name, company.name AS company_name FROM person:alice;
-- Returns: [{ name: "Alice", company_name: "Acme Corp" }]

-- Multi-level traversal
SELECT name, manager.company.name AS manager_company FROM person:alice;

-- Array link traversal
SELECT skills.name AS skill_names FROM person:alice;
-- Returns skill names from all linked skill records
```

**Why good:** Links are direct disk lookups (no table scan), dot notation traverses transparently across tables, works on single values and arrays

### Bad Example -- Storing ID as String

```surql
-- BAD: Storing the ID as a plain string instead of a record link
CREATE person:alice SET
  name = "Alice",
  company = "company:acme";  -- This is a STRING, not a record link

-- Dot notation won't work on strings
SELECT company.name FROM person:alice;
-- Returns: NONE (string has no .name property)
```

**Why bad:** String values are not record links -- dot notation traversal fails silently, returning NONE instead of the linked record's data

---

## Pattern 2: Graph Edges with RELATE

Graph edges are full records stored in a relation table. They support metadata, bidirectional traversal, and type constraints via `DEFINE TABLE TYPE RELATION`.

### Good Example -- Creating Edges

```surql
-- Simple edge
RELATE person:alice->follows->person:bob;

-- Edge with metadata
RELATE person:alice->follows->person:carol SET
  followed_at = time::now(),
  notifications = true;

-- Multiple edges in one statement
RELATE person:alice->likes->post:hello_world SET
  liked_at = time::now();

RELATE person:alice->likes->post:surrealdb_guide SET
  liked_at = time::now();

-- Typed relation table (enforces valid endpoints)
DEFINE TABLE follows TYPE RELATION IN person OUT person;
DEFINE TABLE likes TYPE RELATION IN person OUT post;

-- ENFORCED ensures referenced records exist
DEFINE TABLE follows TYPE RELATION IN person OUT person ENFORCED;
```

**Why good:** `RELATE` creates edge records with `in`/`out` fields automatically, `SET` adds metadata, `TYPE RELATION` constrains valid endpoints, `ENFORCED` prevents dangling references

### Good Example -- Forward and Reverse Traversal

```surql
-- Forward: who does Alice follow?
SELECT ->follows->person.name AS following FROM person:alice;
-- Returns: [{ following: ["Bob", "Carol"] }]

-- Reverse: who follows Bob?
SELECT <-follows<-person.name AS followers FROM person:bob;
-- Returns: [{ followers: ["Alice"] }]

-- Bidirectional (for symmetric relationships like "knows")
DEFINE TABLE knows TYPE RELATION IN person OUT person;
RELATE person:alice->knows->person:bob;
SELECT <->knows<->person.name AS connections FROM person:alice;
-- Returns: [{ connections: ["Bob"] }]

-- Filter during traversal
SELECT ->follows->person.* AS following
  FROM person:alice
  WHERE ->follows->person.role = "admin";

-- Multi-hop traversal (friends of friends)
SELECT ->follows->person->follows->person.name AS fof FROM person:alice;
```

**Why good:** Arrow syntax for direction, dot notation on target for field selection, bidirectional with `<->`, multi-hop in a single query without separate joins

### Good Example -- Querying Edge Metadata

```surql
-- Query the edge table directly
SELECT *, in.name AS follower, out.name AS followed
  FROM follows
  WHERE in = person:alice;

-- Filter edges by metadata
SELECT ->follows[WHERE notifications = true]->person.name AS notified_following
  FROM person:alice;

-- Aggregate on edges
SELECT
  out.name AS followed,
  count() AS follower_count
  FROM follows
  GROUP BY out
  ORDER BY follower_count DESC
  LIMIT 10;
```

**Why good:** Edges are full records (queryable with SELECT), bracket filters on traversal path, aggregation on edge tables for analytics

### Bad Example -- Using Record Links for Bidirectional Relationships

```surql
-- BAD: Manually maintaining bidirectional links
UPDATE person:alice SET friends += person:bob;
UPDATE person:bob SET friends += person:alice;
-- Must manually keep both sides in sync -- fragile
-- If one update fails, the relationship is inconsistent
```

**Why bad:** Manual bidirectional maintenance is error-prone, no atomicity guarantee, use `RELATE` with `<->` traversal instead

---

## Pattern 3: Relationship Modeling Decisions

### Good Example -- Social Graph

```surql
-- Define typed relation tables
DEFINE TABLE follows TYPE RELATION IN person OUT person SCHEMAFULL;
DEFINE FIELD followed_at ON follows TYPE datetime VALUE time::now();
DEFINE FIELD notifications ON follows TYPE bool DEFAULT true;

DEFINE TABLE blocks TYPE RELATION IN person OUT person SCHEMAFULL;
DEFINE FIELD blocked_at ON blocks TYPE datetime VALUE time::now();

-- Complex social query: mutual followers
SELECT
  ->follows->person AS i_follow,
  <-follows<-person AS follows_me
FROM person:alice;

-- Find mutual follows (both directions)
-- People Alice follows who also follow Alice
SELECT ->follows->person INTERSECT <-follows<-person AS mutuals
  FROM person:alice;
```

**Why good:** Typed relation tables enforce valid connections, metadata on edges (timestamps, preferences), `INTERSECT` for set operations on traversal results

### Good Example -- Access Control Graph

```surql
-- Model permission inheritance via graph
DEFINE TABLE member_of TYPE RELATION IN user OUT team SCHEMAFULL;
DEFINE FIELD role ON member_of TYPE string ASSERT $value IN ["viewer", "editor", "admin"];
DEFINE FIELD joined_at ON member_of TYPE datetime VALUE time::now();

DEFINE TABLE owns TYPE RELATION IN team OUT resource SCHEMAFULL;
DEFINE FIELD permission ON owns TYPE string ASSERT $value IN ["read", "write", "admin"];

-- Create team membership
RELATE user:alice->member_of->team:engineering SET role = "admin";
RELATE user:bob->member_of->team:engineering SET role = "viewer";

-- Create resource ownership
RELATE team:engineering->owns->resource:api_server SET permission = "admin";

-- Check if user has access to resource (graph traversal)
SELECT
  ->member_of->team->owns->resource AS accessible_resources,
  ->member_of[WHERE role = "admin"]->team.name AS admin_teams
FROM user:alice;
```

**Why good:** Graph models permission inheritance naturally, edge metadata stores role/permission level, traversal checks access without manual joins, bracket filters on edges

---

## Pattern 4: Recursive and Advanced Traversal

### Good Example -- Org Chart Traversal

```surql
-- Define hierarchical relationship
DEFINE TABLE reports_to TYPE RELATION IN employee OUT employee;

RELATE employee:alice->reports_to->employee:ceo;
RELATE employee:bob->reports_to->employee:alice;
RELATE employee:carol->reports_to->employee:alice;

-- Direct reports (one level)
SELECT <-reports_to<-employee.name AS direct_reports FROM employee:alice;

-- Full chain to top (recursive-style)
SELECT
  name,
  ->reports_to->employee.name AS manager,
  ->reports_to->employee->reports_to->employee.name AS skip_manager
FROM employee:bob;
```

**Why good:** Graph naturally models hierarchies, arrow syntax chains for multi-level traversal, each hop adds one `->edge->target` segment

### Good Example -- Wildcard Edge Traversal

```surql
-- Discover all outgoing relationships from a record (any edge type)
SELECT id, ->?->? AS all_connections FROM person:alice;

-- Discover all incoming relationships
SELECT id, <-?<-? AS all_referrers FROM resource:api_server;

-- Useful for debugging and schema discovery
SELECT id, ->?->?.id AS outgoing_ids FROM person:alice;
```

**Why good:** Wildcard `?` matches any edge table and target, useful for schema exploration and debugging, returns all connected records regardless of edge type

---

_For core patterns, see [core.md](core.md). For schema definitions, see [schema-auth.md](schema-auth.md). For live queries, see [live-queries.md](live-queries.md)._

# SurrealDB Live Queries & Transactions Examples

> Live queries, real-time subscriptions, transactions, events, and custom functions. See [SKILL.md](../SKILL.md) for core concepts.

**Core patterns:** See [core.md](core.md). **Graph patterns:** See [graph-relations.md](graph-relations.md). **Schema & auth:** See [schema-auth.md](schema-auth.md).

---

## Pattern 1: Live Queries (Real-Time Subscriptions)

### Good Example -- Table Subscription (SDK v2)

```typescript
import Surreal, { Table } from "surrealdb";

interface ChatMessage {
  id: RecordId;
  channel: string;
  author: RecordId;
  content: string;
  created_at: string;
}

// Subscribe to all changes on a table
const live = await db.live(new Table("chat_message"));

// Callback-based subscription
live.subscribe((message) => {
  switch (message.action) {
    case "CREATE":
      console.log("New message:", message.value);
      break;
    case "UPDATE":
      console.log("Message updated:", message.value);
      break;
    case "DELETE":
      console.log("Message deleted, id:", message.recordId);
      break;
  }
});

// Cleanup when done
await live.kill();
```

**Why good:** SDK v2 `live()` returns a subscription object, callback receives `LiveMessage` with `action`/`value`/`recordId`, `kill()` for cleanup, action-based switch for different event types

### Good Example -- Filtered Live Query (SurrealQL)

```typescript
// Subscribe to specific records only (server-side filtering via SurrealQL)
const [liveId] = await db.query<[string]>(
  `LIVE SELECT * FROM chat_message WHERE channel = $channel`,
  { channel: "general" },
);

// Async iterator pattern (preferred in SDK v2)
const liveFeed = await db.live(new Table("notification"));
for await (const { action, value } of liveFeed) {
  if (action === "CREATE") {
    showNotification(value);
  }
}
```

**Why good:** Server-side WHERE filtering reduces network traffic, async iterator for stream processing, `for await` pattern integrates naturally with TypeScript control flow

### Good Example -- Live Query Cleanup Pattern

```typescript
class RealtimeManager {
  private subscriptions: Array<{ kill: () => Promise<void> }> = [];

  async subscribe(
    db: Surreal,
    table: string,
    callback: (message: LiveMessage) => void,
  ): Promise<void> {
    const live = await db.live(new Table(table));
    live.subscribe(callback);
    this.subscriptions.push(live);
  }

  async cleanup(): Promise<void> {
    await Promise.all(this.subscriptions.map((sub) => sub.kill()));
    this.subscriptions = [];
  }
}

export { RealtimeManager };
```

**Why good:** Centralized subscription management, `cleanup()` kills all live queries on disconnect, prevents subscription leaks

### Bad Example -- Unfiltered Live Query on Large Table

```typescript
// BAD: Subscribing to ALL changes on a high-traffic table
const live = await db.live(new Table("audit_log"));
live.subscribe((message) => {
  // Fires for EVERY insert into audit_log -- could be thousands per second
  console.log(message.action, message.value);
});
// No cleanup -- subscription leaks on disconnect
```

**Why bad:** No WHERE filter means every change triggers the callback (high-traffic tables can overwhelm the client), no `kill()` call causes subscription leak

---

## Pattern 2: Transactions

### Good Example -- Multi-Statement Transaction (SurrealQL)

```surql
BEGIN TRANSACTION;

-- Transfer funds between accounts
LET $source = (SELECT * FROM account:alice);
LET $dest = (SELECT * FROM account:bob);

IF $source.balance < $amount {
  THROW "Insufficient funds";
};

UPDATE account:alice SET balance -= $amount;
UPDATE account:bob SET balance += $amount;

CREATE transfer SET
  from = account:alice,
  to = account:bob,
  amount = $amount,
  transferred_at = time::now();

COMMIT TRANSACTION;
```

**Why good:** `BEGIN`/`COMMIT` wraps multiple operations atomically, `THROW` rolls back the transaction on error, `LET` for intermediate values, all operations succeed or all fail

### Good Example -- Transaction via SDK

```typescript
const TRANSFER_AMOUNT = 50.0;

async function transferFunds(
  db: Surreal,
  fromId: string,
  toId: string,
  amount: number,
): Promise<void> {
  await db.query(
    `BEGIN TRANSACTION;

    LET $source = (SELECT balance FROM account:$from_id);
    IF $source.balance < $amount {
      THROW "Insufficient funds in source account";
    };

    UPDATE account:$from_id SET balance -= $amount;
    UPDATE account:$to_id SET balance += $amount;

    CREATE transfer SET
      from_account = type::record("account", $from_id),
      to_account = type::record("account", $to_id),
      amount = $amount,
      transferred_at = time::now();

    COMMIT TRANSACTION;`,
    {
      from_id: fromId,
      to_id: toId,
      amount,
    },
  );
}

export { transferFunds };
```

**Why good:** Parameterized transaction, `THROW` for validation, `type::record()` to construct record IDs from parameters, atomic commit/rollback

### Good Example -- Transaction with RETURN

```surql
BEGIN TRANSACTION;

LET $order = CREATE order SET
  customer = $customer_id,
  items = $items,
  status = "pending",
  total = $total;

-- Decrement stock for each item
FOR $item IN $items {
  LET $product = (SELECT stock FROM product WHERE id = $item.product_id);
  IF $product.stock < $item.quantity {
    THROW "Insufficient stock for " + <string> $item.product_id;
  };
  UPDATE $item.product_id SET stock -= $item.quantity;
};

RETURN $order;

COMMIT TRANSACTION;
```

**Why good:** `RETURN` provides the created order back to the caller from within the transaction, `FOR` loop processes items, stock validation prevents overselling

### Bad Example -- No Transaction for Related Operations

```surql
-- BAD: Two operations that should be atomic but aren't
UPDATE account:alice SET balance -= 100;
-- If the server crashes here, Alice lost money and Bob didn't receive it
UPDATE account:bob SET balance += 100;
```

**Why bad:** No transaction wrapper means partial failure leaves data inconsistent, use `BEGIN TRANSACTION` / `COMMIT TRANSACTION` for atomic operations

---

## Pattern 3: Events (Database Triggers)

### Good Example -- Audit Logging with Events

```surql
-- Trigger on any change to the user table
DEFINE EVENT user_audit ON user WHEN $event IN ["CREATE", "UPDATE", "DELETE"] THEN {
  CREATE audit_log SET
    table_name = "user",
    record_id = $value.id,
    event_type = $event,
    timestamp = time::now(),
    before = $before,
    after = $after;
};

-- Trigger only on specific conditions
DEFINE EVENT price_alert ON product
  WHEN $event = "UPDATE" AND $before.price != $after.price
  THEN {
    CREATE notification SET
      message = "Price changed for " + <string> $value.id,
      old_price = $before.price,
      new_price = $after.price,
      created_at = time::now();
  };
```

**Why good:** `$event` is the action type, `$before`/`$after` capture old and new values, conditional triggers with `WHEN`, events fire within the same transaction as the triggering operation

---

## Pattern 4: Custom Functions

### Good Example -- Reusable Business Logic

```surql
-- Custom function for full-text search with pagination
DEFINE FUNCTION fn::search_posts($term: string, $page: int, $limit: int) {
  LET $offset = ($page - 1) * $limit;

  RETURN SELECT
    id, title, content,
    search::score(1) AS relevance
  FROM post
  WHERE title @1@ $term OR content @1@ $term
  ORDER BY relevance DESC
  LIMIT $limit
  START $offset;
};

-- Usage
SELECT * FROM fn::search_posts("surrealdb tutorial", 1, 20);
```

**Why good:** Encapsulates query logic, parameterized for reuse, returns structured results, pagination built in

### Gotcha -- Random ID in Functions

```surql
-- BAD: rand()/ulid()/uuid() generate the SAME value per function call
DEFINE FUNCTION fn::create_with_id() {
  -- This generates the same ULID on every call, causing duplicate key errors
  CREATE thing:ulid() SET data = "test";
  CREATE thing:ulid() SET data = "test2";
  -- Both get the SAME ulid!
};

-- WORKAROUND: Pass the ID as a parameter
DEFINE FUNCTION fn::create_with_param($id: string) {
  CREATE type::record("thing", $id) SET data = "test";
};
```

**Why bad:** Random ID functions (`rand()`, `ulid()`, `uuid()`) in custom function bodies generate the same value per function invocation, causing duplicate key errors on subsequent creates within the same function

---

## Pattern 5: Change Feeds

### Good Example -- Change Data Capture

```surql
-- Enable change feed on a table (retain 7 days of changes)
DEFINE TABLE order CHANGEFEED 7d INCLUDE ORIGINAL;

-- Query changes since a timestamp
SHOW CHANGES FOR TABLE order SINCE d"2025-01-01T00:00:00Z" LIMIT 100;
```

**Why good:** `CHANGEFEED` retains history for replay/sync, `INCLUDE ORIGINAL` preserves pre-change values, `SHOW CHANGES` for consuming changes, useful for CDC pipelines and event sourcing

**When to use:** Event sourcing, audit trails, data sync between services, replaying state changes. When you need the change history itself, not just the current state.

---

_For core patterns, see [core.md](core.md). For graph patterns, see [graph-relations.md](graph-relations.md). For schema definitions, see [schema-auth.md](schema-auth.md)._

# SurrealDB Schema & Authentication Examples

> DEFINE TABLE, DEFINE FIELD, SCHEMAFULL mode, permissions, DEFINE ACCESS, authentication patterns. See [SKILL.md](../SKILL.md) for core concepts.

**Core patterns:** See [core.md](core.md). **Graph patterns:** See [graph-relations.md](graph-relations.md). **Live queries:** See [live-queries.md](live-queries.md).

---

## Pattern 1: SCHEMAFULL Table Definition

### Good Example -- Complete Table Schema

```surql
-- Strict schema enforcement
DEFINE TABLE user SCHEMAFULL;

-- Field definitions with types, defaults, and validation
DEFINE FIELD name ON user TYPE string
  ASSERT string::len($value) >= 2 AND string::len($value) <= 100;

DEFINE FIELD email ON user TYPE string
  VALUE string::lowercase($value)
  ASSERT string::is::email($value);

DEFINE FIELD role ON user TYPE string
  DEFAULT "user"
  ASSERT $value IN ["admin", "user", "moderator"];

DEFINE FIELD age ON user TYPE option<int>
  ASSERT $value = NONE OR ($value >= 0 AND $value <= 150);

DEFINE FIELD tags ON user TYPE array<string>
  DEFAULT [];

DEFINE FIELD metadata ON user FLEXIBLE TYPE object;

DEFINE FIELD created_at ON user TYPE datetime
  VALUE time::now()
  READONLY;

DEFINE FIELD updated_at ON user TYPE datetime
  VALUE time::now();

-- Indexes
DEFINE INDEX email_idx ON user FIELDS email UNIQUE;
DEFINE INDEX role_idx ON user FIELDS role;
```

**Why good:** `SCHEMAFULL` enforces all fields, `ASSERT` for validation, `VALUE` with `string::lowercase` for transformation, `READONLY` prevents modification after creation, `FLEXIBLE TYPE object` for schemaless subtree within strict table, `option<int>` for nullable field, `DEFAULT` for sensible defaults

### Good Example -- Relation Table Schema

```surql
-- Typed relation table with constraints
DEFINE TABLE follows TYPE RELATION IN user OUT user SCHEMAFULL ENFORCED;

DEFINE FIELD followed_at ON follows TYPE datetime
  VALUE time::now()
  READONLY;

DEFINE FIELD notifications ON follows TYPE bool
  DEFAULT true;

DEFINE FIELD strength ON follows TYPE string
  DEFAULT "normal"
  ASSERT $value IN ["close", "normal", "acquaintance"];
```

**Why good:** `TYPE RELATION IN user OUT user` constrains endpoints, `ENFORCED` prevents edges to nonexistent records, metadata fields with defaults and validation

### Bad Example -- SCHEMALESS in Production

```surql
-- BAD: No field enforcement in production
DEFINE TABLE user SCHEMALESS;

-- Anyone can insert arbitrary fields
CREATE user SET
  name = "Alice",
  emal = "alice@example.com",  -- Typo goes undetected
  role = "superadmin",          -- Invalid role accepted
  _internal_notes = "hack";     -- Unintended field stored
```

**Why bad:** Typos in field names silently create new fields, no validation on values, arbitrary fields stored without restriction, data integrity impossible to guarantee

---

## Pattern 2: Computed and Derived Fields

### Good Example -- VALUE vs COMPUTED

```surql
DEFINE TABLE product SCHEMAFULL;

DEFINE FIELD name ON product TYPE string;
DEFINE FIELD price ON product TYPE float ASSERT $value >= 0;
DEFINE FIELD quantity ON product TYPE int ASSERT $value >= 0;
DEFINE FIELD discount_pct ON product TYPE float DEFAULT 0 ASSERT $value >= 0 AND $value <= 100;

-- VALUE: recalculated on CREATE and UPDATE
DEFINE FIELD updated_at ON product TYPE datetime
  VALUE time::now();

-- VALUE with field dependencies
DEFINE FIELD display_name ON product TYPE string
  VALUE string::uppercase(name);

-- COMPUTED (v3.0+): recalculated on every ACCESS (read)
DEFINE FIELD total_value ON product
  COMPUTED price * quantity;

DEFINE FIELD discounted_price ON product
  COMPUTED price * (1 - discount_pct / 100);

-- VALUE using $before for change tracking
DEFINE FIELD previous_price ON product TYPE option<float>
  VALUE $before.price;
```

**Why good:** `VALUE` recalculates on write (good for timestamps, derived strings), `COMPUTED` recalculates on read (good for live calculations), `$before` captures previous value on update

### Important Gotcha

```surql
-- Fields with VALUE compute in ALPHABETICAL ORDER
-- Field "b_total" computes BEFORE "a_price" is processed in the same CREATE
-- If b_total depends on a_price, the dependency may not resolve correctly

-- SAFE: name dependencies so alphabetical order matches dependency order
DEFINE FIELD a_base_price ON product TYPE float;
DEFINE FIELD b_tax ON product TYPE float VALUE a_base_price * 0.1;
DEFINE FIELD c_total ON product TYPE float VALUE a_base_price + b_tax;
```

**Why good:** Named to respect alphabetical processing order, each field depends only on alphabetically-prior fields

---

## Pattern 3: Table and Field Permissions

### Good Example -- Record-Level Access Control

```surql
-- Users can read public data, modify only their own records
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 OR $auth.role = "admin";

DEFINE FIELD title ON post TYPE string ASSERT string::len($value) >= 1;
DEFINE FIELD content ON post TYPE string;
DEFINE FIELD author ON post TYPE record<user> VALUE $auth.id READONLY;
DEFINE FIELD published ON post TYPE bool DEFAULT false;
DEFINE FIELD created_at ON post TYPE datetime VALUE time::now() READONLY;

-- Field-level permissions (restrict sensitive data)
DEFINE FIELD email ON user TYPE string
  PERMISSIONS
    FOR select WHERE id = $auth.id OR $auth.role = "admin"
    FOR update WHERE id = $auth.id;
```

**Why good:** Table permissions per operation type, `$auth.id` for ownership checks, `$auth.role` for admin access, `READONLY` author field set from auth context, field-level permissions for sensitive data like email

### Good Example -- Pre-Computed View Table

```surql
-- Materialized view that auto-updates
DEFINE TABLE post_stats TYPE NORMAL AS
  SELECT
    author,
    count() AS post_count,
    math::mean(<float> published) AS publish_rate
  FROM post
  GROUP BY author;

-- Query the view instead of aggregating on every request
SELECT * FROM post_stats WHERE author = user:alice;
```

**Why good:** `AS SELECT` creates an auto-updating materialized view, avoids running expensive aggregation on every read, `TYPE NORMAL` makes it queryable like a regular table

---

## Pattern 4: DEFINE ACCESS (Authentication)

### Good Example -- Record-Based Authentication

```surql
-- Define access method for application users
DEFINE ACCESS account ON DATABASE TYPE RECORD
  SIGNUP (
    CREATE user SET
      email = string::lowercase($email),
      password = crypto::argon2::generate($password),
      name = $name,
      role = "user",
      created_at = time::now()
  )
  SIGNIN (
    SELECT * FROM user
      WHERE email = string::lowercase($email)
      AND crypto::argon2::compare(password, $password)
  )
  WITH JWT ALGORITHM HS512 KEY "your-secret-key-min-64-chars-long-for-hs512-algorithm-security"
  WITH REFRESH
  DURATION FOR TOKEN 15m FOR SESSION 12h;
```

```typescript
// SDK usage -- signup
await db.signup({
  access: "account",
  variables: {
    email: "alice@example.com",
    password: "secure-password-123",
    name: "Alice",
  },
});

// SDK usage -- signin
const token = await db.signin({
  access: "account",
  variables: {
    email: "alice@example.com",
    password: "secure-password-123",
  },
});

// After signin, $auth is available in permissions
// $auth.id = user:xxx (the authenticated user's record ID)
```

**Why good:** `crypto::argon2` for password hashing (not MD5/SHA), `string::lowercase` normalizes email, short token duration (15m) limits token theft impact, refresh tokens for long sessions, `$auth` available in all subsequent permission checks

### Good Example -- JWT-Based Authentication (External Provider)

```surql
-- Validate tokens from external auth provider
DEFINE ACCESS external_auth ON DATABASE TYPE JWT
  ALGORITHM RS256
  URL "https://auth.example.com/.well-known/jwks.json";

-- Or with a static key
DEFINE ACCESS api_auth ON DATABASE TYPE JWT
  ALGORITHM HS256
  KEY "your-shared-secret";
```

**Why good:** `URL` for JWKS-based dynamic key rotation (production), RS256 asymmetric algorithm prevents key exposure, static key option for simpler setups

### Bad Example -- Weak Authentication

```surql
-- BAD: Multiple security issues
DEFINE ACCESS account ON DATABASE TYPE RECORD
  SIGNUP (
    CREATE user SET
      email = $email,                          -- Not normalized
      password = $password,                     -- Stored in PLAINTEXT
      role = $role                              -- User can set their own role!
  )
  SIGNIN (
    SELECT * FROM user WHERE email = $email AND password = $password
  )
  WITH JWT ALGORITHM HS256 KEY "secret"
  DURATION FOR TOKEN 30d;                       -- Token valid for 30 days
```

**Why bad:** Password stored in plaintext (must use `crypto::argon2::generate`), user controls their own role (privilege escalation), email not normalized, weak JWT key, excessively long token duration

---

## Pattern 5: Indexes and Performance

### Good Example -- Index Definitions

```surql
-- Unique index
DEFINE INDEX email_unique ON user FIELDS email UNIQUE;

-- Standard index for frequent queries
DEFINE INDEX role_idx ON user FIELDS role;

-- Composite index
DEFINE INDEX user_role_created ON user FIELDS role, created_at;

-- Full-text search index
DEFINE ANALYZER custom_analyzer TOKENIZERS blank, class FILTERS lowercase, snowball(english);
DEFINE INDEX post_search ON post FIELDS title, content SEARCH ANALYZER custom_analyzer BM25;

-- Vector index for embeddings
DEFINE INDEX embedding_idx ON document FIELDS embedding MTREE DIMENSION 1536;
```

**Why good:** `UNIQUE` for constraints, composite index for multi-field queries, BM25 full-text search with custom analyzer, MTREE for vector similarity search

### Good Example -- Full-Text Search Query

```surql
-- Search with BM25 scoring
SELECT *, search::score(1) AS relevance
  FROM post
  WHERE title @1@ "surrealdb tutorial"
  ORDER BY relevance DESC
  LIMIT 20;

-- Highlight matching terms
SELECT *, search::highlight("<b>", "</b>", 1) AS highlighted
  FROM post
  WHERE content @1@ "graph database"
  LIMIT 10;
```

**Why good:** `@1@` binds to index reference 1, `search::score` for relevance ranking, `search::highlight` for result snippets, `LIMIT` prevents unbounded results

---

## Pattern 6: Multi-Tenancy with Namespaces

### Good Example -- Tenant Isolation

```surql
-- Root-level: create tenant namespaces
DEFINE NAMESPACE tenant_acme;
DEFINE NAMESPACE tenant_globex;

-- Within each namespace, create identical schema
-- (run for each tenant namespace)
USE NS tenant_acme DB production;

DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD name ON user TYPE string;
DEFINE FIELD email ON user TYPE string;
DEFINE INDEX email_idx ON user FIELDS email UNIQUE;
```

```typescript
// Switch tenant context at runtime
async function switchTenant(db: Surreal, tenantId: string): Promise<void> {
  await db.use({
    namespace: `tenant_${tenantId}`,
    database: "production",
  });
}

// Tenant-scoped queries -- data is fully isolated
await switchTenant(db, "acme");
const acmeUsers = await db.query<[User[]]>("SELECT * FROM user");

await switchTenant(db, "globex");
const globexUsers = await db.query<[User[]]>("SELECT * FROM user");
// acmeUsers and globexUsers are completely separate datasets
```

**Why good:** Namespace-level isolation guarantees data separation, identical schema per tenant, runtime context switching, no cross-tenant data leakage possible at the database level

---

_For core patterns, see [core.md](core.md). For graph patterns, see [graph-relations.md](graph-relations.md). For live queries, see [live-queries.md](live-queries.md)._