Skip to content

Best Practices

Jump to bottom
Temp edited this page Oct 4, 2025 · 2 revisions

Best Practices

Last Updated: October 4, 2025 7:00 AM EST

Essential usage patterns and recommendations for optimal SQLite MCP Server performance and security.

📋 Standard Query Workflow

1. Explore Schema First

Start with list_tables to identify available tables:

list_tables()

2. Verify Table Structure

For each relevant table, use describe_table to verify exact schema:

describe_table({"table_name": "users"})

3. Construct Precise Queries

Based on verified schema, construct appropriate queries using exact column names:

read_query({
  "query": "SELECT id, name, email FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 5"
})

4. Use Wildcards for Search

When searching for specific content, use LIKE with wildcards (%) to increase match probability:

read_query({
  "query": "SELECT id, project_type, description FROM projects WHERE description LIKE '%keyword%' ORDER BY last_updated DESC LIMIT 5"
})

5. Leverage JSON Helper Tools

For JSON operations, use the new v2.6.0 JSON Helper Tools for simplicity and safety:

// ✅ RECOMMENDED: Use JSON Helper Tools
json_insert({
  "table": "products",
  "column": "metadata",
  "data": {"category": "electronics", "tags": ["new", "popular"]}
})

// vs. raw SQL (more complex)
write_query({
  "query": "INSERT INTO products (metadata) VALUES (?)",
  "params": [{"category": "electronics", "tags": ["new", "popular"]}]
})

🛡️ Security Best Practices

Always Use Parameter Binding

// ✅ SECURE: Parameter binding prevents injection
read_query({
  "query": "SELECT * FROM users WHERE username = ? AND role = ?",
  "params": ["john_doe", "admin"]
})

// ❌ VULNERABLE: String concatenation
read_query({
  "query": `SELECT * FROM users WHERE username = '${username}'`
})

JSON Security Validation

// Validate user-supplied JSON before storage
json_validate_security({
  "json_data": userProvidedJSON,
  "check_injection": true,
  "check_xss": true,
  "max_depth": 10
})

Regular Security Testing

# Run comprehensive security tests
python test_runner.py --security

# Test SQL injection protection specifically
cd tests && python test_sql_injection.py

🗃️ SQLite-Specific Best Practices

Data Types and Schema Design

  • Use SQLite-style PRIMARY KEY: INTEGER PRIMARY KEY not AUTO_INCREMENT
  • Use TEXT for strings: SQLite uses TEXT instead of VARCHAR
  • JSON storage is automatic: Direct JSON strings are automatically stored efficiently
  • Use proper date functions: SQLite date functions differ from MySQL
  • No enum type: Use CHECK constraints instead of ENUM
  • Pagination syntax: Use LIMIT x OFFSET y syntax

Example Schema

create_table({
  "query": `CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK (price > 0),
    category TEXT CHECK (category IN ('electronics', 'books', 'clothing')),
    metadata TEXT,  -- JSON data stored as TEXT
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )`
})

🎯 JSON Operations Best Practices

Use JSON Helper Tools (v2.6.0+)

// ✅ RECOMMENDED: JSON Helper Tools
json_insert({
  "table": "products",
  "column": "details",
  "data": {
    "name": "Gaming Laptop",
    "specs": {"cpu": "Intel i9", "ram": "32GB"},
    "features": ["RGB Keyboard", "144Hz Display"]
  }
})

// Update specific nested values
json_update({
  "table": "products",
  "column": "details",
  "path": "$.specs.ram",
  "value": "64GB",
  "where_clause": "id = 123"
})

Legacy JSON Operations (Still Supported)

// Insert JSON with automatic serialization
write_query({
  "query": "INSERT INTO products (name, details, metadata) VALUES (?, ?, ?)",
  "params": ["Product A", "High-quality item", {"category": "electronics", "tags": ["new", "popular"]}]
})

// Extract JSON values
read_query({
  "query": "SELECT json_extract(metadata, '$.tags[0]') FROM products WHERE name = ?",
  "params": ["Product A"]
})

// Update nested JSON values
write_query({
  "query": "UPDATE products SET metadata = json_set(metadata, '$.category', ?) WHERE id = ?",
  "params": ["updated_category", 123]
})

🚀 Performance Optimization

Database Maintenance

// Regular maintenance for optimal performance
vacuum_database()      // Reclaim unused space
analyze_database()     // Update query statistics
integrity_check()      // Verify database integrity

Index Usage

// Monitor index effectiveness
index_usage_stats()

