← Back to DevBytes
Developer Tutorial August 16, 2026 7 min read

Building a SQL Query Agent with MCP (Model Context Protocol): Complete Guide

Introduction to MCP and SQL Query Agents

The Model Context Protocol (MCP) is an open standard that enables AI assistants to securely connect to external data sources and tools. By building a SQL Query Agent with MCP, you give large language models the ability to introspect database schemas, execute parameterized queries, and return structured results — all through a standardized protocol that any MCP-compatible client (Claude Desktop, Cursor, custom apps) can consume.

This tutorial walks through building a production-ready SQL Query Agent using the official MCP TypeScript SDK. By the end, you'll have a server that exposes your database as a set of tools and resources that any MCP client can invoke.

What Is the Model Context Protocol?

MCP defines a JSON-RPC 2.0-based protocol for communication between MCP clients (AI applications) and MCP servers (data/tool providers). A server can expose three primitives:

For a SQL agent, the most important primitive is the tool. The model decides which tool to call, the MCP server executes it against the database, and the result flows back to the model for synthesis into a natural-language answer.

Why Build a SQL Query Agent with MCP?

Traditional text-to-SQL pipelines require custom glue code for every LLM provider. MCP decouples the data layer from the model layer, giving you:

Prerequisites and Project Setup

You'll need Node.js 18+, a SQL database (we'll use SQLite for portability, but the pattern applies to Postgres, MySQL, etc.), and basic familiarity with TypeScript.

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

Create a sample database so the agent has something to query:

// seed.ts
import Database from "better-sqlite3";

const db = new Database("sample.db");
db.exec(`
  CREATE TABLE IF NOT EXISTS customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    signup_date TEXT,
    country TEXT
  );
  CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    amount REAL,
    order_date TEXT,
    status TEXT
  );
`);

const insertCustomer = db.prepare(
  "INSERT INTO customers (name, email, signup_date, country) VALUES (?, ?, ?, ?)"
);
const insertOrder = db.prepare(
  "INSERT INTO orders (customer_id, amount, order_date, status) VALUES (?, ?, ?, ?)"
);

insertCustomer.run("Alice Lee", "alice@example.com", "2024-01-15", "US");
insertCustomer.run("Bob Chen", "bob@example.com", "2024-02-20", "CN");
insertCustomer.run("Carla Diaz", "carla@example.com", "2024-03-10", "ES");

insertOrder.run(1, 99.50, "2024-04-01", "shipped");
insertOrder.run(1, 22.00, "2024-04-18", "shipped");
insertOrder.run(2, 150.75, "2024-05-02", "pending");
insertOrder.run(3, 49.99, "2024-05-12", "shipped");

db.close();
console.log("Database seeded.");

Run it with npx tsx seed.ts.

Building the MCP Server

Server Skeleton

Create server.ts. We'll use the stdio transport, which is the default for local MCP servers consumed by desktop clients.

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

const db = new Database("sample.db", { readonly: false });

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

// Tool and resource handlers go here...

const transport = new StdioServerTransport();
await server.connect(transport);
console.error("SQL MCP server running on stdio");

Exposing the Schema as a Resource

Before the model can write SQL, it needs to know the schema. Exposing it as a resource lets the client fetch it on demand.

server.setRequestHandler(ListResourcesRequestSchema, async () => ({
  resources: [
    {
      uri: "schema://main",
      name: "Database Schema",
      mimeType: "application/json",
      description: "Tables, columns, and types for the sample database",
    },
  ],
}));

server.setRequestHandler(ReadResourceRequestSchema, async (request) => {
  if (request.params.uri !== "schema://main") {
    throw new Error(`Unknown resource: ${request.params.uri}`);
  }

  const tables = db
    .prepare(
      `SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'`
    )
    .all() as { name: string }[];

  const schema = tables.map((table) => {
    const columns = db.prepare(`PRAGMA table_info(${table.name})`).all() as {
      name: string;
      type: string;
      notnull: number;
      pk: number;
    }[];
    return {
      table: table.name,
      columns: columns.map((c) => ({
        name: c.name,
        type: c.type,
        required: c.notnull === 1,
        primaryKey: c.pk === 1,
      })),
    };
  });

  return {
    contents: [
      {
        uri: request.params.uri,
        mimeType: "application/json",
        text: JSON.stringify(schema, null, 2),
      },
    ],
  };
});

