undefined

user = result[0]["result"][0]
assert user["email"] == "test@example.com"
# Password should be hashed, not plain text
assert user["password"] != "secret123"
assert user["password"].startswith("$argon2")

# Create test users
await db.query("""
    CREATE user:1 CONTENT { email: 'user1@test.com', password: 'hash1' };
    CREATE user:2 CONTENT { email: 'user2@test.com', password: 'hash2' };
""")

# Verify row-level security works
# This requires proper auth context setup
assert True  # Placeholder - implement auth context test

# 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

undefined

undefined

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

undefined

undefined

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()

undefined

undefined

---

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

-- ✅ 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.

undefined

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()

undefined

-- ✅ 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;

-- ✅ 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!
};

-- ✅ 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!

-- ✅ SAFE: Parameterized query
LET $user_email = "user@example.com";
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}"`;

-- ✅ 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;

-- ✅ 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
};

// ✅ 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!
}

-- ✅ 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: 'user@example.com',
    password: 'userpassword'
});

-- Or signin
const token = await db.signin({
    scope: 'user_scope',
    email: 'user@example.com',
    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;

-- ✅ 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 = 'user@example.com'; -- 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)

-- ❌ 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;

// ❌ VULNERABLE
const userId = req.params.id;
const query = `SELECT * FROM user:${userId}`;

// ✅ SECURE
const result = await db.query(
    'SELECT * FROM $record',
    { record: `user:${userId}` }
);

-- ❌ 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);

-- ❌ 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;

undefined

---

-- ❌ 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';

// ❌ 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 });

-- ❌ DON'T: Plain text
CREATE user CONTENT { password: $password };

-- ✅ DO: Hashed
CREATE user CONTENT {
    password: crypto::argon2::generate($password)
};

// ❌ 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);

-- ❌ 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!

-- ❌ 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;

-- ❌ 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;

undefined

assert user["email"] == "test@example.com"
assert "password" not in user
assert "id" in user

undefined

undefined

# Create admin and regular user
await db.query("""
    CREATE user:admin CONTENT {
        email: 'admin@test.com',
        password: crypto::argon2::generate('admin123'),
        role: 'admin'
    };
    CREATE user:regular CONTENT {
        email: 'user@test.com',
        password: crypto::argon2::generate('user123'),
        role: 'user'
    };
""")

# Try to access admin user
result = await user_db.query("SELECT * FROM user:admin")
assert len(result[0]["result"]) == 0  # Should be empty

await user_db.close()

# Admin permissions depend on table definitions
# This test verifies RBAC is working
await admin_db.close()

undefined

undefined

# Insert 10,000 products
for batch in range(100):
    products = [
        f"CREATE product:{batch*100+i} CONTENT {{ name: 'Product {batch*100+i}', category: 'cat{i%10}', price: {i*1.5} }}"
        for i in range(100)
    ]
    await db.query("; ".join(products))

yield db

# Create index
await populated_db.query("DEFINE INDEX cat_idx ON TABLE product COLUMNS category")

# Query with index
start = time.time()
for _ in range(10):
    await populated_db.query("SELECT * FROM product WHERE category = 'cat5'")
time_with_index = time.time() - start

# Index should provide at least 2x improvement
assert time_with_index < time_without_index / 2

pool = SurrealDBPool("ws://localhost:8000/rpc", "test", "test_db", pool_size=10)
await pool.initialize({"user": "root", "pass": "root"})

async def query_task():
    async with pool.connection() as conn:
        await conn.query("SELECT * FROM product LIMIT 10")

# Run 100 concurrent queries
start = time.time()
await asyncio.gather(*[query_task() for _ in range(100)])
elapsed = time.time() - start

# Should complete in reasonable time with pooling
assert elapsed < 5.0  # 5 seconds for 100 queries

await pool.close_all()

undefined

undefined

---