SQLite MCP Server

SQLite MCP Server and Client

This project implements an MCP (Model Context Protocol) server and client for interacting with a SQLite database. The server provides tools to query an existing SQLite database, while the client, built with Chainlit and LangGraph, allows users to send natural language prompts to perform database operations via a web-based chat interface.

Project Overview

• MCP Server (mcp_sqlite_server.py): A server using the FastMCP library that exposes four tools to interact with a SQLite database (example.db):
  • list_tables: Lists all tables in the database.
  • get_table_schema: Retrieves column names and data types for a specified table.
  • count_rows: Counts the number of rows in a specified table.
  • execute_query: Executes read-only SQL SELECT queries and returns results.
• MCP Client (mcp_client_chainlit.py): A Chainlit-based client using LangGraph's create_react_agent and the langchain-mcp-adapters library to connect to the server. Users can input natural language prompts (e.g., "List all tables" or "Show me the first 5 rows of the users table"), and the agent dynamically calls the server's tools to generate responses.
• Transport: The server uses streamable-http transport (HTTP with Server-Sent Events) for JSON-RPC 2.0 communication, running at http://localhost:8080/mcp.
• Security: The server restricts queries to SELECT statements and validates table names to prevent SQL injection.

Prerequisites

• Python: Version 3.8 or higher.
• SQLite Database: An existing example.db file in the project directory with data (e.g., tables like users or orders).
• API Key: An Anthropic API key for the client (or another LLM provider like OpenAI if preferred).

Dependencies

The project requires the following Python packages, listed in requirements.txt:

Setup Instructions

1. Clone the Repository (if applicable):
   git clone <repository-url> cd <repository-directory>
2. Create and Activate a Virtual Environment:
   python -m venv venv source venv/bin/activate # On Windows: venv\Scripts\activate
3. Install Dependencies:
   pip install -r requirements.txt
4. Set Environment Variables:
   • Configure the Anthropic API key for the client:
     export ANTHROPIC_API_KEY=your_anthropic_api_key
   • If using another LLM (e.g., OpenAI), set the appropriate API key:
     export OPENAI_API_KEY=your_openai_api_key
     and update mcp_client_chainlit.py to use langchain-openai instead of langchain-anthropic.
5. Prepare the SQLite Database:
   • Ensure an example.db file exists in the project directory with the desired tables and data.
   • The server does not initialize or modify the database; it assumes the database is pre-populated.

Running the Project

1. Start the MCP Server:
   • Run the server script to expose the SQLite database tools:
     python mcp_sqlite_server.py
   • The server will start at http://localhost:8080/mcp using streamable-http transport.
   • Verify it’s running by checking the terminal for log messages (e.g., "Starting MCP server with streamable-http transport...").
2. Start the Chainlit Client:
   • Run the client script to launch the web-based chat interface:
     chainlit run mcp_client_chainlit.py
   • Open a browser and navigate to http://localhost:8001 (or the port shown in the terminal).
   • The Chainlit UI will display a welcome message: "Welcome to the SQLite MCP Client! Ask about the database..."

Usage

1. Interact via Chainlit UI:
   • In the Chainlit interface (http://localhost:8001), enter natural language prompts to query the database.
   • Example prompts:
     • "List all tables in the database" → ["users", "orders"]
     • "What is the schema of the users table?" → [{"column_name": "id", "data_type": "INTEGER"}, {"column_name": "name", "data_type": "TEXT"}]
     • "How many rows are in the orders table?" → 42 (example)
     • "Run SELECT name FROM users WHERE id = 1" → Results: name = Alice
   • The LangGraph agent will use the MCP server's tools to process the prompt and return a formatted response.
2. Expected Behavior:
   • The client sends prompts to the LangGraph agent, which decides which tools to call (e.g., list_tables, execute_query).
   • The MultiServerMCPClient communicates with the server at http://localhost:8080/mcp to execute tool requests.
   • Responses are displayed in the Chainlit chat interface.

Example Interactions

• Prompt: "Show the schema and row count for the users table."
  • Agent Process:
    1. Calls get_table_schema("users") → [{"column_name": "id", "data_type": "INTEGER"}, {"column_name": "name", "data_type": "TEXT"}]
    2. Calls count_rows("users") → 10 (example)
    3. Response: "The users table has columns: id (INTEGER), name (TEXT). It contains 10 rows."
• Prompt: "Get all names from the users table."
  • Agent Process:
    1. Calls execute_query("SELECT name FROM users") → {"columns": ["name"], "rows": [["Alice"], ["Bob"]]} (example)
    2. Response: "Names in the users table: Alice, Bob"

Troubleshooting

• Server Not Running: Ensure mcp_sqlite_server.py is running and accessible at http://localhost:8080/mcp. Check terminal logs for errors.
• Client Errors:
  • If the Chainlit UI fails to load, verify the port (8001) is not in use.
  • If the agent fails to connect to the server, check the MCP_SERVER_CONFIG URL in mcp_client_chainlit.py.
• LLM Issues: Ensure the Anthropic API key is valid. For alternative LLMs, install the appropriate package (e.g., langchain-openai) and update the client script.
• Database Errors: Confirm example.db exists and is readable. Check server logs (loguru) for SQL errors.

Notes

• Security: The server restricts queries to SELECT statements and validates table names to prevent SQL injection. For production, consider adding OAuth 2.1 authentication.
• Extensibility: Add more tools to the server by defining new @mcp.tool() functions in mcp_sqlite_server.py. The client will automatically discover them via MultiServerMCPClient.
• LLM Flexibility: The client uses Anthropic's Claude by default. To use another LLM (e.g., OpenAI's GPT-4o), replace langchain-anthropic with langchain-openai in requirements.txt and update the LLM import in mcp_client_chainlit.py.
• Dependencies: Check for version compatibility in requirements.txt. Update versions if newer releases are available.

Contributing

Contributions are welcome! To add features or fix bugs:

1. Fork the repository.
2. Create a branch (git checkout -b feature/your-feature).
3. Commit changes (git commit -m "Add your feature").
4. Push to the branch (git push origin feature/your-feature).
5. Open a pull request.