# 🗄️ MCP SQLite Server

> Query, explore, and manage **SQLite databases** through the **Model Context Protocol**. Connect any MCP-compatible AI client to your databases — Claude Desktop, Cursor, VS Code, and more.

[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](LICENSE)
[![Python 3.10+](https://img.shields.io/badge/python-3.10+-blue.svg)](https://www.python.org/downloads/)

---

## ✨ Features

- **🔍 3 Tools**: `query`, `execute`, `list_tables` — full database interaction
- **📄 2+ Resources**: `sqlite://tables` (full schema), `sqlite://{table}/schema` (per-table)
- **💬 1 Prompt**: `database_analyst(question)` — guided analysis workflow
- **🔒 Security-First**: Read-only by default, explicit `--allow-write` flag for mutations
- **📊 WAL Mode**: Write-Ahead Logging for better concurrent access
- **🖥️ Dual Transport**: stdio (local) and Streamable HTTP (remote)
- **🎲 Sample Data**: Seed script included for instant testing

---

## 🚀 Quick Start

```bash
# 1. Setup
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

# 2. Create sample database
python seed_data.py --db-path /tmp/sample.db

# 3. Start server (read-only mode)
python sqlite_server.py --db-path /tmp/sample.db
```

---

## 🛠️ Tools

### `query(sql: str) -> str`
Execute a SELECT query. **Only SELECT and PRAGMA** statements are allowed.

```
Example: query("SELECT name, price FROM products WHERE category = 'Electronics'")
```

### `execute(sql: str) -> str`
Run INSERT, UPDATE, or DELETE. **Requires `--allow-write` flag.**

```
Example (with --allow-write):
  execute("INSERT INTO products (name, price, category, stock) VALUES ('Webcam 4K', 129.99, 'Electronics', 50)")
```

### `list_tables() -> str`
List all tables with row counts.

---

## 🔒 Security Model

| Feature | Default | With `--allow-write` |
|---------|---------|---------------------|
| SELECT/PRAGMA queries | ✅ Allowed | ✅ Allowed |
| INSERT/UPDATE/DELETE | ❌ Blocked | ✅ Allowed |
| DROP/ALTER/CREATE | ❌ Blocked | ❌ Blocked |
| Row factory protection | ✅ Enabled | ✅ Enabled |

> ⚠️ **Production tip**: Always use read-only mode for public-facing or shared databases. Only enable `--allow-write` in trusted environments.

---

## 📄 Resources

### `sqlite://tables`
Returns complete database schema with column types, nullability, defaults, and primary keys.

### `sqlite://{table}/schema`
Returns schema for a specific table.

---

## 💬 Prompts

### `database_analyst(query_question: str)`
Creates a structured prompt for analyzing database data.

---

## 🔌 Connecting to Clients

### Claude Desktop

```json
{
  "mcpServers": {
    "sqlite": {
      "command": "python",
      "args": ["/ABSOLUTE/PATH/mcp-sqlite-server/sqlite_server.py", "--db-path", "/tmp/sample.db"]
    }
  }
}
```

### Cursor

Settings → Features → MCP → Add Server:
- **Name**: `sqlite`
- **Type**: `command`
- **Command**: `python /ABSOLUTE/PATH/mcp-sqlite-server/sqlite_server.py --db-path /tmp/sample.db`

---

## 📁 Project Structure

```
mcp-sqlite-server/
├── sqlite_server.py      # Main server (FastMCP)
├── seed_data.py          # Sample database creator
├── requirements.txt
├── setup.sh
├── README.md
└── .gitignore
```

---

## 📜 License

MIT