# mcp-postgres

MCP Server com acesso a PostgreSQL — Clean Architecture, Repository Pattern, asyncpg.

Compatível com **qualquer agente MCP**: Claude Code, Claude Desktop, LangChain, LlamaIndex e outros via HTTP.

---

## Índice

- [Visão Geral](#visão-geral)
- [Arquitetura](#arquitetura)
- [Estrutura de Ficheiros](#estrutura-de-ficheiros)
- [Instalação](#instalação)
- [Configuração](#configuração)
  - [Conexão com o Banco](#conexão-com-o-banco)
  - [Transporte MCP](#transporte-mcp)
  - [Referência completa de variáveis](#referência-completa-de-variáveis)
- [Execução](#execução)
- [Capacidades MCP](#capacidades-mcp)
  - [Tools](#tools)
  - [Resources](#resources)
  - [Prompts](#prompts)
- [Segurança](#segurança)
- [Testes](#testes)
- [Integração com Agentes](#integração-com-agentes)
  - [Claude Code / Claude Desktop](#claude-code--claude-desktop)
  - [LangChain / LlamaIndex](#langchain--llamaindex)
  - [Agentes HTTP genéricos](#agentes-http-genéricos)
- [Decisões de Arquitetura](#decisões-de-arquitetura)

---

## Visão Geral

Este servidor implementa o **Model Context Protocol (MCP)** para expor um banco de dados PostgreSQL a modelos de linguagem e agentes de IA.

O agente pode:
- Executar queries SQL de leitura
- Inspecionar schemas, tabelas, índices e foreign keys
- Obter estatísticas de tabelas
- Executar operações de escrita (quando explicitamente habilitado)

O servidor é **read-only por padrão** e funciona com **qualquer banco PostgreSQL** — basta mudar o `.env` ou a variável `DATABASE_URL`.

---

## Arquitetura

```
┌─────────────────────────────────────────────────────────┐
│              Agente (Claude / LangChain / …)            │
└────────────────────────┬────────────────────────────────┘
                         │ MCP Protocol
                    stdio │ ou HTTP (SSE / streamable-http)
┌────────────────────────▼────────────────────────────────┐
│                    MCP Server (FastMCP)                  │
│                                                         │
│   ┌──────────┐   ┌───────────┐   ┌──────────────────┐  │
│   │  Tools   │   │ Resources │   │    Prompts       │  │
│   └────┬─────┘   └─────┬─────┘   └──────────────────┘  │
│        │               │                                │
│   ┌────▼───────────────▼────────────────────────────┐   │
│   │              Repositories                       │   │
│   │   BaseRepository → QueryRepository              │   │
│   │                  → SchemaRepository             │   │
│   └────────────────────┬────────────────────────────┘   │
│                        │                                │
│   ┌────────────────────▼────────────────────────────┐   │
│   │           Database (asyncpg Pool)               │   │
│   └────────────────────┬────────────────────────────┘   │
│                        │                                │
│   ┌────────────────────▼────────────────────────────┐   │
│   │         Config (Pydantic Settings + .env)       │   │
│   └─────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────┘
                         │ TCP
┌────────────────────────▼────────────────────────────────┐
│                     PostgreSQL                          │
└─────────────────────────────────────────────────────────┘
```

**Regra de dependência:** cada camada conhece apenas a camada imediatamente abaixo. Tools não conhecem o banco diretamente; Config não conhece ninguém.

---

## Estrutura de Ficheiros

```
mcp-postgres/
│
├── .env                             # Configuração activa (não commitado)
├── .env.example                     # Template documentado de todas as variáveis
├── pyproject.toml                   # Dependências, scripts, ruff, mypy, pytest
├── .gitignore
├── claude_mcp_config.json           # Config pronta para Claude Desktop / Claude Code
│
├── src/
│   └── mcp_postgres/
│       ├── __init__.py
│       ├── server.py                # Entry point: cria FastMCP e selecciona transporte
│       │
│       ├── config/
│       │   ├── __init__.py
│       │   └── settings.py          # Pydantic Settings — DATABASE_URL ou POSTGRES_*
│       │
│       ├── database/
│       │   ├── __init__.py
│       │   └── connection.py        # Singleton pool asyncpg + context managers
│       │
│       ├── repositories/
│       │   ├── __init__.py
│       │   ├── base.py              # fetch_all, fetch_one, execute, transaction
│       │   ├── query_repository.py  # SQL arbitrário com guard de writes
│       │   └── schema_repository.py # Introspection: tabelas, colunas, índices, FK, stats
│       │
│       ├── tools/
│       │   ├── __init__.py
│       │   ├── query_tools.py       # execute_query, execute_query_one, execute_statement
│       │   └── schema_tools.py      # list_schemas/tables/indexes/fks, get_table_stats
│       │
│       ├── resources/
│       │   ├── __init__.py
│       │   └── schema_resources.py  # URIs: postgres://schema/…
│       │
│       └── prompts/
│           ├── __init__.py
│           └── sql_prompts.py       # explore_database, analyse_table, write_query, optimise_query
│
└── tests/
    ├── __init__.py
    ├── conftest.py
    └── tools/
        ├── test_query_repository.py
        └── test_schema_repository.py
```

---

## Instalação

**Pré-requisitos:** Python 3.11+, [uv](https://docs.astral.sh/uv/), PostgreSQL acessível.

```bash
cd mcp-postgres

# Instalar dependências
uv sync

# Com dependências de desenvolvimento
uv sync --extra dev
```

---

## Configuração

Toda a configuração é feita no ficheiro `.env` na raiz do projeto.

```bash
cp .env.example .env
# editar .env com os dados do teu ambiente
```

### Conexão com o Banco

Existem duas formas de configurar a conexão — usa a que for mais conveniente:

**Opção A — `DATABASE_URL` (tem prioridade)**

Uma única variável com o DSN completo:

```dotenv
DATABASE_URL=postgresql://user:password@host:5432/dbname
```

**Opção B — variáveis individuais**

```dotenv
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=mydb
POSTGRES_USER=postgres
POSTGRES_PASSWORD=secret
```

> Se `DATABASE_URL` estiver definida, os valores de `POSTGRES_HOST`, `POSTGRES_PORT`, etc., são ignorados. Caso contrário, as vars individuais são usadas. Se nenhuma for fornecida, o servidor tenta ligar a `localhost:5432/postgres`.

### Transporte MCP

A variável `MCP_TRANSPORT` define como o servidor comunica com o agente:

| Valor | Protocolo | Endpoint | Indicado para |
|---|---|---|---|
| `stdio` *(padrão)* | stdin/stdout | — | Claude Code, Claude Desktop, agentes locais |
| `sse` | HTTP Server-Sent Events | `http://host:port/sse` | LangChain, LlamaIndex, agentes HTTP legados |
| `streamable-http` | HTTP streaming | `http://host:port/mcp` | Agentes MCP modernos via HTTP |

Para HTTP, define também o host e a porta:

```dotenv
MCP_TRANSPORT=sse
MCP_HOST=0.0.0.0
MCP_PORT=8080
```

### Referência completa de variáveis

| Variável | Padrão | Descrição |
|---|---|---|
| `DATABASE_URL` | — | DSN completo (prioridade sobre vars individuais) |
| `POSTGRES_HOST` | `localhost` | Host do PostgreSQL |
| `POSTGRES_PORT` | `5432` | Porta |
| `POSTGRES_DB` | `postgres` | Nome do banco de dados |
| `POSTGRES_USER` | `postgres` | Utilizador |
| `POSTGRES_PASSWORD` | *(vazio)* | Password |
| `POSTGRES_MIN_POOL_SIZE` | `2` | Conexões mínimas no pool |
| `POSTGRES_MAX_POOL_SIZE` | `10` | Conexões máximas no pool |
| `POSTGRES_COMMAND_TIMEOUT` | `60` | Timeout de comando em segundos |
| `POSTGRES_ALLOWED_SCHEMAS` | `public` | Schemas expostos (vírgula; vazio = todos) |
| `POSTGRES_ALLOW_WRITES` | `false` | Habilita INSERT/UPDATE/DELETE/DDL |
| `MCP_SERVER_NAME` | `postgres-mcp` | Nome do servidor MCP |
| `MCP_LOG_LEVEL` | `INFO` | Nível de log (`DEBUG`, `INFO`, `WARNING`, `ERROR`) |
| `MCP_TRANSPORT` | `stdio` | Transporte: `stdio`, `sse`, `streamable-http` |
| `MCP_HOST` | `0.0.0.0` | Host do servidor HTTP (só para SSE/streamable-http) |
| `MCP_PORT` | `8080` | Porta do servidor HTTP (só para SSE/streamable-http) |

---

## Execução

```bash
# Modo stdio (padrão)
uv run mcp-postgres

# Modo SSE — servidor HTTP na porta 8080
MCP_TRANSPORT=sse uv run mcp-postgres

# Modo desenvolvimento com MCP Inspector
uv run mcp dev src/mcp_postgres/server.py
```

---

## Capacidades MCP

### Tools

Tools são funções que o agente chama activamente para executar operações.

---

#### `execute_query`

Executa uma query SELECT e retorna os resultados como JSON.

| Parâmetro | Tipo | Obrigatório | Descrição |
|---|---|---|---|
| `sql` | `str` | sim | Query SQL a executar |
| `params` | `list` | não | Parâmetros posicionais (`$1`, `$2`, …) |

```sql
-- Exemplo com parâmetro
SELECT id, name FROM users WHERE active = $1
params: [true]
```

---

#### `execute_query_one`

Executa uma query e retorna apenas o primeiro registo.

| Parâmetro | Tipo | Obrigatório | Descrição |
|---|---|---|---|
| `sql` | `str` | sim | Query SQL |
| `params` | `list` | não | Parâmetros posicionais |

---

#### `execute_statement`

Executa um statement de escrita. Requer `POSTGRES_ALLOW_WRITES=true`.

| Parâmetro | Tipo | Obrigatório | Descrição |
|---|---|---|---|
| `sql` | `str` | sim | INSERT / UPDATE / DELETE / DDL |
| `params` | `list` | não | Parâmetros posicionais |

Retorna o command tag do PostgreSQL (ex: `UPDATE 3`).

---

#### `list_schemas`

Lista todos os schemas não-sistema do banco.

---

#### `list_tables`

Lista tabelas e views de um schema.

| Parâmetro | Tipo | Padrão | Descrição |
|---|---|---|---|
| `schema` | `str` | `public` | Nome do schema |

---

#### `describe_table`

Retorna as definições de colunas de uma tabela.

| Parâmetro | Tipo | Padrão | Descrição |
|---|---|---|---|
| `table` | `str` | — | Nome da tabela |
| `schema` | `str` | `public` | Nome do schema |

---

#### `list_indexes`

Lista os índices de uma tabela com unicidade e colunas cobertas.

| Parâmetro | Tipo | Padrão | Descrição |
|---|---|---|---|
| `table` | `str` | — | Nome da tabela |
| `schema` | `str` | `public` | Nome do schema |

---

#### `list_foreign_keys`

Lista as foreign keys de uma tabela.

| Parâmetro | Tipo | Padrão | Descrição |
|---|---|---|---|
| `table` | `str` | — | Nome da tabela |
| `schema` | `str` | `public` | Nome do schema |

---

#### `get_table_stats`

Retorna estatísticas operacionais via `pg_stat_user_tables`.

| Parâmetro | Tipo | Padrão | Descrição |
|---|---|---|---|
| `table` | `str` | — | Nome da tabela |
| `schema` | `str` | `public` | Nome do schema |

Inclui: `live_rows`, `dead_rows`, `last_vacuum`, `last_analyze`, `total_size`.

---

### Resources

Resources expõem dados como URIs navegáveis — o agente lê-os para obter contexto antes de agir.

| URI | Descrição |
|---|---|
| `postgres://schemas` | Lista todos os schemas do banco |
| `postgres://schema/{schema}/tables` | Lista tabelas de um schema |
| `postgres://schema/{schema}/table/{table}` | Colunas + índices + FK + stats de uma tabela |

---

### Prompts

Prompts são templates reutilizáveis que guiam o agente numa tarefa complexa.

| Prompt | Parâmetros | Descrição |
|---|---|---|
| `explore_database` | — | Roteiro para explorar um banco desconhecido |
| `analyse_table` | `table`, `schema` | Análise detalhada de uma tabela |
| `write_query` | `question` | Gera SQL a partir de linguagem natural |
| `optimise_query` | `sql` | Analisa e optimiza uma query existente |

---

## Segurança

| Mecanismo | Detalhe |
|---|---|
| **Read-only por padrão** | Writes bloqueados via regex antes de qualquer acesso ao banco |
| **Schemas permitidos** | `POSTGRES_ALLOWED_SCHEMAS` limita a exposição |
| **Queries parametrizadas** | Todos os inputs são `$1, $2` — sem interpolação de strings |
| **Logs sanitizados** | Password nunca aparece em logs (`safe_dsn`) |
| **Erros sanitizados** | Exceções retornam mensagem simples ao agente, sem stack trace |
| **Pool limitado** | `max_pool_size=10` por padrão — evita saturar o banco |

Para habilitar escritas:
```dotenv
POSTGRES_ALLOW_WRITES=true
```

---

## Testes

Os testes são de integração e requerem uma instância PostgreSQL acessível. Configura o `.env` antes de correr.

```bash
# Todos os testes
uv run pytest

# Com coverage
uv run pytest --cov=src/mcp_postgres --cov-report=html

# Verbose
uv run pytest -v
```

---

## Integração com Agentes

### Claude Code / Claude Desktop

Adicionar ao `~/.claude.json` (user-level, disponível em todos os projetos):

```json
{
  "mcpServers": {
    "postgres": {
      "type": "stdio",
      "command": "uv",
      "args": [
        "--directory", "/caminho/para/mcp-postgres",
        "run", "mcp-postgres"
      ],
      "env": {
        "DATABASE_URL": "postgresql://user:pass@host:5432/db"
      }
    }
  }
}
```

Ou usar o ficheiro `claude_mcp_config.json` incluído no projeto como referência.

---

### LangChain / LlamaIndex

Iniciar o servidor em modo SSE:

```bash
MCP_TRANSPORT=sse MCP_PORT=8080 uv run mcp-postgres
```

Conectar a partir do agente:

```python
# LangChain + MCP
from langchain_mcp_adapters.client import MultiServerMCPClient

client = MultiServerMCPClient({
    "postgres": {
        "url": "http://localhost:8080/sse",
        "transport": "sse",
    }
})
tools = await client.get_tools()
```

---

### Agentes HTTP genéricos

Iniciar em modo `streamable-http`:

```bash
MCP_TRANSPORT=streamable-http MCP_PORT=8080 uv run mcp-postgres
```

Endpoint disponível em `http://localhost:8080/mcp`.

---

### Docker

```dockerfile
FROM python:3.12-slim
WORKDIR /app
COPY . .
RUN pip install uv && uv sync
EXPOSE 8080
CMD ["uv", "run", "mcp-postgres"]
```

```bash
docker run -p 8080:8080 \
  -e DATABASE_URL=postgresql://user:pass@host:5432/db \
  -e MCP_TRANSPORT=sse \
  mcp-postgres
```

---

## Decisões de Arquitetura

### Conexão dinâmica — `DATABASE_URL` vs `POSTGRES_*`

`DATABASE_URL` é o padrão de facto em ambientes cloud (Heroku, Railway, Render, etc.). As vars individuais são mais legíveis para desenvolvimento local. O `model_validator` do Pydantic extrai os campos do DSN se fornecido, garantindo que o `dsn` interno está sempre correcto independentemente de qual forma foi usada.

### Transporte configurável

O protocolo MCP suporta vários transportes. `stdio` é o padrão para agentes locais (Claude Code lança o processo e comunica por stdin/stdout). Para agentes remotos ou multi-tenant, `sse` e `streamable-http` expõem o servidor como um serviço HTTP sem qualquer alteração de código — só muda a variável de ambiente.

### `src/` layout

Previne que o Python encontre o módulo via path local sem instalação — o que mascararia erros de packaging e tornaria os testes menos fiáveis.

### Repository Pattern

Isola o SQL das tools MCP. As tools expressam intenção (`list_tables`), os repositórios expressam implementação (query contra `information_schema`). Trocar o banco ou reescrever uma query não exige tocar nas tools.

### asyncpg em vez de psycopg2/3

Construído especificamente para `asyncio` — não é um wrapper sobre uma biblioteca síncrona. Em workloads com múltiplas queries concorrentes, a diferença de performance é significativa. O pool reutiliza conexões TCP e prepara statements automaticamente.

### Lifespan para o pool

Garante que o pool abre antes do servidor aceitar requests e fecha sempre ao terminar — mesmo com `Ctrl+C` ou sinal do OS. Não há risco de conexões a vazar.