Skip to content

sqlite-vec-backend.md

Latest commit

 

History

History
620 lines (478 loc) · 15.8 KB

sqlite-vec-backend.md

File metadata and controls

620 lines (478 loc) · 15.8 KB
Raw

SQLite-vec Backend Guide

Overview

SQLite-vec is the default storage backend for MCP Memory Service. It provides a lightweight, high-performance vector database solution:

  • Lightweight: Single file database with no external dependencies
  • Fast: Optimized vector operations with efficient indexing (~5 ms reads)
  • Portable: Easy to backup, copy, and share memory databases
  • Reliable: Built on SQLite's proven reliability and ACID compliance
  • Memory Efficient: Low memory footprint suited to single-user deployments

Installation

Prerequisites

The sqlite-vec backend requires the sqlite-vec Python package:

# Install sqlite-vec
pip install sqlite-vec

# Or with UV (recommended)
uv add sqlite-vec

Verification

You can verify sqlite-vec is available by running:

try:
    import sqlite_vec
    print("✅ sqlite-vec is available")
except ImportError:
    print("❌ sqlite-vec is not installed")

Configuration

Environment Variables

To use the sqlite-vec backend, set the storage backend environment variable:

# Primary configuration
export MCP_MEMORY_STORAGE_BACKEND=sqlite_vec

# Optional: Custom database path
export MCP_MEMORY_SQLITE_PATH=/path/to/your/memory.db

Platform-Specific Setup

macOS (Bash/Zsh)

# Add to ~/.bashrc or ~/.zshrc
export MCP_MEMORY_STORAGE_BACKEND=sqlite_vec
export MCP_MEMORY_SQLITE_PATH="$HOME/Library/Application Support/mcp-memory/sqlite_vec.db"

Windows (PowerShell)

# Add to PowerShell profile
$env:MCP_MEMORY_STORAGE_BACKEND = "sqlite_vec"
$env:MCP_MEMORY_SQLITE_PATH = "$env:LOCALAPPDATA\mcp-memory\sqlite_vec.db"

Windows (Command Prompt)

set MCP_MEMORY_STORAGE_BACKEND=sqlite_vec
set MCP_MEMORY_SQLITE_PATH=%LOCALAPPDATA%\mcp-memory\sqlite_vec.db

Linux

# Add to ~/.bashrc
export MCP_MEMORY_STORAGE_BACKEND=sqlite_vec
export MCP_MEMORY_SQLITE_PATH="$HOME/.local/share/mcp-memory/sqlite_vec.db"

Claude Desktop Configuration

Update your Claude Desktop MCP configuration:

{
  "mcpServers": {
    "memory": {
      "command": "uv",
      "args": ["--directory", "/path/to/mcp-memory-service", "run", "memory"],
      "env": {
        "MCP_MEMORY_STORAGE_BACKEND": "sqlite_vec"
      }
    }
  }
}

Migrating Legacy ChromaDB Data

ChromaDB was removed as a supported backend in v8.0.0. If you still have data from a ChromaDB install, see the dedicated guide: guides/chromadb-migration.md. The script is preserved on the chromadb-legacy branch.

After migration, set the environment variable and restart Claude Desktop:

export MCP_MEMORY_STORAGE_BACKEND=sqlite_vec

Migration Verification

After migration, verify your memories are accessible:

# Test the new backend
python scripts/verify_environment.py

# Check database statistics
python -c "
import asyncio
from src.mcp_memory_service.storage.sqlite_vec import SqliteVecMemoryStorage

async def check_stats():
    storage = SqliteVecMemoryStorage('path/to/your/db')
    await storage.initialize()
    stats = storage.get_stats()
    print(f'Total memories: {stats[\"total_memories\"]}')
    print(f'Database size: {stats[\"database_size_mb\"]} MB')
    storage.close()

asyncio.run(check_stats())
"

Performance Characteristics

Memory Usage

Collection Size SQLite-vec RAM
1,000 memories ~50 MB
10,000 memories ~200 MB
100,000 memories ~1 GB

Query Performance

  • Semantic Search: ~5 ms local reads for typical collections
  • Tag Search: Fast SQL-indexed lookups
  • Metadata Queries: Efficient SQL WHERE clauses
  • Startup Time: Single-file database loads in <1 second

