# SQLite MCP Server

A Model Context Protocol (MCP) server for SQLite database operations, built with FastMCP. This server allows LLM agents to read, create, update, and delete data in SQLite databases.

## Features

- **Database Management**: Open/close SQLite databases
- **CRUD Operations**: Create tables, insert, read, update, and delete records
- **Query Execution**: Execute raw SQL SELECT queries
- **Schema Inspection**: List tables and view table schemas
- **Type-Safe**: Full type hints and error handling

## Installation

### Prerequisites
- Python 3.8 or higher
- pip

### Setup

1. Clone or navigate to the project directory:
```bash
cd sqlite-mcp
```

2. Create a virtual environment (recommended):
```bash
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate
```

3. Install dependencies:
```bash
pip install -r requirements.txt
```

## Quick Start

### Running the Server

```bash
# Using the npm script
npm start

# Or directly with Python
python -m sqlite_mcp.server

# Or with uvicorn (if using HTTP transport)
uvicorn sqlite_mcp.server:mcp --reload
```

## Available Tools

### 1. **open_database**
Opens or creates a SQLite database file.

**Parameters:**
- `path` (string): Path to the SQLite database file

**Example:**
```json
{
  "path": "/path/to/my_database.db"
}
```

### 2. **close_database**
Closes the current database connection.

**Example:**
```json
{}
```

### 3. **execute_query**
Execute a SELECT query and return results.

**Parameters:**
- `query` (string): SQL SELECT query
- `parameters` (array, optional): Query parameters for prepared statements

**Example:**
```json
{
  "query": "SELECT * FROM users WHERE age > ?",
  "parameters": [18]
}
```

### 4. **create_table**
Create a new table in the database.

**Parameters:**
- `table` (string): Table name
- `schema` (string): Column definitions

**Example:**
```json
{
  "table": "users",
  "schema": "id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, age INTEGER"
}
```

### 5. **insert**
Insert a row into a table.

**Parameters:**
- `table` (string): Table name
- `data` (object): Column names and values

**Example:**
```json
{
  "table": "users",
  "data": {
    "name": "John Doe",
    "email": "john@example.com",
    "age": 30
  }
}
```

### 6. **update**
Update rows in a table.

**Parameters:**
- `table` (string): Table name
- `data` (object): Column names and new values
- `where` (string): WHERE clause condition
- `where_params` (array, optional): Parameters for WHERE clause

**Example:**
```json
{
  "table": "users",
  "data": {
    "age": 31
  },
  "where": "id = ?",
  "where_params": [1]
}
```

### 7. **delete**
Delete rows from a table.

**Parameters:**
- `table` (string): Table name
- `where` (string): WHERE clause condition
- `where_params` (array, optional): Parameters for WHERE clause

**Example:**
```json
{
  "table": "users",
  "where": "id = ?",
  "where_params": [1]
}
```

### 8. **list_tables**
List all tables in the database.

**Example:**
```json
{}
```

**Returns:**
```json
{
  "tables": ["users", "products", "orders"]
}
```

### 9. **get_table_schema**
Get the schema of a table (columns, types, constraints).

**Parameters:**
- `table` (string): Table name

**Example:**
```json
{
  "table": "users"
}
```

**Returns:**
```json
{
  "columns": [
    {
      "cid": 0,
      "name": "id",
      "type": "INTEGER",
      "notnull": 0,
      "dflt_value": null,
      "pk": 1
    },
    {
      "cid": 1,
      "name": "name",
      "type": "TEXT",
      "notnull": 1,
      "dflt_value": null,
      "pk": 0
    }
  ]
}
```

## Usage Examples

### Example 1: Create a Database and Table

```bash
# Open database
call open_database with path="/tmp/myapp.db"

# Create a users table
call create_table with table="users" schema="id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, age INTEGER"

# List tables
call list_tables with no parameters
```

### Example 2: Insert and Query Data

```bash
# Insert a user
call insert with table="users" data={"name": "Alice Johnson", "email": "alice@example.com", "age": 28}

# Query users
call execute_query with query="SELECT * FROM users WHERE age >= ?" parameters=[25]
```

### Example 3: Update Records

```bash
# Update user's age
call update with table="users" data={"age": 29} where="name = ?" where_params=["Alice Johnson"]

# Verify update
call execute_query with query="SELECT * FROM users WHERE name = ?" parameters=["Alice Johnson"]
```

### Example 4: Delete Records

```bash
# Delete a user
call delete with table="users" where="id = ?" where_params=[1]

# List remaining users
call execute_query with query="SELECT * FROM users"
```

## Integration with LLM Agents

This MCP server is designed to be used with LLM agents. When configured properly, the agent can:

1. Create databases and tables
2. Insert, update, and delete records
3. Query data
4. Inspect database schemas

### Example Agent Prompt

```
You have access to a SQLite database through MCP tools.
Create a simple task management database with the following requirements:
1. Create a "tasks" table with columns: id (PRIMARY KEY), title, description, status, and created_at
2. Insert 3 sample tasks
3. Query all tasks with status='pending'
4. Update the first task's status to 'completed'
```

## Error Handling

All tools include comprehensive error handling. Common errors:

- **"No database is open"**: Call `open_database` first
- **"Table creation failed"**: Check SQL syntax in schema parameter
- **"Query execution failed"**: Verify SQL query syntax and parameters
- **"Insert/Update/Delete failed"**: Check table name, column names, and data types

## Project Structure

```
sqlite-mcp/
├── sqlite_mcp/
│   ├── __init__.py        # Package initialization
│   ├── server.py          # FastMCP server with tool definitions
│   └── db.py              # SQLite database operations
├── requirements.txt       # Python dependencies
├── package.json           # Project metadata
└── README.md             # This file
```

## Configuration

To use this server with Claude or other MCP clients, add it to your configuration file:

### For Claude Desktop

Edit `~/.config/Claude/claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "sqlite-mcp": {
      "command": "python",
      "args": ["-m", "sqlite_mcp.server"],
      "cwd": "/path/to/sqlite-mcp"
    }
  }
}
```

## Performance Notes

- SQLite is suitable for single-user and small-team applications
- For concurrent access, consider using connection pooling
- Large queries may benefit from appropriate indexing
- Use transactions for data consistency (can be added if needed)

## Security Considerations

⚠️ **Important**: This server executes SQL queries directly. When using with untrusted input:

1. Always use parameterized queries (the `parameters` fields in tools)
2. Validate input data before sending to the server
3. Restrict database file permissions
4. Don't expose sensitive data in database files

## Troubleshooting

### Server Won't Start
- Check Python version (3.8+)
- Verify all dependencies installed: `pip install -r requirements.txt`
- Check for port conflicts if using HTTP transport

### Database File Not Found
- Ensure the directory path exists
- Check file permissions
- Use absolute paths for database files

### Query Errors
- Verify table names and column names match exactly
- Use proper SQL syntax
- Check data types match column definitions

## Development

To modify the server:

1. Edit `sqlite_mcp/server.py` to add new tools
2. Edit `sqlite_mcp/db.py` to modify database operations
3. Restart the server to apply changes

## License

MIT

## Contributing

Feel free to submit issues and enhancement requests!