# mcp-database
A Node.js MCP (Model Context Protocol) server that provides database operations for AI assistants and agents. Supports 40+ databases through [genai-toolbox](https://github.com/googleapis/genai-toolbox).
## Features
- ✅ **Easy Installation**: Install via npm/npx - no manual binary downloads
- ✅ **Universal Database Support**: PostgreSQL, MySQL, MongoDB, Redis, SQLite, and 35+ more
- ✅ **Simple Configuration**: YAML config or environment variables
- ✅ **MCP Standard**: Full MCP protocol implementation
- ✅ **Production Ready**: Connection pooling, auth, and observability built-in
## Installation
Install from npm and pick the bundle that fits your environment:
- Core package (no bundled binaries, ~50KB): `npm install -g @adversity/mcp-database`
- Platform-specific (includes binary for one OS/CPU, ~15MB):
- macOS ARM64: `npm install -g @adversity/mcp-database-darwin-arm64`
- macOS Intel: `npm install -g @adversity/mcp-database-darwin-x64`
- Linux x64: `npm install -g @adversity/mcp-database-linux-x64`
- Windows x64: `npm install -g @adversity/mcp-database-win32-x64`
- No install: `npx @adversity/mcp-database --help`
## Quick Start
### Using Prebuilt Configurations
The easiest way to get started is with prebuilt database configurations:
```bash
# PostgreSQL
DATABASE_HOST=localhost \
DATABASE_NAME=mydb \
DATABASE_USER=user \
DATABASE_PASSWORD=password \
npx @adversity/mcp-database --prebuilt postgres
# MySQL
DATABASE_HOST=localhost \
DATABASE_NAME=mydb \
DATABASE_USER=root \
DATABASE_PASSWORD=password \
npx @adversity/mcp-database --prebuilt mysql
# SQLite (no credentials needed)
DATABASE_NAME=./my-database.db \
npx @adversity/mcp-database --prebuilt sqlite
# MongoDB
DATABASE_HOST=localhost \
DATABASE_NAME=mydb \
DATABASE_USER=user \
DATABASE_PASSWORD=password \
npx @adversity/mcp-database --prebuilt mongodb
```
### Using Custom Configuration
Create a `tools.yaml` file:
```yaml
sources:
my-postgres:
kind: postgres
host: ${POSTGRES_HOST:localhost}
port: ${POSTGRES_PORT:5432}
database: ${POSTGRES_DATABASE:mydb}
user: ${POSTGRES_USER:postgres}
password: ${POSTGRES_PASSWORD}
tools:
get_user_by_id:
kind: postgres-sql
source: my-postgres
description: Get user by ID
parameters:
- name: user_id
type: number
description: The user ID
statement: SELECT * FROM users WHERE id = $1;
```
Run with custom config:
```bash
mcp-database --config tools.yaml
```
### SQLite Built-in Introspection Tools (Recommended for AI)
Designed for AI to explore unknown databases without schema knowledge:
```bash
DATABASE_NAME=./your-database.db \
npx @adversity/mcp-database --config prebuilt/sqlite-introspection.yaml
```
**15 Built-in Introspection Tools:**
| Tool Set | Tool Name | Function |
|----------|-----------|----------|
| **Basic Exploration** | `sqlite_list_tables` | List all tables and views |
| | `sqlite_describe_table` | View table structure (columns, types, keys) |
| | `sqlite_list_columns` | List all column information |
| | `sqlite_preview_table` | Preview first N rows of data |
| | `sqlite_database_summary` | Complete database summary (tables, indexes, views) |
| **Advanced Analysis** | `sqlite_list_indexes` | List all indexes |
| | `sqlite_list_table_indexes` | View indexes for a specific table |
| | `sqlite_describe_index` | View columns in an index |
| | `sqlite_list_foreign_keys` | View foreign key relationships |
| | `sqlite_get_table_schema` | Get table's CREATE statement |
| **Statistics** | `sqlite_count_rows` | Count total rows in a table |
| | `sqlite_table_stats` | Get statistics for all tables |
| | `sqlite_database_info` | Database basic information |
| | `sqlite_get_schema_version` | Get schema version number |
**Typical AI Workflow:**
```
1. sqlite_list_tables → Discover users, orders, products tables
2. sqlite_describe_table("users") → Understand column structure
3. sqlite_preview_table("users", 5) → See actual data format
4. sqlite_list_foreign_keys("orders") → Understand table relationships
5. Now AI can confidently construct complex queries!
```
**MCP Client Configuration Example:**
```json
{
"mcpServers": {
"database": {
"command": "npx",
"args": ["@adversity/mcp-database", "--config", "prebuilt/sqlite-introspection.yaml"],
"env": {
"DATABASE_NAME": "./your-database.db"
}
}
}
}
```
### SQLite Custom YAML Example
1. Copy the example: `cp tools.example.yaml tools.yaml`
2. Verify sample database exists: `ls sample.sqlite`
3. Run:
```bash
DATABASE_NAME=$(pwd)/sample.sqlite \
npx @adversity/mcp-database --config tools.yaml --verbose
```
4. The MCP client can access `sqlite_crud` toolset from `tools.example.yaml`:
- `sqlite_list_recent_users`: List recent 20 user records
- `sqlite_get_user_by_id`: Query by ID
- `sqlite_find_user_by_email`: Search by email
- `sqlite_create_user` / `sqlite_update_user_email` / `sqlite_delete_user`: Create/Update/Delete
Example YAML snippet (from `tools.example.yaml`):
```yaml
tools:
sqlite_list_recent_users:
kind: sqlite-sql
source: my-sqlite
description: List recent 20 users ordered by creation time
statement: |
SELECT id, name, email, created_at
FROM users
ORDER BY datetime(created_at) DESC
LIMIT 20;
sqlite_create_user:
kind: sqlite-sql
source: my-sqlite
description: Create new user and return result
parameters:
- { name: name, type: string }
- { name: email, type: string }
statement: |
INSERT INTO users (name, email, created_at)
VALUES (?1, ?2, CURRENT_TIMESTAMP)
RETURNING id, name, email, created_at;
toolsets:
sqlite_crud:
- sqlite_list_recent_users
- sqlite_get_user_by_id
- sqlite_find_user_by_email
- sqlite_create_user
- sqlite_update_user_email
- sqlite_delete_user
```
### Unified Environment Variables and CLI Override
All prebuilt databases use simple, unified environment variables:
- `DATABASE_HOST` - Database host (default: localhost)
- `DATABASE_PORT` - Database port (default: depends on database type)
- `DATABASE_NAME` - Database name
- `DATABASE_USER` - Database user
- `DATABASE_PASSWORD` - Database password
You can also override via CLI:
```bash
mcp-database --prebuilt postgres \
--db-host prod-db.internal \
--db-port 6543 \
--db-name inventory \
--db-user readonly
```
### 自定义 toolbox 端口
默认使用 STDIO 模式，不占用 TCP 端口；当需要切换到 HTTP（例如调试或远程访问）时，可指定传输方式与端口：
```bash
npx @adversity/mcp-database --prebuilt sqlite \
--transport http \
--toolbox-host 0.0.0.0 \
--toolbox-port 5900
```
对应环境变量：
| 变量 | 说明 |
| --- | --- |
| `MCP_TOOLBOX_TRANSPORT` | `stdio`（默认）或 `http` |
| `MCP_TOOLBOX_HOST` | HTTP 监听地址，默认 `127.0.0.1` |
| `MCP_TOOLBOX_PORT` | HTTP 端口，默认 `5000` |
CLI 也保留 `--stdio`（布尔）用于兼容旧脚本；推荐使用 `--transport` 来切换模式。
## MCP Integration
### Claude Code / Claude Desktop
Add to your `.mcp.json` or `claude_desktop_config.json`:
```json
{
"mcpServers": {
"database": {
"command": "npx",
"args": ["@adversity/mcp-database", "--prebuilt", "postgres"],
"env": {
"DATABASE_HOST": "localhost",
"DATABASE_PORT": "5432",
"DATABASE_NAME": "mydb",
"DATABASE_USER": "postgres",
"DATABASE_PASSWORD": "your-password"
}
}
}
}
```
### Cursor IDE
Create `.cursor/mcp.json`:
```json
{
"mcpServers": {
"database": {
"command": "npx",
"args": ["@adversity/mcp-database", "--prebuilt", "mysql"],
"env": {
"DATABASE_HOST": "localhost",
"DATABASE_NAME": "mydb",
"DATABASE_USER": "root",
"DATABASE_PASSWORD": "your-password"
}
}
}
}
```
### VS Code (Copilot)
Create `.vscode/mcp.json`:
```json
{
"servers": {
"database": {
"command": "npx",
"args": ["@adversity/mcp-database", "--prebuilt", "postgres"],
"env": {
"DATABASE_HOST": "localhost",
"DATABASE_NAME": "mydb"
}
}
}
}
```
### Windsurf
Configure via the Cascade assistant's MCP settings with the same JSON format as Cursor.
## Supported Databases
### Relational Databases
- PostgreSQL
- MySQL
- SQL Server
- SQLite
- Oracle
- Cloud SQL (Postgres, MySQL, SQL Server)
- AlloyDB for PostgreSQL
- Spanner
- TiDB
- OceanBase
- YugabyteDB
### NoSQL Databases
- MongoDB
- Redis
- Valkey
- Firestore
- Bigtable
- Cassandra
- Couchbase
- Neo4j
- Dgraph
### Analytics & Warehouses
- BigQuery
- ClickHouse
- Trino
- Serverless Spark
- Elasticsearch
- SingleStore
### Cloud Services
- Looker
- MindsDB
- Dataplex
- Cloud Healthcare API
- Cloud Monitoring
See [genai-toolbox documentation](https://googleapis.github.io/genai-toolbox/resources/sources/) for complete list and configuration details.
## Available Tools
When connected via MCP, the following tools are available to AI assistants:
### `list_tables`
List all tables in the database with their descriptions.
**Example:**
```
Show me all tables in the database
```
### `execute_sql`
Execute any SQL statement on the database.
**Parameters:**
- `sql` (string, required): The SQL statement to execute
**Example:**
```
Get all users from the users table where age > 25
```
## Environment Variables
All database types use the same unified environment variables:
- `DATABASE_HOST` - Database host (default: localhost)
- `DATABASE_PORT` - Database port (default: varies by database type)
- PostgreSQL: 5432
- MySQL: 3306
- MongoDB: 27017
- Redis: 6379
- MSSQL: 1433
- `DATABASE_NAME` - Database name or file path
- `DATABASE_USER` - Database user
- `DATABASE_PASSWORD` - Database password
For cloud services (Cloud SQL, AlloyDB, BigQuery, Spanner, Firestore), additional GCP-specific variables are required:
- `GCP_PROJECT` - GCP project ID
- `GCP_REGION` - GCP region (default: us-central1)
- `CLOUD_SQL_INSTANCE` / `ALLOYDB_CLUSTER` / etc. - Service-specific identifiers
## CLI Reference
```bash
mcp-database [OPTIONS]
OPTIONS:
-c, --config <path> Path to tools.yaml configuration file
-p, --prebuilt <type> Use prebuilt config for database type
--db-host <value> Override MCP_DATABASE_HOST（所有预置类型通用）
--db-port <value> Override MCP_DATABASE_PORT
--db-name <value> Override MCP_DATABASE_NAME（对应 database/schema）
--db-user <value> Override MCP_DATABASE_USER
--db-password <value> Override MCP_DATABASE_PASSWORD
--transport <mode> Transport between wrapper 与 toolbox：`stdio`(默认) / `http`
--toolbox-host <value> 当 transport=http 时的监听地址（默认 127.0.0.1）
--toolbox-port <value> 当 transport=http 时的端口（默认 5000）
--stdio Use stdio transport (default: true)
-v, --version Print version
-h, --help Print help
--verbose Enable verbose logging
PREBUILT TYPES:
postgres, mysql, sqlite, mongodb, redis, mssql,
cloud-sql-postgres, cloud-sql-mysql, alloydb-pg,
bigquery, spanner, firestore
```
## Programmatic Usage
You can also use mcp-database programmatically:
```typescript
import { startServer, generatePrebuiltConfig } from '@adversity/mcp-database';
const config = generatePrebuiltConfig('postgres');
const server = await startServer({
binaryPath: '/path/to/toolbox',
config,
verbose: true,
});
// Server is now running
```
## Testing
The project includes comprehensive test coverage, especially for SQLite features:
```bash
# Run all tests
npm test
# Run only SQLite tests
npm test -- sqlite
# Run with coverage
npm run test:coverage
```
**SQLite Test Coverage:**
- ✅ 63 test cases, 100% passing
- ✅ Configuration loading and environment variable handling
- ✅ 15 built-in introspection tools validation
- ✅ CRUD operations and parameterized queries
- ✅ Toolset integration tests
## Development
```bash
# Install dependencies
npm install
# Build
npm run build
# Watch mode
npm run dev
# Run tests
npm test
# Lint
npm run lint
# Format
npm run format
```
## How It Works
This package wraps the [genai-toolbox](https://github.com/googleapis/genai-toolbox) binary and exposes it as an MCP server:
1. **Binary Management**: Automatically downloads the correct genai-toolbox binary for your platform
2. **Configuration**: Generates or loads YAML configuration with environment variable substitution
3. **MCP Protocol**: Implements MCP server protocol over stdio
4. **Tool Execution**: Proxies tool calls to genai-toolbox subprocess
## Requirements
- Node.js 18 or higher
- Internet connection (for initial binary download)
- Database credentials for your target database
## License
MIT
## Related Projects
- [genai-toolbox](https://github.com/googleapis/genai-toolbox) - The underlying database connectivity engine
- [Model Context Protocol](https://modelcontextprotocol.io/) - The protocol specification
- [@modelcontextprotocol/sdk](https://github.com/modelcontextprotocol/sdk) - MCP SDK
## Contributing
Contributions are welcome! Please open an issue or pull request.
## Support
- GitHub Issues: [Report bugs or request features](https://github.com/yourusername/mcp-database/issues)
- Documentation: [genai-toolbox docs](https://googleapis.github.io/genai-toolbox/)
