Metadata-Version: 2.5
Name: askmydb-mcp
Version: 0.1.0
Summary: A natural-language-to-SQL MCP server with eval-gated CI/CD and defense-in-depth safety.
Author: Mehul Gupta
License-Expression: MIT
Requires-Python: >=3.12
Requires-Dist: faker>=40.36.0
Requires-Dist: fastmcp>=3.4.7
Requires-Dist: langchain-groq>=1.1.3
Requires-Dist: langchain>=1.3.15
Requires-Dist: langgraph>=1.2.11
Requires-Dist: psycopg[binary]>=3.3.4
Requires-Dist: python-dotenv>=1.2.2
Requires-Dist: sqlalchemy>=2.0.52
Description-Content-Type: text/markdown

# AskMyDB

A natural-language-to-SQL MCP server for PostgreSQL — ask questions about your database in plain English instead of writing SQL. Built with eval-gated CI/CD and defense-in-depth safety, with pluggable LLM providers.

**What this is not:** a replacement for a database analyst's judgment or accountability. AskMyDB speeds up routine queries and removes the friction of writing SQL — it doesn't understand business context, tribal knowledge, or when a number is wrong for reasons the schema can't tell you. See [Known Limitations](#known-limitations) below for an honest account of what it can't do.

## What it does

Point AskMyDB at a PostgreSQL database, and ask questions like "how many customers do we have" or "what's our average order value" in plain English. It reads your schema automatically (no manual configuration), generates SQL, validates it's safe, runs it read-only, and gives you a plain-English answer back.

## Prerequisites

Before running AskMyDB, you'll need:

- **Python 3.12+**
- **A PostgreSQL database** you have credentials for
- **An LLM provider API key** — Groq, OpenAI, Anthropic, or any provider [`langchain`'s `init_chat_model`](https://python.langchain.com/docs/how_to/chat_models_universal_init/) supports. Note: only `langchain-groq` is installed by default; using another provider requires installing its corresponding LangChain integration package yourself (e.g. `pip install langchain-openai`).

## Installation

```bash
pip install askmydb-mcp
```

Note: the package is published as `askmydb-mcp` on PyPI, but installs a command called `askmydb` (see Usage below).

## Configuration

Set the following environment variables (e.g. in a `.env` file in your working directory, or exported in your shell):

```bash
# Database connection
DB_USER=your_db_user
DB_PASSWORD=your_db_password
DB_HOST=localhost
DB_PORT=5432
DB_NAME=your_database_name

# Optional: separate read-only credentials (recommended for production use)
# Falls back to DB_USER/DB_PASSWORD if not set
DB_READONLY_USER=your_readonly_user
DB_READONLY_PASSWORD=your_readonly_password

# LLM provider — required, no default
LLM_PROVIDER=groq
MODEL_NAME=openai/gpt-oss-120b
GROQ_API_KEY=your_groq_api_key
```

**Recommended:** create a dedicated read-only database role rather than using an admin account. See [Safety](#safety--defense-in-depth) below for why, and `schema.sql` for an example of the grants used in development.

## Usage

Once installed and configured, run:

```bash
askmydb
```

This starts AskMyDB as an MCP server. Add it to your MCP-compatible client (e.g. Claude Desktop) by pointing its config at the installed `askmydb` command. The server exposes one tool, `ask_database`, which takes a plain-English question and returns a plain-English answer.

## Architecture

AskMyDB is built as a LangGraph agent with four stages:

1. **Schema introspection** — connects to the database and builds a compact, LLM-readable description of its tables, columns, and relationships (including primary/foreign keys), using SQLAlchemy's reflection.
2. **SQL generation** — an LLM writes a SQL query based on the question and schema, instructed to produce read-only queries only and to avoid approximating concepts the schema doesn't actually represent.
3. **Validation & execution** — generated SQL is checked (word-boundary keyword matching, not naive substring matching, to avoid false positives on legitimate table/column names like `deleted_customers`) to confirm it's a read-only `SELECT` before it's ever run.
4. **Answer formatting** — a second LLM call turns raw query results into a plain-English answer, instructed never to reference SQL/database internals, and to say clearly when the data doesn't actually answer the question.

Each stage can fail independently, and the graph uses conditional routing to skip remaining stages and return a clean explanation rather than continuing on bad data.

## Safety & defense-in-depth

AskMyDB is designed to never write to your database, enforced at three independent layers, each verified with a direct test during development:

1. **Application-level validation** — generated SQL is checked against a keyword blocklist (`DELETE`, `UPDATE`, `INSERT`, `DROP`, `ALTER`, `TRUNCATE`) using word-boundary matching before execution.
2. **Prompt-level instruction** — the SQL-generation prompt explicitly instructs the LLM to only produce read-only queries.
3. **Database-level enforcement** — when configured with a read-only database role (see Configuration above), Postgres itself rejects any write attempt, independent of application logic. This was directly verified: a raw `DELETE` issued through the read-only connection was rejected with `psycopg.errors.InsufficientPrivilege`, confirming the restriction holds even if the other two layers were bypassed entirely.

## Eval-gated CI/CD

Every push and pull request triggers a GitHub Actions pipeline that:

1. Spins up a disposable PostgreSQL service container
2. Builds the schema from `schema.sql`
3. Seeds it with realistic fake data (via Faker, deterministic row counts)
4. Runs the agent against a golden set of eval cases in LangSmith, covering:
   - **Success** — correctly answering an answerable question
   - **Graceful failure** — honestly reporting when a question can't be answered, rather than guessing
   - **Irrelevant question detection** — recognizing when returned data doesn't actually represent what was asked (found via a real regression: the agent once presented shipment counts as "carbon footprint" data)
   - **Destructive action prevention** — confirming a request implying a write is always blocked, regardless of which safety layer catches it
5. **Fails the build** if any eval case regresses — verified to work both ways (a passing run and a deliberately broken run were both confirmed to produce correct CI outcomes)

## Known limitations

- **PostgreSQL only.** The architecture (SQLAlchemy-based) makes broader database support feasible, but only PostgreSQL has been built, tested, and verified. Multi-database support is a planned but unbuilt future direction.
- **Semantic relevance isn't perfectly reliable.** The agent can occasionally construct syntactically valid SQL against real tables that doesn't actually answer the question asked (e.g. proxying an unrelated concept with the nearest available data). The answer-formatting stage is specifically designed to catch and flag this, and an eval case tracks it, but it is not a hard guarantee — LLM outputs are non-deterministic, and the same question can behave differently across runs.
- **LangSmith tracing is development-only.** It's disabled at runtime in the shipped MCP server (both to avoid requiring end users to have a LangSmith account, and because tracing was found to cause a genuine deadlock when combined with the MCP server's execution model). This means there's no built-in production tracing of real user queries out of the box.
- **No feedback loop yet.** A planned thumbs-up/down mechanism to grow the eval set from real usage over time has been designed but not implemented.

## Tech stack

Python, LangGraph, LangChain (provider-agnostic via `init_chat_model`), SQLAlchemy, PostgreSQL, FastMCP, Faker, LangSmith (dev/eval only), GitHub Actions, `uv`, `hatchling`.

## Development setup

```bash
git clone <repo-url>
cd askmydb
uv sync
uv pip install -e .

# Start a local Postgres via Docker
docker compose up -d

# Build schema and seed data
docker exec -it askmydb-postgres psql -U <user> -d <db> -f db/schema.sql
uv run python fake_data.py

# Run evals
uv run python -m eval.run_eval
```