SETUP_GUIDE.md•8.38 kB
# SQLite MCP Server - Setup Guide
Complete step-by-step guide to set up and configure the SQLite MCP server.
## Prerequisites
- Python 3.8 or higher
- pip package manager
- Git (optional, for cloning)
## Installation Steps
### 1. Clone or Navigate to Project
```bash
# If cloning from repository
git clone <repository-url>
cd sqlite-mcp
# Or if you already have the directory
cd /path/to/sqlite-mcp
```
### 2. Create Virtual Environment (Recommended)
```bash
# On macOS/Linux
python3 -m venv venv
source venv/bin/activate
# On Windows
python -m venv venv
venv\Scripts\activate
```
### 3. Install Dependencies
```bash
pip install -r requirements.txt
```
Verify installation:
```bash
pip list | grep -E "fastmcp|pydantic"
```
### 4. Test the Server
```bash
# Start the server
python -m sqlite_mcp.server
# You should see: "SQLite MCP server started"
# Press Ctrl+C to stop
```
## Configuration for LLM Clients
### Configuration for Claude Desktop
1. **Find the config file:**
- **macOS**: `~/.config/Claude/claude_desktop_config.json`
- **Windows**: `%APPDATA%\Claude\claude_desktop_config.json`
- **Linux**: `~/.config/Claude/claude_desktop_config.json`
2. **Edit the config file** (create if it doesn't exist):
```json
{
"mcpServers": {
"sqlite-mcp": {
"command": "python",
"args": ["-m", "sqlite_mcp.server"],
"cwd": "/absolute/path/to/sqlite-mcp"
}
}
}
```
3. **Restart Claude Desktop** for the changes to take effect
4. **Verify Integration:**
- Open Claude Desktop
- In a conversation, you should see tool options for SQLite operations
- Try calling a tool like `open_database`
### Configuration for Cline (VS Code Extension)
1. **Install Cline extension** if not already installed
2. **Add to Cline settings** (`.cline/settings.json`):
```json
{
"mcp_servers": [
{
"name": "sqlite-mcp",
"command": "python",
"args": ["-m", "sqlite_mcp.server"],
"cwd": "/absolute/path/to/sqlite-mcp"
}
]
}
```
3. **Restart VS Code**
### Configuration for Other MCP Clients
For other MCP-compatible clients, the general pattern is:
```yaml
mcpServers:
sqlite-mcp:
command: python
args:
- -m
- sqlite_mcp.server
cwd: /absolute/path/to/sqlite-mcp
```
## Database File Location
### Default Behavior
- Databases are created in the path specified by `open_database` tool
- Use relative paths for databases in the project directory:
```
open_database("/data/app.db") # Creates in /data/ directory
```
- Use absolute paths for databases outside the project:
```
open_database("/home/user/databases/app.db")
```
### Recommended Structure
```
sqlite-mcp/
├── sqlite_mcp/
├── data/ # Store database files here
│ ├── development.db
│ ├── testing.db
│ └── production.db
├── requirements.txt
├── README.md
└── setup_guide.md
```
Create the data directory:
```bash
mkdir -p ./data
```
## Development Setup
### Running in Development Mode
```bash
# Using nodemon for auto-restart (requires Node.js)
npm install -g nodemon
# Run with auto-restart
nodemon --exec python -m sqlite_mcp.server
# Or run directly
python -m sqlite_mcp.server
```
### Debugging
Enable debug mode (modify `sqlite_mcp/server.py`):
```python
import logging
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)
# Then use in functions:
logger.debug(f"Database operation: {query}")
```
### Running Tests
Create a test file `test_server.py`:
```python
"""Test the SQLite MCP server."""
import sqlite3
from sqlite_mcp.db import SQLiteDatabase
def test_basic_operations():
"""Test basic database operations."""
db = SQLiteDatabase()
# Test open
result = db.open("/tmp/test.db")
assert "opened successfully" in result
# Test create table
result = db.create_table("test", "id INTEGER PRIMARY KEY, name TEXT")
assert "created successfully" in result
# Test insert
result = db.insert("test", {"name": "Test Item"})
assert result["changes"] == 1
# Test query
result = db.execute_query("SELECT * FROM test")
assert len(result["rows"]) == 1
# Test close
result = db.close()
assert "closed successfully" in result
print("All tests passed!")
if __name__ == "__main__":
test_basic_operations()
```
Run tests:
```bash
python test_server.py
```
## Troubleshooting
### Issue: "ModuleNotFoundError: No module named 'fastmcp'"
**Solution:**
```bash
# Make sure you're in the virtual environment
source venv/bin/activate # macOS/Linux
# or
venv\Scripts\activate # Windows
# Then install dependencies
pip install -r requirements.txt
```
### Issue: "Port already in use" (if using HTTP transport)
**Solution:**
```bash
# Find and kill the process using the port
lsof -i :8000 # macOS/Linux
netstat -ano | findstr :8000 # Windows
# Or use a different port (modify server configuration)
```
### Issue: "Permission denied" when opening database
**Solution:**
```bash
# Check file permissions
ls -la /path/to/database.db
# Fix permissions (macOS/Linux)
chmod 644 /path/to/database.db
# Or create in a writable directory
open_database("/tmp/app.db")
```
### Issue: "No database is open" error
**Solution:**
1. Always call `open_database` before any query
2. Check if database path is correct
3. Ensure database file exists or directory is writable
### Issue: Server not responding to MCP client
**Solution:**
1. Verify server is running: `python -m sqlite_mcp.server`
2. Check configuration file syntax
3. Verify absolute path to sqlite-mcp directory
4. Restart the MCP client (Claude Desktop, etc.)
5. Check system logs for errors
## Environment Variables (Optional)
Create a `.env` file for configuration:
```bash
# Database directory
DB_PATH=/path/to/databases
# Logging level
LOG_LEVEL=INFO
# Server settings
SERVER_TIMEOUT=30
```
Load in server (requires `python-dotenv`):
```python
from dotenv import load_dotenv
import os
load_dotenv()
db_path = os.getenv("DB_PATH", "./data")
log_level = os.getenv("LOG_LEVEL", "INFO")
```
## Performance Optimization
### For Large Databases
1. **Add Indexes:**
```sql
CREATE INDEX idx_column ON table(column)
```
2. **Limit Result Sets:**
```sql
SELECT * FROM table LIMIT 100 OFFSET 0
```
3. **Use Proper Data Types:**
- Use INTEGER for IDs
- Use TEXT for strings
- Use REAL for decimals
- Use BOOLEAN for true/false
### Connection Pooling (Advanced)
For high-concurrency scenarios, modify `db.py` to use connection pooling:
```python
from queue import Queue
class DatabasePool:
def __init__(self, size=5):
self.pool = Queue(maxsize=size)
for _ in range(size):
self.pool.put(sqlite3.connect(':memory:'))
def get_connection(self):
return self.pool.get()
def return_connection(self, conn):
self.pool.put(conn)
```
## Upgrading
To upgrade to a newer version:
```bash
# Pull latest changes
git pull origin main
# Update dependencies
pip install --upgrade -r requirements.txt
# Restart the server
```
## Uninstalling
To completely uninstall:
```bash
# Deactivate virtual environment
deactivate
# Remove virtual environment
rm -rf venv
# Remove project directory (optional)
rm -rf sqlite-mcp
```
## Next Steps
1. Read [README.md](README.md) for available tools
2. Check [EXAMPLES.md](EXAMPLES.md) for usage examples
3. Start using with your LLM agent
4. Customize for your specific use case
## Support
For issues or questions:
1. Check the [Troubleshooting](#troubleshooting) section
2. Review error messages carefully
3. Check MCP client logs
4. Verify database file accessibility
## Security Best Practices
1. **Restrict Database Access:**
```bash
chmod 600 ./data/*.db # Only owner can read/write
```
2. **Use Parameterized Queries:**
- Always use the `parameters` field
- Never concatenate user input into queries
3. **Validate Input:**
- Agent should validate table names before sending
- Use whitelists for known tables
4. **Backup Databases:**
```bash
cp ./data/app.db ./data/app.db.backup
```
5. **Never Expose to Untrusted Networks:**
- Keep MCP server local to the machine
- Don't expose to public internet