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:

cd sqlite-mcp

2. Create a virtual environment (recommended):

python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

3. Install dependencies:

pip install -r requirements.txt

Quick Start

Running the Server

# 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:

{
  "path": "/path/to/my_database.db"
}

2. close_database

Closes the current database connection.

Example:

{}

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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "table": "users",
  "where": "id = ?",
  "where_params": [1]
}

8. list_tables

List all tables in the database.

Example:

{}

Returns:

{
  "tables": ["users", "products", "orders"]
}

9. get_table_schema

Get the schema of a table (columns, types, constraints).

Parameters:

• table (string): Table name

Example:

{
  "table": "users"
}

Returns:

{
  "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

# 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

# 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

# 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

# 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:

{
  "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!