TypeScript MCP Server with PostgreSQL

Overview

This template provides a production-ready Model Context Protocol (MCP) server built with TypeScript, using HTTP transport and API key authentication. It integrates directly with PostgreSQL for persistent data storage, giving your AI tools real database capabilities — not just in-memory state that disappears on restart.

The server ships with three fully implemented tools: one for querying and filtering database records, one for creating structured data entries, and one for running analytics aggregations. These tools demonstrate the full lifecycle of an MCP tool: input validation, authenticated access, database interaction, structured error handling, and typed responses. Every pattern shown here is directly transferable to your own domain-specific tools.

This template is aimed at TypeScript developers who are comfortable with Node.js and SQL, and who want to move beyond toy MCP examples into something they can actually deploy. Whether you're building internal tooling, a SaaS product feature, or a personal productivity server, this template gives you the foundation — Docker, health checks, correlation IDs, graceful shutdown, and all.

What You'll Learn

• How to initialise and configure an MCP server using the @modelcontextprotocol/sdk package with HTTP/SSE transport
• How to implement API key authentication middleware that validates keys on every request
• How to register MCP tools with full Zod input schemas and typed handler functions
• How to connect to PostgreSQL using pg with connection pooling and graceful shutdown
• How to structure a TypeScript project with separation of concerns across config, auth, logging, database, and tools
• How to set up Pino for structured JSON logging with request correlation IDs and configurable log levels
• How to build a /health endpoint that checks each dependency and returns machine-readable status
• How to write multi-stage Dockerfiles that produce lean production images
• How to use docker-compose.yml for local development with hot reload and a local PostgreSQL instance
• How to validate all environment variables at startup to fail fast with clear error messages
• How to implement graceful shutdown handling for SIGTERM and SIGINT signals
• How to handle MCP tool errors in a way that surfaces useful messages to the LLM

Architecture

Claude Desktop / Claude Code / Cursor
        │
        │  HTTP POST /mcp  (with X-API-Key header)
        ▼
  Express HTTP Server
        │
        ├── Auth Middleware  (validates X-API-Key against API_KEYS env)
        │
        ├── Correlation ID Middleware  (injects req-id into Pino logger)
        │
        ├── MCP SDK Handler  (parses JSON-RPC, routes tool calls)
        │         │
        │         ├── tool: query_records    ──► PostgreSQL Pool
        │         ├── tool: create_record    ──► PostgreSQL Pool
        │         └── tool: aggregate_stats  ──► PostgreSQL Pool
        │
        └── GET /health  (checks DB connectivity, returns JSON)

Project Structure

postgres-mcp-server/
├── src/
│   ├── server.ts          # Main MCP server — tool registration & HTTP setup
│   ├── config.ts          # Environment variable validation & typed config
│   ├── auth.ts            # API key authentication middleware
│   ├── logger.ts          # Pino structured logger with correlation ID support
│   ├── database.ts        # PostgreSQL pool, query helpers, graceful shutdown
│   └── health.ts          # /health endpoint checking all dependencies
├── schema.sql             # Database schema — tables, indexes, initial data
├── Dockerfile             # Multi-stage production Dockerfile
├── docker-compose.yml     # Local dev: MCP server + PostgreSQL
├── package.json           # Dependencies with pinned versions
├── tsconfig.json          # TypeScript compiler configuration
├── .env.example           # All environment variables documented
└── .gitignore             # Node/TypeScript/Docker ignores

Prerequisites

• Node.js 20+ (LTS recommended)
• npm 9+ or pnpm 8+
• Docker and Docker Compose (for local development)
• PostgreSQL 15+ (or use the Docker Compose setup — recommended)
• A MCP-compatible client: Claude Desktop, Claude Code, Cursor, Windsurf, or Continue
• Basic familiarity with TypeScript and SQL

Quick Start

# 1. Clone or copy this template
git clone https://github.com/your-org/postgres-mcp-server.git
cd postgres-mcp-server

# 2. Install dependencies
npm install

# 3. Copy and edit environment variables
cp .env.example .env
# Edit .env — at minimum set API_KEYS and DATABASE_URL

# 4. Start PostgreSQL + the MCP server with Docker Compose
docker compose up --build

# The server will:
# - Start on http://localhost:3000
# - Apply schema.sql automatically on first run
# - Be ready at http://localhost:3000/health

# 5. Verify it's working
curl http://localhost:3000/health

# 6. Test an MCP tool call manually
curl -X POST http://localhost:3000/mcp \
  -H 'Content-Type: application/json' \
  -H 'X-API-Key: dev-api-key-change-me' \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/call",
    "params": {
      "name": "query_records",
      "arguments": { "table": "events", "limit": 5 }
    }
  }'

# 7. For local development without Docker (requires local PostgreSQL)
npm run dev

Features

