Building an MCP Server for Your Database (Postgres Example)

Why your database needs an MCP server, not another SQL-writing prompt

Every AI engineering team eventually hits the same wall: the model is great at reasoning, terrible at knowing your schema, and one bad prompt away from running DROP TABLE users in production. You can keep pasting schema dumps into the system prompt, or you can build the missing piece that turns your database into something an agent can actually use safely — an MCP server.

The Model Context Protocol (MCP) gives you a standard interface between an AI application (the "host," like Claude Desktop, an IDE, or your own agent runtime) and an external system (in this case, Postgres). Instead of hand-rolling a custom function-calling layer for every model you use, you write one MCP server, and every MCP-compatible client — Claude Code, Claude Desktop, or a custom SDK agent — can talk to your database through it.

This matters more than it sounds. Ad-hoc "let the LLM write SQL" integrations tend to leak three ways: unrestricted query execution, no audit trail, and prompt-injected instructions that get treated as trusted commands. An MCP server lets you put a real boundary around what the model can do — specific tools, specific resources, specific permissions — instead of a raw SQL socket with a system prompt taped in front of it.

In this article we'll build a working MCP server for Postgres in TypeScript, then look at the equivalent Python approach, cover schema introspection, read/write safety, connection pooling, and how to wire it into Claude Desktop or Claude Code. By the end you'll have a pattern you can reuse for MySQL, SQLite, or any other data store.

What MCP actually gives you over a plain function-calling API

Before writing code, it's worth being precise about what problem MCP solves, because "just expose a function" is a fair question.

Three things MCP standardizes that you'd otherwise reinvent per-client:

• Discovery. A client can ask your server "what tools do you have?" and get back structured metadata (name, description, JSON Schema for inputs) without you writing custom docs for each host application.
• Resources vs tools. MCP separates read-only context (resources, like "here's the current schema") from actions (tools the model actively invokes with arguments). This distinction alone prevents a lot of accidental-write bugs, because you can expose schema as a resource nobody has to "call" and keep mutations behind explicit, narrowly-scoped tools.
• Transport-agnostic hosting. The same server can run over stdio (for local desktop clients) or over HTTP/SSE (for remote, multi-user deployments) without changing your tool logic.

For a database specifically, this means you can expose:

• A list_tables tool or resource for schema discovery
• A describe_table tool for column-level detail
• A query_database tool that runs read-only SQL with guardrails
• Optionally, a tightly scoped run_migration or execute_write tool gated behind explicit confirmation

That's the shape we'll build.

Project setup

We'll use the official TypeScript SDK, @modelcontextprotocol/sdk, plus pg for Postgres connectivity.

mkdir mcp-postgres-server && cd mcp-postgres-server
npm init -y
npm install @modelcontextprotocol/sdk pg zod
npm install -D typescript @types/node @types/pg tsx
npx tsc --init

Set up a minimal tsconfig.json target (Node16/ESNext module resolution works well with the SDK), and add a .env file for your connection string:

DATABASE_URL=postgres://app_user:password@localhost:5432/teachyou_dev

Important first decision: create a dedicated Postgres role for this server. Don't reuse your app's admin credentials. We'll come back to this in the security section, but set it up now:

CREATE ROLE mcp_reader WITH LOGIN PASSWORD 'change_me';
GRANT CONNECT ON DATABASE teachyou_dev TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_reader;

This role can read everything but write nothing — which means even if the model gets tricked by a prompt injection into requesting a destructive query, the database itself refuses it. That's the guardrail that actually matters; everything else is defense in depth.

Building the server: connection layer and schema resource

Start with a connection pool and a resource that exposes the schema. Resources in MCP are meant for exactly this — static or slowly-changing context the client can fetch without "calling" anything.

// db.ts
import { Pool } from "pg";

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 5,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

export async function getSchemaSummary(): Promise<string> {
  const { rows } = await pool.query(`
    SELECT table_name, column_name, data_type, is_nullable
    FROM information_schema.columns
    WHERE table_schema = 'public'
    ORDER BY table_name, ordinal_position
  `);

  const byTable = new Map<string, string[]>();
  for (const row of rows) {
    const line = `${row.column_name} ${row.data_type}${row.is_nullable === "NO" ? " NOT NULL" : ""}`;
    if (!byTable.has(row.table_name)) byTable.set(row.table_name, []);
    byTable.get(row.table_name)!.push(line);
  }

  return [...byTable.entries()]
    .map(([table, cols]) => `## ${table}\n${cols.map((c) => `  - ${c}`).join("\n")}`)
    .join("\n\n");
}