// Create indexes for frequently queried columns
write_query({
  "query": "CREATE INDEX idx_users_status ON users(status)"
})

// Create indexes for JSON paths (SQLite 3.38+)
write_query({
  "query": "CREATE INDEX idx_metadata_category ON products(json_extract(metadata, '$.category'))"
})

Query Optimization

// Use EXPLAIN QUERY PLAN to optimize queries
read_query({
  "query": "EXPLAIN QUERY PLAN SELECT * FROM products WHERE json_extract(metadata, '$.category') = ?",
  "params": ["electronics"]
})

📊 Business Intelligence Workflow

Capture Insights

// Document discoveries and insights
append_insight({
  "insight": "Customer retention improved 15% after implementing the new onboarding flow. JSON metadata analysis shows users with complete profiles are 3x more likely to convert."
})

Statistical Analysis

// Use built-in statistical tools
descriptive_statistics({
  "table_name": "sales_data",
  "column_name": "revenue",
  "where_clause": "date >= '2025-01-01'"
})

// Analyze trends
moving_averages({
  "table_name": "daily_sales",
  "value_column": "revenue",
  "time_column": "date",
  "window_sizes": [7, 30, 90]
})

🔍 Advanced Search Patterns

Full-Text Search

// Create FTS5 table for advanced text search
create_fts_table({
  "table_name": "documents_fts",
  "columns": ["title", "content", "category"],
  "content_table": "documents"
})

// Perform ranked search
fts_search({
  "table_name": "documents_fts",
  "query": "database optimization",
  "limit": 10,
  "snippet_length": 50
})

Semantic Search

// Create embeddings table
create_embeddings_table({
  "table_name": "document_embeddings",
  "embedding_dim": 1536
})

// Perform semantic similarity search
semantic_search({
  "table_name": "document_embeddings",
  "query_embedding": your_query_vector,
  "limit": 10,
  "similarity_threshold": 0.7
})

🧪 Testing and Validation

Comprehensive Testing

All 73 tools have been verified through comprehensive MCP testing ✅

# Quick smoke test
python test_runner.py --quick

# Standard comprehensive test (recommended)
python test_runner.py --standard

# Full test suite with edge cases
python test_runner.py --full

# Test specific categories
python test_runner.py --json        # JSON helper tools
python test_runner.py --security    # Security features

Code Quality:

  • ✅ Passes strict Pyright type checking in Cursor
  • ✅ All 73 tools tested and verified via live MCP calls
  • ✅ SQL injection protection validated
  • ✅ Parameter binding security confirmed

Data Validation

// Validate JSON before insertion
json_validate_security({
  "json_data": userInput,
  "check_injection": true,
  "check_xss": true
})

// Check database integrity regularly
integrity_check()

// Verify backups
verify_backup({
  "backup_path": "./backups/database_2025-09-23.db"
})

💡 Common Patterns

E-commerce Product Catalog

// Product with rich metadata
json_insert({
  "table": "products",
  "column": "details",
  "data": {
    "name": "Gaming Laptop",
    "brand": "TechCorp",
    "specs": {"cpu": "Intel i9", "gpu": "RTX 4080", "ram": "32GB"},
    "features": ["RGB Keyboard", "144Hz Display"],
    "pricing": {"base": 2499.99, "discount": 0.1}
  }
})

// Query products by specifications
json_query_complex({
  "table": "products",
  "column": "details",
  "filters": [
    {"path": "$.specs.ram", "operator": ">=", "value": "32GB"},
    {"path": "$.pricing.base", "operator": "<=", "value": 3000}
  ],
  "logic": "AND"
})

User Analytics

// Track user behavior
json_insert({
  "table": "user_events",
  "column": "event_data",
  "data": {
    "user_id": 12345,
    "event": "page_view",
    "page": "/products/gaming-laptop",
    "timestamp": "2025-09-23T13:48:00Z",
    "metadata": {"referrer": "google", "device": "mobile"}
  }
})

// Analyze user patterns
read_query({
  "query": `SELECT 
    json_extract(event_data, '$.user_id') as user_id,
    COUNT(*) as page_views,
    json_extract(event_data, '$.metadata.device') as device
  FROM user_events 
  WHERE json_extract(event_data, '$.event') = 'page_view'
  GROUP BY user_id, device`
})

📚 Related Pages

📝 Remember: These best practices ensure optimal performance, security, and maintainability. Start with the basics and gradually adopt advanced features as your needs grow.

Clone this wiki locally