AI
LearningsBlog
By Mohan Sai Puttagunta3 min read

Building a Production-Ready SQLite MCP Server in TypeScript

A hands-on tutorial on developing, packaging, and testing a Model Context Protocol (MCP) server that exposes database exploration tools to LLMs.

In this tutorial, we will build an enterprise-safe Model Context Protocol (MCP) server in TypeScript. The server allows AI assistants to safely query a local SQLite database, introspect schema definitions, and execute parameterized queries without risking data corruption.


Prerequisites

  • Node.js version 20+ installed
  • Basic TypeScript familiarity

1. Project Initialization

Create a directory and initialize npm:

mkdir sqlite-mcp-server
cd sqlite-mcp-server
npm init -y
npm install @modelcontextprotocol/sdk better-sqlite3 zod
npm install -D typescript @types/better-sqlite3 @types/node tsx
npx tsc --init

Update your tsconfig.json target and module resolution:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "NodeNext",
    "moduleResolution": "NodeNext",
    "outDir": "./dist",
    "rootDir": "./src",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true
  },
  "include": ["src/**/*"]
}

2. Server Implementation

Create src/index.ts:

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

// Connect to SQLite DB in read-only mode for safety
const db = new Database("analytics.db", { readonly: true });

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

// 1. Expose Tools
server.setRequestHandler(ListToolsRequestSchema, async () => {
  return {
    tools: [
      {
        name: "list_tables",
        description: "List all database tables and view names.",
        inputSchema: {
          type: "object",
          properties: {},
        },
      },
      {
        name: "describe_table",
        description: "Get column names and schema types for a specific table.",
        inputSchema: {
          type: "object",
          properties: {
            tableName: { type: "string", description: "Name of the table" },
          },
          required: ["tableName"],
        },
      },
      {
        name: "query_readonly",
        description: "Execute a read-only SELECT SQL query with a limit applied.",
        inputSchema: {
          type: "object",
          properties: {
            sql: { type: "string", description: "SQL SELECT statement" },
          },
          required: ["sql"],
        },
      },
    ],
  };
});

// 2. Handle Tool Invocations
server.setRequestHandler(CallToolRequestSchema, async (request) => {
  const { name, arguments: args } = request.params;

  if (name === "list_tables") {
    const stmt = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';");
    const tables = stmt.all();
    return {
      content: [{ type: "text", text: JSON.stringify(tables, null, 2) }],
    };
  }

  if (name === "describe_table") {
    const { tableName } = args as { tableName: string };
    const stmt = db.prepare(`PRAGMA table_info(${tableName});`);
    const cols = stmt.all();
    return {
      content: [{ type: "text", text: JSON.stringify(cols, null, 2) }],
    };
  }

  if (name === "query_readonly") {
    const { sql } = args as { sql: string };
    
    // Safety check: ensure query begins with SELECT
    const trimmed = sql.trim().toUpperCase();
    if (!trimmed.startsWith("SELECT") && !trimmed.startsWith("WITH")) {
      throw new Error("Only SELECT queries are permitted.");
    }

    const stmt = db.prepare(sql);
    const rows = stmt.all();
    return {
      content: [{ type: "text", text: JSON.stringify(rows.slice(0, 50), null, 2) }],
    };
  }

  throw new Error(`Unknown tool: ${name}`);
});

// 3. Connect via Standard Input/Output
async function run() {
  const transport = new StdioServerTransport();
  await server.connect(transport);
  console.error("SQLite MCP server running on stdio.");
}

run().catch((error) => {
  console.error("Fatal error running server:", error);
  process.exit(1);
});

3. Configuring the AI Host

To test this server in Claude Desktop or Cursor, update your configuration:

{
  "mcpServers": {
    "sqlite-explorer": {
      "command": "node",
      "args": ["/absolute/path/to/sqlite-mcp-server/dist/index.js"]
    }
  }
}

Now, the AI assistant can automatically list tables, inspect schema definitions, and compose correct SQL queries against your local SQLite database without manual prompt grounding!

Share:

Frequently Asked Questions

How do I connect Claude Desktop or Cursor to a custom local MCP server?

You add the command and arguments pointing to your built server script (e.g. 'node /path/to/dist/index.js') to the client's configuration file (e.g., claude_desktop_config.json).

Why is stdio preferred for local MCP development?

Standard I/O requires zero network configuration, eliminates port conflicts, and automatically binds the server lifecycle directly to the host client process.

Comments

Type to start searching AI Learnings...
↑↓ Navigate↵ Select
Instant client-side search