MCP 服务器

SQLite MCP Server

Enables LLM agents to perform complete database operations on SQLite databases, including creating tables, executing queries, and managing data through CRUD operations with schema inspection capabilities.

README

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

Clone or navigate to the project directory:

cd sqlite-mcp

Create a virtual environment (recommended):

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

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:

Create databases and tables
Insert, update, and delete records
Query data
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"
    }
  }
}