Metadata-Version: 2.4
Name: db-compare-mcp
Version: 0.1.1
Summary: db-compare: read-only MCP server that diffs SQL Server / Azure SQL schema between named environments.
Author: Yadu Krishnan
License-Expression: MIT
Project-URL: Homepage, https://github.com/yadu-tv/db-compare
Project-URL: Repository, https://github.com/yadu-tv/db-compare
Project-URL: Issues, https://github.com/yadu-tv/db-compare/issues
Keywords: mcp,sql-server,azure-sql,schema,database,diff,readonly
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: mcp>=1.2.0
Requires-Dist: pyodbc>=5.1.0
Provides-Extra: dev
Requires-Dist: pytest>=8.0; extra == "dev"
Requires-Dist: pytest-dotenv>=0.5; extra == "dev"
Provides-Extra: jdbc
Requires-Dist: jaydebeapi>=1.2.1; extra == "jdbc"
Requires-Dist: JPype1>=1.5.0; extra == "jdbc"
Dynamic: license-file

# db-compare MCP

<!-- mcp-name: io.github.yadu-tv/db-compare -->

Read-only MCP server for comparing SQL Server / Azure SQL schema between named environments (e.g. dev vs prod).

**This server never writes to the database.** All operations are SELECT queries against catalog views (`sys.*`). No DDL, DML, or EXEC is performed.

## What it compares

- Tables (columns, PKs, FKs, indexes, check constraints)
- Stored procedures, views, functions, triggers (line-by-line definition diff)
- Object inventory (objects only in A, only in B, or changed)

## Prerequisites

1. **Python 3.10+** and [uv](https://docs.astral.sh/uv/)
2. **Read-only database credentials** for each environment (`db_datareader` + `VIEW DEFINITION`)
3. **One of these drivers:**
   - **ODBC (default):** ODBC Driver 18 for SQL Server (`Get-OdbcDriver | Where-Object Name -like '*SQL Server*'`)
   - **JDBC (optional):** Java 11+ and [Microsoft JDBC Driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server) JAR

## Setup

```powershell
cd c:\Users\yadukrishnan\Develop\DBCompare
uv sync

# JDBC only:
uv sync --extra jdbc
```

### ODBC configuration (default)

```
SQLDIFF_DRIVER=odbc
SQLDIFF_CONN_<NAME>=<odbc connection string>
```

Example:

```
SQLDIFF_CONN_DEV=Driver={ODBC Driver 18 for SQL Server};Server=tcp:myserver-dev.database.windows.net,1433;Database=mydb;Uid=readonly;Pwd=...;Encrypt=yes;TrustServerCertificate=no;ApplicationIntent=ReadOnly;
```

### JDBC configuration

```
SQLDIFF_DRIVER=jdbc
SQLDIFF_JDBC_JAR=C:/path/to/mssql-jdbc-12.8.1.jre11.jar
SQLDIFF_CONN_<NAME>=jdbc:sqlserver://host:1433;databaseName=mydb;encrypt=true;trustServerCertificate=false;
SQLDIFF_JDBC_USER_<NAME>=readonly
SQLDIFF_JDBC_PASSWORD_<NAME>=...
```

Notes:
- A connection string starting with `jdbc:sqlserver:` automatically uses JDBC even if `SQLDIFF_DRIVER=odbc`.
- Per-environment override: `SQLDIFF_DRIVER_DEV=jdbc`, `SQLDIFF_DRIVER_PROD=odbc`.
- Credentials can be embedded in the JDBC URL (`user=...;password=...`) instead of separate env vars.
- The server automatically appends read-only intent (`ApplicationIntent=ReadOnly` / `applicationIntent=ReadOnly`) if not present.

## Run standalone

```powershell
uv run server.py
# or
uv run db-compare
```

## Cursor MCP integration

Edit [`.cursor/mcp.json`](.cursor/mcp.json) with your connection strings (or use Cursor secrets). Restart Cursor, then verify tools appear in the MCP panel.

Example `mcp.json`:

```json
{
  "mcpServers": {
    "db-compare": {
      "command": "uv",
      "args": ["run", "server.py"],
      "cwd": "/path/to/DBCompare",
      "env": {
        "SQLDIFF_CONN_DEV": "...",
        "SQLDIFF_CONN_PROD": "..."
      }
    }
  }
}
```

Example chat prompts:

- "Use db-compare to list configured environments"
- "Diff schema inventory between dev and prod"
- "Show full drift report between dev and prod for schema dbo"
- "Diff table structure for dbo.Orders between dev and prod"

## MCP tools

| Tool | Description |
|------|-------------|
| `list_environments` | Show configured environment names |
| `test_connection` | Verify read-only connectivity |
| `list_schemas` | List user schemas in one environment |
| `list_objects` | List tables/views/procs/functions/triggers |
| `diff_schema_inventory` | High-level inventory diff |
| `diff_object_definition` | Line diff for a proc/view/function/trigger |
| `diff_table_structure` | Structural diff for a table |
| `full_drift_report` | Inventory + deep compare of all common objects |

## Testing

```powershell
# Unit tests (no database required)
uv sync --extra dev
uv run pytest tests/test_differ.py tests/test_readonly.py -v

# Integration tests (requires .env.test with SQLDIFF_CONN_DEV and SQLDIFF_CONN_PROD)
uv run pytest tests/ -m integration -v
```

Copy `.env.test.example` to `.env.test` and fill in credentials. Integration tests are read-only — they never modify the database.

## Known limitations

- Encrypted modules cannot be diffed (reported explicitly)
- Computed columns and filtered index WHERE clauses are not captured
- Synonyms, sequences, and user-defined types are not yet included

## Security

- Use read-only database users, not admin accounts
- Never commit connection strings — use `.env.test` (gitignored) or Cursor env secrets
- All SQL passes through a SELECT-only guard in `db.py`

## Install (for users)

After publishing to PyPI:

```powershell
uvx db-compare-mcp
```

Or add to Cursor `mcp.json`:

```json
{
  "mcpServers": {
    "db-compare": {
      "command": "uvx",
      "args": ["db-compare-mcp"],
      "env": {
        "SQLDIFF_CONN_DEV": "...",
        "SQLDIFF_CONN_PROD": "..."
      }
    }
  }
}
```

See [PUBLISHING.md](PUBLISHING.md) for maintainer release steps.

## Publishing

| Item | Value |
|------|-------|
| GitHub | https://github.com/yadu-tv/db-compare |
| PyPI package | `db-compare-mcp` |
| MCP registry name | `io.github.yadu-tv/db-compare` |
| CLI entry point | `db-compare` |
