Building a Database MCP Tool with PostgreSQL

Let AI agents explore your database safely. This tutorial builds a production-ready PostgreSQL MCP tool.

What You'll Build

• list_tables — discover database schema
• describe_table — get column details
• run_query — execute read-only SQL queries
• Safety guardrails against writes and dangerous operations

Step 1: Setup

mkdir pg-mcp && cd pg-mcp
npm init -y
npm install @modelcontextprotocol/sdk pg

Step 2: Database Connection

import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 5, // Limit connections
  idleTimeoutMillis: 30000
});

// Test connection on startup
pool.query("SELECT 1").then(() => {
  console.error("✅ Database connected");
}).catch(err => {
  console.error("❌ Database connection failed:", err.message);
  process.exit(1);
});

Step 3: Define Tools

server.setRequestHandler("tools/list", async () => ({
  tools: [
    {
      name: "list_tables",
      description: "List all tables in the database with row counts",
      inputSchema: {
        type: "object",
        properties: {
          schema: {
            type: "string",
            description: "Schema name (default: public)"
          }
        }
      }
    },
    {
      name: "describe_table",
      description: "Get column names, types, and constraints for a table",
      inputSchema: {
        type: "object",
        properties: {
          table: {
            type: "string",
            description: "Table name"
          }
        },
        required: ["table"]
      }
    },
    {
      name: "run_query",
      description: "Execute a read-only SQL query (SELECT only)",
      inputSchema: {
        type: "object",
        properties: {
          query: {
            type: "string",
            description: "SQL SELECT query to execute"
          },
          limit: {
            type: "number",
            description: "Maximum rows to return (default: 100, max: 1000)"
          }
        },
        required: ["query"]
      }
    }
  ]
}));

Step 4: Implement Safety Guards

function isReadOnly(query) {
  const trimmed = query.trim().toUpperCase();
  const dangerous = ["INSERT", "UPDATE", "DELETE", "DROP", "ALTER",
                     "CREATE", "TRUNCATE", "GRANT", "REVOKE"];
  return !dangerous.some(cmd => trimmed.startsWith(cmd));
}

function sanitizeIdentifier(name) {
  // Only allow alphanumeric and underscore
  return name.replace(/[^a-zA-Z0-9_]/g, "");
}

Step 5: Handle Tool Calls

case "run_query": {
  const { query, limit = 100 } = args;

  // Safety check
  if (!isReadOnly(query)) {
    return {
      content: [{
        type: "text",
        text: "❌ Only SELECT queries are allowed for safety."
      }]
    };
  }

  // Add limit if not present
  const finalQuery = query.toUpperCase().includes("LIMIT")
    ? query
    : `${query} LIMIT ${Math.min(limit, 1000)}`;

  try {
    const result = await pool.query(finalQuery);
    const columns = result.fields.map(f => f.name);
    const rows = result.rows.slice(0, limit);

    // Format as markdown table
    let output = `📊 Query returned ${result.rowCount} rows\n\n`;
    output += `| ${columns.join(" | ")} |\n`;
    output += `| ${columns.map(() => "---").join(" | ")} |\n`;

    for (const row of rows) {
      output += `| ${columns.map(c => String(row[c] ?? "NULL").slice(0, 50)).join(" | ")} |\n`;
    }

    if (result.rowCount > limit) {
      output += `\n*Showing first ${limit} of ${result.rowCount} rows*`;
    }

    return { content: [{ type: "text", text: output }] };
  } catch (err) {
    return {
      content: [{
        type: "text",
        text: `❌ Query error: ${err.message}`
      }]
    };
  }
}

Step 6: Deploy and Publish

# Set your database URL
export DATABASE_URL="postgresql://user:pass@localhost:5432/mydb"

# Test
node index.js

# Publish
mcpm-dev publish

Configuration for Users

In their Claude/Cursor/Windsurf config:

{
  "mcpServers": {
    "pg-mcp": {
      "command": "npx",
      "args": ["-y", "pg-mcp"],
      "env": {
        "DATABASE_URL": "postgresql://user:pass@host:5432/db"
      }
    }
  }
}

Security Checklist

• ✅ Read-only queries enforced
• ✅ Connection pooling with limits
• ✅ Row limits on all queries
• ✅ Identifier sanitization
• ✅ No raw error exposure
• ✅ Timeout on long queries

Your AI agents can now safely explore your database. 🎉

#MCP #Tutorial #PostgreSQL #Database #mcpm