🗄️ Build an MCP Server for PostgreSQL: Query Your Database with Claude & Any AI ClientHappy building

Source: Dev.to

Introduction

The Model Context Protocol (MCP) is transforming how AI applications interact with data sources. While weather servers and basic examples are great starting points, most real‑world applications need database access. In this guide, we’ll build a production‑ready MCP Server for PostgreSQL that allows Claude, VS Code, and any MCP client to query your database safely and efficiently.

By the end, you’ll have:

• A fully functional PostgreSQL MCP Server
• Examples for SELECT queries, aggregations, and data retrieval
• A public repository you can deploy to production
• Integration with Claude Desktop and other MCP clients

What Problem Does This Solve?

Imagine asking Claude:

“What are our top 10 customers by revenue this quarter?”

Or:

“Show me all incidents from the past 7 days with critical severity.”

With a PostgreSQL MCP Server, Claude can:

• Connect directly to your database
• Execute safe, predefined queries
• Return structured data
• Help you analyze and act on that data in real‑time

This is perfect for:

• BI dashboards powered by AI
• Incident management automation
• Customer data analysis
• Real‑time reporting and insights

Architecture Overview

Our MCP Server acts as a bridge between MCP clients (Claude, VS Code) and PostgreSQL:

MCP Client (Claude)
       |
    MCP Protocol
       |
    MCP Server (TypeScript)
       |
  pg (PostgreSQL Driver)
       |
  PostgreSQL Database

Prerequisites

• Node.js 18+
• TypeScript knowledge
• PostgreSQL database
• Basic MCP understanding

Step 1: Project Setup

Create the project:

mkdir mcp-postgres-server
cd mcp-postgres-server
npm init -y
npm install @modelcontextprotocol/sdk pg dotenv
npm install -D typescript @types/node @types/pg

Create tsconfig.json:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "ES2022",
    "moduleResolution": "node",
    "outDir": "./dist",
    "rootDir": "./src",
    "strict": true,
    "esModuleInterop": true
  }
}

Step 2: Database Service

Create src/database.ts:

import { Pool } from 'pg';
import dotenv from 'dotenv';

dotenv.config();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export class DatabaseService {
  async queryUsers(): Promise {
    const result = await pool.query(
      'SELECT id, name, email FROM users LIMIT 10'
    );
    return result.rows;
  }

  async queryIncidents(status: string): Promise {
    const result = await pool.query(
      'SELECT id, title, status, severity FROM incidents WHERE status = $1 ORDER BY created_at DESC',
      [status]
    );
    return result.rows;
  }

  async getStats(): Promise {
    const result = await pool.query(`
      SELECT 
        COUNT(*) as total_incidents,
        SUM(CASE WHEN severity = 'critical' THEN 1 ELSE 0 END) as critical_count,
        SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) as open_count
      FROM incidents
    `);
    return result.rows[0];
  }
}

Step 3: MCP Server Implementation

Create src/server.ts:

import { Server } from '@modelcontextprotocol/sdk/server/index.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import { CallToolRequestSchema, ListToolsRequestSchema } from '@modelcontextprotocol/sdk/types.js';
import { DatabaseService } from './database.js';

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

const db = new DatabaseService();

server.setRequestHandler(ListToolsRequestSchema, async () => ({
  tools: [
    {
      name: 'get_users',
      description: 'Retrieve recent users from the database',
      inputSchema: {
        type: 'object',
        properties: {},
        required: [],
      },
    },
    {
      name: 'get_open_incidents',
      description: 'Get all open incidents sorted by date',
      inputSchema: {
        type: 'object',
        properties: {},
        required: [],
      },
    },
    {
      name: 'get_database_stats',
      description: 'Get incident statistics (total, critical, open)',
      inputSchema: {
        type: 'object',
        properties: {},
        required: [],
      },
    },
  ],
}));

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

  try {
    if (name === 'get_users') {
      const users = await db.queryUsers();
      return {
        content: [{ type: 'text', text: JSON.stringify(users, null, 2) }],
      };
    }

    if (name === 'get_open_incidents') {
      const incidents = await db.queryIncidents('open');
      return {
        content: [{ type: 'text', text: JSON.stringify(incidents, null, 2) }],
      };
    }

    if (name === 'get_database_stats') {
      const stats = await db.getStats();
      return {
        content: [{ type: 'text', text: JSON.stringify(stats, null, 2) }],
      };
    }

    return {
      content: [{ type: 'text', text: `Unknown tool: ${name}` }],
      isError: true,
    };
  } catch (error) {
    return {
      content: [
        {
          type: 'text',
          text: `Error: ${error instanceof Error ? error.message : String(error)}`,
        },
      ],
      isError: true,
    };
  }
});

async function main() {
  const transport = new StdioServerTransport();
  await server.connect(transport);
  console.error('PostgreSQL MCP Server running on stdio');
}

main().catch(console.error);

Step 4: Configuration

Create .env:

DATABASE_URL=postgresql://user:password@localhost:5432/mydb

Update package.json scripts:

{
  "scripts": {
    "build": "tsc",
    "start": "node dist/server.js",
    "dev": "tsc && npm start"
  },
  "type": "module"
}

Step 5: Running Locally

npm run build
npm start

Step 6: Connecting to Claude Desktop

Edit your Claude Desktop config file:

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows: %APPDATA%\\Claude\\claude_desktop_config.json

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/absolute/path/to/dist/server.js"],
      "env": {
        "DATABASE_URL": "postgresql://user:password@localhost:5432/mydb"
      }
    }
  }
}

Restart Claude Desktop. You should now see a database icon and access to these tools!

Best Practices for Production

• Connection Pooling: Use pg Pool (already in code)
• Parameterized Queries: Always use $1, $2 placeholders
• Error Handling: Catch and return meaningful errors
• Validation: Validate tool inputs before querying
• Monitoring: Log all queries and response times
• Rate Limiting: Implement backpressure for high‑volume requests

Public Repository

Complete source code available at:
GitHub Repository: mcp‑postgres‑server

The repository includes:

• Full TypeScript implementation
• Docker support
• Deployment scripts
• Example schema
• Integration tests

Deployment Options

Google Cloud Run

gcloud run deploy mcp-postgres \
  --source . \
  --set-env-vars DATABASE_URL=your_connection_string

Docker Locally

docker build -t mcp-postgres .
docker run -e DATABA