# db-mcp (PostgreSQL)

[MCP](https://modelcontextprotocol.io/) server in JavaScript: **PostgreSQL** over stdio, read-only tools (`SELECT` / `WITH`) plus schema helpers.

## Repo layout

| Path | What |
|------|------|
| `src/index.js` | MCP server (stdio) |
| `src/pool.js` | `pg` pool + `default_transaction_read_only` + `statement_timeout` |
| `playground/` | Optional **Next.js** UI + **Express** API + **Gemini** — spawns this MCP per request ([`playground/README.md`](playground/README.md)) |

Root `package.json` deps: `@modelcontextprotocol/sdk`, `pg`, `zod`, `dotenv`.

## Requirements

- Node.js **18+**
- PostgreSQL and a `DATABASE_URL`

## Install (MCP server)

```bash
cd db-mcp
npm install
```

Copy `.env.example` → `.env` in **repo root** (next to root `package.json`). Set `DATABASE_URL`. Do not commit `.env`.

`src/index.js` loads that `.env` via `dotenv` using path **repo root** (derived from `import.meta.url`), so Cursor can run `node …/src/index.js` with any cwd. Vars already in the real environment are **not** overwritten.

Alternatively: export `DATABASE_URL` in the shell, or set `env` in Cursor MCP config (those win over `.env`).

## Configuration (MCP)

| Variable | Required | Description |
|----------|----------|-------------|
| `DATABASE_URL` | Yes | URI, e.g. `postgresql://user:pass@localhost:5432/dbname` |
| `MCP_STATEMENT_TIMEOUT_MS` | No | Statement timeout in ms (default `60000`) |

Connection startup uses libpq `-c default_transaction_read_only=on` and `-c statement_timeout=…` (see `src/pool.js`). Prefer a **read-only DB role** in production.

## Run MCP (stdio)

```bash
npm start
```

PowerShell (if not using `.env`):

```powershell
$env:DATABASE_URL = "postgresql://USER:PASSWORD@HOST:5432/DBNAME"
npm start
```

## Cursor / Claude Desktop

Point `command` + `args` at `src/index.js`. Pass `DATABASE_URL` in `env` if you do not rely on root `.env`.

**Cursor** (`mcp.json`):

```json
{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["C:/Users/YourUser/Desktop/db-mcp/src/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://USER:PASSWORD@localhost:5432/mydb"
      }
    }
  }
}
```

Use a real absolute path. Reload MCP after edits.

## How to test

| Method | What you do |
|--------|-------------|
| **Cursor** | Agent (or chat with tools). Natural language, e.g. “List `public` tables via postgres MCP”. No SQL file inside MCP — model calls `pg_*` tools. |
| **`npm run inspect`** | `@modelcontextprotocol/inspector` + `node src/index.js`. Pick tool, pass JSON args. Needs `DATABASE_URL` in env. |
| **Playground** | Browser UI: Gemini drives same MCP over stdio. `cd playground`, copy `.env`, `npm run dev` → [http://localhost:3000](http://localhost:3000). Details: [`playground/README.md`](playground/README.md). |

If tools never show: MCP disconnected in UI, or chat mode has no tool use.

## Tools

| Tool | Purpose |
|------|---------|
| `pg_list_schemas` | Non-system schemas |
| `pg_list_tables` | Tables/views in a schema (default `public`) |
| `pg_describe_table` | Columns for a table or view |
| `pg_query` | Single `SELECT` or `WITH`; optional `$1`… binds |

`pg_query`: no multi-statement (`;` only at end); must start with `SELECT` or `WITH`.

## Security

- `DATABASE_URL` = secret; least-privilege role.
- Read-only intent; DB should still enforce if `default_transaction_read_only` bypassed.
- LLM + SQL = cost + data exposure risk; tight timeouts, read replica when possible.
- Playground = **local dev only**; do not expose API without auth.

## License

MIT