Skip to content

Core Database Tools

Jump to bottom
Temp edited this page Sep 23, 2025 · 1 revision

Core Database Tools

Last Updated: September 23, 2025 1:48 PM EST

The core database tools provide essential CRUD operations, schema management, and database administration functionality. These 15 tools form the foundation of the SQLite MCP Server.

🔧 Available Tools

Tool Description
read_query Execute SELECT queries on the SQLite database
write_query Execute INSERT, UPDATE, or DELETE queries
create_table Create new tables in the database
list_tables List all tables in the database
describe_table Get schema information for a specific table
append_insight Add business insights to the memo
vacuum_database Optimize database by reclaiming unused space
analyze_database Update database statistics for query optimization
integrity_check Check database integrity and report corruption
database_stats Get database performance and usage statistics
index_usage_stats Get index usage statistics for optimization
backup_database Create database backups to files
restore_database Restore database from backup files
verify_backup Verify integrity of backup files
pragma_settings Get/set SQLite PRAGMA configuration settings

🚀 Quick Examples

Basic Query Operations

Reading Data:

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

Writing Data:

write_query({
  "query": "INSERT INTO users (name, email, status) VALUES (?, ?, ?)",
  "params": ["John Doe", "john@example.com", "active"]
})

Creating Tables:

create_table({
  "query": "CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, category TEXT)"
})

Schema Management

List All Tables:

list_tables()

Describe Table Structure:

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

Database Maintenance

Optimize Database:

vacuum_database()

Update Statistics:

analyze_database()

Check Integrity:

integrity_check()

🛡️ Security Features

All core database tools support parameter binding to prevent SQL injection attacks:

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

// ✅ SECURE: Write operations with parameters  
write_query({
  "query": "INSERT INTO products (name, price, category) VALUES (?, ?, ?)",
  "params": ["Laptop", 999.99, "electronics"]
})

Security Benefits:

  • 🛡️ SQL Injection Prevention: Malicious input treated as literal data, not executable code
  • 🔄 Backward Compatible: Existing queries without params continue to work
  • ⚡ Performance: Query plan caching and optimization
  • 🎯 Automatic JSON Handling: Dict/list objects automatically serialized to JSON
  • 📝 Best Practice: Follows secure coding standards

📊 Database Administration

Performance Monitoring

Database Statistics:

database_stats()
// Returns: database size, page count, table statistics, performance metrics

Index Usage Analysis:

index_usage_stats()
// Returns: index efficiency, usage patterns, optimization recommendations

Business Intelligence

Capture Insights:

append_insight({
  "insight": "Customer retention improved 15% after implementing the new onboarding flow"
})

The insights are stored in a persistent memo that can be accessed via the memo://insights MCP resource.

🔍 Best Practices

Standard Query Workflow

  1. Start with list_tables to identify available tables
  2. Use describe_table to verify exact schema
  3. Construct queries using exact column names
  4. Use parameter binding for dynamic queries
  5. Use LIKE with wildcards (%) to increase match probability

SQLite-Specific Query Structure

  • 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
  • Use LIMIT with OFFSET: LIMIT x OFFSET y syntax

Example Workflow

// 1. Explore schema
list_tables()

// 2. Understand table structure
describe_table({"table_name": "users"})

// 3. Query data with parameters
read_query({
  "query": "SELECT id, name, email FROM users WHERE status = ? LIMIT ?",
  "params": ["active", 10]
})

// 4. Update data safely
write_query({
  "query": "UPDATE users SET last_login = ? WHERE id = ?",
  "params": ["2025-09-23 13:48:00", 123]
})

🚀 Advanced Features

Transaction Safety

All write operations are automatically wrapped in transactions with proper rollback on errors:

  • Automatic Transactions: Every write operation is wrapped in a transaction
  • Error Rollback: Failed operations automatically roll back changes
  • Data Integrity: Ensures database consistency even during failures
  • Zero Configuration: Works automatically without setup

Foreign Key Enforcement

Automatic enforcement of foreign key constraints across all connections:

  • Referential Integrity: Ensures data relationships remain valid
  • Cascade Operations: Supports CASCADE, RESTRICT, SET NULL operations
  • Cross-Connection Consistency: Enforced across all database connections
  • PRAGMA Support: Uses SQLite's foreign_keys pragma for enforcement

📚 Related Pages

📝 Note: These core tools provide the foundation for all database operations. For specialized functionality, explore the advanced feature pages listed above.

Clone this wiki locally