Storage Characteristics

  • Database File: Single .db file (easy backup/restore)
  • Concurrent Access: SQLite-level locking (single writer, multiple readers) — enable WAL via MCP_MEMORY_SQLITE_PRAGMAS=journal_mode=WAL,busy_timeout=15000

Advanced Configuration

Custom Embedding Models

# Initialize with custom model
storage = SqliteVecMemoryStorage(
    db_path="memory.db",
    embedding_model="all-mpnet-base-v2"  # Higher quality, slower
)

Multi-Client Access Configuration

SQLite-vec supports advanced multi-client access through two complementary approaches:

  1. Phase 1: WAL Mode - Direct SQLite access with Write-Ahead Logging
  2. Phase 2: HTTP Coordination - Automatic HTTP server coordination for seamless multi-client access

Phase 1: WAL Mode (Default)

The backend automatically enables WAL mode with these default settings:

  • WAL Mode: Enables multiple readers + single writer
  • Busy Timeout: 5 seconds (prevents immediate lock errors)
  • Synchronous: NORMAL (balanced performance/safety)

Phase 2: HTTP Server Auto-Detection (Advanced)

The system automatically detects the optimal coordination mode:

Auto-Detection Modes:

  • http_client: Existing HTTP server detected → Connect as client
  • http_server: No server found, port available → Start HTTP server
  • direct: Port in use by other service → Fall back to WAL mode

Coordination Flow:

  1. Check if MCP Memory Service HTTP server is running
  2. If found → Use HTTP client to connect to existing server
  3. If not found and port available → Auto-start HTTP server (optional)
  4. If port busy → Fall back to direct SQLite with WAL mode

Custom SQLite Pragmas

You can customize SQLite behavior using environment variables:

# Recommended configuration (v8.9.0+) - For concurrent HTTP + MCP access
export MCP_MEMORY_SQLITE_PRAGMAS="busy_timeout=15000,cache_size=20000"

# Example configurations for different scenarios:
# High concurrency setup (longer timeout)
export MCP_MEMORY_SQLITE_PRAGMAS="busy_timeout=30000,cache_size=20000,wal_autocheckpoint=1000"

# Performance optimized (use with caution - trades safety for speed)
export MCP_MEMORY_SQLITE_PRAGMAS="synchronous=NORMAL,temp_store=MEMORY,cache_size=50000,busy_timeout=15000"

# Conservative/safe mode (maximum data safety)
export MCP_MEMORY_SQLITE_PRAGMAS="synchronous=FULL,busy_timeout=60000,cache_size=20000"

HTTP Coordination Configuration

Enable automatic HTTP server coordination for optimal multi-client access:

# Enable HTTP server auto-start
export MCP_HTTP_ENABLED=true

# Configure HTTP server settings (optional)
export MCP_HTTP_PORT=8000
export MCP_HTTP_HOST=localhost

# Combine with SQLite-vec backend
export MCP_MEMORY_STORAGE_BACKEND=sqlite_vec

Coordination Modes Explained:

  1. Automatic Mode (Recommended)

    # No configuration needed - auto-detects best mode
export MCP_MEMORY_STORAGE_BACKEND=sqlite_vec

  2. Forced HTTP Client Mode

    # Always connect to existing server (fails if none running)
export MCP_MEMORY_STORAGE_BACKEND=sqlite_vec
export MCP_HTTP_ENABLED=false
# Requires running: memory launch

  3. Direct WAL Mode Only

    # Disable HTTP coordination entirely
export MCP_MEMORY_STORAGE_BACKEND=sqlite_vec
export MCP_HTTP_ENABLED=false
export MCP_HTTP_ENABLED=false

Multi-Client Claude Desktop Configuration

Option 1: Automatic Coordination (Recommended)

{
  "mcpServers": {
    "memory": {
      "command": "uv",
      "args": ["--directory", "/path/to/mcp-memory-service", "run", "memory"],
      "env": {
        "MCP_MEMORY_STORAGE_BACKEND": "sqlite_vec",
        "MCP_HTTP_ENABLED": "true"
      }
    }
  }
}

