README.md•7.77 kB
# 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!