Now the server entry point, registering that summary as a resource:

// server.ts
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { getSchemaSummary } from "./db.js";

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

server.resource(
  "schema",
  "postgres://schema",
  { mimeType: "text/plain", description: "Live table and column definitions" },
  async (uri) => ({
    contents: [{ uri: uri.href, text: await getSchemaSummary() }],
  })
);

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

Notice what this buys you already: a client can pull postgres://schema once at the start of a session and cache it, instead of the model guessing column names or you re-pasting DDL into every prompt. Schema drift stops being a prompt-maintenance problem — the resource is always live.

Adding tools: safe reads first

Tools are where the model actually does things. Define inputs with zod so the SDK generates a proper JSON Schema and validates arguments before your handler ever runs.

// tools/listTables.ts
import { z } from "zod";
import { pool } from "../db.js";

export const listTablesTool = {
  name: "list_tables",
  description: "List all tables in the public schema with row count estimates",
  inputSchema: z.object({}),
  handler: async () => {
    const { rows } = await pool.query(`
      SELECT relname AS table_name, n_live_tup AS approx_rows
      FROM pg_stat_user_tables
      ORDER BY relname
    `);
    return {
      content: [{ type: "text", text: JSON.stringify(rows, null, 2) }],
    };
  },
};

Register it, and add the more useful query_database tool — the one that actually lets the model answer arbitrary questions about your data:

// tools/queryDatabase.ts
import { z } from "zod";
import { pool } from "../db.js";

const FORBIDDEN = /\b(insert|update|delete|drop|alter|truncate|grant|revoke)\b/i;

export const queryDatabaseTool = {
  name: "query_database",
  description:
    "Run a read-only SQL SELECT query against the application database. " +
    "Only SELECT statements are permitted. Always include a LIMIT clause.",
  inputSchema: z.object({
    sql: z.string().describe("A single SELECT statement"),
  }),
  handler: async ({ sql }: { sql: string }) => {
    const trimmed = sql.trim().replace(/;$/, "");

    if (!/^select/i.test(trimmed)) {
      throw new Error("Only SELECT statements are allowed.");
    }
    if (FORBIDDEN.test(trimmed)) {
      throw new Error("Query contains a forbidden keyword.");
    }
    if (!/limit\s+\d+/i.test(trimmed)) {
      throw new Error("Query must include a LIMIT clause.");
    }

    const client = await pool.connect();
    try {
      await client.query("SET statement_timeout = 3000");
      const result = await client.query(trimmed);
      return {
        content: [
          { type: "text", text: JSON.stringify(result.rows, null, 2) },
        ],
      };
    } finally {
      client.release();
    }
  },
};

Three guardrails stacked here, deliberately redundant:

1. Application-level string checks — reject non-SELECT statements and forbidden keywords before the query ever reaches Postgres.
2. A statement timeout — a runaway query on a large table can't hang the connection pool.
3. A read-only database role (from the setup step) — even if the first two layers have a bug, Postgres itself rejects writes.

Register both tools in server.ts:

import { listTablesTool } from "./tools/listTables.js";
import { queryDatabaseTool } from "./tools/queryDatabase.js";

for (const tool of [listTablesTool, queryDatabaseTool]) {
  server.tool(
    tool.name,
    tool.description,
    tool.inputSchema.shape,
    tool.handler
  );
}

Handling writes deliberately (if you need them at all)

Most database MCP servers should stay read-only — that's the honest recommendation, and it covers the majority of real use cases: analytics questions, debugging, report generation, ad-hoc data exploration. But if you genuinely need the agent to write data (say, an internal admin tool that creates records), don't reuse query_database. Build a separate, narrow tool per write operation, with fixed shape and no free-form SQL.

// tools/createEnrollment.ts
import { z } from "zod";
import { pool } from "../db.js";

