How to build an MCP server for a SQLite database

{
  "name": "mcp-sqlite-demo",
  "private": true,
  "type": "module",
  "version": "1.0.0",
  "dependencies": {
    "@modelcontextprotocol/sdk": "^1.30.0",
    "zod": "^3.25.76"
  }
}

npm install @modelcontextprotocol/[email protected] [email protected]

// seed.js: create a demo SQLite database the server can read.
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("app.db");

db.exec(`
  DROP TABLE IF EXISTS customers;
  CREATE TABLE customers (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL,
    plan    TEXT NOT NULL,
    country TEXT NOT NULL,
    mrr     INTEGER NOT NULL
  );
`);

const insert = db.prepare(
  "INSERT INTO customers (name, plan, country, mrr) VALUES (?, ?, ?, ?)"
);
insert.run("Ada Lovelace", "pro", "GB", 49);
insert.run("Alan Turing", "team", "GB", 199);
insert.run("Grace Hopper", "pro", "US", 49);
insert.run("Katherine Johnson", "enterprise", "US", 999);

db.close();
console.log("Seeded app.db with 4 customers.");

// server.js: an MCP server that exposes a read-only SQLite database.
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { DatabaseSync } from "node:sqlite";
import { z } from "zod";

// Path to the database file. Default to app.db in the current directory.
const DB_PATH = process.env.SQLITE_DB_PATH ?? "app.db";

// Open read-only. The server can never write, drop, or alter the database.
const db = new DatabaseSync(DB_PATH, { readOnly: true });

const server = new McpServer({ name: "sqlite-server", version: "1.0.0" });

server.registerTool(
  "list_tables",
  {
    title: "List tables",
    description: "List the names of all tables in the database.",
    inputSchema: {},
  },
  async () => {
    const rows = db
      .prepare(
        "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
      )
      .all();
    const names = rows.map((r) => r.name);
    return { content: [{ type: "text", text: JSON.stringify(names) }] };
  }
);

server.registerTool(
  "describe_table",
  {
    title: "Describe table",
    description: "Return the columns and types of one table.",
    inputSchema: { table: z.string() },
  },
  async ({ table }) => {
    // Validate the name against the catalog before interpolating it.
    // PRAGMA does not accept bound parameters for the table name.
    const exists = db
      .prepare(
        "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?"
      )
      .get(table);
    if (!exists) {
      return {
        isError: true,
        content: [{ type: "text", text: `No such table: ${table}` }],
      };
    }
    const cols = db.prepare(`PRAGMA table_info(${JSON.stringify(table)})`).all();
    const schema = cols.map((c) => ({
      name: c.name,
      type: c.type,
      notnull: Boolean(c.notnull),
      pk: Boolean(c.pk),
    }));
    return { content: [{ type: "text", text: JSON.stringify(schema, null, 2) }] };
  }
);

server.registerTool(
  "query",
  {
    title: "Run a read-only SQL query",
    description:
      "Run a single read-only SELECT query and return the rows as JSON. Writes are rejected.",
    inputSchema: {
      sql: z.string(),
      params: z.array(z.union([z.string(), z.number(), z.null()])).optional(),
    },
  },
  async ({ sql, params = [] }) => {
    const trimmed = sql.trim().replace(/;+\s*$/, "");
    // Reject anything that is not a single SELECT/WITH statement.
    if (!/^(select|with)\b/i.test(trimmed)) {
      return {
        isError: true,
        content: [
          { type: "text", text: "Only SELECT queries are allowed." },
        ],
      };
    }
    if (/;/.test(trimmed)) {
      return {
        isError: true,
        content: [
          { type: "text", text: "Only a single statement is allowed." },
        ],
      };
    }
    try {
      const rows = db.prepare(trimmed).all(...params);
      return { content: [{ type: "text", text: JSON.stringify(rows, null, 2) }] };
    } catch (err) {
      return {
        isError: true,
        content: [{ type: "text", text: `Query failed: ${err.message}` }],
      };
    }
  }
);

await server.connect(new StdioServerTransport());

// client.js: connect to the SQLite MCP server and exercise every tool.
import { Client } from "@modelcontextprotocol/sdk/client/index.js";
import { StdioClientTransport } from "@modelcontextprotocol/sdk/client/stdio.js";

const transport = new StdioClientTransport({ command: "node", args: ["server.js"] });
const client = new Client({ name: "demo-client", version: "1.0.0" });
await client.connect(transport);

const call = async (name, args) => {
  const r = await client.callTool({ name, arguments: args });
  return r.content[0].text + (r.isError ? "  [isError]" : "");
};

console.log("TOOLS:", (await client.listTools()).tools.map((t) => t.name).join(", "));
console.log("list_tables ->", await call("list_tables", {}));
console.log("describe_table ->", await call("describe_table", { table: "customers" }));
console.log("query (pro plan) ->", await call("query", {
  sql: "SELECT name, mrr FROM customers WHERE plan = ? ORDER BY mrr DESC",
  params: ["pro"],
}));
console.log("query (aggregate) ->", await call("query", {
  sql: "SELECT country, SUM(mrr) AS total FROM customers GROUP BY country ORDER BY total DESC",
}));
console.log("write blocked ->", await call("query", { sql: "DELETE FROM customers" }));
console.log("multi blocked ->", await call("query", { sql: "SELECT 1; DROP TABLE customers" }));
console.log("bad table ->", await call("describe_table", { table: "nope" }));

await client.close();

TOOLS: list_tables, describe_table, query
list_tables -> ["customers"]
describe_table -> [ ... id, name, plan, country, mrr ... ]
query (pro plan) -> [{ "name": "Ada Lovelace", "mrr": 49 }, { "name": "Grace Hopper", "mrr": 49 }]
query (aggregate) -> [{ "country": "US", "total": 1048 }, { "country": "GB", "total": 248 }]
write blocked -> Only SELECT queries are allowed.  [isError]
multi blocked -> Only a single statement is allowed.  [isError]
bad table -> No such table: nope  [isError]