syntaxis/docs/databases/surrealdb/surrealdb-comparison.md

SQLite vs SurrealDB Comparison for syntaxis

Side-by-Side Feature Comparison

Feature	SQLite	SurrealDB	Advantage
Type	Relational SQL	Multi-model (Document + Graph + SQL)	SurrealDB (more flexible)
Schema	Fixed tables	Flexible/Dynamic	SurrealDB (domain-driven)
Relationships	Foreign Keys (joins)	Native Graph (edges)	SurrealDB (phase transitions)
Data Format	Row/Column	JSON Documents	SurrealDB (JSON-native)
Real-time	Polling only	WebSocket subscriptions	SurrealDB (live updates)
Scalability	Single file (embedded)	Distributed	SurrealDB (cloud-ready)
Query Language	SQL	SurrealQL (SQL-like + graph)	SurrealDB (richer)
ACID	✅ Yes	✅ Yes	Same
Async Driver	✅ sqlx	✅ Native	Same
Versioning	Manual (audit tables)	Built-in time-travel	SurrealDB
Transactions	✅ Yes	✅ Yes	Same
JSON Support	TEXT/JSON1	Native	SurrealDB
Setup Complexity	Simple (file-based)	Medium (standalone server)	SQLite (easier)
Operator Cost	Low	Medium	SQLite

---

Query Comparison: Real-world syntaxis Scenarios

Scenario 1: Get All Completed Tasks for a Phase

SQLite:

SELECT ci.*, p.name as project_name
FROM checklist_items ci
JOIN projects p ON ci.project_id = p.id
WHERE ci.phase = 'Development'
  AND ci.completed = true
  AND p.archived = false
ORDER BY ci.completed_at DESC
LIMIT 50;

SurrealDB:

SELECT id, task_id, description, completed_at, (->checklist_items<-projects).name as project_name
FROM checklist_items
WHERE phase = 'Development'
  AND completed = true
  AND !projects<-checklist_items<-projects[archived = true]
ORDER BY completed_at DESC
LIMIT 50;

Verdict: SQLite simpler, SurrealDB more flexible

---

Scenario 2: Find All Phase Transitions for a Project (Graph Query)

SQLite:

-- Manual path query with JOINs
SELECT pt.*, p1.name as from_phase, p2.name as to_phase
FROM phase_transitions pt
LEFT JOIN phases p1 ON p1.id = pt.from_phase
LEFT JOIN phases p2 ON p2.id = pt.to_phase
WHERE pt.project_id = ?
ORDER BY pt.timestamp DESC;

-- Alternative: serialize in code and parse
SELECT * FROM phase_transitions WHERE project_id = ? ORDER BY timestamp DESC;
-- Then manually traverse

SurrealDB:

-- Query graph edges directly
SELECT * FROM project:123 -> transitioned_to -> *
ORDER BY time DESC;

-- Or with details
SELECT
    id,
    type::string(->transitioned_to) as to_phase,
    reason,
    timestamp
FROM project:123 -> transitioned_to;

-- Or get full path (recursive)
SELECT <- transitioned_to <- * FROM project:123;

Verdict: 🏆 SurrealDB wins - Natural graph traversal, no JOINs needed

---

Scenario 3: Tool Configuration with Dynamic Fields

SQLite:

-- Store as JSON string
CREATE TABLE tool_configurations (
    id TEXT PRIMARY KEY,
    project_id TEXT,
    tool_name TEXT,
    config_json TEXT,  -- {"rust_version": "1.75", "clippy_level": "pedantic", ...}
    ...
);

-- Retrieve and parse in Rust
let result = sqlx::query!(
    "SELECT config_json FROM tool_configurations WHERE id = ?",
    id
).fetch_one(&pool).await?;

let config: serde_json::Value = serde_json::from_str(&result.config_json)?;
let rust_version = config["rust_version"].as_str();

SurrealDB:

-- Native document storage
CREATE toolconfig:cargo-clippy SET
    tool_name = 'cargo-clippy',
    enabled = true,
    config = {
        rust_version: '1.75',
        clippy_level: 'pedantic',
        lint_rules: ['all', 'pedantic'],
        exclude_rules: ['unsafe-code'],
    },
    updated_at = now();

