AI导哈

SQL数据库访问MCP服务器

这是一个以只读方式向大语言模型提供SQL数据库访问能力的MCP服务器，支持MSSQL、PostgreSQL和MySQL，并具备查询、表结构浏览、执行计划分析、结果导出与Markdown证据保存等功能，适用于需要让LLM安全访问数据库的开发与测试场景。

  • by akrym1582
  • TypeScript
  • 本地部署
  • 更新于
在 GitHub 查看 akrym1582/sqldb-mcp-server

使用说明

@akrym1582/sqldb-mcp-server

A read-only Model Context Protocol (MCP) server that exposes SQL database access to LLMs.

Features

  • Multi-database – supports MSSQL, PostgreSQL, and MySQL
  • Read-only – only SELECT statements are allowed (enforced via AST-level SQL parsing with the correct dialect per DB type)
  • LLM-optimised – results use a compact columnar format (column list + value rows) to reduce token usage
  • Pagination – skip / take parameters with automatic cap at 100 rows
  • Total-count aware – every query result includes meta.totalCount so the LLM knows how many rows exist
  • Caching – query / schema results are cached with a configurable TTL
  • File export – stream query results to CSV or JSON files without a row-count limit
  • Markdown evidence export – save SQL and query results as a Markdown report file for test evidence
  • Database selection – allow exact database names and regular-expression matches, then select a database per tool call
  • Seven MCP tools: listDatabases, query, listTables, describeTable, explainQuery, exportQuery, saveQueryEvidence

Installation

From npm (recommended)

# Install globally
npm install -g @akrym1582/sqldb-mcp-server

# Or run directly with npx (no install needed)
npx @akrym1582/sqldb-mcp-server

From source

git clone https://github.com/akrym1582/sqldb-mcp-server.git
cd sqldb-mcp-server
npm install
npm run build

Quick Start

# 1. Install globally
npm install -g @akrym1582/sqldb-mcp-server

# 2. Configure environment variables (see below)
export DB_TYPE=postgresql
export DB_HOST=localhost
export DB_USER=myuser
export DB_PASSWORD=mypassword
export DB_NAME=mydb

# 3. Run
sqldb-mcp-server

Or use in your MCP client configuration (e.g. Claude Desktop claude_desktop_config.json):

{
  "mcpServers": {
    "sqldb": {
      "command": "npx",
      "args": ["-y", "@akrym1582/sqldb-mcp-server"],
      "env": {
        "DB_TYPE": "postgresql",
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_USER": "myuser",
        "DB_PASSWORD": "mypassword",
        "DB_NAME": "mydb"
      }
    }
  }
}

Environment Variables

Variable Default Description
DB_TYPE mssql Database type: mssql, postgresql, or mysql
DB_HOST – Database server hostname
DB_PORT 1433 / 5432 / 3306 Database server port (default depends on DB_TYPE)
DB_USER – Database username
DB_PASSWORD – Database password
DB_NAME – Allowed databases: comma-separated exact names and/or JavaScript regex literals (for example app,analytics,/^tenant_[0-9]+$/)
DB_DEFAULT first allowed database Default database when a tool call omits database; it must be allowed by DB_NAME
DB_ENCRYPT true for MSSQL/PostgreSQL, false for MySQL Enables encrypted DB connections. MSSQL trusts the server certificate. PostgreSQL tries SSL first and falls back to plain if SSL is unavailable. MySQL uses TLS with certificate verification disabled when enabled.
DB_QUERY_TIMEOUT 30000 Query timeout in milliseconds (used by query / explainQuery)
EXPORT_QUERY_TIMEOUT 300000 Export query timeout in milliseconds (used by exportQuery; default 5 min)
CACHE_TTL 60 Cache TTL in seconds

Default ports by DB type

DB_TYPE Default DB_PORT
mssql 1433
postgresql 5432
mysql 3306

MCP Tools

Every database-aware tool accepts an optional database string. If omitted, DB_DEFAULT is used, or otherwise the first exact/matched database in DB_NAME. A requested database must match the configured allow-list; arbitrary database access is rejected.

