MCP SQLite Server
# 🗄️ MCP SQLite Server
> **Day 10 of [30 Days of AI Agents](https://github.com/obiokoye)**: a Model Context Protocol server that lets any AI app (Claude Desktop, Cursor, your own agent) explore a SQLite database with safe, read-only SQL tools.





The repo has two parts:
- **The MCP server** (`src/index.ts`): exposes a SQLite database as tools. Plug it into any MCP client.
- **A demo agent** (`npm run chat`): starts the server as a child process, connects to it as an MCP client, and lets an LLM answer questions with it.
## What it shows
- **MCP (Model Context Protocol)**: write tools once and every MCP client can discover and call them. The server registers **tools**, a **resource** (`sqlite://schema`) and a **prompt** (`explore-data`).
- **Tool discovery**: the agent has no SQLite code. It calls `tools/list`, converts the JSON Schemas to OpenAI function tools, and forwards the model's calls with `tools/call`.
- **Guardrails for model-written SQL**, in layers:
1. `read_query` only accepts `SELECT` / `WITH`.
2. SQLite itself must report the prepared statement as read-only (`stmt.readonly`), which catches writes hidden inside a CTE.
3. The connection is opened read-only unless you set `ALLOW_WRITES=true`.
4. Stacked statements are refused and results are capped at `MAX_ROWS`.
- **Errors the model can recover from**: a blocked query comes back as an MCP tool error, so the agent explains instead of crashing.
- **Opt-in writes**: `write_query` only exists when `ALLOW_WRITES=true`, accepts INSERT/UPDATE/DELETE only, and refuses UPDATE/DELETE without `WHERE`.
- **Swappable providers**: Gemini (free tier, default), Groq, OpenAI or Anthropic through one OpenAI-compatible client.
- **Tests without API keys**: an in-memory MCP client/server pair plus a scripted LLM. 35 tests run in CI for free.
## Quick start
Needs **Node.js 22 or newer**.
```bash
git clone https://github.com/obiokoye/mcp-sqlite-server.git
cd mcp-sqlite-server
npm install
cp .env.example .env # optional: add a free Gemini key from https://aistudio.google.com/apikey
npm run chat
```
The first run creates `data/shop.db`, a small bakery's sample orders (customers, products, orders, order_items). Ask things like:
- `What's in this database?`
- `Who are our top 3 customers by total spend?`
- `Which product category sold the most units in October?`
- `Delete all the cancelled orders` (watch the server refuse)
One-shot mode: `npm run chat -- "Who are our top 3 customers by total spend?"`
### Windows
```powershell
git clone https://github.com/obiokoye/mcp-sqlite-server.git
cd mcp-sqlite-server
npm install
copy .env.example .env
npm run chat
```
`better-sqlite3` ships prebuilt binaries for Windows. If `npm install` tries to compile it and fails, update to the current Node.js LTS from [nodejs.org](https://nodejs.org) and run `npm install` again.
## Demo mode
With no API key, `npm run chat` switches to **demo mode** automatically and prints `Demo mode: scripted AI replies`. You can also force it with `DEMO_MODE=true` or `npm run chat -- --demo`.
In demo mode only the "model" is scripted: the tool calls are real. They go over stdio to the real MCP server and run against the real database, and the scripted answers are built from the rows that come back. The scripted client is the same `ScriptedLlm` the tests use.
## Use it from Claude Desktop (or any MCP client)
Build once, then point your client at `dist/index.js`:
```bash
npm run build
```
`claude_desktop_config.json` (Windows: `%APPDATA%\Claude\claude_desktop_config.json`):
```json
{
"mcpServers": {
"sqlite": {
"command": "node",
"args": ["C:\\path\\to\\mcp-sqlite-server\\dist\\index.js"],
"env": { "DB_PATH": "data/shop.db" }
}
}
}
```
Restart Claude Desktop and ask it about your data. To poke at the server by hand, run `npm run inspect` (opens the official MCP Inspector).
### Server settings
| Variable | Default | What it does |
|----------------|----------------|--------------|
| `DB_PATH` | `data/shop.db` | SQLite file to serve, relative to the project folder. The default file is created with sample data if missing. |
| `ALLOW_WRITES` | `false` | `true` adds the `write_query` tool and opens a writable connection. |
| `MAX_ROWS` | `100` | Row cap per `read_query` (max 1000). |
### Tools, resource and prompt
| Name | Kind | Description |
|------|------|-------------|
| `list_tables` | tool | Every table with its row count |
| `describe_table` | tool | Columns, types, primary key and foreign keys |
| `read_query` | tool | One `SELECT`/`WITH` statement, rows as JSON |
| `write_query` | tool | Only when `ALLOW_WRITES=true`: one INSERT/UPDATE/DELETE |
| `sqlite://schema` | resource | `CREATE` statements for all tables and indexes |
| `explore-data` | prompt | Turns a business question into a guided, tool-using prompt |
## Switching providers (demo agent)
| `LLM_PROVIDER` | Key variable | Default model | Free tier |
|----------------|---------------------|---------------------------|-----------|
| `gemini` | `GEMINI_API_KEY` | `gemini-2.5-flash` | ✅ |
| `groq` | `GROQ_API_KEY` | `llama-3.3-70b-versatile` | ✅ |
| `openai` | `OPENAI_API_KEY` | `gpt-4o-mini` | ❌ |
| `anthropic` | `ANTHROPIC_API_KEY` | `claude-haiku-4-5` | ❌ |
Override the model with `LLM_MODEL=...`.
## Project structure
```text
src/
index.ts # MCP server entry point (stdio transport)
server.ts # registers tools, the schema resource and the prompt
db.ts # SQLite access + SQL guardrails
seed.ts # sample bakery data (npm run seed)
config.ts # server settings from env
chat.ts # terminal chat: spawns the server, connects an MCP client
agent/
agent.ts # agent loop: MCP tools -> LLM -> MCP tool calls
llm.ts # LLM interface + OpenAI-compatible client
providers.ts # provider table, demo-mode fallback
scripted.ts # ScriptedLlm (tests + demo mode)
demo-scenarios.ts # scripted demo conversations
tests/ # vitest, no network or API key needed
```
## Extending it
- **Add a tool**: write the function in `src/db.ts`, then `server.registerTool(...)` in `src/server.ts` with a zod `inputSchema`. Every MCP client (and the demo agent) picks it up through `tools/list` with no other changes.
- **Serve your own database**: set `DB_PATH=path/to/your.db`. The server opens it read-only.
- **Try another client**: the agent in `src/agent/agent.ts` works with any MCP server. Change the command in `chat.ts` to start a different one.
## Scripts
| Command | What it does |
|---------|--------------|
| `npm run chat` | Demo agent in the terminal |
| `npm run dev` | Run the MCP server from source (stdio) |
| `npm run build` / `npm start` | Compile to `dist/` / run the compiled server |
| `npm run seed` | Recreate `data/shop.db` |
| `npm run inspect` | Open the MCP Inspector against the built server |
| `npm test` | Run the tests |
New to TypeScript or MCP? See **[LEARN.md](LEARN.md)**, which walks through this codebase in Laravel terms.
---
MIT © Nicholas Okoye
TDQS
Scored across 3 tools
Each tool targets a clearly distinct step of database exploration: enumerating tables, inspecting a single table's schema, and running a query. There is no overlap between schema metadata retrieval and row retrieval, and the descriptions reinforce the boundaries.
All three names follow a clean verb_noun snake_case pattern (list_tables, describe_table, read_query), with distinct verbs that map directly to their actions.
Three tools is minimal but well-matched to a narrow read-only database inspection purpose; nothing is redundant. It sits at the thin edge of the ideal range, so a user wanting write access would find the surface small.
The read path is well covered (discover tables, inspect schema, query data), but there is no way to mutate data or run non-SELECT statements such as INSERT, UPDATE, DELETE, or DDL. Agents can explore but cannot modify the database, which is a notable gap for a general SQLite server.