# Phx DB Explorer MCP Server

A [Model Context Protocol (MCP)](https://modelcontextprotocol.io) server that exposes your **SQL Server** database schema to AI coding assistants (GitHub Copilot, Cursor, etc.). It allows AI tools to discover tables, views, stored procedures, functions, indexes, foreign keys, and more — without writing any SQL themselves — and to read row data through guarded, read-only tools.

---

## Prerequisites

| Requirement | Version | Notes |
|---|---|---|
| [.NET SDK](https://dotnet.microsoft.com/download) | 10.0 or later | Required to build and run |
| [Docker](https://www.docker.com/products/docker-desktop) | Any recent version | **Required for integration tests only** |

---

## Project Structure

```
src/
├── PhxDbExplorer/                  # MCP server application
├── PhxDbExplorer.Tests/            # Unit tests (xUnit, Moq)
└── PhxDbExplorer.IntegrationTests/ # Integration tests (Testcontainers, requires Docker)
```

---

## Configuration

The server is configured entirely through environment variables.

| Variable | Required | Description |
|---|---|---|
| `DB_TYPE` | ✅ Yes | Database type. Use `mssql` or `sqlserver` for SQL Server. |
| `CONNECTION_STRING` | ✅ Yes | Full ADO.NET connection string for the target database. |
| `SCHEMA_FILTER` | ❌ No | Comma-separated list of schemas to expose (default: `dbo`). |
| `MAX_ROWS` | ❌ No | Hard ceiling on rows returned by any data-read tool (default: `100`). A caller's `limit` is clamped to this — it can lower the cap, never raise it. |
| `QUERY_TIMEOUT_SECONDS` | ❌ No | Command timeout for data-read queries (default: `30`). |

**Example values:**

```
DB_TYPE=mssql
CONNECTION_STRING=Server=localhost,1433;Database=MyDb;User Id=sa;Password=YourPassword;TrustServerCertificate=True;
SCHEMA_FILTER=dbo,hr
MAX_ROWS=100
QUERY_TIMEOUT_SECONDS=30
```

> **The connection string is the real access boundary.** The server never escalates
> privileges, but it also cannot grant itself any it wasn't given. If the assistant
> should not be able to read a table, connect with a login that cannot read it.
> Pointing this server at production with a `db_owner` login gives every connected
> AI client read access to every row in the configured schemas.

---

## Installation

### Option A — Download a prebuilt binary (recommended)

Self-contained, single-file executables are published as [GitHub Releases](../../releases) for every tagged version — no .NET SDK (or even the .NET runtime) required on the target machine.

1. Go to the [Releases](../../releases) page.
2. Download the archive matching your OS/architecture:

   | Asset | Platform |
   |---|---|
   | `PhxDbExplorer-<version>-win-x64.zip` | Windows x64 |
   | `PhxDbExplorer-<version>-linux-x64.tar.gz` | Linux x64 |
   | `PhxDbExplorer-<version>-osx-x64.tar.gz` | macOS (Intel) |
   | `PhxDbExplorer-<version>-osx-arm64.tar.gz` | macOS (Apple Silicon) |

3. Extract it and point your MCP client at the extracted `PhxDbExplorer` (or `PhxDbExplorer.exe`) binary.

Each release should have all four assets attached — if one is missing, check the [`release` workflow run](../../actions/workflows/release.yml) for that tag.

### Option B — Build from source

Requires the .NET SDK (see Prerequisites above).

```bash
dotnet build
```

### Option C — `npx` (used by the PHR-Foundry Claude Code plugin)

`package.json` at the repo root wraps Option A behind an `npx` launcher, so
nothing needs to be downloaded or installed by hand:

```bash
npx -y github:hsenidBiz/phx-dbexplorer
```

On first run it downloads the release asset matching your OS/arch into
`~/.cache/phx-dbexplorer-mcp/<version>/<rid>/` and execs it; later runs reuse
the cached binary. Pin a specific tag with `PHX_DBEXPLORER_VERSION=1.2.0`
(defaults to the latest release). This is what an MCP client's `command`
should point at instead of a local binary path — see
[phr-foundry](https://github.com/hsenidBiz/phr-foundry)'s `org-standards`
plugin for the registered `mcpServers` entry.

**This repo must stay public.** The launcher's download step (`GET
/repos/.../releases/latest` and the release asset itself) is unauthenticated
— it has no way to use a developer's own git/GitHub credentials — so a
private repo would 404 for anyone without direct access, defeating the point
of the `npx` install path.

**You still need to set `DB_TYPE` / `CONNECTION_STRING` / `SCHEMA_FILTER`
yourself** (see [Configuration](#configuration) above) — `npx` only fetches
and runs the binary, it doesn't supply your database credentials. In the
`org-standards` plugin these are wired up from `PHX_DB_TYPE` /
`PHX_DB_CONNECTION_STRING` / `PHX_DB_SCHEMA_FILTER` in your own shell
environment.

Archive extraction on Windows uses a small self-contained zip reader (Node's
built-in `zlib`, no external dependency) rather than shelling out to `tar` —
GNU tar (e.g. the one bundled with Git Bash) can't read `.zip` at all, and a
Windows path's drive-letter colon confuses tar's remote-archive detection
regardless. Linux/macOS releases are `.tar.gz` and still extract via the
system `tar`, which every POSIX machine has.

---

## Registering the Server with an MCP Client

The server is not launched directly. Instead, it is registered in your editor's MCP configuration file so the editor starts and manages it automatically.

### VS Code — `.vscode/mcp.json`

Create (or update) `.vscode/mcp.json` in your workspace:

```json
{
  "servers": {
    "phx-dbexplorer": {
      "type": "stdio",
      "command": "Path to PhxDbExplorer.exe",
      "args": [],
      "env": {
        "DB_TYPE": "mssql",
        "CONNECTION_STRING": "Server=localhost,1433;Database=YourDatabase;User Id=YourUsername;Password=YourPassword;TrustServerCertificate=True;",
        "SCHEMA_FILTER": "YourSchema"
      }
    }
  }
}
```

> **Tip:** For a published/built binary, replace the `dotnet run` command with the path to the compiled executable (e.g. `"command": "path/to/PhxDbExplorer.exe"`).

Once registered, restart your editor and the MCP server will be available to any AI assistant that supports the MCP protocol.

---

## Available MCP Tools

These tools are automatically available to your AI assistant once the server is running.

| Tool | Description |
|---|---|
| `list_tables` | Lists all tables and views in the configured schema(s) with type and description. |
| `get_table_schema` | Returns full schema for a table/view: columns, foreign keys, indexes, and constraints. |
| `list_stored_procedures` | Lists all stored procedures in the configured schema(s). |
| `get_procedure_definition` | Returns the full definition of a stored procedure including parameters and SQL source. |
| `list_functions` | Lists all user-defined functions (UDFs) in the configured schema(s). |
| `get_function_definition` | Returns the full definition of a function including parameters and SQL source. |
| `search_schema` | Case-insensitive keyword search across tables, views, columns, procedures, and functions. |

### Data-read tools

| Tool | Description |
|---|---|
| `sample_table_data` | Returns rows from a table or view, with an optional `WHERE` predicate, `ORDER BY` list, and row limit. |
| `execute_query` | Runs a single read-only `SELECT` (or `WITH … SELECT`) — for joins, aggregates, and anything sampling can't express. |
| `get_table_row_count` | Exact row count for a table or view. |
| `get_data_read_limits` | Reports the active `MAX_ROWS`, query timeout, and readable schemas, so a client can size requests before making them. |

Both `sample_table_data` and `execute_query` return `{ columns, rows, rowCount, rowLimit, truncated }`.
`truncated: true` means the row cap was hit and more rows matched than were returned.

#### How reads are kept read-only

Data access is enabled by default and constrained by four independent layers, so no single
mistake makes a write possible:

1. **Statement validation** — `execute_query` accepts only a *single* statement that starts with
   `SELECT` or `WITH`. Writes, DDL, `EXEC`/`CALL`, `SELECT … INTO`, transaction and session
   control, and file/external access (`OPENROWSET`, `pg_read_file`, `sp_`/`xp_` procedures, …)
   are rejected. Validation runs against SQL with string literals, quoted identifiers, and
   comments blanked out, so `'DELETE'` inside a literal and a `[Update]` column name are fine
   while `DR/**/OP` cannot smuggle a keyword past it.
2. **A transaction that is always rolled back** — every caller-supplied statement runs inside a
   transaction the server rolls back unconditionally. On PostgreSQL it is additionally a
   `READ ONLY` transaction, so the engine itself refuses writes.
3. **No identifier interpolation** — `sample_table_data` resolves the table against the catalog
   first and only ever splices the catalog's own spelling into SQL, so a table or schema name
   cannot carry syntax. `WHERE`/`ORDER BY` fragments are raw SQL by necessity and go through the
   same validator, which additionally rejects `;` and unbalanced parentheses.
4. **Schema and row limits** — reads are confined to `SCHEMA_FILTER`, and no response can exceed
   `MAX_ROWS` regardless of the `limit` a caller asks for.

Layer 1 is a filter, not a parser, and it fails closed: an unusual-but-legitimate query may be
rejected. Rephrase it, or use `sample_table_data`. Layers 2–4 are the guarantees.

---

## Running Tests

### Unit Tests

No extra dependencies required.

```bash
dotnet test src/PhxDbExplorer.Tests
```

Tests use xUnit, Moq, and FluentAssertions to verify tool behavior and configuration logic in isolation.

### Integration Tests

> ⚠️ **Docker is required.** Integration tests use [Testcontainers](https://dotnet.testcontainers.org/) to automatically pull and start a **SQL Server 2022** container. Docker must be running before executing these tests.

```bash
dotnet test src/PhxDbExplorer.IntegrationTests
```

The container is started automatically at the beginning of the test run and torn down when the tests complete. No manual database setup is needed.

---

## Releasing

Pushing a tag matching `v*.*.*` (e.g. `v1.2.0`) triggers the [`release` workflow](.github/workflows/release.yml), which runs the unit tests, publishes self-contained single-file binaries for `win-x64`, `linux-x64`, `osx-x64`, and `osx-arm64`, and attaches them to a new GitHub Release.

```bash
git tag v1.2.0
git push origin v1.2.0
```

See [`docs/readme.md`](docs/readme.md) for the full CI/CD pipeline documentation, including artifact naming, job breakdown, and pipeline verification history.

---

## Contributing

1. Fork the repository and create a feature branch.
2. Make your changes — keep them focused and well-tested.
3. Ensure all unit tests pass (`dotnet test src/PhxDbExplorer.Tests`).
4. Open a pull request with a clear description of the change.

Every pull request runs the [`CI` workflow](.github/workflows/ci.yml) automatically: build, unit tests, and integration tests (Testcontainers spins up its own SQL Server container on the runner — no setup needed on your end).