-- Query fields directly
SELECT config.rust_version FROM toolconfig:cargo-clippy;

-- Update nested fields
UPDATE toolconfig:cargo-clippy SET
    config.clippy_level = 'strict'
    config.updated_at = now();

Verdict: 🏆 SurrealDB wins - Type-safe nested documents, no string parsing

---

Scenario 4: Audit Trail - "Show me what changed"

SQLite:

-- Need separate activity_logs table
CREATE TABLE activity_logs (
    id TEXT PRIMARY KEY,
    project_id TEXT,
    table_name TEXT,
    action TEXT,  -- 'INSERT', 'UPDATE', 'DELETE'
    before_data TEXT,
    after_data TEXT,
    timestamp TEXT,
    user_id TEXT
);

-- Query changes
SELECT * FROM activity_logs
WHERE project_id = ?
  AND table_name = 'projects'
  AND timestamp BETWEEN ? AND ?
ORDER BY timestamp DESC;

-- Manually compare before/after
let before = serde_json::from_str(&result.before_data)?;
let after = serde_json::from_str(&result.after_data)?;
let diff = compute_diff(before, after);

SurrealDB:

-- Built-in version control
-- Query project state at specific time
SELECT * FROM projects VERSION AT <d'2025-01-14T10:00:00Z'>;

-- See what changed between times
SELECT * FROM projects
DIFF FROM <d'2025-01-14T10:00:00Z'>
TO <d'2025-01-15T10:00:00Z'>;

-- See all versions
SELECT * FROM projects VERSIONS;

-- Automatic - no manual audit tables needed!

Verdict: 🏆 SurrealDB wins - Built-in versioning, no manual tracking

---

Scenario 5: Real-time Dashboard Updates

SQLite:

// Polling approach (bad for real-time)
let mut interval = tokio::time::interval(Duration::from_secs(5));
loop {
    interval.tick().await;
    let projects = sqlx::query_as::<_, Project>(
        "SELECT * FROM projects WHERE updated_at > ?"
    )
    .bind(&last_update)
    .fetch_all(&pool)
    .await?;

    // Send to dashboard...
    send_to_dashboard(projects).await;
    last_update = Utc::now();
}

SurrealDB:

// WebSocket subscription (real-time push)
let mut stream = db.query("LIVE SELECT * FROM projects")
    .await?
    .take(0)?;

// Automatically gets PUSH notifications for ANY change
while let Some(change) = stream.next().await {
    match change {
        Ok(notification) => {
            // Instant notification of change
            send_to_dashboard(notification).await;
        }
        Err(e) => eprintln!("Subscription error: {}", e),
    }
}

Verdict: 🏆 SurrealDB wins - Built-in WebSocket subscriptions, real-time push vs polling

---

Scenario 6: Complex Filter - Uncompleted Tasks in Active Projects

SQLite:

SELECT ci.*
FROM checklist_items ci
JOIN projects p ON ci.project_id = p.id
WHERE ci.completed = false
  AND p.archived = false
  AND p.current_phase IN ('Development', 'Testing')
  AND ci.phase = p.current_phase
ORDER BY ci.task_priority DESC,
         CASE
           WHEN ci.task_due IS NULL THEN 1
           ELSE 0
         END,
         ci.task_due ASC;

SurrealDB:

SELECT * FROM checklist_items
WHERE
    completed = false
    AND (->projects<-checklist_items).archived = false
    AND (->projects<-checklist_items).current_phase = phase
    AND (->projects<-checklist_items).current_phase IN ['Development', 'Testing']
ORDER BY
    task_priority DESC,
    task_due ASC NULLS LAST;

Verdict: Roughly equivalent, SurrealDB graph relations slightly more natural

---

Performance Comparison

Read Performance (Queries)

                    Simple Select    Complex Join    Graph Traversal
SQLite              ✅✅✅ Very Fast  ✅✅ Good        ⚠️ Slow (many JOINs)
SurrealDB           ✅✅ Fast         ✅✅ Good        ✅✅✅ Excellent

Write Performance (Inserts)

                    Single Insert    Batch Insert    Transaction
SQLite              ✅✅✅ Very Fast  ✅✅ Good        ✅✅ Good
SurrealDB           ✅✅ Good         ✅✅ Good        ✅✅ Good

