# pg-context

> Model Context Protocol (MCP) server that gives AI coding assistants deep PostgreSQL context: live schemas as DDL, index health, foreign key associations, query execution plans, and performance statistics.

`pg-context` connects directly to your PostgreSQL database in read-only mode and exposes tools and resources for AI assistants such as Cursor, Claude Code, Cline, and Antigravity.

---

## Why pg-context?

Generic SQL tools often execute arbitrary queries without giving the AI assistant structural context. This causes models to guess column names, hallucinate relations, and generate invalid joins.

`pg-context` resolves this problem by providing:
- **Zero-guess schema context**: Exports real table structures formatted as PostgreSQL `CREATE TABLE` DDL statements with foreign key comments and index definitions.
- **Read-only security**: Automatically sets `default_transaction_read_only = ON` on every client connection and rejects mutating SQL statements (such as `DROP`, `DELETE`, `UPDATE`, `INSERT`, `TRUNCATE`, `ALTER`).
- **Optimization insights**: Detects unindexed foreign keys, high sequential scan ratios, unused indexes, and slow queries from `pg_stat_statements`.
- **Extension awareness**: Identifies PostGIS, TimescaleDB, and pgvector extensions.

---

## Quick Start

You can run `pg-context` directly via `npx` without manual installation:

```bash
npx -y pg-context-mcp
```

Or install it globally:

```bash
npm install -g pg-context-mcp
pg-context-mcp
```

---

## Configuration

`pg-context` reads connection parameters from environment variables or standard PostgreSQL connection strings:

| Variable | Description | Default |
|---|---|---|
| `DATABASE_URL` | Standard PostgreSQL connection URI | `undefined` |
| `PGHOST` | Database host | `localhost` |
| `PGPORT` | Database port | `5432` |
| `PGDATABASE` | Database name | `postgres` |
| `PGUSER` | Database user | `postgres` |
| `PGPASSWORD` | Database password | `""` |
| `PGSSL` | Enable SSL connection (`true`/`false`) | `false` |
| `PG_SCHEMAS` | Comma-separated list of target schemas | `public` |
| `PG_MAX_CONNECTIONS` | Connection pool size | `5` |
| `PG_CACHE_TTL` | Schema cache TTL in seconds | `60` |
| `PG_QUERY_LIMIT` | Default row limit for `read_query` | `100` |
| `PG_QUERY_LIMIT_MAX` | Hard upper limit for `read_query` | `1000` |

---

## Client Integration

### 1. Claude Desktop & Antigravity

Add the following entry to your `claude_desktop_config.json` or Antigravity MCP settings:

```json
{
  "mcpServers": {
    "pg-context": {
      "command": "npx",
      "args": ["-y", "pg-context-mcp"],
      "env": {
        "DATABASE_URL": "postgresql://user:password@localhost:5432/my_database"
      }
    }
  }
}
```

### 2. Cursor

Add the following to your Cursor MCP configuration (`.cursor/mcp.json` or Cursor Settings > Features > MCP):

```json
{
  "mcpServers": {
    "pg-context": {
      "command": "npx",
      "args": ["-y", "pg-context-mcp"],
      "env": {
        "DATABASE_URL": "postgresql://user:password@localhost:5432/my_database"
      }
    }
  }
}
```

### 3. Cline (VS Code Extension)

Add this configuration into `cline_mcp_settings.json`:

```json
{
  "mcpServers": {
    "pg-context": {
      "command": "npx",
      "args": ["-y", "pg-context-mcp"],
      "env": {
        "DATABASE_URL": "postgresql://user:password@localhost:5432/my_database"
      }
    }
  }
}
```

---

## Tools Reference

`pg-context` exposes 12 MCP tools:

| Tool | Parameters | Description |
|---|---|---|
| `list_schemas` | _none_ | Lists all non-system schemas in the database. |
| `list_tables` | `schema` (default: `"public"`) | Lists tables and views with sizes, estimated row counts, and comments. |
| `describe_table` | `table`, `schema` (default: `"public"`) | Returns full structural DDL of a table including columns, types, defaults, NOT NULL, foreign keys, and indexes. |
| `list_foreign_keys` | `schema` (default: `"public"`), `table` (optional) | Displays foreign key relationships as readable `source -> target` associations. |
| `list_indexes` | `schema` (default: `"public"`), `table` (optional) | Displays indexes, definitions, sizes, and scan counts. |
| `suggest_indexes` | `schema` (default: `"public"`), `table` (optional) | Identifies unindexed foreign keys, sequential scan warnings, and unused indexes. |
| `read_query` | `sql`, `limit` (optional) | Executes read-only queries with enforced limits and returns Markdown tables. |
| `explain_query` | `sql`, `analyze` (boolean, default: `false`) | Returns query execution plans from PostgreSQL `EXPLAIN` or `EXPLAIN ANALYZE`. |
| `get_database_info` | _none_ | Returns PostgreSQL version, total database size, connection counts, and buffer settings. |
| `get_table_stats` | `schema` (default: `"public"`), `table` (optional) | Returns sequential vs index scans, live rows, dead rows, and maintenance dates. |
| `get_slow_queries` | `limit` (default: `15`) | Fetches top slowest queries recorded by `pg_stat_statements`. |
| `list_extensions` | _none_ | Lists installed PostgreSQL extensions and flags capabilities for PostGIS, TimescaleDB, and pgvector. |

---

## Resources Reference

`pg-context` exposes 3 MCP resources:

| Resource URI | MIME Type | Description |
|---|---|---|
| `pg://schema/full` | `text/x-sql` | Full DDL dump of all tables across configured schemas. |
| `pg://schema/{tableName}` | `text/x-sql` | DDL definition for an individual table. |
| `pg://stats/overview` | `text/markdown` | Database health overview: storage sizes, dead rows, and optimization alerts. |

---

## Development

```bash
# Clone the repository
git clone https://github.com/mewsyy/pg-context.git
cd pg-context

# Install dependencies
npm install

# Run test suite
npm test

# Build TypeScript
npm run build

# Start local server
npm start
```

---

## License

MIT (c) Semen