Option 2: Manual HTTP Server + Client Mode

{
  "mcpServers": {
    "memory": {
      "command": "uv", 
      "args": ["--directory", "/path/to/mcp-memory-service", "run", "memory"],
      "env": {
        "MCP_MEMORY_STORAGE_BACKEND": "sqlite_vec",
        "MCP_HTTP_ENABLED": "false"
      }
    }
  }
}

Note: Requires manually running memory launch first

Option 3: WAL Mode Only (Simple)

{
  "mcpServers": {
    "memory": {
      "command": "uv",
      "args": ["--directory", "/path/to/mcp-memory-service", "run", "memory"],
      "env": {
        "MCP_MEMORY_STORAGE_BACKEND": "sqlite_vec",
        "MCP_MEMORY_SQLITE_PRAGMAS": "busy_timeout=10000"
      }
    }
  }
}

Database Optimization

# Optimize database periodically
python -c "
import asyncio
from src.mcp_memory_service.storage.sqlite_vec import SqliteVecMemoryStorage

async def optimize():
    storage = SqliteVecMemoryStorage('path/to/db')
    await storage.initialize()
    
    # Clean up duplicates
    count, msg = await storage.cleanup_duplicates()
    print(f'Cleaned up {count} duplicates')
    
    # Vacuum database
    storage.conn.execute('VACUUM')
    print('Database vacuumed')
    
    storage.close()

asyncio.run(optimize())
"

Backup and Restore

# Create backup
python scripts/migrate_storage.py \
  --from sqlite_vec \
  --to sqlite_vec \
  --source-path memory.db \
  --target-path backup.db

# Or simple file copy
cp memory.db memory_backup.db

# Restore from JSON backup
python scripts/migrate_storage.py \
  --restore backup.json \
  --to sqlite_vec \
  --target-path restored_memory.db

Troubleshooting

Common Issues

1. sqlite-vec Not Found

ImportError: No module named 'sqlite_vec'

Solution: Install sqlite-vec package

pip install sqlite-vec
# or
uv add sqlite-vec

2. Database Lock Errors

sqlite3.OperationalError: database is locked

✅ Fixed in v8.9.0 - Proper SQLite pragmas now automatically configured by installer

For Single Client Issues:

# Kill existing processes
pkill -f "mcp-memory-service"
# Restart Claude Desktop

For Multi-Client Setup (Claude Desktop + Claude Code + HTTP Server):

# v8.9.0+ Solution: Configure recommended pragma values
export MCP_MEMORY_SQLITE_PRAGMAS="busy_timeout=15000,cache_size=20000"

# Restart all services to apply changes
# Note: Installer automatically sets these for hybrid/sqlite_vec backends

# If issues persist, try longer timeout:
export MCP_MEMORY_SQLITE_PRAGMAS="busy_timeout=30000,cache_size=20000"

# Check for stale lock files (rare)
ls -la /path/to/your/database-wal
ls -la /path/to/your/database-shm

# If stale locks exist (no active processes), remove them
rm /path/to/your/database-wal
rm /path/to/your/database-shm

# 4. Restart all MCP clients

Prevention Tips:

  • Always use WAL mode (enabled by default)
  • Configure appropriate busy timeouts for your use case
  • Ensure proper shutdown of MCP clients
  • Use connection retry logic (built-in)

5. HTTP Coordination Issues

Failed to initialize HTTP client storage: Connection refused

Solutions:

Auto-Detection Problems:

# Check if HTTP server auto-start is working
export LOG_LEVEL=DEBUG
export MCP_HTTP_ENABLED=true

# Check coordination mode detection
python -c "
import asyncio
from src.mcp_memory_service.utils.port_detection import detect_server_coordination_mode
print(asyncio.run(detect_server_coordination_mode()))
"

Manual HTTP Server Setup:

# Start HTTP server manually in separate terminal
memory launch

# Then start MCP clients (they'll auto-detect the running server)

Port Conflicts:

# Check what's using the port
netstat -an | grep :8000  # Linux/macOS
netstat -an | findstr :8000  # Windows

# Use different port
export MCP_HTTP_PORT=8001