Real-time Updates

                    Polling          Push Notification
SQLite              ⚠️ Expensive      ✗ Not possible
SurrealDB           ✗ Not needed      ✅✅✅ Excellent

---

Cost Analysis

Operational Complexity

SQLite:

• ✅ File-based, no server to run
• ✅ Zero configuration
• ✅ Single process
• ✅ Backup = copy file

SurrealDB:

• ⚠️ Standalone server process
• ⚠️ Minimal configuration needed
• ⚠️ Network communication (WebSocket/HTTP)
• ✅ Cloud-native deployment

Learning Curve

SQLite:

• ✅ Standard SQL
• ✅ Familiar to most developers
• ✅ Excellent documentation

SurrealDB:

• ⚠️ New query language (SurrealQL)
• ⚠️ Graph concepts (edges, relations)
• ⚠️ Fewer examples available

Migration Effort

Switching from SQLite to SurrealDB:

1. Define Database trait (4-6 hours)
2. Refactor SQLite to adapter (6-8 hours)
3. Implement SurrealDB adapter (12-16 hours)
4. Data migration (4-6 hours)
5. Testing & validation (8-10 hours)
6. Total: ~40-50 hours (1 developer-week)

---

Recommendation Matrix

Use SQLite if:

• ✅ Single-user or small team
• ✅ Embedded deployment (CLI tool, local only)
• ✅ Minimal operational overhead desired
• ✅ Simple relational data model
• ✅ Fast MVP/PoC needed
• ✅ Budget constrained

Current Status: ✅ syntaxis uses SQLite

Use SurrealDB if:

• ✅ Multi-user collaborative environment
• ✅ Complex relationships (phases, tasks, dependencies)
• ✅ Real-time updates required
• ✅ Distributed/cloud deployment
• ✅ Graph queries common
• ✅ Audit trail important

Why SurrealDB fits syntaxis:

1. Graph-native phases - Transitions are natural edges
2. Complex relationships - Task dependencies, project relationships
3. Real-time dashboard - TUI/Web dashboard updates
4. Flexible schema - Tool configs, extensible design
5. Future-proof - Scales to enterprise

---

Migration Path

Current (Recommended)

syntaxis
├── persistence.rs (SQLite only)
├── config.toml (engine: "sqlite")
└── workspace.db (embedded)

Phase 1 (Add Trait)

syntaxis
├── persistence/
│   ├── mod.rs (Database trait)
│   ├── sqlite_impl.rs (current code)
│   └── error.rs
├── config.toml (engine: "sqlite")
└── workspace.db

Phase 2 (Add SurrealDB Support)

syntaxis
├── persistence/
│   ├── mod.rs (Database trait)
│   ├── sqlite_impl.rs
│   ├── surrealdb_impl.rs (NEW)
│   └── error.rs
├── config.toml (engine: "sqlite" OR "surrealdb")
└── workspace.db / surrealdb-server

Phase 3 (Recommendation for Enterprise)

syntaxis
├── persistence/
│   ├── mod.rs (Database trait)
│   ├── sqlite_impl.rs (default)
│   ├── surrealdb_impl.rs (advanced)
│   ├── postgres_impl.rs (future)
│   └── error.rs
├── configs/
│   ├── sqlite.toml (embedded)
│   ├── surrealdb.toml (cloud-ready)
│   └── postgres.toml (enterprise)
└── docker-compose.yml (SurrealDB + PostgreSQL)

---

Final Recommendation

Scenario	Recommendation	Reason
Development	SQLite	No setup, fast iteration
Single User	SQLite	No overhead needed
Small Team	SQLite	Still manageable
Large Team	SurrealDB	Real-time, graph queries
Cloud Deployment	SurrealDB	Distributed, scalable
Enterprise	SurrealDB + PostgreSQL	Choice, backup options

For syntaxis:

• Keep SQLite as default (current)
• Add SurrealDB support in Phase 2 (next quarter)
• Let users choose via configuration
• No breaking changes with trait-based design

---

References

• SQLite Docs: https://sqlite.org/
• SurrealDB Docs: https://surrealdb.com/docs
• SurrealDB Rust Client: https://github.com/surrealdb/surrealdb.rs
• Example Code: See SURREALDB_EXAMPLE.rs in this directory