# 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](https://nodejs.org/) (v20 or higher)
- [Docker & Docker Compose](https://www.docker.com/)

### 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:
```bash
docker compose up -d
```

To stop the database:
```bash
docker compose down
```

### 2. Install Dependencies
Install all package dependencies:
```bash
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:
```bash
npm run start
```

For development (watch mode):
```bash
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.
    ```json
    [
      { "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:
```bash
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`):

```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:

```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"
      }
    }
  }
}
```

---


## Development Workflow (OpenSpec)

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

*   **Propose a Change:**
    ```bash
    openspec new change "<change-name>"
    ```
*   **Check Status:**
    ```bash
    openspec status --change "<change-name>"
    ```
*   **Get Instructions:**
    ```bash
    openspec instructions <artifact-id> --change "<change-name>"
    ```
*   **Archive a Change:**
    Move completed specifications to the local archive:
    ```bash
    openspec archive --change "<change-name>"
    ```