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

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:

Always use parameterized queries (the parameters fields in tools)
Validate input data before sending to the server
Restrict database file permissions
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:

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

License

MIT

Contributing

Feel free to submit issues and enhancement requests!

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

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:

Always use parameterized queries (the parameters fields in tools)
Validate input data before sending to the server
Restrict database file permissions
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:

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

License

MIT

Contributing

Feel free to submit issues and enhancement requests!