PostgreSQL Model Context Protocol (MCP) Server

A model context protocol server exposing tools to inspect database schemas and run read-only SQL queries on a PostgreSQL database. Built with the NestJS framework and containerized using Docker Compose.

Technologies Used

• Runtime & Framework: Node.js (v20+), TypeScript, NestJS (v10)
• Architecture Pattern: Clean Architecture & Command Query Responsibility Segregation (CQRS)
• Database: PostgreSQL (v15)
• MCP Protocol Implementation: @rekog/mcp-nest
• Version Control & Spec-Driven Development: Git, OpenSpec CLI

---

Architecture

The project follows a Clean Architecture directory layout divided into distinct layers:

src/
├── domain/            # Core business models, database entities, repository interfaces
├── application/       # CQRS query definitions and handlers
├── infrastructure/    # Concrete database providers, repository implementations (pg pool)
├── presentation/      # MCP tool definitions/decorators exposing capabilities to clients
└── main.ts            # Bootstraps standard I/O transport for MCP JSON-RPC

CQRS Pattern

• All tool operations are decoupled through the NestJS CQRS @nestjs/cqrs module.
• The presentation tool definitions dispatch queries (GetTablesQuery, DescribeTableQuery, RunQueryQuery) to their respective handlers which execute operations using an injected database repository.

---

Getting Started

Prerequisites

• Node.js (v20 or higher)
• Docker & Docker Compose

1. Database Setup

The repository includes a Docker Compose definition configured to host a test PostgreSQL database mapping port 5433 locally. The container automatically seeds schema definitions and mock data using init.sql.

Start the database container:

docker compose up -d

To stop the database:

docker compose down

2. Install Dependencies

Install all package dependencies:

npm install

3. Running the Server

The application is designed to boot in a headless mode communicating via JSON-RPC 2.0 over standard input/output (Stdio). Framework logs and errors are outputted to stderr to avoid polluting the JSON-RPC streams on stdout.

To run the server locally:

npm run start

For development (watch mode):

npm run start:dev

---

MCP Tools Reference

The server exposes three lazy-loaded tools to MCP clients:

1. list_tables

List all tables and views available in the database (excluding system schemas like pg_catalog and information_schema).

• Parameters: None
• Response: JSON array listing table names and schemas.

  [
    { "name": "users", "schema": "public" },
    { "name": "orders", "schema": "public" }
  ]
  

2. describe_table

Retrieve the structural schema details, primary/foreign keys, and indexes of a specific table.

• Parameters:
  • tableName (string, required): The name of the table to describe.
• Response: JSON schema structure mapping fields, types, indexes, and primary/foreign key status.

3. run_query

Execute a read-only SQL SELECT query against the database.

• Parameters:
  • query (string, required): The SQL SELECT query to execute.
• Response: Tabular results in a JSON array.
• Safeguards Enforced:
  • Read-Only Verification: Automatically blocks modifying statements (INSERT, UPDATE, DELETE, etc.).
  • Result Limits: Wraps query in a subquery enforcing a maximum response cap of 100 rows.
  • Timeout: Aborts execution and cleans connections if queries take longer than 5 seconds.

---

IDE Integration / Connecting to MCP Clients

Because the server communicates over standard input/output (Stdio), you can easily add this server to any MCP-compliant client or IDE.

First, build the project to compile TypeScript to JavaScript:

npm run build

1. Claude Desktop

Add the following configuration to your claude_desktop_config.json (on Windows: %APPDATA%\Claude\claude_desktop_config.json; on macOS: ~/Library/Application Support/Claude/claude_desktop_config.json):

{
  "mcpServers": {
    "postgres-mcp-server": {
      "command": "node",
      "args": [
        "C:\\absolute\\path\\to\\postgresql-mcp\\dist\\main.js"
      ],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5433",
        "DB_NAME": "mcp_test",
        "DB_USER": "mcp_readonly",
        "DB_PASSWORD": "mcp_readonly_password"
      }
    }
  }
}

(Ensure you replace C:\\absolute\\path\\to\\postgresql-mcp with the actual path to your repository).

2. VS Code / Cursor (using extensions like Roo Code / Claude Dev)

Add this server block to your extension's MCP configuration settings file:

{
  "mcpServers": {
    "postgres-mcp-server": {
      "command": "node",
      "args": [
        "C:\\absolute\\path\\to\\postgresql-mcp\\dist\\main.js"
      ],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5433",
        "DB_NAME": "mcp_test",
        "DB_USER": "mcp_readonly",
        "DB_PASSWORD": "mcp_readonly_password"
      }
    }
  }
}

---

Development Workflow (OpenSpec)

This project uses OpenSpec for managing and tracking specification-driven changes.

• Propose a Change:

  openspec new change "<change-name>"
  

• Check Status:

  openspec status --change "<change-name>"
  

• Get Instructions:

  openspec instructions <artifact-id> --change "<change-name>"
  

• Archive a Change: Move completed specifications to the local archive:

  openspec archive --change "<change-name>"