# MCP SQL Console

Production-ready monorepo that connects to **Microsoft SQL Server LocalDB** via:

1. An **MCP server** (stdio) for Cursor / Claude Desktop tools & resources  
2. An **HTTP API bridge** sharing the same database service layer  
3. A **React + Vite + TypeScript** console UI (search, sort, pagination)

## Features

- Connect to SQL Server or LocalDB with Windows Authentication or SQL Authentication
- Expose the same database layer through MCP tools and a REST HTTP API
- Run natural-language queries with local-first routing and optional LLM fallback
- Browse tables, inspect columns, and view paginated records from the React console
- Keep SQL execution read-only for safety in the console and MCP workflows

## Quick start

```powershell
git clone <your-repo-url>
cd mcp-sql-console
copy server\.env.example server\.env
npm install
npm approve-scripts msnodesqlv8 esbuild
npm rebuild msnodesqlv8
npm run dev
```

Then open:

- UI: http://localhost:5173
- API: http://127.0.0.1:3001

## Project structure

```
mcp-sql-console/
├── package.json                 # npm workspaces root
├── server/
│   ├── .env.example
│   ├── package.json
│   ├── scripts/sample-schema.sql
│   └── src/
│       ├── index.ts             # MCP stdio entry
│       ├── http-server.ts       # REST API for the React app
│       ├── config.ts
│       ├── db/sqlServerService.ts
│       ├── mcp/server.ts
│       ├── mcp/tools.ts
│       └── queries/predefined.ts
└── client/
    ├── .env.example
    ├── package.json
    └── src/                     # React UI
```

## Prerequisites

- Node.js **20+**
- Microsoft SQL Server **LocalDB** (or full SQL Server)
- **ODBC Driver 17 or 18 for SQL Server** (already common with SSMS / Azure Data Studio)
- Windows Authentication via the native `msnodesqlv8` driver
- Visual C++ Build Tools (only if the native addon needs to compile)

Start LocalDB if needed:

```powershell
& "C:\Program Files\Microsoft SQL Server\170\Tools\Binn\SqlLocalDB.exe" start MSSQLLocalDB
```

## Setup

```powershell
cd C:\Users\manoj.kumar\Projects\mcp-sql-console
copy server\.env.example server\.env
npm install
# Allow native install scripts (required once for msnodesqlv8 / esbuild)
npm approve-scripts msnodesqlv8 esbuild
npm rebuild msnodesqlv8
```

Default connection (matches Azure Data Studio / SSMS):

| Setting | Value |
|--------|--------|
| Server | `(localdb)\MSSQLLocalDB` |
| Auth | Windows Authentication |
| Database | `master` (set `DB_DATABASE=EmployeeDB` to use your app DB) |
| ODBC driver | `ODBC Driver 18 for SQL Server` |
| Encrypt | `true` |
| Trust server certificate | `true` |

Optional sample table:

```powershell
# Run server/scripts/sample-schema.sql in Azure Data Studio against LocalDB
```

## Run locally

### Frontend + HTTP API (recommended for the UI)

```powershell
npm run dev
```

- UI: http://localhost:5173  
- API: http://127.0.0.1:3001  

### HTTP API only

```powershell
npm run dev:server
```

### MCP server only (stdio)

```powershell
npm run dev:mcp
```

### Cursor MCP config example

Add to your Cursor MCP settings:

```json
{
  "mcpServers": {
    "sql-console": {
      "command": "npx",
      "args": ["tsx", "src/index.ts"],
      "cwd": "C:\\Users\\manoj.kumar\\Projects\\mcp-sql-console\\server",
      "env": {
        "DB_SERVER": "(localdb)\\MSSQLLocalDB",
        "DB_DATABASE": "master",
        "DB_USE_WINDOWS_AUTH": "true",
        "DB_ENCRYPT": "true",
        "DB_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}
```

## Natural language queries

Ask **any** question about the connected database. The MCP server owns the full flow for every call:

1. Read the live schema  
2. Generate a read-only SQL query for that question  
3. Execute it  
4. Return JSON rows + the generated SQL  

Examples that work against whatever tables exist:

- `list Departments`
- `how many Projects`
- `list Salaries`
- `Employees with Departments`
- `Attendance where Status is Present`

Engine selection (`NL_ENGINE` in `server/.env`):

| Value | Behavior |
|-------|----------|
| `auto` (default) | **Local/rich first**; LLM only as fallback when needed |
| `llm` | Always LLM |
| `local` | Never LLM |

### Performance (minimal LLM)

Already implemented:

