@adevguide/mcp-database-server
by iPraBhu
README.md
# @adevguide/mcp-database-server
[](https://www.npmjs.com/package/@adevguide/mcp-database-server)
[](https://www.npmjs.com/package/@adevguide/mcp-database-server)
Production-grade Model Context Protocol (MCP) server for unified SQL database access. Connect multiple databases through a single MCP server with schema discovery, relationship mapping, caching, and safety controls.
- npm: https://www.npmjs.com/package/@adevguide/mcp-database-server
- GitHub: https://github.com/iPraBhu/mcp-database-server
## Contents
- [Features](#features)
- [Why this exists](#why-this-exists)
- [Installation](#installation)
- [Configuration](#configuration)
- [MCP client integration](#mcp-client-integration)
## Features
- Multi-database support: PostgreSQL, MySQL/MariaDB, SQLite, SQL Server, Oracle
- Automatic schema discovery: tables, columns, indexes, foreign keys, relationships
- Persistent schema caching: TTL + versioning, manual refresh, cache stats
- Relationship inference: foreign keys + heuristics
- Query intelligence: tracking, statistics, timeouts
- Join assistance: suggested join paths based on relationship graphs
- Safety controls: read-only mode, allow/deny write operations, secret redaction
- **Query optimization**: index recommendations, performance profiling, slow query detection
- **Performance monitoring**: detailed execution analytics, bottleneck identification
- **Query rewriting**: automated optimization suggestions with performance impact estimates
## Why this exists
This project was originally vibe-coded to solve real issues I was facing when wiring LLM tools to multiple SQL databases (consistent connectivity, schema discovery, and safe query execution). It has since been hardened into a reusable MCP server with caching and security defaults.
## Architecture
```
┌─────────────────────────────────────────────────────────┐
│ MCP Client │
│ (Claude Desktop, IDEs, etc.) │
└────────────────┬────────────────────────────────────────┘
│ JSON-RPC over stdio
┌────────────────▼────────────────────────────────────────┐
│ MCP Database Server │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Schema Cache (TTL + Versioning) │ │
│ └──────────────────────────────────────────────────┘ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Query Tracker (History + Statistics) │ │
│ └──────────────────────────────────────────────────┘ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Security Layer (Read-only, Operation Controls) │ │
│ └──────────────────────────────────────────────────┘ │
└────┬─────────┬─────────┬──────────┬──────────┬─────────┘
│ │ │ │ │
┌────▼───┐ ┌──▼────┐ ┌──▼─────┐ ┌──▼──────┐ ┌▼────────┐
│Postgres│ │ MySQL │ │ SQLite │ │ MSSQL │ │ Oracle │
└────────┘ └───────┘ └────────┘ └─────────┘ └─────────┘
```
## Supported Databases
| Database | Driver | Status | Notes |
|----------|--------|--------|-------|
| PostgreSQL | `pg` | ✅ Full Support | Includes CockroachDB compatibility |
| MySQL/MariaDB | `mysql2` | ✅ Full Support | Includes Amazon Aurora MySQL compatibility |
| SQLite | `sql.js` | ✅ Full Support | WASM-backed SQLite with file persistence |
| SQL Server | `tedious` | ✅ Full Support | Microsoft SQL Server / Azure SQL |
| Oracle | `oracledb` | ⚠️ Stub | Requires Oracle Instant Client |
## Installation
### Global install (recommended)
```bash
npm install -g @adevguide/mcp-database-server
```
Run:
```bash
mcp-database-server --config /absolute/path/to/.mcp-database-server.config
```
### Run via npx (no global install)
```bash
npx -y @adevguide/mcp-database-server --config /absolute/path/to/.mcp-database-server.config
```
### Install from source
```bash
git clone https://github.com/iPraBhu/mcp-database-server.git
cd mcp-database-server
npm install
npm run build
node dist/index.js --config ./.mcp-database-server.config
```
## Configuration
Create a `.mcp-database-server.config` file in your project root:
> **Note:** The config file is automatically discovered inside the current project tree. If you don't specify `--config`, the tool searches upward from the current directory until it reaches the detected project root (for example a directory containing `package.json` or `.git`). It does not continue past the project root. If you use `credentialCommand`, pass `--config` explicitly.
```json
{
"databases": [
{
"id": "postgres-main",
"type": "postgres",
"secretRef": "DB_URL_POSTGRES",
"readOnly": true,
"pool": {
"min": 2,
"max": 10,
"idleTimeoutMillis": 30000
},
"introspection": {
"includeViews": true,
"excludeSchemas": ["pg_catalog"]
}
},
{
"id": "mariadb-reporting",
"type": "mysql",
"secretRef": "DB_URL_MARIADB",
"readOnly": true,
"pool": {
"min": 1,
"max": 5
}
},
{
"id": "sqlite-local",
"type": "sqlite",
"path": "./data/app.db"
}
],
"cache": {
"directory": ".sql-mcp-cache",
"ttlMinutes": 10
},
"security": {
"allowWrite": false,
"allowedWriteOperations": ["INSERT", "UPDATE"],
"disableDangerousOperations": true,
"redactSecrets": true
},
"logging": {
"level": "info",
"pretty": false
}
}
```
### Configuration Reference
#### Database Configuration
Each database in the `databases` array represents a connection to a SQL database.
##### Core Properties
| Property | Type | Required | Default | Description |
|----------|------|----------|---------|-------------|
| `id` | string | ✅ Yes | - | Unique identifier for this database connection. Used in all MCP tool calls. Must be unique across all databases. |
| `type` | enum | ✅ Yes | - | Database system type. Valid values: `postgres`, `mysql`, `sqlite`, `mssql`, `oracle` |
| `url` | string | Conditional* | - | Explicit database connection string. Supports environment variable interpolation with `${DB_URL}` but is not the recommended secret-handling path. |
| `secretRef` | string | Conditional* | - | Name of an environment variable that contains the full connection string. Resolved from the process environment or a `.env` file beside the config file. |
| `credentialCommand` | string | Conditional* | - | Shell command that prints the full connection string to stdout at startup. Useful for 1Password, Vault, AWS helpers, or custom secret tooling. Requires launching the server with an explicit `--config` path. |
| `path` | string | Conditional** | - | Filesystem path to SQLite database file. Required only for `type: sqlite`. Can be relative or absolute. |
| `readOnly` | boolean | No | `true` | When `true`, blocks all write operations (INSERT, UPDATE, DELETE, etc.). Recommended for production safety. |
| `eagerConnect` | boolean | No | `false` | When `true`, connects to database immediately at startup (fail-fast). When `false`, connects on first query (lazy loading). |
<sub>* Required for postgres, mysql, mssql, oracle</sub>
<sub>** Required for sqlite only</sub>
**Connection String Formats:**
```
PostgreSQL: postgresql://username:password@host:5432/database
MySQL: mysql://username:password@host:3306/database
SQL Server: Server=host,1433;Database=dbname;User Id=user;Password=pass
SQLite: (use path property instead)
Oracle: username/password@host:1521/servicename
```
##### Connection Pool Configuration
The `pool` object controls connection pooling behavior. Improves performance by reusing database connections.
| Property | Type | Required | Default | Description |
|----------|------|----------|---------|-------------|
| `min` | number | No | `2` | Minimum number of connections to maintain in the pool. Kept alive even when idle. |
| `max` | number | No | `10` | Maximum number of concurrent connections. Do not exceed your database's connection limit. |
| `idleTimeoutMillis` | number | No | `30000` | Time (ms) to keep idle connections alive before closing. Example: `60000` = 1 minute. |
| `connectionTimeoutMillis` | number | No | `10000` | Time (ms) to wait when establishing a connection before timing out. Fail-fast if database is unreachable. |
**Recommendations:**
- **Development:** `min: 1`, `max: 5`
- **Production (Low Traffic):** `min: 2`, `max: 10`
- **Production (High Traffic):** `min: 5`, `max: 20`
##### Introspection Configuration
The `introspection` object controls schema discovery behavior. Determines what database objects are analyzed.
| Property | Type | Required | Default | Description |
|----------|------|----------|---------|-------------|
| `includeViews` | boolean | No | `true` | Include database views in schema discovery. Set to `false` if views cause performance issues. |
| `includeRoutines` | boolean | No | `false` | Include stored procedures and functions. (Not fully implemented - planned feature) |
| `maxTables` | number | No | unlimited | Limit introspection to first N tables. Useful for databases with 1000+ tables. May result in incomplete relationship discovery. |
| `includeSchemas` | string[] | No | all | Whitelist of schemas to introspect. Only applicable to PostgreSQL and SQL Server. Example: `["public", "app"]` |
| `excludeSchemas` | string[] | No | none | Blacklist of schemas to skip. Common values: `["pg_catalog", "information_schema", "sys"]` |
**Schema vs Database:**
- **PostgreSQL/SQL Server:** Support multiple schemas per database. Use `includeSchemas`/`excludeSchemas`.
- **MySQL/MariaDB:** Schema = database. Use database name in connection string.
- **SQLite:** Single-file database, no schema concept.
#### Cache Configuration
Controls schema metadata caching to improve startup performance and reduce database load.
| Property | Type | Required | Default | Description |
|----------|------|----------|---------|-------------|
| `directory` | string | No | `.sql-mcp-cache` | Directory path where cached schema files are stored. One JSON file per database. |
| `ttlMinutes` | number | No | `10` | Time-To-Live in minutes. How long cached schema is considered valid before automatic refresh. |
**Cache Behavior:**
- **On Startup:** Loads schema from cache if available and not expired
- **After TTL Expiry:** Next query triggers automatic re-introspection
- **Manual Refresh:** Use `clear_cache` tool or `introspect_schema` with `forceRefresh: true`
- **Cache Files:** Stored as `{database-id}.json` (e.g., `postgres-main.json`)
**Recommended TTL Values:**
- **Development:** `5` minutes (schema changes frequently)
- **Staging:** `30-60` minutes
- **Production (Static):** `1440` minutes (24 hours)
- **Production (Active):** `60-240` minutes (1-4 hours)
#### Security Configuration
Comprehensive security controls to protect your databases from unauthorized or dangerous operations.
| Property | Type | Required | Default | Description |
|----------|------|----------|---------|-------------|
| `allowWrite` | boolean | No | `false` | Master switch for write operations. When `false`, all writes are blocked across all databases. |
| `allowedWriteOperations` | string[] | No | all | Whitelist of allowed SQL operations when `allowWrite: true`. Valid values: `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `ALTER`, `DROP`, `TRUNCATE`, `REPLACE`, `MERGE` |
| `disableDangerousOperations` | boolean | No | `true` | **Extra safety layer.** When `true`, blocks `DELETE`, `TRUNCATE`, and `DROP` operations even if writes are allowed. Prevents accidental data loss. |
| `redactSecrets` | boolean | No | `true` | Redact connection strings, passwords, and similar credentials in logs and returned error messages. |
**Security Layers (Evaluated in Order):**
1. **Database-level `readOnly`** → Blocks all writes for specific database
2. **Global `allowWrite`** → Master switch for all databases
3. **`disableDangerousOperations`** → Blocks DELETE/TRUNCATE/DROP specifically
4. **`allowedWriteOperations`** → Whitelist of permitted operations
**Example Configurations:**
```json
// Read-only access (default - safest)
{
"allowWrite": false
}
// Allow INSERT and UPDATE only (no deletes)
{
"allowWrite": true,
"allowedWriteOperations": ["INSERT", "UPDATE"],
"disableDangerousOperations": true
}
// Full write access (development only - dangerous!)
{
"allowWrite": true,
"disableDangerousOperations": false
}
```
#### Logging Configuration
Controls log output verbosity and formatting.
| Property | Type | Required | Default | Description |
|----------|------|----------|---------|-------------|
| `level` | enum | No | `info` | Log level. Valid values: `trace`, `debug`, `info`, `warn`, `error`. Lower levels include higher levels. |
| `pretty` | boolean | No | `false` | When `true`, formats logs as human-readable text. When `false`, outputs structured JSON (better for production log aggregation). |
**Log Levels:**
- **`trace`:** Everything (extremely verbose - use for debugging only)
- **`debug`:** Detailed diagnostic information
- **`info`:** General informational messages (recommended for production)
- **`warn`:** Warning messages that don't prevent operation
- **`error`:** Error messages only
**Recommendations:**
- **Development:** `level: "debug"`, `pretty: true`
- **Production:** `level: "info"`, `pretty: false`
- **Troubleshooting:** `level: "trace"`, `pretty: true`
---
### Complete Configuration Example
```json
{
"databases": [
{
"id": "postgres-production",
"type": "postgres",
"url": "${DATABASE_URL}",
"readOnly": true,
"pool": {
"min": 5,
"max": 20,
"idleTimeoutMillis": 60000,
"connectionTimeoutMillis": 5000
},
"introspection": {
"includeViews": true,
"includeRoutines": false,
"excludeSchemas": ["pg_catalog", "information_schema"]
},
"eagerConnect": true
},
{
"id": "mysql-analytics",
"type": "mysql",
"url": "${MYSQL_URL}",
"readOnly": true,
"pool": {
"min": 2,
"max": 10
},
"introspection": {
"includeViews": true,
"maxTables": 100
}
},
{
"id": "sqlite-local",
"type": "sqlite",
"path": "./data/app.db",
"readOnly": true
}
],
"cache": {
"directory": ".sql-mcp-cache",
"ttlMinutes": 60
},
"security": {
"allowWrite": false,
"allowedWriteOperations": ["INSERT", "UPDATE"],
"disableDangerousOperations": true,
"redactSecrets": true
},
"logging": {
"level": "info",
"pretty": false
}
}
```
---
### Secret Resolution
**Recommended approach: `secretRef`**
Keep secrets out of the MCP client config and out of the server config values themselves.
**Example Configuration:**
```json
{
"databases": [
{
"id": "production-db",
"type": "postgres",
"secretRef": "DATABASE_URL"
}
]
}
```
The server resolves `secretRef` from the process environment first, and then from a `.env` file next to `.mcp-database-server.config`.
**Environment File (`.env`):**
```env
DATABASE_URL=postgresql://user:password@localhost:5432/dbname
DB_URL_MYSQL=mysql://user:password@localhost:3306/dbname
DB_URL_MARIADB=mysql://report_user:password@mariadb.local:3306/reporting
DB_URL_MSSQL=Server=host,1433;Database=db;User Id=sa;Password=pass
```
**Alternative: `credentialCommand`**
```json
{
"databases": [
{
"id": "analytics-db",
"type": "mysql",
"credentialCommand": "op read op://analytics/mysql/url"
}
]
}
```
The command must print only the connection string to stdout.
For safety, `credentialCommand` is only allowed when the server is launched with an explicit `--config` path. Auto-discovered configs cannot execute credential commands.
**Still Supported: direct env interpolation**
You can still write `"url": "${DATABASE_URL}"`, but `secretRef` is the cleaner option because it makes the secret source explicit.
**Best Practices:**
- ✅ Store `.env` file outside version control (add to `.gitignore`)
- ✅ Use different `.env` files for each environment (dev, staging, prod)
- ✅ Never commit credentials to git repositories
- ✅ Use secret management services (AWS Secrets Manager, HashiCorp Vault) in production
---
### Connection String Reference
| Database | Format | Example |
|----------|--------|---------|
| **PostgreSQL** | `postgresql://user:pass@host:port/db` | `postgresql://admin:secret@localhost:5432/myapp` |
| **MySQL** | `mysql://user:pass@host:port/db` | `mysql://root:password@localhost:3306/myapp` |
| **MariaDB** | `mysql://user:pass@host:port/db` | `mysql://report_user:password@mariadb.local:3306/reporting` |
| **SQL Server** | `Server=host,port;Database=db;User Id=user;Password=pass` | `Server=localhost,1433;Database=myapp;User Id=sa;Password=secret` |
| **SQLite** | Use `path` property | `"path": "./data/app.db"` or `"path": "/var/db/app.sqlite"` |
**Additional Parameters:**
**PostgreSQL:**
```
postgresql://user:pass@host:5432/db?sslmode=require&connect_timeout=10
```
**MySQL:**
```
mysql://user:pass@host:3306/db?charset=utf8mb4&timezone=Z
```
**MariaDB:**
```
mysql://user:pass@host:3306/db?charset=utf8mb4
```
**SQL Server:**
```
Server=host;Database=db;User Id=user;Password=pass;Encrypt=true;TrustServerCertificate=false
```
---
## MCP Client Integration
### Configuration File Locations
| MCP Client | Configuration File Path |
|------------|------------------------|
| **Claude Desktop** (macOS) | `~/Library/Application Support/Claude/claude_desktop_config.json` |
| **Claude Desktop** (Windows) | `%APPDATA%\Claude\claude_desktop_config.json` |
| **Cline** (VS Code) | VS Code settings → MCP Servers |
| **Other Clients** | Refer to client-specific documentation |
### Setup Methods
#### Method 1: Global npm Installation
**Configuration:**
```json
{
"mcpServers": {
"database": {
"command": "mcp-database-server",
"args": ["--config", "/absolute/path/to/.mcp-database-server.config"]
}
}
}
```
#### Method 2: Source Installation
**Configuration:**
```json
{
"mcpServers": {
"database": {
"command": "node",
"args": [
"/absolute/path/to/mcp-database-server/dist/index.js",
"--config",
"/absolute/path/to/.mcp-database-server.config"
]
}
}
}
```
### Configuration Properties
| Property | Description | Example |
|----------|-------------|---------|
| `command` | Executable to run. Use `mcp-database-server` for npm install, `node` for source install. | `"mcp-database-server"` |
| `args` | Array of command-line arguments. First arg is usually `--config` followed by config file path. | `["--config", "/path/to/config"]` |
| `env` | Optional environment variables passed to the server. Prefer `secretRef` with a local `.env` file or external secret tooling for DB credentials. | `{"APP_ENV": "production"}` |
**Finding Absolute Paths:**
```bash
# macOS/Linux
cd /path/to/mcp-database-server
pwd # prints: /Users/username/projects/mcp-database-server
# Windows (PowerShell)
cd C:\path\to\mcp-database-server
$PWD.Path # prints: C:\Users\username\projects\mcp-database-server
```
---
## Available MCP Tools
This server provides 15 tools for comprehensive database interaction and optimization.
### Tool Reference
| Tool | Purpose | Write Access | Cached Data |
|------|---------|--------------|-------------|
| `list_databases` | List all configured databases with status | No | Uses cache |
| `introspect_schema` | Discover and cache database schema | No | Writes cache |
| `get_schema` | Retrieve cached schema metadata | No | Reads cache |
| `run_query` | Execute SQL queries with safety controls | Conditional* | Updates stats |
| `export_query` | Export large read-only query results to a local file | No | No cache |
| `explain_query` | Analyze query execution plans | No | No cache |
| `suggest_joins` | Get intelligent join path recommendations | No | Uses cache |
| `clear_cache` | Clear schema cache and statistics | No | Clears cache |
| `cache_status` | View cache health and statistics | No | Reads cache |
| `health_check` | Test database connectivity | No | No cache |
| `analyze_performance` | Get detailed performance analytics | No | Uses stats |
| `suggest_indexes` | Analyze queries and recommend indexes | No | Uses stats |
| `detect_slow_queries` | Identify and alert on slow queries | No | Uses stats |
| `rewrite_query` | Suggest optimized query versions | No | Uses cache |
| `profile_query` | Profile query performance with bottlenecks | No | No cache |
<sub>* Requires `allowWrite: true` and respects security settings</sub>
---
### 1. list_databases
Lists all configured databases with their connection status and cache information.
**Input Parameters:**
None required.
**Response:**
```json
[
{
"id": "postgres-main",
"type": "postgres",
"connected": true,
"cached": true,
"cacheAge": 45000,
"version": "abc123"
}
]
```
**Response Fields:**
| Field | Type | Description |
|-------|------|-------------|
| `id` | string | Database identifier from configuration |
| `type` | string | Database type (postgres, mysql, sqlite, mssql, oracle) |
| `connected` | boolean | Whether database connection is active |
| `cached` | boolean | Whether schema is currently cached |
| `cacheAge` | number | Age of cached schema in milliseconds (if cached) |
| `version` | string | Cache version hash (if cached) |
---
### 2. introspect_schema
Discovers and caches complete database schema including tables, columns, indexes, foreign keys, and relationships.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database identifier to introspect |
| `forceRefresh` | boolean | No | Force re-introspection even if cache is valid (default: `false`) |
| `schemaFilter` | object | No | Filter which objects to introspect |
| `schemaFilter.includeSchemas` | string[] | No | Only introspect these schemas (PostgreSQL/SQL Server) |
| `schemaFilter.excludeSchemas` | string[] | No | Skip these schemas during introspection |
| `schemaFilter.includeViews` | boolean | No | Include database views (default: `true`) |
| `schemaFilter.maxTables` | number | No | Limit to first N tables |
**Example Request:**
```json
{
"dbId": "postgres-main",
"forceRefresh": false,
"schemaFilter": {
"includeSchemas": ["public"],
"excludeSchemas": ["temp"],
"includeViews": true,
"maxTables": 100
}
}
```
**Response:**
```json
{
"dbId": "postgres-main",
"version": "a1b2c3d4",
"introspectedAt": "2026-01-26T10:00:00.000Z",
"schemas": [
{
"name": "public",
"tableCount": 15,
"viewCount": 3
}
],
"totalTables": 15,
"totalRelationships": 12
}
```
---
### 3. get_schema
Retrieves detailed schema metadata from cache without querying the database.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database identifier |
| `schema` | string | No | Filter to specific schema name |
| `table` | string | No | Filter to specific table name |
**Example Request:**
```json
{
"dbId": "postgres-main",
"schema": "public",
"table": "users"
}
```
**Response:** Complete schema metadata including tables, columns, data types, indexes, foreign keys, and inferred relationships.
---
### 4. run_query
Executes SQL queries with automatic schema caching, relationship annotation, and comprehensive security controls.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database identifier to query |
| `sql` | string | Yes | SQL query to execute |
| `params` | array | No | Parameterized query values (prevents SQL injection) |
| `limit` | number | No | Maximum number of rows to return |
| `offset` | number | No | Row offset for paginated reads. Requires `limit`. |
| `maxBytes` | number | No | Approximate max serialized bytes for returned rows. |
| `includeMetadata` | boolean | No | Include relationship and query statistics metadata in the response. Default: `true`. |
| `trackQuery` | boolean | No | Track this query in history and performance analytics. Default: `true`. |
| `timeoutMs` | number | No | Query timeout in milliseconds |
**Example Request:**
```json
{
"dbId": "postgres-main",
"sql": "SELECT * FROM users WHERE active = $1 ORDER BY id",
"params": [true],
"limit": 10,
"offset": 0,
"maxBytes": 32768,
"includeMetadata": false,
"trackQuery": false,
"timeoutMs": 5000
}
```
**Response:**
```json
{
"rows": [
{"id": 1, "name": "Alice", "email": "alice@example.com", "active": true},
{"id": 2, "name": "Bob", "email": "bob@example.com", "active": true}
],
"columns": ["id", "name", "email", "active"],
"rowCount": 2,
"executionTimeMs": 15,
"metadata": {
"relationships": [...],
"queryStats": {
"totalQueries": 10,
"avgExecutionTime": 20,
"errorCount": 0
},
"pagination": {
"limit": 10,
"offset": 0,
"hasMore": true,
"nextOffset": 10
},
"responseSize": {
"maxBytes": 32768,
"rowsBytes": 1842,
"rowsTrimmed": false,
"omittedRowCount": 0
}
}
}
```
For the fastest MariaDB/MySQL read path, set `"includeMetadata": false` and `"trackQuery": false` when you only need result rows and do not need relationship annotations, query history, or performance analytics for that request.
**Security Controls:**
- ✅ Write operations blocked by default (`allowWrite: false`)
- ✅ Dangerous operations (DELETE, TRUNCATE, DROP) disabled by default
- ✅ Specific operations can be whitelisted via `allowedWriteOperations`
- ✅ Per-database `readOnly` mode
---
### 5. explain_query
Retrieves database query execution plan without executing the query.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database identifier |
| `sql` | string | Yes | SQL query to analyze |
| `params` | array | No | Query parameters (for parameterized queries) |
**Example Request:**
```json
{
"dbId": "postgres-main",
"sql": "SELECT * FROM users JOIN orders ON users.id = orders.user_id WHERE users.active = $1",
"params": [true]
}
```
**Response:** Database-native execution plan (format varies by database type).
---
### 5a. export_query
Exports large read-only query results to a local file under `.sql-mcp-cache/exports`.
**Execution Strategy:**
- MySQL/MariaDB uses adapter-level row streaming to avoid loading the full result set into memory.
- PostgreSQL and SQLite use paged export by rewriting top-level `LIMIT/OFFSET` windows.
- SQL Server export requires a future adapter-specific streaming path and will currently fail unless paging rewrite is supported.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database identifier |
| `sql` | string | Yes | Read-only SQL query to export |
| `params` | array | No | Query parameters |
| `format` | string | No | Output format: `jsonl` or `csv` (default: `jsonl`) |
| `pageSize` | number | No | Page size for non-streaming adapters (default: `1000`) |
| `fileName` | string | No | Optional output file name written inside the export directory |
| `timeoutMs` | number | No | Query timeout in milliseconds |
**Example Request:**
```json
{
"dbId": "mariadb-reporting",
"sql": "SELECT id, email, created_at FROM users ORDER BY id",
"format": "jsonl",
"fileName": "users-export.jsonl",
"timeoutMs": 10000
}
```
**Response:**
```json
{
"dbId": "mariadb-reporting",
"outputPath": "/absolute/path/to/.sql-mcp-cache/exports/users-export.jsonl",
"format": "jsonl",
"strategy": "stream",
"rowsExported": 250000,
"columns": ["id", "email", "created_at"],
"fileSizeBytes": 18342011,
"executionTimeMs": 8421
}
```
---
### 6. suggest_joins
Analyzes relationship graph to recommend optimal join paths between multiple tables.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database identifier |
| `tables` | string[] | Yes | Array of table names to join (2-10 tables) |
**Example Request:**
```json
{
"dbId": "postgres-main",
"tables": ["users", "orders", "products"]
}
```
**Response:**
```json
[
{
"tables": ["users", "orders", "products"],
"joins": [
{
"fromTable": "users",
"toTable": "orders",
"relationship": {
"type": "one-to-many",
"confidence": 1.0
},
"joinCondition": "users.id = orders.user_id"
},
{
"fromTable": "orders",
"toTable": "products",
"relationship": {
"type": "many-to-one",
"confidence": 1.0
},
"joinCondition": "orders.product_id = products.id"
}
],
"sql": "FROM users JOIN orders ON users.id = orders.user_id JOIN products ON orders.product_id = products.id"
}
]
```
---
### 7. clear_cache
Clears schema cache and query statistics for one or all databases.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | No | Database to clear (omit to clear all) |
**Example Request:**
```json
{
"dbId": "postgres-main"
}
```
**Response:** Confirmation message.
---
### 8. cache_status
Retrieves detailed cache statistics and health information.
**Input Parameters:**
None required.
**Response:**
```json
{
"directory": ".sql-mcp-cache",
"ttlMinutes": 10,
"databases": [
{
"dbId": "postgres-main",
"cached": true,
"version": "abc123",
"age": 120000,
"expired": false,
"tableCount": 15,
"sizeBytes": 45678
}
]
}
```
---
### 9. health_check
Tests database connectivity and returns status information.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | No | Database to check (omit to check all) |
**Response:**
```json
{
"databases": [
{
"dbId": "postgres-main",
"healthy": true,
"connected": true,
"version": "PostgreSQL 15.3",
"responseTimeMs": 12
}
]
}
```
---
### 10. analyze_performance
Get comprehensive performance analytics across all queries for a database.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database to analyze |
**Response:**
```json
{
"totalQueries": 1250,
"slowQueries": 23,
"avgExecutionTime": 45.67,
"p95ExecutionTime": 234.5,
"errorRate": 1.2,
"mostFrequentTables": [
{ "table": "users", "count": 456 },
{ "table": "orders", "count": 234 }
],
"performanceTrend": "improving"
}
```
---
### 11. suggest_indexes
Analyze query patterns and recommend optimal database indexes.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database to analyze |
**Response:**
```json
[
{
"table": "orders",
"columns": ["customer_id", "order_date"],
"type": "composite",
"reason": "Frequently used in WHERE and JOIN conditions",
"impact": "high"
},
{
"table": "products",
"columns": ["category_id"],
"type": "single",
"reason": "Column category_id is frequently queried",
"impact": "medium"
}
]
```
---
### 12. detect_slow_queries
Identify queries that exceed performance thresholds and provide alerts.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database to analyze |
**Response:**
```json
[
{
"dbId": "postgres-main",
"queryId": "a1b2c3",
"sql": "SELECT * FROM large_table WHERE slow_column = ?",
"executionTimeMs": 2500,
"thresholdMs": 1000,
"timestamp": "2024-01-27T10:30:00Z",
"frequency": 5,
"recommendations": [
{
"type": "add_index",
"description": "Add index on slow_column for better performance",
"impact": "high",
"effort": "medium"
}
]
}
]
```
---
### 13. rewrite_query
Suggest optimized versions of SQL queries with performance improvements.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database ID |
| `sql` | string | Yes | SQL query to optimize |
**Response:**
```json
{
"originalQuery": "SELECT * FROM users WHERE active = 1",
"optimizedQuery": "SELECT id, name, email FROM users WHERE active = 1 LIMIT 1000",
"improvements": [
"Removed unnecessary SELECT *",
"Added LIMIT clause to prevent large result sets"
],
"performanceGain": 35,
"confidence": "high"
}
```
---
### 14. profile_query
Profile a specific query's performance with detailed bottleneck analysis.
**Input Parameters:**
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `dbId` | string | Yes | Database ID |
| `sql` | string | Yes | SQL query to profile |
| `params` | array | No | Query parameters |
**Response:**
```json
{
"queryId": "def456",
"sql": "SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id",
"executionTimeMs": 1250,
"rowCount": 5000,
"bottlenecks": [
{
"type": "join",
"severity": "high",
"description": "Nested loop join on large tables",
"estimatedCost": 150
}
],
"recommendations": [
{
"type": "add_index",
"description": "Add index on orders.user_id",
"impact": "high",
"effort": "low"
}
],
"overallScore": 65
}
```
## Resources
The server exposes cached schemas as MCP resources:
- **URI:** `schema://{dbId}`
- **MIME Type:** `application/json`
- **Content:** Complete cached schema metadata
## Schema Introspection
### Automatic Discovery
The server automatically discovers:
1. **Tables and Views**: All user tables and optionally views
2. **Columns**: Name, data type, nullability, defaults, auto-increment
3. **Indexes**: Including primary keys and unique constraints
4. **Foreign Keys**: Explicit relationship metadata
5. **Relationships**: Both explicit and inferred
### Relationship Inference
When foreign keys are not defined, the server infers relationships using heuristics:
- Column names matching `{table}_id` or `{table}Id`
- Data type compatibility with target primary key
- Confidence scoring for inferred relationships
### Caching Strategy
- **Memory + Disk**: Dual-layer caching for performance
- **TTL-based**: Configurable time-to-live
- **Version Tracking**: Content-based versioning (hash)
- **Concurrency Safe**: Prevents duplicate introspection
- **On-Demand Refresh**: Manual or automatic refresh
## Query Tracking
The server maintains per-database query history:
- Timestamp and SQL text
- Execution time and row count
- Referenced tables (best-effort extraction)
- Error tracking
- Aggregate statistics
Use this data to:
- Monitor query performance
- Identify frequently accessed tables
- Detect query patterns
- Debug issues
## Development
```bash
# Install dependencies
npm install
# Run in development mode
npm run dev
# Build
npm run build
# Run tests
npm test
# Run tests with coverage
npm run test:coverage
# Lint
npm run lint
# Format code
npm run format
# Type check
npm run typecheck
```
## Project Structure
```
src/
├── adapters/ # Database adapters
│ ├── base.ts # Base adapter class
│ ├── postgres.ts # PostgreSQL adapter
│ ├── mysql.ts # MySQL adapter
│ ├── sqlite.ts # SQLite adapter
│ ├── mssql.ts # SQL Server adapter
│ ├── oracle.ts # Oracle adapter (stub)
│ └── index.ts # Adapter factory
├── cache.ts # Schema caching
├── config.ts # Configuration loader
├── database-manager.ts # Database orchestration
├── logger.ts # Logging setup
├── mcp-server.ts # MCP server implementation
├── query-tracker.ts # Query history tracking
├── types.ts # TypeScript types
├── utils.ts # Utility functions
└── index.ts # Entry point
```
## Adding New Database Adapters
1. Implement the `DatabaseAdapter` interface in `src/adapters/`
2. Follow the pattern from existing adapters
3. Add to adapter factory in `src/adapters/index.ts`
4. Update type definitions if needed
5. Add tests
Example:
```typescript
import { BaseAdapter } from './base.js';
export class CustomAdapter extends BaseAdapter {
async connect(): Promise<void> { /* ... */ }
async disconnect(): Promise<void> { /* ... */ }
async introspect(): Promise<DatabaseSchema> { /* ... */ }
async query(): Promise<QueryResult> { /* ... */ }
async explain(): Promise<ExplainResult> { /* ... */ }
async testConnection(): Promise<boolean> { /* ... */ }
async getVersion(): Promise<string> { /* ... */ }
}
```
## Troubleshooting
### Connection Issues
- Verify connection strings and credentials
- Check network connectivity and firewall rules
- Enable debug logging: `"logging": { "level": "debug" }`
- Use `health_check` tool to test connectivity
### Cache Issues
- Clear cache: Use `clear_cache` tool
- Check cache directory permissions
- Verify TTL settings
- Review cache status with `cache_status` tool
### Performance
- Adjust connection pool settings
- Use `maxTables` to limit introspection scope
- Set appropriate cache TTL
- Enable read-only mode when possible
### Oracle Setup
The Oracle adapter requires additional setup:
1. Install Oracle Instant Client
2. Set environment variables (`LD_LIBRARY_PATH` or `PATH`)
3. Install `oracledb` package
4. Implement stub methods in `src/adapters/oracle.ts`
## Security Considerations
- Always use read-only mode in production unless write access is required
- Use environment variables for credentials, never hardcode
- Enable secret redaction in logs
- Restrict write operations with `allowedWriteOperations`
- Use connection string encryption where supported
- Regular security audits of configurations
## License
MIT
## Contributing
Contributions welcome! Please:
1. Fork the repository
2. Create a feature branch
3. Add tests for new functionality
4. Ensure all tests pass
5. Submit a pull request
## Support
For issues, questions, or feature requests, please open an issue on GitHub.
This server cannot be deployed
Maintenance
ActivityInactive
ResponsivenessNo issues