Content
<div align="center">
# Text to SQL MCP
An MCP (Model Context Protocol) server that connects any LLM client to a PostgreSQL database. The host LLM generates SQL queries using the exposed schema and executes them.
[](https://www.python.org/downloads/)
[](https://www.postgresql.org/)
[](https://github.com/MagicStack/asyncpg)
[](https://docs.pydantic.dev/)
[](https://docs.docker.com/compose/)
[](https://modelcontextprotocol.io/)
[](https://github.com/astral-sh/uv)
</div>
<div align="center">
<p><em>See how the user interacts with the database by simply asking questions to the LLM</em></p>
</div>
## 📋 Table of Contents
- [📖 Overview](#-overview)
- [✨ Features](#-features)
- [🚀 Installation](#-installation)
- [💻 Usage](#-usage)
- [⚙️ Configuration](#️-configuration)
- [🛠️ Development](#️-development)
- [📁 Project Structure](#-project-structure)
- [🏗️ Tech Stack](#️-tech-stack)
- [🔧 Troubleshooting](#-troubleshooting)
- [🤝 Contributing](#-contributing)
## 📖 Overview
This MCP server provides a secure bridge between Large Language Models (Claude, GPT, etc.) and PostgreSQL databases, enabling natural language to SQL query generation and execution.
### 💡 Inspiration
Born from countless hours of typing SELECT \* FROM users WHERE..., I realized I was spending more time crafting SQL queries than actually analyzing data. Why not just ask an LLM about your database instead?
Writing SQL is a powerful skill, but it's often a **bottleneck for quick data exploration**. I built this to eliminate the friction of "mental context switching", those moments spent forgetting table schemas or wrestling with timestamp syntax just to get a simple answer.
By leveraging LLMs to handle the boilerplate SQL, you can focus on the questions you want to ask, rather than the syntax required to ask them.
### MCP Tools
| Tool | Description |
| ----------------- | ---------------------------------------------------- |
| `get_schema` | Returns all tables, columns, types, and foreign keys |
| `get_sample_data` | Returns sample rows from a specified table |
| `execute_query` | Executes SQL and returns results (read-only) |
## ✨ Features
- **Schema Inspection**: Retrieve complete database schema including tables, columns, types, and foreign keys
- **Sample Data**: Get sample rows from any table to understand data structure
- **Query Execution**: Execute SQL queries with built-in security validation
- **Read-Only Mode**: Configurable read-only mode to prevent data modifications
- **Query Validation**: Automatic validation to prevent dangerous operations
- **Row Limiting**: Configurable maximum rows to prevent overwhelming responses
### Security Features
- **Query Validation**: Blocks dangerous SQL operations (DROP, DELETE, etc. in read-only mode)
- **Function Blocking**: Prevents execution of dangerous PostgreSQL functions
- **Multiple Statement Prevention**: Only allows single SQL statements
- **Read-Only Mode**: Configurable to restrict to SELECT queries only
- **Row Limiting**: Prevents overwhelming responses with too much data
## 🚀 Installation
### Prerequisites
- [uv](https://github.com/astral-sh/uv) - Fast Python package manager
- Docker and Docker Compose (for PostgreSQL database)
### Quick Start
```bash
# Reset database and start fresh with sample data
make fresh
# View available commands
make help
# Test the connection
make check-connection
# View the database schema
make check-schema
```
### Install uv
```bash
# macOS/Linux
curl -LsSf https://astral.sh/uv/install.sh | sh
# or with Homebrew
brew install uv
```
### Setup Project
```bash
# Install dependencies
make install
# or
uv sync
# Start local database with sample data
make fresh
```
## 💻 Usage
### Local Development
```bash
# Start local database
make up-db
# Run MCP server
uv run text_to_sql_mcp.server
```
### Claude Desktop Integration
1. **Start the database:**
```bash
make up-db
```
2. **Add to Claude Desktop config** (`~/Library/Application Support/Claude/claude_desktop_config.json`):
```json
{
"mcpServers": {
"text-to-sql": {
"command": "uv",
"args": [
"--directory",
"/path/to/the/project/text-to-SQL-mcp",
"run",
"text-to-sql-mcp"
],
"env": {
"DATABASE_URL": "url/to/your/postgres/database"
}
}
}
}
```
3. **Restart Claude Desktop** and start querying your database!
### Docker Commands (Database Only)
```bash
# Start database
make up-db
# Stop database
make down
# Reset database and start fresh
make fresh
# View database logs
make logs-db
```
### Test Database
When you run `make fresh`, a PostgreSQL database is automatically created and populated with sample e-commerce data.
#### Sample Database Schema
| Table | Description | Row Count |
| ------------- | ------------------------------------- | --------- |
| `users` | Customer accounts | 10 |
| `categories` | Product categories | 6 |
| `products` | Product catalog with prices and stock | 16 |
| `orders` | Customer orders with status | 10 |
| `order_items` | Individual items in each order | 20 |
| `reviews` | Product reviews with ratings (1-5) | 14 |
**Helpful Views:**
- `order_summary` - Orders with customer info and item counts
- `product_ratings` - Products with average ratings and review counts
#### Resetting the Test Database
```bash
make fresh
# Or manually with confirmation
make reset
make up-db
```
### Testing Commands
```bash
# Test database connection
make check-connection
# View database schema
make check-schema
# Run test query
make query QUERY="SELECT * FROM users LIMIT 5"
```
## ⚙️ Configuration
This project uses **Pydantic Settings** for configuration management.
Create `.env` file for your environment variables:
```bash
# Required
DATABASE_URL=postgresql://testuser:testpass@localhost:5433/testdb
# Optional
QUERY_TIMEOUT=30 # Query timeout in seconds
MAX_ROWS=1000 # Maximum rows to return
READ_ONLY=true # Restrict to SELECT queries only
```
**Note:** Use `localhost:5433` to connect to the Docker PostgreSQL database from your local MCP server.
## 🛠️ Development
### Setup
```bash
# Install development dependencies
make install-dev
# or
uv sync --all-extras
```
### Code Quality
```bash
# Format code
make format
# or
uv run black src/
# Lint code
make lint
# or
uv run ruff check src/å
```
### Cleanup
```bash
# Clean up containers, volumes, and Python cache
make clean
```
## 📁 Project Structure
```
text-to-sql-mcp/
│
├── src/
│ └── text_to_sql_mcp/
│ ├── __init__.py
│ ├── server.py # MCP server entry point
│ │
│ ├── tools/
│ │ ├── __init__.py
│ │ ├── schema.py # get_schema, get_sample_data
│ │ └── query.py # execute_query
│ │
│ ├── core/
│ │ ├── __init__.py
│ │ ├── database.py # Postgres connection
│ │ └── security.py # Query validation
│ │
│ ├── config.py # Pydantic Settings
│ └── __init__.py
│
├── scripts/
│ ├── check_connection.py # Test database connection
│ ├── check_schema.py # Print schema to console
│ └── check_query.py # Run a test query
│
├── docker-compose.yml # PostgreSQL database setup
├── init.sql # Sample database initialization
├── Makefile # Common commands
├── pyproject.toml
├── README.md
├── .env.example
└── LICENSE
```
## 🏗️ Tech Stack
### Core
- **Language**: Python 3.11+
- **Package Manager**: `uv` (Astral)
- **MCP Protocol**: `mcp` (official SDK)
- **Database Driver**: `asyncpg`
- **Validation**: `pydantic` v2
- **Configuration**: `pydantic-settings`
### Infrastructure
- **Database**: PostgreSQL 15 (Alpine)
- **Container Orchestration**: Docker Compose
### Development Tools
- **Formatter**: `black`
- **Linter**: `ruff`
- **Type Checker**: `mypy`
## 🔧 Troubleshooting
### Connection Issues
1. **Check DATABASE_URL format:**
```
postgresql://testuser:testpass@localhost:5433/testdb
```
2. **Ensure database is running:**
```bash
make up-db
make logs-db # Check for errors
```
3. **Test connection:**
```bash
make check-connection
```
4. **Verify port mapping:**
```bash
docker ps # Should show 0.0.0.0:5433->5432/tcp
```
5. **Viewing MCP logs from Claude Desktop**
```bash
tail -n 20 -F ~/Library/Logs/Claude/mcp\*.log
```
### Query Validation Errors
If queries are being blocked:
1. Check if `READ_ONLY=true` is set (only SELECT queries allowed)
2. Verify the query doesn't use dangerous functions
3. Ensure only a single SQL statement is being executed
### Performance Issues
1. Adjust `QUERY_TIMEOUT` for long-running queries
2. Reduce `MAX_ROWS` if responses are too large
3. Add appropriate indexes to your database tables
### Additional Resources
For more debugging help, refer to the [official MCP debugging documentation](https://modelcontextprotocol.io/legacy/tools/debugging).
## 🤝 Contributing
Contributions are welcome! Please feel free to submit a Pull Request.
