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

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:

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

Setup

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:

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

Run locally

Frontend + HTTP API (recommended for the UI)

npm run dev

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

HTTP API only

npm run dev:server

MCP server only (stdio)

npm run dev:mcp

Cursor MCP config example

Add to your Cursor MCP settings:

{
  "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:

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:

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:

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:

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