Core Database Tools

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", "[email protected]", "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

• Security-and-Data-Integrity - Parameter binding and security features
• JSON-Helper-Tools - Specialized JSON operations
• Best-Practices - Usage patterns and recommendations
• Troubleshooting - Common issues and solutions

---

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