Skip to main content
Glama
SQL MCP Server

SQL MCP Server

by harutlc

SQL MCP Server

An AI-powered Model Context Protocol (MCP) server that allows you to query and analyze an e-commerce SQLite database using natural language.

Ask questions like:

  • "Who are our top 5 customers by total spending?"

  • "Show all products in the Electronics category with stock below 50"

  • "What was our total revenue for completed orders in 2026?"

Four tools, three of which need no API key at all. Read-only at two independent levels, paged results, SQLite's own error text passed back to the caller, and 74 automated tests.

Contents — Quick Start · Configure a Provider · Tools · Paging · Errors · Tests · Docker · MCP Clients · Configuration · Safety · Data Egress · Project Layout


🚀 Quick Start

1. Prerequisites

  • Node.js: v22.5.0 or higher (for the built-in node:sqlite module); v24 recommended

  • npm: v11.0.0 or higher

2. Installation

Clone this repository and install dependencies:

npm install
cp .env.example .env
npm run build

That is enough to connect the server to a client and use list_tables, describe_table and execute_sql. A provider is needed only for the natural language tool — see below.


Related MCP server: shop-db MCP Server

🔑 Configure Your AI Provider

Open the .env file and set up your preferred AI model. The server automatically detects your provider based on the variables you set:

ANTHROPIC_API_KEY=sk-ant-api03-...
ANTHROPIC_MODEL=claude-opus-5

Option B: Local Ollama (Free & Offline)

OLLAMA_BASE_URL=http://localhost:11434
OLLAMA_MODEL=llama3.2

Note: Make sure Ollama is running (ollama serve) and you have pulled the model (ollama pull llama3.2).

Option C: OpenAI

OPENAI_API_KEY=sk-proj-...
OPENAI_MODEL=gpt-4o-mini

Option D: Custom / Third-Party (Groq, DeepSeek, OpenRouter)

OPENAI_API_KEY=your_api_key
OPENAI_BASE_URL=https://api.groq.com/openai/v1
OPENAI_MODEL=llama-3.3-70b-versatile

🛠 Available Tools

Three of the four talk to SQLite directly — no API key, no cost, instant:

Tool

What it does

Needs a provider

list_tables

Every table with a plain-language explanation of what it holds, its row count and columns, plus the relationships between tables and the revenue convention this database uses.

No

describe_table

One table in full — columns with types, keys and descriptions, foreign keys, the CREATE TABLE statement, caveats, and the range date columns actually cover.

No

execute_sql

Any read-only SELECT, returning structured JSON rows and column names. Supports limit / offset paging. This is the tool to use for analytical work you want to drive yourself.

No

query_database

Takes a plain-language question, generates and executes the appropriate SQL, and returns a written answer with insights.

Yes

Each tool's description tells the calling agent not just what it does but when not to use it — query_database states that it returns prose rather than values, costs money and makes two LLM calls, and points at execute_sql for anything the agent intends to compute with. Both tools state the row cap and the revenue convention inline, so the agent does not have to discover them by trial.

Example: describe_table

// describe_table { "table_name": "orders" } — abridged
{
  "table": "orders",
  "purpose": "Order headers — one row per order placed by a customer, carrying its date, lifecycle status and total.",
  "rowCount": 750,
  "columns": [
    { "name": "status", "type": "TEXT", "primaryKey": false, "notNull": true, "default": null,
      "description": "Lifecycle stage, one of: new, processing, shipped, completed, cancelled. Determines whether the order counts as revenue." }
  ],
  "foreignKeys": [
    { "column": "customer_id", "referencesTable": "customers", "referencesColumn": "id", "onDelete": "CASCADE" }
  ],
  "notes": ["Revenue convention: count every order whose status is not 'cancelled' …"],
  "dataCoverage": { "order_date": { "min": "2026-02-17 18:53:30", "max": "2026-08-22 17:06:30" } },
  "createStatement": "CREATE TABLE orders ( … )"
}

dataCoverage is there so an agent can tell an empty result from an out-of-range question: asking about 2025 returns "the data runs from … to …" rather than a bare zero that reads like a bug.