1. **One-shot schema introspection** — single SQL for all tables/columns (not N+1)  
2. **Schema cache** (`NL_SCHEMA_CACHE_TTL_MS`, default 5 min) + startup warm-up  
3. **Answer cache** (`NL_ANSWER_CACHE_TTL_MS`, default 1 min) — identical questions return instantly  
4. **SQL plan cache** (`NL_PLAN_CACHE_TTL_MS`, default 10 min) — skips NL, re-runs SQL for fresh rows  
5. **In-flight dedupe** — concurrent identical asks share one pipeline  
6. **Local-first routing** — rich/local before any LLM call  
7. **Relevant-schema LLM prompts** — fewer tokens when LLM runs  

Recommended `.env`:

```env
NL_ENGINE=auto
LLM_MODEL=gpt-4o-mini
NL_SCHEMA_CACHE_TTL_MS=300000
NL_ANSWER_CACHE_TTL_MS=60000
NL_PLAN_CACHE_TTL_MS=600000
```

For open-ended phrasing (aggregations, nuanced filters, multi-hop joins), set:

```env
LLM_API_KEY=sk-...
NL_ENGINE=auto
```

- **UI:** Natural language mode at http://localhost:5173 (suggestion chips are generated from your live tables)  
- **HTTP:** `POST /api/ask` with `{ "question": "..." }`  
- **MCP tool:** `ask_natural_language`  
- **Schema:** `GET /api/schema` / MCP `get_schema`

## MCP tools

| Tool | Purpose |
|------|---------|
| `ask_natural_language` | NL → SQL → rows (recommended) |
| `sql_health` | Connectivity check |
| `list_predefined_queries` | Catalog of named queries |
| `run_predefined_query` | Execute a named query by id |
| `execute_sql` | Run a read-only `SELECT` / `WITH` query |
| `list_tables` | User tables in the current database |
| `list_columns` | Columns for a table |
| `get_table_records` | Parameterized paginated table read |

Resource: `sql://predefined-queries`

## HTTP API

| Method | Path | Description |
|--------|------|-------------|
| GET | `/api/health` | DB health |
| GET | `/api/queries` | Predefined queries |
| POST | `/api/queries/:queryId/run` | Run predefined query |
| POST | `/api/ask` | Body: `{ "question": "get employee list" }` |
| POST | `/api/sql/execute` | Body: `{ "sql": "SELECT ..." }` |
| GET | `/api/tables` | List tables |
| GET | `/api/tables/:schema/:table/columns` | List columns |
| GET | `/api/tables/:schema/:table/records` | Paginated records |

## Security notes

- Console / MCP `execute_sql` allows **read-only** `SELECT` / `WITH` statements only.
- Table reads use **parameterized** `OFFSET` / `FETCH` and validated identifiers.
- Prefer predefined queries for recurring access patterns.
- Do not expose the HTTP API beyond localhost without auth / network controls.

## SQL authentication fallback

If `msnodesqlv8` cannot be installed, set in `server/.env`:

```env
DB_USE_WINDOWS_AUTH=false
DB_USER=sa
DB_PASSWORD=YourStrong!Passw0rd
DB_SERVER=localhost
```

(Tedious requires a TCP-reachable SQL Server instance; LocalDB usually needs the native driver.)

## Expected UI

Matches the Phase 0 reference:

- Purple “PHASE 0” badge, **MCP SQL Console** title, short description  
- Large SQL textarea + purple **Run** button  
- Status line (`N row(s) returned`)  
- Search box, sortable headers, paginated table, loading spinner, error state  

## Troubleshooting

- If the native SQL driver fails to install, run `npm approve-scripts msnodesqlv8 esbuild` and `npm rebuild msnodesqlv8` again.
- If LocalDB is not reachable, start it with the PowerShell command shown above and verify that the server name matches your environment.
- If you see missing ODBC errors, install ODBC Driver 17 or 18 for SQL Server.
- For SQL Authentication fallback, set the `DB_USE_WINDOWS_AUTH`, `DB_USER`, and `DB_PASSWORD` values in the server environment file.

## Contributing

Contributions are welcome. If you plan to improve the UI, add new MCP tools, or refine the natural-language pipeline, please:

1. Fork the repository
2. Create a feature branch
3. Make your changes and test locally
4. Open a pull request with a clear summary

## Publish to GitHub

If you want to publish this repository on GitHub, run:

```powershell
git init
git add .
git commit -m "Initial commit"
git branch -M main
git remote add origin https://github.com/<your-username>/<your-repo-name>.git
git push -u origin main
```

## License

This project is licensed under the MIT License. See the LICENSE file for details.

## Scripts

| Command | Description |
|---------|-------------|
| `npm run dev` | API + React together |
| `npm run dev:server` | HTTP API with reload |
| `npm run dev:client` | Vite only |
| `npm run dev:mcp` | MCP stdio server |
| `npm run build` | Build server + client |