Skip to main content
Glama
MCP SQLite Server

MCP SQLite Server

by obiokoye

🗄️ MCP SQLite Server

Day 10 of 30 Days of AI Agents: 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.

TypeScript Node Tests License

The demo agent querying the database through MCP, and the server blocking a DELETE

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.

Related MCP server: sqlite-mcp-local

Quick start

Needs Node.js 22 or newer.

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

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 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:

npm run build

claude_desktop_config.json (Windows: %APPDATA%\Claude\claude_desktop_config.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

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, which walks through this codebase in Laravel terms.


MIT © Nicholas Okoye

Available Tools

3 tools
describe_tableDescribe tableB
Read-only

Show a table's columns (name, type, primary key, not null) and foreign keys.

Inputs: table (required).

list_tablesList tablesA
Read-only

List every table in the database with its row count.

Inputs: no named parameters.

read_queryRun a read-only SQL queryA
Read-only

Run ONE SQLite SELECT (or WITH ... SELECT) statement and get rows back as JSON. At most 100 rows are returned; use LIMIT, GROUP BY and aggregates for large tables.

Inputs: sql (required).

Tool Schema Changelog

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

Loading tool schema changelog…

    TDQS

    A3.8/5.0

    Scored across 3 tools

    Disambiguation5/5

    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.

    Naming Consistency5/5

    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.

    Tool Count4/5

    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.

    Completeness3/5

    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.

    Maintenance

    ActivityMaintained
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • F
      license
      Not graded
      quality
      D
      maintenance
      Enables read-only querying of a local SQLite database via MCP, with tools to list tables, retrieve schema, and execute SELECT/WITH/EXPLAIN queries.
      -
    • A
      license
      Not graded
      quality
      D
      maintenance
      Enables read-only access to SQLite databases via MCP, with tools for browsing tables, schemas, and executing SELECT queries securely.
      MIT
    • A
      license
      Not graded
      quality
      B
      maintenance
      Enables MCP clients to interact with pre-registered SQLite databases using aliases, supporting schema inspection, read-only queries, and data modification via tools.
      70 npm
      MIT