How to build a custom MCP server for local SQLite databases

The Model Context Protocol (MCP) is a game-changer for Cursor and Claude Desktop because it stops the "context guessing game" when dealing with local data. Instead of dumping a 50MB SQL export into a chat window, building a custom MCP server lets the AI query your SQLite database in real-time, only pulling the rows it actually needs.

I spent the weekend setting up a local SQLite MCP server to manage my project's metadata, and the productivity jump is massive. You aren't just asking the AI to "write a query"; you're giving it a tool to execute that query and see the result.

To get this running, you need the @modelcontextprotocol/sdk. I recommend using TypeScript for the server because the type safety prevents the AI from hallucinating tool arguments that the SQLite driver can't handle.

Here is the core logic for the tool handler. The key is defining a query tool that the AI can call:

import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { CallToolRequestSchema, ListToolsRequestSchema } from "@modelcontextprotocol/sdk/types.js";
import Database from "better-sqlite3";

const db = new Database("local_data.db");

const server = new Server({
  name: "sqlite-explorer",
  version: "1.0.0",
}, {
  capabilities: { tools: {} },
});

server.setRequestHandler(ListToolsRequestSchema, async () => ({
  tools: [{
    name: "execute_query",
    description: "Run a read-only SQL query against the local SQLite database",
    inputSchema: {
      type: "object",
      properties: {
        sql: { type: "string", description: "The SQL query to execute" },
      },
      required: ["sql"],
    },
  }],
}));

server.setRequestHandler(CallToolRequestSchema, async (request) => {
  if (request.params.name === "execute_query") {
    const sql = request.params.arguments?.sql as string;
    try {
      const rows = db.prepare(sql).all();
      return {
        content: [{ type: "text", text: JSON.stringify(rows) }],
      };
    } catch (err: any) {
      return {
        content: [{ type: "text", text: `SQL Error: ${err.message}` }],
        isError: true,
      };
    }
  }
  throw new Error("Tool not found");
});

const transport = new StdioServerTransport();
await server.connect(transport);

To hook this into Cursor or Claude Desktop, you have to edit your claude_desktop_config.json. If you're using ts-node for quick iterations, the config looks like this:

Configuration details:

• command: node
• args: [/absolute/path/to/your/server/dist/index.js]
• env: Add any necessary environment variables here, though SQLite usually just needs the file path.

One major "gotcha" I hit: the AI loves to write complex joins that occasionally timeout or lock the database if you have other processes writing to it. I highly recommend using better-sqlite3 over the standard sqlite3 package because it's synchronous and significantly faster for these small, rapid-fire tool calls.

Another pro tip: don't just give the AI a query tool. If your database is large, the AI might struggle to know the schema. I added a second tool called get_schema that runs SELECT sql FROM sqlite_master WHERE type='table';. This allows the AI to "inspect" the database structure before it attempts to write a query, which drastically reduces the number of SQL syntax errors.

The real gain here is the loop. I can now say "Find all users who signed up in October and summarize their activity," and the AI writes the SQL, executes it via the MCP server, reads the JSON result, and gives me the answer—all without me leaving the editor or manually running a CLI tool.