export const createEnrollmentTool = {
  name: "create_enrollment",
  description: "Enroll a user in a course. Requires explicit user and course IDs.",
  inputSchema: z.object({
    userId: z.string().uuid(),
    courseId: z.string().uuid(),
  }),
  handler: async ({ userId, courseId }: { userId: string; courseId: string }) => {
    const { rows } = await pool.query(
      `INSERT INTO enrollments (user_id, course_id, created_at)
       VALUES ($1, $2, now())
       ON CONFLICT (user_id, course_id) DO NOTHING
       RETURNING id`,
      [userId, courseId]
    );
    return {
      content: [
        {
          type: "text",
          text: rows.length
            ? `Enrollment created: ${rows[0].id}`
            : "Enrollment already existed, no changes made.",
        },
      ],
    };
  },
};

Notice this is parameterized SQL with a fixed statement — no string concatenation, no model-authored SQL, no injection surface. The model chooses *when* to call create_enrollment and *with what arguments*, but it never gets to shape the query itself. That's the general rule for MCP write tools: the model picks intent and parameters, your code owns the SQL.

If you want a middle ground — model-authored SQL that can also write — pair it with a host-side confirmation step (most MCP clients, including Claude Desktop and Claude Code, prompt the user before executing a tool call flagged as mutating) and log every invocation with the resolved SQL and arguments to an audit table.

The Python equivalent

If your stack is Python, the mcp package gives you the same primitives. Here's a compact version of the same server using asyncpg:

# server.py
import asyncpg
import os
from mcp.server.fastmcp import FastMCP

mcp = FastMCP("postgres-mcp-server")
FORBIDDEN = ("insert", "update", "delete", "drop", "alter", "truncate")

_pool: asyncpg.Pool | None = None

async def get_pool() -> asyncpg.Pool:
    global _pool
    if _pool is None:
        _pool = await asyncpg.create_pool(
            dsn=os.environ["DATABASE_URL"],
            min_size=1,
            max_size=5,
            command_timeout=5,
        )
    return _pool

@mcp.resource("postgres://schema")
async def schema_summary() -> str:
    pool = await get_pool()
    rows = await pool.fetch("""
        SELECT table_name, column_name, data_type
        FROM information_schema.columns
        WHERE table_schema = 'public'
        ORDER BY table_name, ordinal_position
    """)
    tables: dict[str, list[str]] = {}
    for r in rows:
        tables.setdefault(r["table_name"], []).append(
            f"{r['column_name']} {r['data_type']}"
        )
    return "\n\n".join(
        f"## {t}\n" + "\n".join(f"  - {c}" for c in cols)
        for t, cols in tables.items()
    )

@mcp.tool()
async def query_database(sql: str) -> str:
    """Run a read-only SELECT query. Must include a LIMIT clause."""
    cleaned = sql.strip().rstrip(";")
    lowered = cleaned.lower()

    if not lowered.startswith("select"):
        raise ValueError("Only SELECT statements are allowed.")
    if any(word in lowered for word in FORBIDDEN):
        raise ValueError("Query contains a forbidden keyword.")
    if "limit" not in lowered:
        raise ValueError("Query must include a LIMIT clause.")

    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(cleaned)
        return "\n".join(str(dict(r)) for r in rows)

if __name__ == "__main__":
    mcp.run(transport="stdio")

FastMCP handles the tool/resource registration and JSON Schema generation from type hints and docstrings, which keeps the Python version noticeably shorter than the manual TypeScript registration — a fair tradeoff if you're already Python-first for data tooling.

Connecting your server to Claude Desktop and Claude Code

Once the server runs locally, wire it into a host. For Claude Desktop, edit the config file (~/Library/Application Support/Claude/claude_desktop_config.json on macOS) and add an entry under mcpServers:

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-postgres-server/dist/server.js"],
      "env": {
        "DATABASE_URL": "postgres://mcp_reader:change_me@localhost:5432/teachyou_dev"
      }
    }
  }
}

Restart Claude Desktop and the tools (list_tables, query_database) show up automatically in the tool picker.

For Claude Code, register it as a project-level or user-level MCP server:

claude mcp add postgres -- node /absolute/path/to/mcp-postgres-server/dist/server.js