Defining the Query Tool

The core of the agent is the run_query tool. We use Zod to validate inputs and enforce a read-only mode by default to prevent accidental data loss.

const QuerySchema = z.object({
  sql: z.string().min(1).describe("A single SQL SELECT statement"),
  params: z.array(z.any()).optional().describe("Bound parameters"),
});

server.setRequestHandler(ListToolsRequestSchema, async () => ({
  tools: [
    {
      name: "run_query",
      description:
        "Execute a read-only SQL query against the sample database. " +
        "Use the schema://main resource to inspect available tables first.",
      inputSchema: {
        type: "object",
        properties: {
          sql: { type: "string", description: "A single SQL SELECT statement" },
          params: {
            type: "array",
            items: {},
            description: "Bound parameters for the query",
          },
        },
        required: ["sql"],
      },
    },
  ],
}));

server.setRequestHandler(CallToolRequestSchema, async (request) => {
  if (request.params.name !== "run_query") {
    throw new Error(`Unknown tool: ${request.params.name}`);
  }

  const parsed = QuerySchema.safeParse(request.params.arguments);
  if (!parsed.success) {
    return {
      content: [{ type: "text", text: `Invalid input: ${parsed.error.message}` }],
      isError: true,
    };
  }

  const { sql, params = [] } = parsed.data;

  // Enforce read-only: reject anything that isn't a SELECT
  const normalized = sql.trim().toLowerCase();
  if (
    !normalized.startsWith("select") &&
    !normalized.startsWith("with")
  ) {
    return {
      content: [
        {
          type: "text",
          text: "Only SELECT or WITH ... SELECT queries are permitted.",
        },
      ],
      isError: true,
    };
  }

  try {
    const stmt = db.prepare(sql);
    const rows = stmt.all(...params);
    return {
      content: [
        {
          type: "text",
          text: JSON.stringify(rows, null, 2),
        },
      ],
    };
  } catch (err) {
    return {
      content: [
        { type: "text", text: `Query failed: ${(err as Error).message}` },
      ],
      isError: true,
    };
  }
});

Start the server with npx tsx server.ts. It will listen on stdio for MCP messages.

Connecting a Client

Wiring Up Claude Desktop

The fastest way to test your server is to register it with Claude Desktop. Edit claude_desktop_config.json:

{
  "mcpServers": {
    "sql-agent": {
      "command": "npx",
      "args": ["tsx", "/absolute/path/to/sql-mcp-agent/server.ts"]
    }
  }
}

Restart Claude Desktop. You can now ask questions like "What's the total order amount per customer?" and Claude will fetch the schema resource, write a SELECT query, call run_query, and explain the results.

Programmatic Client

For custom applications, use the MCP client SDK directly:

// client.ts
import { Client } from "@modelcontextprotocol/sdk/client/index.js";
import { StdioClientTransport } from "@modelcontextprotocol/sdk/client/stdio.js";
import { spawn } from "child_process";

const transport = new StdioClientTransport({
  command: "npx",
  args: ["tsx", "server.ts"],
});

const client = new Client(
  { name: "test-client", version: "1.0.0" },
  { capabilities: {} }
);

await client.connect(transport);

const tools = await client.listTools();
console.log("Available tools:", tools.tools.map((t) => t.name));

const result = await client.callTool({
  name: "run_query",
  arguments: {
    sql: "SELECT c.name, SUM(o.amount) AS total FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.name ORDER BY total DESC",
  },
});

console.log("Result:", result.content);

await client.close();

Best Practices

Extending the Agent

Once the basics work, consider adding these tools to make the agent more capable:

For Postgres or MySQL, swap better-sqlite3 for pg or mysql2 and adapt the schema introspection queries — the MCP scaffolding stays identical.

Conclusion

Building a SQL Query Agent with MCP gives you a clean, provider-agnostic way to let language models interact with your database. By exposing the schema as a resource and the query execution as a validated, read-only tool, you create a secure and reusable integration that any MCP-compatible client can consume. Start with the read-only pattern shown here, layer in audit logging and row limits, and gradually expand the tool surface as your use cases demand. The result is a SQL agent that is portable, observable, and ready for production.

— Ad —

Google AdSense will appear here after approval

← Back to all articles