📄 Paging Through Large Results

Every result is capped — at DATABASE_MAX_ROWS (default 100), or at a smaller limit you pass. A larger limit is clamped rather than rejected, so a caller always gets rows back.

execute_sql takes limit and offset and tells you whether there is more:

// execute_sql { "sql": "SELECT id, name FROM products ORDER BY id", "limit": 2, "offset": 2 }
{
  "columns": ["id", "name"],
  "rows": [
    { "id": 3, "name": "Ноутбук UltraBook 15" },
    { "id": 4, "name": "Умные часы FitWatch" }
  ],
  "rowCount": 2,
  "offset": 2,
  "hasMore": true,
  "nextOffset": 4,
  "note": "More rows matched than were returned. Call again with offset=4 for the next page.",
  "executionTimeMs": 0.09
}

Keep calling with offset: nextOffset until hasMore is false. When a result fits in one page, hasMore is false and totalAvailableRows reports the true total.

The cap is enforced while stepping the statement, not by trimming a finished result: the server stops one row past the cap and never materialises the rest. The SQL is model-generated, so an accidental cross join would otherwise pull millions of rows into memory before any were discarded. Paging is likewise done during iteration rather than by appending LIMIT/OFFSET to the SQL, which would have to survive whatever the generated statement already ends with.

query_database shares the row cap but does not page — it summarises in prose, where a page number has nothing to attach to. Use execute_sql for anything larger than one page.


🚦 What an Error Looks Like

Failures come back as normal MCP tool results with isError: true and a message the calling agent can act on, rather than as transport-level faults.

You send

You get back

SELECT nope FROM products

Query execution failed: no such column: nope

DELETE FROM orders

Only read-only queries are permitted. A statement must begin with SELECT, WITH or VALUES, but this one begins with "DELETE".

SELECT 1; SELECT 2

Only a single SQL statement may be executed. Multiple statements were provided.

describe_table {"table_name": "custmers"}

No table named "custmers". Available tables: customers, order_items, orders, products.

A natural-language request to delete data

This request asks to modify the database, which is not permitted … No changes were made. You can still ask about the same records: …

Two rules govern that text:

  • SQLite's own message is preserved. "no such column: nope" is the single most useful thing an agent can be told, because it is enough to rewrite the query and retry. It is never flattened into "query failed".

  • Host detail never escapes. Unrecognised errors — which may carry a stack trace — collapse to a generic line, and everything on the way out is scrubbed of the database path, the project root and the home directory. Full detail stays in the server logs. This is covered by its own test file.


🧪 Automated Tests

npm test          # 74 tests across 4 files, runs in well under a second
npm run test:watch
npm run typecheck

Plain node --test with tsx — no test framework dependency. The suites run against the real db/shop.db, not a mock, so they fail if the schema and the documentation drift apart.

File

Covers

tests/sql-guard.test.ts

Every way a write could be smuggled past the read-only guard: leading comments, WITH x AS (…) DELETE, stacked statements, markdown-fenced DML. Plus the reverse — that replace(), a keyword inside a string literal, and a quoted identifier named after a keyword are not rejected.

tests/database.test.ts

Row capping, offset paging, an offset past the end, a runaway cross join that must not materialise, column names on an empty result, refused writes leaving the database unchanged, SQLite's message surviving.

tests/errors.test.ts

What a caller is allowed to see: actionable messages pass through, unknown errors collapse, and the database path / project root / home directory are redacted from both.

tests/schema-metadata.test.ts

That every table and column in the live database has a written description, that no description refers to a table that no longer exists, and that the revenue convention is stated.

The guard suite is the one that matters most: it is the boundary that makes "read-only" true rather than merely intended, and one of its cases is a real false positive it caught during development.


🐳 Docker

docker build -t sql-mcp .

The image bundles the database, so it needs no volume mount. Because this is a stdio server, it must be run with -i and no TTY — the container's stdin and stdout carry the JSON-RPC stream:

docker run -i --rm -e ANTHROPIC_API_KEY sql-mcp