Fallback to WAL Mode:

# Force WAL mode if HTTP coordination fails
export MCP_HTTP_ENABLED=false
export MCP_HTTP_ENABLED=false

3. Permission Errors

PermissionError: [Errno 13] Permission denied

Solution: Check database file permissions

# Fix permissions
chmod 644 /path/to/sqlite_vec.db
chmod 755 /path/to/directory

4. Migration Failures

Migration failed: No memories found

Solution: Verify source path and initialize if needed

# Check source exists
ls -la /path/to/chroma_db
# Use absolute paths in migration

Debug Mode

Enable debug logging for troubleshooting:

export LOG_LEVEL=DEBUG
export DEBUG_MODE=1
# Run your MCP client

Health Checks

# Check backend health
import asyncio
from src.mcp_memory_service.storage.sqlite_vec import SqliteVecMemoryStorage

async def health_check():
    storage = SqliteVecMemoryStorage('path/to/db')
    await storage.initialize()
    
    stats = storage.get_stats()
    print(f"Backend: {stats['backend']}")
    print(f"Total memories: {stats['total_memories']}")
    print(f"Database size: {stats['database_size_mb']} MB")
    print(f"Embedding model: {stats['embedding_model']}")
    
    storage.close()

asyncio.run(health_check())

Comparison: SQLite-vec vs Cloudflare vs Hybrid

Feature SQLite-vec Cloudflare Hybrid
Read latency ~5 ms (local) Network-dependent ~5 ms (local)
Setup complexity Low Medium (API tokens, D1 + Vectorize) Medium
Multi-device sync ❌ ✅ ✅ (background sync)
Offline access ✅ ❌ ✅
External dependencies None Cloudflare account Cloudflare account
Recommended for Single-user / dev Edge / cloud-only Production

See guides/STORAGE_BACKENDS.md for the full comparison.

Best Practices

When to Use SQLite-vec

✅ Use SQLite-vec when:

  • Single-user or development setup
  • Multi-client access on the same machine (Claude Desktop + Claude Code)
  • Offline / air-gapped environments
  • Portability and backup simplicity are important
  • Limited system resources

For production with multi-device sync, use the Hybrid backend instead (local SQLite-vec for reads + background Cloudflare sync).

Multi-Client Coordination Tips

  1. Automatic Mode (Recommended)

    # Let the system choose the best coordination method
export MCP_MEMORY_STORAGE_BACKEND=sqlite_vec
export MCP_HTTP_ENABLED=true

  2. Monitoring Coordination Mode

    # Check which mode is being used
export LOG_LEVEL=INFO
# Look for "Detected coordination mode: ..." in logs

  3. HTTP Server Management

    # Manual server control
memory launch  # Start manually

# Check server health
curl http://localhost:8000/health

  4. Fallback Strategy

    # If HTTP coordination fails, system falls back to WAL mode
# No manual intervention needed - fully automatic

Performance Tips

  1. Regular Optimization

    # Run monthly
python scripts/optimize_sqlite_vec.py

  2. Batch Operations

    # Store memories in batches for better performance
for batch in chunk_memories(all_memories, 100):
    for memory in batch:
        await storage.store(memory)

  3. Index Maintenance

    -- Rebuild indexes periodically
REINDEX;
VACUUM;

API Reference

The sqlite-vec backend implements the standard BaseStorage interface shared by all backends:

# All standard operations work identically
await storage.store(memory)
results = await storage.retrieve(query, n_results=5)
memories = await storage.search_by_tag(["tag1", "tag2"])
success, msg = await storage.delete(content_hash)
success, msg = await storage.update_memory_metadata(hash, updates)

See the main API documentation for complete method signatures.

Contributing

To contribute to sqlite-vec backend development:

  1. Run tests: pytest tests/test_sqlite_vec_storage.py
  2. Check performance: python tests/performance/test_sqlite_vec_perf.py
  3. Add features following the MemoryStorage interface
  4. Update this documentation

Support

For sqlite-vec backend issues:

  1. Check sqlite-vec documentation
  2. Review this guide's troubleshooting section
  3. Open an issue on the MCP Memory Service repository