Or drop the equivalent block into .claude/settings.json under mcpServers if you want it checked into the repo for teammates. Either way, once connected you can ask Claude Code things like "how many users signed up last week" and it will call query_database with a generated SELECT ... LIMIT 100, read the schema resource first if it needs column names, and return the answer — no manual SQL from you, and no direct database credentials in the model's context.

Testing your server without a full host

Before wiring anything into a chat client, test the server directly. The MCP SDK ships an inspector for exactly this:

npx @modelcontextprotocol/inspector node dist/server.js

This opens a local web UI where you can list tools, submit arguments by hand, and inspect raw JSON responses — invaluable for catching schema mistakes (like a zod type that doesn't match what you documented) before an LLM ever touches it. Run every new tool through the inspector at least once: confirm the input schema renders correctly, that error messages are clear text (not stack traces — models handle plain error strings far better), and that large result sets get truncated sensibly rather than blowing out the context window.

A quick manual test worth adding to your checklist:

• Call query_database with a valid SELECT ... LIMIT 10 — confirm rows come back as text.
• Call it with DROP TABLE users — confirm you get a rejection, not a stack trace, and definitely not a dropped table.
• Call it with a query missing LIMIT — confirm the guardrail fires.
• Check pg_stat_activity while a slow query runs — confirm the statement timeout actually kills it.

Production considerations: pooling, observability, and least privilege

A few things that matter once this moves past your laptop:

• Connection pooling. Keep the pool small (5-10 connections) for an MCP server — it's typically serving one interactive session at a time, not a web app's concurrent traffic. If you're deploying over HTTP/SSE for multiple simultaneous users, consider PgBouncer in front of Postgres rather than growing the Node/Python pool unbounded.
• Logging every tool call. Log the tool name, resolved arguments, calling user (if you have multi-user auth on the transport), row count returned, and duration. When something goes wrong — and with LLM-generated SQL, eventually something will look odd — you want to be able to answer "what did the model actually run" without guessing.
• Least privilege, always. The mcp_reader role from earlier should never have write grants, full stop. If you add a write tool, give it its own role scoped to only the tables and even only the columns it needs, not blanket INSERT/UPDATE on the whole schema.
• Row and column limits. Cap query_database results server-side (say, 500 rows max) regardless of what LIMIT the model requests, so a huge unfiltered pull doesn't blow past the client's context window or your Postgres memory.
• Timeouts everywhere. statement_timeout at the query level, connectionTimeoutMillis at the pool level, and a wrapper timeout at the tool handler level if your SDK version doesn't already enforce one.
• Treat the model's SQL as untrusted input, not because the model is malicious, but because it's often working from an ambiguous natural-language request and prompt injection from retrieved data (e.g., a course description containing hidden instructions) is a real, documented failure mode for tool-using agents.

None of these are exotic — they're the same production hygiene you'd apply to any API endpoint that touches a database. MCP doesn't remove that responsibility; it just gives you one clean seam to enforce it at, instead of scattering ad-hoc checks across every LLM integration in your codebase.

Where this pattern goes next

Once you have a working Postgres MCP server, the extension path is straightforward: add a describe_table tool for column-level detail on demand rather than dumping the entire schema resource every time, add pagination to query_database for large result sets, and consider a second read replica connection string so analytical queries never compete with production traffic. The same skeleton — resource for schema, one guarded read tool, narrow parameterized write tools if needed — works for MySQL, SQLite, or even a REST-fronted data warehouse; only the connection library and the SQL dialect change.

The bigger shift is architectural, not just technical: once your database is behind an MCP server, every agent you build afterward — a support bot, an internal analytics assistant, a code review agent that checks migration safety — gets database access through the same audited, permission-scoped path instead of a new one-off integration each time. That's the actual payoff of standardizing on MCP: you write the guardrails once, and every future agent inherits them for free.

If you want to go deeper on this — building multiple tool servers, composing them into a single agent runtime, handling auth and multi-tenant MCP deployments, and debugging tool-call failures in production — that's exactly the ground we cover in Building & Integrating MCP Servers on TeachYou.ai, with hands-on labs that go beyond a single Postgres example into the full lifecycle of production MCP tooling.