# db-semantic-mcp

English | [简体中文](README.zh-CN.md)

A multi-backend MCP server for AI coding agents — supports both PostgreSQL and
SQL Server.

Exposes your database schema — table names, column types, comments, and bounded
sample data — as MCP tools. Includes semantic search powered by any OpenAI-compatible
LLM, enriched by a user-authored semantic layer document.

No SQL execution. Read-only. No vector database required.

## Backends

| Backend | Scheme | Driver | Required Extras |
|---|---|---|---|
| PostgreSQL | `postgresql://...` | asyncpg | (built-in) |
| SQL Server | `sqlserver://...` | pymssql | `[sqlserver]` |

The backend is auto-detected from `DATABASE_URL`. Everything else works the same.

## Features

- **list_tables** — discover all tables with comments
- **describe_table** — inspect column names, types, nullability, and comments
- **sample_data** — fetch example rows from any table
- **search_schema** — semantic keyword search across tables and columns using LLM

## Install

```bash
# PostgreSQL only
pip install db-semantic-mcp

# With SQL Server support
pip install "db-semantic-mcp[sqlserver]"
```

Requires Python 3.11+.

## Quick Start

```bash
# PostgreSQL
export DATABASE_URL="postgresql://user:pass@localhost:5432/mydb"

# SQL Server (Kingdee ERP or any MSSQL instance)
export DATABASE_URL="sqlserver://user:pass@host:1433?database=mydb&encrypt=disable"

export LLM_API_KEY="sk-..."          # required only for search_schema
pg-semantic-mcp
```

## Configuration

| Variable | Required | Default | Description |
|---|---|---|---|
| `DATABASE_URL` | yes | — | PostgreSQL or SQL Server connection string |
| `SEMANTIC_FILE` | no | — | Path to your semantic layer markdown |
| `LLM_BASE_URL` | no | `https://api.openai.com/v1` | OpenAI-compatible endpoint |
| `LLM_API_KEY` | no | — | Required for `search_schema` |
| `LLM_MODEL` | no | `gpt-4o-mini` | LLM model name |
| `CACHE_REFRESH_MINUTES` | no | `30` | Background cache refresh interval |
| `CACHE_SCHEMAS` | no | all | Comma-separated schema names to cache |
| `CACHE_TABLE_PREFIX` | no | — | Comma-separated table name prefixes to cache |
| `SAMPLE_DATA_LIMIT` | no | `5` | Default row count for `sample_data` |
| `SAMPLE_DATA_MAX_ROWS` | no | `20` | Hard maximum rows returned by `sample_data` |
| `SAMPLE_DATA_MAX_BYTES` | no | `50000` | Approximate hard maximum serialized response bytes for `sample_data` |
| `SAMPLE_DATA_ALLOW_COLUMNS` | no | all | Comma-separated case-insensitive glob patterns for columns that may be returned |
| `SAMPLE_DATA_DENY_COLUMNS` | no | — | Comma-separated case-insensitive glob patterns for columns that must be omitted |
| `SAMPLE_DATA_REDACT_COLUMNS` | no | built-in sensitive patterns | Comma-separated case-insensitive glob patterns for columns whose values are replaced with `[REDACTED]` |

You can also use a `.env` file in the working directory.

## sample_data Security Boundary

`sample_data` is read-only, but it is not metadata-only: it can expose actual
business data from the connected database. Treat it as a small data-plane tool.

The tool applies these response controls before returning rows to the MCP client:

- `limit` must be greater than 0 and is hard-capped by `SAMPLE_DATA_MAX_ROWS`.
- The response is reduced until its serialized size is within `SAMPLE_DATA_MAX_BYTES`.
- `SAMPLE_DATA_ALLOW_COLUMNS` limits returned columns when set.
- `SAMPLE_DATA_DENY_COLUMNS` omits matching columns and takes precedence over allow rules.
- `SAMPLE_DATA_REDACT_COLUMNS` masks matching values with `[REDACTED]`.

Column policies use case-insensitive glob patterns. For example:

```bash
SAMPLE_DATA_ALLOW_COLUMNS="id,name,email,created_at"
SAMPLE_DATA_DENY_COLUMNS="*password*,*token*"
SAMPLE_DATA_REDACT_COLUMNS="*email*,*phone*,*secret*"
```

The response shape is intentionally short to save model context:

```json
{
  "rows": [
    {"id": 1, "email": "[REDACTED]"}
  ],
  "_meta": {
    "table": "public.customers",
    "returned": 1,
    "truncated": false
  }
}
```

## Register with OpenCode

Add to your `opencode.jsonc`:

```jsonc
{
  "mcp": {
    "pg-data": {
      "type": "local",
      "command": "pg-semantic-mcp",
      "environment": {
        "DATABASE_URL": "postgresql://user:pass@host:5432/dbname",
        "SEMANTIC_FILE": "/path/to/SCHEMA.md",
        "LLM_API_KEY": "sk-..."
      }
    }
  }
}
```

Same config format works for Claude Code, Cursor, and any MCP-compatible agent.

## Semantic Layer

Create a `SCHEMA.md` file describing your database — naming conventions,
business term mappings, design decisions. See
[SCHEMA.md.example](SCHEMA.md.example) for a template.

This document is loaded at startup and included in the `search_schema` LLM
prompt. It is the main way to teach the agent about your specific domain.

## Compatible LLMs

`search_schema` calls any OpenAI-compatible endpoint:

- OpenAI (`gpt-4o-mini`, `gpt-4o`, …)
- DeepSeek (`deepseek-v4`, set `LLM_BASE_URL=https://api.deepseek.com/v1`)
- Anthropic via proxy
- Local models via Ollama or LM Studio

## License

MIT