Regular-expression entries are resolved against databases visible to the configured user. When using regex-only PostgreSQL configuration, ensure the user has a connectable maintenance database (normally the database with the same name as the user); MSSQL and MySQL can enumerate databases without selecting one.

listDatabases

List the databases available to the MCP tools and identify the current default:

[
  { "name": "app", "isDefault": true },
  { "name": "tenant_42", "isDefault": false }
]

query

Execute a SELECT SQL statement.

{
  "sql": "SELECT id, name FROM users WHERE active = 1",
  "database": "app",
  "skip": 0,
  "take": 10
}

Response format (compact / token-efficient):

{
  "meta": { "totalCount": 42, "returnedCount": 10, "skip": 0, "take": 10 },
  "columns": ["id", "name"],
  "rows": [[1, "Alice"], [2, "Bob"], ...]
}

listTables

List all base tables in a selected database. Pass { "database": "app" }, or omit it to use the default.

[{ "schema": "dbo", "name": "users" }, ...]

describeTable

Describe a table's columns, indexes, foreign keys, check constraints, and size statistics.

{ "database": "app", "table": "dbo.users" }

explainQuery

Return the estimated execution plan for a SELECT query without executing it.

{ "database": "app", "sql": "SELECT * FROM orders WHERE status = 'open'" }

exportQuery

Stream a SELECT query result to a file. Designed for large datasets – there is no row-count limit and results are written directly to disk using Node.js streams.

{
  "sql": "SELECT * FROM large_table",
  "database": "analytics",
  "filepath": "/tmp/export.csv",
  "format": "csv",
  "options": { "delimiter": ",", "bom": false }
}

format defaults to "csv" if omitted. "json" is also supported.

CSV options (all optional):

Option Default Description
delimiter "," Column separator
nullValue "" String to write for NULL / undefined cells
bom false Prepend UTF-8 BOM (useful for Excel)

JSON options (all optional):

Option Default Description
pretty false Indent the output JSON

Response format:

{
  "filepath": "/tmp/export.csv",
  "format": "csv",
  "rowCount": 50000
}

The tool uses a separate, longer-lived connection pool whose requestTimeout is controlled by EXPORT_QUERY_TIMEOUT (default 300 000 ms = 5 min). Increase this value for very large exports.

saveQueryEvidence

Execute a SELECT query and save the SQL plus the returned rows as a Markdown report file for test evidence.

{
  "sql": "SELECT id, name FROM users LIMIT 10",
  "database": "app",
  "filepath": "/tmp/query-evidence.md"
}

Response format:

{
  "filepath": "/tmp/query-evidence.md",
  "rowCount": 10,
  "previewRows": [
    { "id": 1, "name": "Alice" },
    { "id": 2, "name": "Bob" }
  ]
}

If an error occurs, the tool returns the error message text instead of a success payload.

Development

# Clone the repository
git clone https://github.com/akrym1582/sqldb-mcp-server.git
cd sqldb-mcp-server

# Install dependencies
npm install

# Configure environment
cp .env.example .env
# Edit .env with your DB credentials

# Run in dev mode (no compile step)
npm run dev

# Or build and run
npm run build
npm start

# Run unit tests
npm test

Project Structure

src/
  mcp/
    server.ts           # MCP server entry point
    tools/
      query.ts          # query tool
      listTables.ts     # listTables tool
      describeTable.ts  # describeTable tool
      explainQuery.ts   # explainQuery tool
      exportQuery.ts    # exportQuery tool (streaming file export)
  db/
    index.ts            # DB adapter factory (selects adapter from DB_TYPE)
    types.ts            # DB interfaces (including queryStream)
    adapters/
      mssql.ts          # Microsoft SQL Server implementation
      postgresql.ts     # PostgreSQL implementation (pg + pg-cursor)
      mysql.ts          # MySQL implementation (mysql2)
  utils/
    row-result.ts       # Compact columnar result format
    sanitize.ts         # AST-based SQL read-only validation (dialect-aware)
    pagination.ts       # skip/take normalisation
    cache.ts            # TTL in-memory cache
    export-writer.ts    # Streaming CSV / JSON file writer
  __tests__/            # Unit tests

相关 MCP