Wire it into a client with examples/claude_desktop_config.docker.json. Drop the -e ANTHROPIC_API_KEY to run without credentials — list_tables, describe_table and execute_sql work without a provider.

The build is multi-stage: TypeScript is compiled in a node:24-alpine builder, and only dist/, db/ and production dependencies are copied into the runtime image. It runs as the unprivileged node user, no .env is ever copied in (credentials come from -e), and there are no native addons to compile because SQLite ships inside Node itself.


🔌 Connecting to MCP Clients

Ready-to-use configuration files are in examples/ — copy the one matching your client and replace the path. examples/claude_desktop_config.no-api-key.json runs the server with no credentials at all, which is enough for list_tables, describe_table and execute_sql.

Claude Desktop Configuration

Add this server to your Claude Desktop configuration file (claude_desktop_config.json):

  • macOS: ~/Library/Application Support/Claude/claude_desktop_config.json

  • Windows: %APPDATA%\Claude\claude_desktop_config.json

(Make sure to run npm run build once before connecting)

Example 1: Anthropic Claude (Default)

{
  "mcpServers": {
    "sql-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/sql-mcp/dist/index.js"],
      "env": {
        "ANTHROPIC_API_KEY": "sk-ant-api03-your-key-here",
        "ANTHROPIC_MODEL": "claude-opus-5"
      }
    }
  }
}

Example 2: Local Ollama (Free & Offline)

{
  "mcpServers": {
    "sql-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/sql-mcp/dist/index.js"],
      "env": {
        "OLLAMA_BASE_URL": "http://localhost:11434",
        "OLLAMA_MODEL": "llama3.2"
      }
    }
  }
}

Example 3: OpenAI

{
  "mcpServers": {
    "sql-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/sql-mcp/dist/index.js"],
      "env": {
        "OPENAI_API_KEY": "sk-proj-your-key-here",
        "OPENAI_MODEL": "gpt-4o-mini"
      }
    }
  }
}

Example 4: Custom / Groq / OpenRouter / DeepSeek

{
  "mcpServers": {
    "sql-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/sql-mcp/dist/index.js"],
      "env": {
        "OPENAI_API_KEY": "gsk_your_groq_api_key",
        "OPENAI_BASE_URL": "https://api.groq.com/openai/v1",
        "OPENAI_MODEL": "llama-3.3-70b-versatile"
      }
    }
  }
}

The server resolves db/shop.db relative to its own location, so DATABASE_PATH is not needed in any of these — MCP clients launch servers from a working directory of their own choosing, and the server does not depend on it.


🔎 Trying It Locally

Instant Terminal Test

You can test natural language questions directly in your terminal:

npm run query -- "Show top 3 products by price"

Visual Web Inspector

Test tools interactively in your browser using the official MCP Inspector:

npm run inspect:dev
  1. Open the inspector URL in your browser (e.g. http://localhost:5173).

  2. Click Connect.

  3. Under Tools, select query_database, enter your question, and click Run Tool.

All npm Scripts

Script

Does

npm run build / npm run clean

Compile to dist/ · remove it

npm start

Run the built server over stdio

npm run dev

Run from source with reload (tsx watch)

npm test / npm run test:watch

Automated tests

npm run typecheck

tsc --noEmit

npm run query -- "…"

Ask a question from the terminal

npm run inspect / npm run inspect:dev

MCP Inspector against dist/ · against source


🔧 Configuration Reference

Every variable is optional; the defaults are what runs if you set nothing.

Variable

Default

Purpose

DATABASE_PATH

db/shop.db

Database location. Absolute, or relative to the project root — never to the working directory.

DATABASE_MAX_ROWS

100

Hard ceiling on rows returned per call, and on rows sent to the LLM. execute_sql's limit can only lower it.

LLM_TIMEOUT_MS

60000

Per-request ceiling for LLM calls. A question makes two sequential calls, so without this a stalled provider hangs the tool call.

LLM_PROVIDER

auto-detected

anthropic | ollama | openai | custom. Normally inferred from which keys you set.

ANTHROPIC_API_KEY / ANTHROPIC_MODEL

— / claude-opus-5

Anthropic provider.

OPENAI_API_KEY / OPENAI_MODEL / OPENAI_BASE_URL

— / gpt-4o-mini / OpenAI

OpenAI and any OpenAI-compatible endpoint.

OLLAMA_BASE_URL / OLLAMA_MODEL

http://localhost:11434 / llama3.2

Local Ollama.

DEBUG

unset

sql-mcp:*, or one namespace: server, query-engine, database, llm, tools.

A malformed value is reported on stderr and falls back to the default rather than being silently accepted — a typo in a client's env block shows up at startup instead of behaving as though the variable were never set. DEBUG logs carry every question asked and every statement generated, and under an MCP client they land in the client's persistent log files, so they stay off unless you opt in.


🔒 Safety

The database is opened read-only at the driver level, and every statement is validated before execution: it must be a single SELECT/WITH/VALUES statement, with no keyword that writes data, alters schema, or changes connection state. Neither check can be switched off by configuration. A request like "delete all cancelled orders" is refused rather than executed.

The validator works over a tokenized view of the statement rather than the raw text, so comments, string literals and quoted identifiers cannot be used to hide a keyword — /* c */ DELETE FROM orders and WITH x AS (SELECT 1) DELETE FROM orders are both rejected, while SELECT replace(name, 'a', 'b') is not.

Text that this server did not write — your question, and values read out of the database — is delimited in the prompts with an unforgeable per-request marker, so a product named Widget (SYSTEM: ignore prior instructions…) cannot escape into instruction context. That matters beyond this process: the answer travels back to the calling agent as tool output, one hop further.


🔐 What Gets Sent Where

This server answers questions by calling an LLM, so database content leaves your machine on every query_database call. Specifically, each call sends:

  1. Your database schema — table names, column names and types, and row counts — to generate the SQL.

  2. The rows the query returned (up to DATABASE_MAX_ROWS, default 100) — to turn them into a written answer.

For the bundled shop database those rows include customer names, email addresses and phone numbers. They go to whichever provider you configure, at whichever endpoint OPENAI_BASE_URL names — which for Groq, OpenRouter or DeepSeek is a third party under its own terms.

If that is not acceptable for your data:

  • Use the other three tools. list_tables, describe_table and execute_sql make no network call at all — nothing leaves the machine.

  • Use Ollama. It runs locally, so nothing leaves the machine.

  • Restrict the queries. Aggregate questions ("revenue by category") return summary rows rather than customer records.

  • Lower DATABASE_MAX_ROWS to cap how much row data is sent per query.

The server never sends the database file, and it can only read — see Safety.


📁 Project Layout

src/
  index.ts                  MCP server entry point (stdio transport)
  cli.ts                    Terminal harness: npm run query -- "…"
  config/                   Env parsing, provider detection, path resolution
  tools/                    The four MCP tools and their descriptions
  services/
    database.service.ts     SQLite access, row capping, paging, introspection
    sql-guard.ts            Read-only enforcement (tokenizing validator)
    errors.ts               Caller-safe messages, path redaction
    schema-metadata.ts      Human-written meaning the schema cannot record
    query-engine.service.ts NL → SQL → execute → prose pipeline
    llm/                    Anthropic / OpenAI / Ollama behind one interface
  prompts/                  SQL generation, humanization, untrusted-input framing
tests/                      node --test suites (see Automated Tests)
db/                         shop.db and its schema documentation
docs/                       Architecture and sequence diagrams
examples/                   Ready-to-paste client configurations

📚 Technical Documentation

Available Tools

4 tools
describe_tableDescribe a Database TableA

Returns everything about one table: its purpose, every column with type, nullability, primary key and a plain-language description, its foreign keys, its CREATE TABLE statement, caveats worth knowing before querying it, and — for date columns — the range the data actually covers, so you can tell an empty result from an out-of-range question. Use it before writing SQL against a table you have not queried yet. Reads SQLite directly: no LLM call, no API key. Returns JSON.

Inputs: table_name (required).

execute_sqlRun a Read-Only SQL QueryA

Runs a single read-only SQL query against the e-commerce SQLite database and returns structured rows plus column names as JSON. Use this to answer analytical questions yourself — joins, aggregates, multi-step work — and when you need the actual values rather than a written summary. Call list_tables and describe_table first if you do not know the schema.

LIMITATIONS: SELECT only. The statement must be a single SELECT (or WITH ... SELECT, or VALUES); anything that writes data, changes schema, or alters connection state is rejected, as is more than one statement per call. SQLite dialect. Results are capped at 100 rows per call — use offset to page through more, and check hasMore in the response. No LLM is involved: no API key needed, no cost, and the result is exact.

Revenue convention: count every order whose status is not 'cancelled' (i.e. new, processing, shipped and completed all count as revenue). Cancelled orders are excluded because the sale did not complete. To count only fully delivered sales instead, filter status = 'completed' and say so in the answer.

Inputs: sql (required), limit, offset.

list_tablesList Database TablesA

Lists every table in the e-commerce database with a plain-language explanation of what it holds, its row count, and its column names. Also returns the relationships between tables and the convention this database uses for revenue. Use this first when you need to know what data exists, or to answer questions about the database's structure. Reads SQLite directly: no LLM call, no API key, no cost, returns immediately. Returns JSON.

Inputs: no named parameters.

query_databaseAsk About the Online ShopA

Answers questions about the online shop in plain language: customers, products, stock, orders and sales. Ask it the way a shop owner would ask a colleague — no SQL, no table names, no technical wording needed.

USE THIS for any question about the shop or the business behind it, however casually it is phrased. For example: 'what sells best?', 'how many customers do we have?', 'which products are nearly out of stock?', 'how much did we earn last month?', 'who are our biggest spenders?', 'are any orders stuck in processing?', 'what was in order 42?', 'which category makes the most money?', 'how many orders were cancelled?'.

IT KNOWS ABOUT: customers (names, email, phone, when they signed up), products (name, category, price, stock on hand), orders (date, status — new, processing, shipped, completed, cancelled — and total), and the individual products inside each order (quantity and price paid).

RETURNS: a written answer in everyday language, with Markdown tables when a list or comparison helps. Numbers are read out of the text rather than returned as structured data.

GOOD TO KNOW: it can only look things up — it can never add, change or delete anything, and any request to do so is refused. One answer covers at most 100 rows of data. It asks a language model to write the SQL behind the scenes, so it needs an API key (or a local Ollama) configured, takes a few seconds, and may phrase the same question slightly differently between runs.

Inputs: message (required), include_sql_details.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    A4.6/5.0

    Scored across 4 tools

    Disambiguation5/5

    Each tool has a distinct, non-overlapping purpose: raw SQL execution, schema listing, table detail inspection, and natural-language querying. No ambiguity between them.

    Naming Consistency5/5

    All tools follow a consistent verb_object pattern (execute_sql, list_tables, describe_table, query_database). Naming is uniform and predictable.

    Tool Count5/5

    Four tools is well-scoped for a read-only SQL MCP server. Each tool adds clear value without redundancy, and the count is ideal for the domain.

    Completeness5/5

    The server fully covers its read-only analytics purpose: schema exploration (list, describe) and data retrieval (raw SQL and natural language). No missing operations or dead ends within its stated scope.

    Maintenance

    ActivityInactive
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • F
      license
      Not graded
      quality
      D
      maintenance
      Enables AI agents to read-only analyze a SQLite e-commerce database, exploring schema and running analytical SQL queries over stdio.
      -
    • F
      license
      A
      quality
      D
      maintenance
      Gives AI agents read-only analytical access to an e-commerce SQLite database (customers, orders, order_items, products) via SQL queries, table listing, and schema inspection.
      3
      -
    • F
      license
      A
      quality
      D
      maintenance
      Enables AI agents to analyze an SQLite e-commerce database via secure read-only SQL queries, providing tools for table inspection and analytical requests.
      2
      -
    • F
      license
      Not graded
      quality
      D
      maintenance
      Enables AI agents to query an SQLite e-commerce database read-only, supporting analytical questions about customers, products, orders, and revenue.
      -