Building a Local MCP Server: Exposing What the Filesystem Cannot Answer

An MCP server sounds like infrastructure. It is a program that reads JSON-RPC on stdin and writes it to stdout — which removes most of the mystique and all of the difficulty.

What a server actually is

An MCP server sounds like infrastructure. It is a process that reads JSON-RPC messages on stdin and writes them to stdout. That is the whole mechanism for a local server, and understanding it removes most of the mystique — you are writing a command-line program with a particular message format.

Remote servers use Server-Sent Events instead of stdio, which matters for deployment and not for the code you write. Start local. Almost everything worth exposing to an agent is on the machine already.

Three primitives, and which one you want

Nearly everyone wants tools. The distinction that matters: a resource is something you push into context whether or not it is needed, and a tool is something fetched on demand. If the data is large, changes often, or is only occasionally relevant, it is a tool. Resources are for the small and always-relevant, and most things people first reach for are neither.

A server worth building

The most useful first server exposes something the agent cannot get from the filesystem. A database schema is the canonical example: it lives in the database, not the repository, and an agent writing queries against a schema it has inferred from ORM models will get it subtly wrong.

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

import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import {
  CallToolRequestSchema,
  ListToolsRequestSchema,
  Tool,
} from "@modelcontextprotocol/sdk/types.js";
import pkg from "pg";

const { Pool } = pkg;

// A dedicated read-only role. Not your application user.
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const SCHEMA_TOOL: Tool = {
  name: "get_table_schema",
  description:
    "Column names, types, nullability and defaults for one table. " +
    "Use this before writing any query — do not infer the schema from " +
    "ORM models, which may be out of date.",
  inputSchema: {
    type: "object",
    properties: {
      tableName: { type: "string", description: "Exact table name" },
    },
    required: ["tableName"],
  },
};

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

server.setRequestHandler(ListToolsRequestSchema, async () => ({
  tools: [SCHEMA_TOOL],
}));

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

  if (name !== "get_table_schema") {
    return {
      content: [{ type: "text", text: `Unknown tool: ${name}` }],
      isError: true,
    };
  }

  const tableName = String(args?.tableName ?? "");

  // Validate before it reaches SQL. Parameterised below as well —
  // this is the cheap check that produces a useful error message.
  if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(tableName)) {
    return {
      content: [{ type: "text", text: "Invalid table name." }],
      isError: true,
    };
  }

  try {
    const { rows } = await pool.query(
      `SELECT column_name, data_type, is_nullable, column_default
         FROM information_schema.columns
        WHERE table_schema = 'public' AND table_name = $1
        ORDER BY ordinal_position`,
      [tableName],
    );

    if (rows.length === 0) {
      return {
        content: [
          { type: "text", text: `No table named '${tableName}' in schema public.` },
        ],
      };
    }

    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,
    };
  }
});

const transport = new StdioServerTransport();
await server.connect(transport);
console.error("schema-context server ready");   // stderr, never stdout

Registering it

{
  "mcpServers": {
    "schema-context": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-schema-server/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://readonly:pw@127.0.0.1:5432/dev"
      }
    }
  }
}

Absolute paths. The client does not run from your project directory, and a relative path that works when you test it manually will fail when the client launches it.

The description is the interface

The part people underinvest in is the tool description, and it determines whether the tool ever gets called. The model reads it and decides. A precise description of what the tool does, when to use it, and what it returns is worth more than any amount of implementation polish.

The right column tells the model when the tool applies and what its limits are. Both matter: a tool with a vague description is either never called or called for the wrong thing.

Failure is a return value, not an exception

Notice that the handler above never throws. Errors come back as content with isError: true, which means the model sees the message and can act on it — correct the table name, try a different approach, tell the user what went wrong.

A thrown exception produces a protocol-level error the model cannot read. The difference in behaviour is large: a readable error is a hint, and an unreadable one is a dead end.

Least privilege, concretely

The server runs with whatever credentials you give it, and it will be called by a model whose behaviour is influenced by everything in its context. Design for the case where it is called with hostile arguments.

CREATE ROLE mcp_readonly LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE dev TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO mcp_readonly;
-- Deliberately absent: INSERT, UPDATE, DELETE, DDL

With that role, the worst outcome from a fully steered session is disclosure of development data. With your application's credentials, it is anything the application can do. The role takes two minutes to create and is the difference between the two.

Three further constraints worth applying from the start: point it at development, never production; cap returned rows so a single call cannot exhaust the context window; and validate arguments before they reach a query, even when the query is parameterised, because a clear rejection message is more useful to the model than a database error.

Testing it without the client

A server is a program that reads stdin. You can drive it by hand, and doing so is much faster than debugging through an IDE.

echo '{"jsonrpc":"2.0","id":1,"method":"tools/list"}' \
  | node dist/index.js

echo '{"jsonrpc":"2.0","id":2,"method":"tools/call",
       "params":{"name":"get_table_schema",
                 "arguments":{"tableName":"orders"}}}' \
  | node dist/index.js

If those return sensible JSON, the server works and any remaining problem is configuration. If they do not, you have a tight loop for fixing it that does not involve restarting an editor.