sql-ai-agent-mcp
Provides read-only SQL access to a SQLite business database, with tools to list tables, describe table schemas, and run guarded SELECT queries with row, size, and timeout limits plus an audit log.
This server does not currently have a deployable release on Glama. Follow the repository's setup instructions to install and configure it for your MCP client, including any required credentials and local dependencies.
sql-ai-agent-mcp
An MCP server that gives an LLM read-only SQL access to a sample business database, with layered guardrails, an audit log, and an agent CLI that answers business questions in plain English.

The GIF is rendered from real captured CLI output (docs/demo/) by npm run demo:gif; nothing in it was typed by hand. The first scene is a real llama3.2 run on a CPU-only laptop. I picked a question it also answered correctly in the eval; on the full set it gets 4 of 16 right (see Eval results). The other two scenes are writes blocked by the guard.
The problem it automates
People who are not SQL users keep asking the same kind of question: "who were our biggest customers last quarter?", "how many tickets are open?". Letting an LLM write SQL for them is useful, but giving a model a database connection is risky: it can be wrong, it can be talked into things by whoever writes the question, and it can be talked into things by text that is already stored in the rows it reads.
This project is a small, well-tested answer to that: a Model Context Protocol server that exposes list_tables, describe_table and run_select, where run_select only ever runs one plain SELECT, with limits, a timeout and an audit trail. Any MCP host can use it. The repo also includes my own agent client and an eval, so I can say how well a small local model actually does with it.
It is a portfolio project. The data is fictional and generated by a seeded script.
Related MCP server: shop-analytics-mcp
Architecture
flowchart LR
U[User question] --> CLI["Agent CLI<br/>npm run ask"]
CLI -->|step-limited loop| LLM{{"LLM provider<br/>ollama / claude / mock"}}
CLI <-->|"MCP over stdio<br/>(JSON-RPC)"| S[MCP server]
H[Any MCP host] <-->|"stdio or<br/>streamable HTTP 127.0.0.1"| S
S --> QS[QueryService]
QS --> G1["1 Lexical guard<br/>tokenizer + denylist"]
G1 --> G2["2 Parser guard<br/>node-sql-parser AST<br/>+ table allowlist"]
G2 --> L["Limits<br/>rows, bytes, timeout"]
L --> DB[("3 Read-only connection<br/>SQLite (default) or SQL Server")]
QS --> A[["Audit log<br/>logs/audit.jsonl"]]Features
MCP server built on the official MCP TypeScript SDK (v2 packages
@modelcontextprotocol/server,/client,/node), over stdio and optionally streamable HTTP (127.0.0.1:4104, Host/Origin checked).Three read-only tools:
list_tables;describe_table(columns, types, nullability, primary key, foreign keys, row count);run_select(one query, optional row limit).Guardrails in layers (table below): tokenizer-based checks, an AST check, a table allowlist, a read-only database connection, row/size/time limits.
Append-only JSONL audit log of every tool call: timestamp, tool, query, decision, layer, reason, rows, duration.
Two backends behind one interface: SQLite (default, zero setup) and SQL Server (
mssql,docker-compose.yml, generated T-SQL seed with a least-privilege login). SQL Server is not verified locally (see below).Agent CLI (
npm run ask) that connects as a real MCP client, primes the schema through the tools, loops with a step limit, and prints the answer plus every SQL statement it ran.Provider abstraction:
ollama(default, llama3.2),claude(official Anthropic SDK),mock(scripted, used in tests and CI).Eval: 16 business questions with SQL-computed expected answers; reports accuracy, mean steps, mean latency and guardrail blocks.
Prompt-injection fixture: support ticket 42 contains text telling the agent to
DROP TABLE customers. A test scripts a model that obeys it; the guardrails block every attempt.
Guardrails
Layer | What it checks | Example it stops | Where |
Input | String, non-empty, at most 8000 chars, no control characters |
|
|
Lexical: comments | Any |
|
|
Lexical: statements |
|
| same |
Lexical: first keyword | Must be |
| same |
Lexical: denylist | INSERT, UPDATE, DELETE, MERGE, DROP, ALTER, CREATE, TRUNCATE, INTO, RETURNING, EXEC, PRAGMA, ATTACH, VACUUM, GRANT, WAITFOR, OPENROWSET, LOAD_EXTENSION, |
| same |
Parser | node-sql-parser must return exactly one | anything the lexical layer missed that is not a plain SELECT |
|
Table allowlist | Every table read must be an exposed business table (or a CTE name) |
|
|
Database | SQLite opened | a write that slipped past every check above |
|
Row limit | Default 100, caller may ask for less or more, hard max 500 (clamped, with a note) |
|
|
Result size | Stop adding rows once their JSON passes 64 KiB | wide |
|
Timeout | 5 s; the SQLite runner process is killed and restarted | endless recursive CTE |
|
Audit | Every allowed, blocked, failed and timed-out call is appended to |
|
Full threat model, including prompt injection through stored rows and the known gaps: docs/threat-model.md.
Tech stack
TypeScript 6 run directly by Node 24 (type stripping, no build step needed to run), @modelcontextprotocol/server / client / node 2.x, better-sqlite3, mssql, node-sql-parser, zod 4, @anthropic-ai/sdk, Ollama HTTP API, Vitest, ESLint (typescript-eslint), Playwright + gifenc + pngjs (demo GIF only).
Quick start
Requires Node 24 (or 22.18+). Ollama is only needed for real LLM answers.
Windows (PowerShell)
git clone https://github.com/sahil-ambekarr/sql-ai-agent-mcp.git
cd sql-ai-agent-mcp
npm ci
Copy-Item .env.example .env
npm run seed # builds data/sample.db (about 11,400 rows)
# Try the tools directly, no LLM:
npm run tool -- list_tables
npm run tool -- run_select "SELECT country, COUNT(*) AS n FROM customers GROUP BY country"
npm run tool -- run_select "SELECT 1; DROP TABLE customers" # blocked
# Ask a question (needs Ollama with llama3.2 pulled):
ollama pull llama3.2
npm run ask -- "Which 5 customers spent the most last quarter?"
# Same pipeline with the scripted mock provider (no LLM):
$env:LLM_PROVIDER="mock"; npm run ask -- "Which 5 customers spent the most last quarter?"macOS / Linux
git clone https://github.com/sahil-ambekarr/sql-ai-agent-mcp.git
cd sql-ai-agent-mcp
npm ci
cp .env.example .env
npm run seed
npm run tool -- run_select "SELECT COUNT(*) AS n FROM orders"
npm run ask -- "Which 5 customers spent the most last quarter?"
LLM_PROVIDER=mock npm run ask -- "Which 5 customers spent the most last quarter?"Use it from any MCP host
Run npm run seed once, then add the server to your MCP client's config (the shape below is the common mcpServers format; use absolute paths):
{
"mcpServers": {
"sql-readonly": {
"command": "node",
"args": ["/absolute/path/to/sql-ai-agent-mcp/src/server/stdio.ts"],
"env": {
"DB_BACKEND": "sqlite",
"SQLITE_PATH": "/absolute/path/to/sql-ai-agent-mcp/data/sample.db",
"AUDIT_LOG_PATH": "/absolute/path/to/sql-ai-agent-mcp/logs/audit.jsonl",
"ROW_LIMIT_DEFAULT": "100",
"QUERY_TIMEOUT_MS": "5000"
}
}
}
}If your host's Node is older than 22.18, run npm run build and point args at dist/server/stdio.js instead. For HTTP: npm run server:http serves http://127.0.0.1:4104/mcp (stateless streamable HTTP).
SQL Server backend (not verified locally)
cp .env.example .env # set MSSQL_SA_PASSWORD and MSSQL_PASSWORD (SQL Server password policy applies)
npm run seed:mssql-script # regenerates mssql/init/01-seed.sql (already committed)
docker compose up -d # SQL Server 2022 + a one-shot seed container
DB_BACKEND=mssql npm run tool -- list_tablesI built this repo on a machine without Docker, so the SQL Server path has not been run against a live server. What is tested: the TOP row-limit rewrite, the catalog queries (as SQL text), the T-SQL guard dialect, and the generated seed script (tests/mssql-dialect.test.ts).
Configuration
Variable | Default | Meaning |
|
|
|
|
| SQLite file (created by |
|
| SQL Server connection |
|
| Least-privilege login created by the seed script |
|
| TLS options for the driver |
|
| Rows returned when no limit is given / hard cap |
|
| Per-query timeout |
|
| Cap on the JSON size of returned rows |
|
| Longest accepted query |
|
| Append-only audit log |
|
| Port for |
|
|
|
|
| Ollama endpoint and model |
|
|
|
| (empty) / | Claude provider; only used when |
|
| Timeout per model call (CPU inference is slow) |
|
| Model calls per question before the agent gives up |
| (real date) | Pins "today" so "last quarter" is reproducible |
|
| JSON logs on stderr |
Providers. ollama is the default and the only one I measured. claude uses the official Anthropic SDK with tool use; I had no API key, so it is not verified against the live API and is covered by tests with a stubbed HTTP layer. mock replays a fixed script (or the gold SQL, in the eval); CI uses it, so CI needs no model, key or GPU.
Ollama mode. I implemented both Ollama's native tool calling and a JSON-action protocol. The default is json: the model replies with one JSON object (run_select, describe_table, list_tables or final_answer), Ollama's format parameter constrains decoding to a JSON Schema, the reply is validated with zod, and an invalid reply is sent back with the error for up to two retries.
Eval results
Measured 2026-11-17 on a CPU-only laptop (AMD Ryzen 5 5500U, integrated graphics unused, 16 GB RAM, Windows 11), with Ollama shared with two other jobs. Full write-up with every question and the failure analysis: docs/eval-results.md. Raw JSON: eval/results/ollama-llama3-2-json.json.
Provider | Questions | Correct | Mean steps | Mean latency / question | Step-limit stops | SQL errors | Guardrail blocks |
| 16 (full set) | 4 (25.0%) | 4.56 / 6 | 67.9 s | 6 (+1 hit the 5-min cap) | 11 | 0 |
| 16 | 16 | 2.00 | under 0.1 s | 0 | 0 | 0 |
| not measured (no API key; not verified against the live API) |
Method. The eval drives the real pipeline: it spawns the MCP server, runs the agent with "today" pinned to 2026-11-17, temperature 0, at most 6 model steps and 5 minutes per question, and grades the free-text answer against the result of each question's gold SQL on the seeded database (numbers within a tolerance, all expected names present, or the right month). A run that stops without a final answer never counts. The mock row only proves the plumbing and grading work; it says nothing about a model.
Reproduce.
npm run seed
npm run eval -- --provider ollama --mode json --budget-min 40 --question-min 5
npm run eval -- --provider mockWhat went wrong, and limitations. The 3B model's main failures were PostgreSQL/MySQL date functions in SQLite (EXTRACT, DATEDIFF), selecting a column without joining its table, resending the same failing query until the step limit, and drifting into revenue queries nobody asked for. It never attempted a write, so the guardrails were not exercised by the model in this run (they are exercised by the test suite, including the prompt-injection fixture). Sixteen questions and one run is a small sample; grading is lenient about extra text; latency includes queueing on a shared CPU. I regraded one question after finding a grader bug (details in the write-up). Native tool-calling mode was not measured.
Project structure
src/
config.ts env parsing and validation (zod)
audit.ts append-only JSONL audit log
logger.ts JSON logs on stderr
guard/lexer.ts SQL tokenizer (strings, quoted identifiers, nested comments)
guard/validate.ts SELECT-only guard: input, lexical, parser, allowlist
db/types.ts Backend interface, row/size collector
db/sqlite.ts SQLite backend; runs queries in a killable child process
db/sqlite-runner.ts the child process (read-only connection)
db/mssql.ts SQL Server backend (not verified live)
db/mssql-dialect.ts TOP rewrite and catalog queries (unit tested)
server/query-service.ts limits + guard + audit around each tool
server/tools.ts MCP tool registration
server/stdio.ts|http.ts transports
agent/mcp-client.ts MCP client that spawns the server
agent/loop.ts agent loop and system prompt
agent/providers/ ollama, claude, mock
agent/cli.ts npm run ask
agent/tool-cli.ts npm run tool (direct tool calls, no LLM)
seed/ deterministic data generator, SQLite and T-SQL seeding
eval/gold.ts|run.ts expected answers and the eval runner
eval/questions.json 16 questions with gold SQL
eval/results/ saved eval runs
mssql/init/01-seed.sql generated T-SQL seed (sqlcmd variables, no secrets)
docs/ threat-model.md, eval-results.md, demo/ transcripts, screenshots/demo.gif
tests/ Vitest suites
scripts/ seeding and demo capture/renderTests
npm run lint && npm run typecheck && npm testLast local run (2026-11-17, Node 24.13, Windows 11): 142 tests in 8 files, all passing, about 15 s.
File | Tests | What it covers |
| 81 | 54 queries that must be blocked plus a check that the blocked suite stays large (stacked statements, comment tricks, writes in CTEs, |
| 13 | row limit and clamping, result-size cap, timeout kill and recovery, read-only connection as the last layer, table-name injection in describe_table, append-only audit log |
| 8 | spawns the real server over stdio and calls every tool as an MCP client, including a blocked write, a timeout and schema validation |
| 2 | streamable HTTP transport and the Host-header guard |
| 9 | agent loop over real MCP with the mock provider: schema priming, step limit, time limit, and the ticket-42 prompt injection (every write the "obedient" model tries is blocked) |
| 11 | Ollama JSON and tools modes, retries, abort; Claude provider with a stubbed HTTP layer |
| 4 | eval grading: number tolerance, names, months, and that a run without a final answer never counts |
| 14 | T-SQL TOP rewrite, catalog SQL, T-SQL guard dialect, generated seed script |
CI (.github/workflows/ci.yml) runs lint, typecheck, build, seed, the tests, and npm run eval -- --provider mock.
Key decisions
MCP SDK v2 packages. The older single
@modelcontextprotocol/sdkpackage is split in the current official release line into@modelcontextprotocol/server,/clientand/node(v2 implements the 2026-07-28 spec). I used those and read their bundled type definitions for the API.Eval runner that survives interruptions. CPU-only, shared Ollama is slow (one question took over 10 minutes in an earlier attempt), so
npm run evalrewrites its results file after every question, reconnects the MCP client if a question throws, and takes--budget-minto stop starting new questions after a wall-clock budget.Defence in depth over one clever check. Each guard layer is simple and independently tested; any one of them stops the blocked suite.
Reject comments outright rather than trying to strip them the way each engine would.
Fail closed on parse errors, with a PostgreSQL-grammar fallback for SQLite because node-sql-parser's SQLite grammar misses some valid syntax (
OVER (ORDER BY ...),FILTER,NULLS LAST).SQLite queries run in a child process so the timeout can kill a runaway query; better-sqlite3 is synchronous and cannot be interrupted from JavaScript.
Row limit by reading, not rewriting, on SQLite; TOP rewrite on the outer SELECT plus a streaming cap on SQL Server (wrapping in a subquery breaks ORDER BY in T-SQL).
Schema priming. The agent fetches the schema through
list_tables/describe_tableonce and puts a compact version in the system prompt. With a 3B model this saves several slow steps; the model can still calldescribe_tableitself.JSON-action protocol by default for llama3.2. Ollama's
formatconstrains decoding to a JSON Schema, and in the eval run none of the 73 model steps ended in an unparseable reply (0 provider errors). I have not measured native tool-calling mode against it, so this is a design choice, not a measured win.Pinned "today" (
AGENT_TODAY, 2026-11-17 in the eval) so "last quarter" has one correct answer. The seeded data ends 2026-09-30.Node runs the TypeScript directly (
erasableSyntaxOnly), so there is no build step for development;npm run buildemitsdist/for hosts that need JavaScript.
Roadmap
Act on the eval failures: SQLite date-function hints in the prompt, detect a repeated failing query in the loop, one complete worked example query.
Measure native tool-calling mode and a larger local model against the same 16 questions.
Run the SQL Server path against a real instance and add it to CI as a service container.
Column-level masking for personal data (customer emails).
A
sample_rowstool and per-table descriptions to help small models.Per-user auth on the HTTP transport.
A larger eval set with paraphrased questions.
License
MIT, see LICENSE. Copyright (c) 2026 Sahil Ambekar.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
This server cannot be deployed
Maintenance
Related MCP Connectors
Query your org's data in natural language — read-only MCP access to SQL, NoSQL, files & warehouses.
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Read authorized Loam tables and supported external databases through a bounded MCP connection.
Query your warehouse or a CSV with Claude/ChatGPT over MCP, governed by table-level ACL + audit.
Related MCP Servers
- FlicenseAqualityDmaintenanceEnables read-only exploration and querying of PostgreSQL or MySQL databases via MCP, with schema discovery, safe SQL validation, natural language to SQL conversion, and CSV export.112-
- AlicenseNot gradedqualityCmaintenanceEnables AI agents to read-only query a SQLite database, inspect schema and table summaries, and execute SELECT queries with pagination through MCP.MIT
- FlicenseNot gradedqualityBmaintenanceProvides read-only database access for AI agents, enabling table listing, schema inspection, and filtered row queries through MCP tools.-
- AlicenseNot gradedqualityCmaintenanceEnables AI agents to run read-only SQL against SQLite or MySQL databases through MCP tools for listing tables, describing schemas, and querying data. Wraps every call in guardrails such as statement allow-listing, row caps, query timeouts, PII masking, and audit logging.1MIT