454 lines
12 KiB
Markdown
454 lines
12 KiB
Markdown
|
# 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:**
|
||
|
|
```sql
|
||
|
|
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:**
|
||
|
|
```surql
|
||
|
|
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:**
|
||
|
|
```sql
|
||
|
|
-- 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:**
|
||
|
|
```surql
|
||
|
|
-- 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:**
|
||
|
|
```sql
|
||
|
|
-- 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:**
|
||
|
|
```surql
|
||
|
|
-- 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:**
|
||
|
|
```sql
|
||
|
|
-- 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:**
|
||
|
|
```surql
|
||
|
|
-- 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:**
|
||
|
|
```rust
|
||
|
|
// 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:**
|
||
|
|
```rust
|
||
|
|
// 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:**
|
||
|
|
```sql
|
||
|
|
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:**
|
||
|
|
```surql
|
||
|
|
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
|
||
|
|
|