• HTTP transport with Express, compatible with all major MCP clients
• API key authentication — multiple keys supported, validated on every request
• PostgreSQL integration with pg connection pool, query timeout, and graceful shutdown
• Three realistic MCP tools: query_records, create_record, aggregate_stats
• Structured JSON logging via Pino with correlation IDs on every log line
• /health endpoint returning structured JSON with database connectivity status
• Multi-stage Dockerfile producing a lean ~120MB production image
• docker-compose.yml for one-command local development with live reload via tsx watch
• Zod input validation on all tool arguments with descriptive error messages
• Graceful shutdown handling SIGTERM/SIGINT — drains connections before exiting
• Environment validation at startup — server refuses to start with missing config
• Full TypeScript — no any types, strict mode enabled
• schema.sql with tables, indexes, and seed data ready to use

Works With

Claude Desktop Configuration

Add to ~/Library/Application Support/Claude/claude_desktop_config.json (macOS):

{
  "mcpServers": {
    "postgres-mcp": {
      "command": "npx",
      "args": ["mcp-remote", "http://localhost:3000/mcp"],
      "env": {
        "MCP_REMOTE_HEADER_X_API_KEY": "your-api-key-here"
      }
    }
  }
}

Configuration

Deployment

Docker (Recommended)

# Build the production image
docker build -t postgres-mcp-server:latest .

# Run with environment variables
docker run -d \
  --name postgres-mcp \
  -p 3000:3000 \
  -e API_KEYS=your-secure-key-here \
  -e DATABASE_URL=postgresql://user:pass@your-db-host:5432/mydb \
  -e LOG_LEVEL=info \
  -e NODE_ENV=production \
  postgres-mcp-server:latest

Railway

# Install Railway CLI
npm install -g @railway/cli

# Login and initialise
railway login
railway init

# Add a PostgreSQL plugin via the Railway dashboard, then:
railway variables set API_KEYS=your-secure-key-here
railway variables set LOG_LEVEL=info
railway variables set NODE_ENV=production
# DATABASE_URL is set automatically by Railway's PostgreSQL plugin

# Deploy
railway up

Fly.io

# Install flyctl
curl -L https://fly.io/install.sh | sh

# Initialise app
fly auth login
fly launch --name postgres-mcp-server --no-deploy

# Create a managed PostgreSQL database
fly postgres create --name postgres-mcp-db
fly postgres attach postgres-mcp-db

# Set secrets
fly secrets set API_KEYS=your-secure-key-here
fly secrets set LOG_LEVEL=info
fly secrets set NODE_ENV=production

# Deploy
fly deploy

# Check status
fly status
fly logs

Production Checklist

• API_KEYS contains at least one strong, randomly generated key (use openssl rand -hex 32)
• DATABASE_URL points to a production PostgreSQL instance with SSL enabled
• NODE_ENV is set to production
• LOG_LEVEL is set to info or warn (not debug or trace in production)
• Database connection pool sizes (DB_POOL_MIN, DB_POOL_MAX) are tuned for your load
• /health endpoint is configured as the health check in your container orchestrator
• PostgreSQL database has been initialised with schema.sql
• Docker image is built from the production stage (not development)
• CORS origins are restricted to your actual client origins (not *)
• Secrets are stored in a secrets manager — not in .env files committed to git
• Database user has only the minimum required permissions (not superuser)
• SSL/TLS is terminated at the load balancer or configured in Express
• Log output is being collected by a log aggregation service (Datadog, Logtail, etc.)
• Alerts are configured for health check failures and high error rates
• A process restart policy is configured (Docker --restart=unless-stopped or equivalent)

Testing

Manual Tool Testing

# Check server health
curl -s http://localhost:3000/health | jq .

# List available tools
curl -s -X POST http://localhost:3000/mcp \
  -H 'Content-Type: application/json' \
  -H 'X-API-Key: dev-api-key-change-me' \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/list","params":{}}' | jq .

# Query records
curl -s -X POST http://localhost:3000/mcp \
  -H 'Content-Type: application/json' \
  -H 'X-API-Key: dev-api-key-change-me' \
  -d '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"query_records","arguments":{"table":"events","limit":10}}}' | jq .

# Create a record
curl -s -X POST http://localhost:3000/mcp \
  -H 'Content-Type: application/json' \
  -H 'X-API-Key: dev-api-key-change-me' \
  -d '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"create_record","arguments":{"table":"events","data":{"name":"test-event","category":"test","metadata":{}}}}}' | jq .

# Get aggregate stats
curl -s -X POST http://localhost:3000/mcp \
  -H 'Content-Type: application/json' \
  -H 'X-API-Key: dev-api-key-change-me' \
  -d '{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"aggregate_stats","arguments":{"table":"events","group_by":"category"}}}' | jq .

# Test invalid API key (should return 401)
curl -s -X POST http://localhost:3000/mcp \
  -H 'Content-Type: application/json' \
  -H 'X-API-Key: wrong-key' \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/list","params":{}}'

Testing with Claude Desktop

1. Start the server: docker compose up
2. Update your Claude Desktop config (see Works With section above)
3. Restart Claude Desktop
4. Open a new conversation and type: "Can you query the events table and show me the first 5 records?"
5. Claude should automatically invoke the query_records tool and display the results
6. Check the server logs: docker compose logs -f mcp-server to see the tool calls with correlation IDs