# DB MCP Server

A Model Context Protocol (MCP) server for database operations. Execute queries, introspect schemas, and manage data for PostgreSQL and MySQL databases. Works with local databases, SSH tunnels, AWS RDS, RDS Proxy, and any compatible database.

## Features

- **db_list_instances** - List RDS database instances (requires AWS credentials)
- **db_test_connection** - Test database connectivity and get version info
- **db_execute_query** - Execute read-only SQL queries (SELECT, SHOW, DESCRIBE, EXPLAIN)
- **db_execute_write** - Execute write queries (INSERT/UPDATE only, DELETE blocked)
- **db_get_schema** - Get full database schema with tables, views, and columns
- **db_get_tables** - List tables and views with filtering options
- **db_get_columns** - Get column details for a specific table
- **db_sample_data** - Get sample rows from a table

## Installation

### Using npx (Recommended)

No installation required. Add directly to your config:

**Claude Desktop** (`~/Library/Application Support/Claude/claude_desktop_config.json` on macOS):

```json
{
  "mcpServers": {
    "db": {
      "command": "npx",
      "args": ["-y", "github:nikhilchintawar/db-mcp"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_NAME": "mydb",
        "DB_USER": "postgres",
        "DB_PASSWORD": "password",
        "DB_ENGINE": "postgresql"
      }
    }
  }
}
```

**Claude Code** (`~/.claude/settings.json`):

```json
{
  "mcpServers": {
    "db": {
      "command": "npx",
      "args": ["-y", "github:nikhilchintawar/db-mcp"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_NAME": "mydb",
        "DB_USER": "postgres",
        "DB_PASSWORD": "password",
        "DB_ENGINE": "postgresql"
      }
    }
  }
}
```

### Manual Installation

```bash
# Clone the repository
git clone https://github.com/nikhilchintawar/db-mcp.git
cd db-mcp

# Install dependencies
npm install

# Build
npm run build
```

Then add to your config:

```json
{
  "mcpServers": {
    "db": {
      "command": "node",
      "args": ["/absolute/path/to/db-mcp/build/index.js"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_NAME": "mydb",
        "DB_USER": "postgres",
        "DB_PASSWORD": "password",
        "DB_ENGINE": "postgresql"
      }
    }
  }
}
```

## Configuration Examples

### Local Database

```json
{
  "mcpServers": {
    "db": {
      "command": "npx",
      "args": ["-y", "github:nikhilchintawar/db-mcp"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_NAME": "mydb",
        "DB_USER": "postgres",
        "DB_PASSWORD": "password",
        "DB_ENGINE": "postgresql",
        "DB_SSL": "false"
      }
    }
  }
}
```

### SSH Tunnel

First, establish your SSH tunnel:
```bash
ssh -L 5432:rds-endpoint.amazonaws.com:5432 bastion-host
```

Then configure:
```json
{
  "mcpServers": {
    "db": {
      "command": "npx",
      "args": ["-y", "github:nikhilchintawar/db-mcp"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_NAME": "mydb",
        "DB_USER": "admin",
        "DB_PASSWORD": "password",
        "DB_ENGINE": "postgresql"
      }
    }
  }
}
```

### AWS RDS / RDS Proxy

```json
{
  "mcpServers": {
    "db": {
      "command": "npx",
      "args": ["-y", "github:nikhilchintawar/db-mcp"],
      "env": {
        "DB_HOST": "my-proxy.proxy-xxxx.us-east-1.rds.amazonaws.com",
        "DB_PORT": "5432",
        "DB_NAME": "mydb",
        "DB_USER": "admin",
        "DB_PASSWORD": "password",
        "DB_ENGINE": "postgresql"
      }
    }
  }
}
```

### AWS RDS with Temporary Credentials (Read Replica)

```json
{
  "mcpServers": {
    "db": {
      "command": "npx",
      "args": ["-y", "github:nikhilchintawar/db-mcp"],
      "env": {
        "AWS_REGION": "eu-west-1",
        "AWS_ACCESS_KEY_ID": "your-access-key",
        "AWS_SECRET_ACCESS_KEY": "your-secret-key",
        "AWS_SESSION_TOKEN": "your-session-token",
        "DB_HOST": "my-proxy-readonly.proxy-xxxx.eu-west-1.rds.amazonaws.com",
        "DB_PORT": "5432",
        "DB_NAME": "mydb",
        "DB_USER": "admin",
        "DB_PASSWORD": "password",
        "DB_ENGINE": "postgresql",
        "DB_IS_READ_REPLICA": "true",
        "DB_WRITE_MODE": "disabled"
      }
    }
  }
}
```

### AWS Secrets Manager

```json
{
  "mcpServers": {
    "db": {
      "command": "npx",
      "args": ["-y", "github:nikhilchintawar/db-mcp"],
      "env": {
        "AWS_REGION": "us-east-1",
        "AWS_PROFILE": "your-profile",
        "DB_SECRET_ARN": "arn:aws:secretsmanager:us-east-1:123456789012:secret:mydb-AbCdEf"
      }
    }
  }
}
```

## Configuration

### Config File Locations

- **Claude Desktop (macOS):** `~/Library/Application Support/Claude/claude_desktop_config.json`
- **Claude Desktop (Windows):** `%APPDATA%\Claude\claude_desktop_config.json`
- **Claude Code:** `~/.claude/settings.json`

### Environment Variables

| Variable | Required | Description |
|----------|----------|-------------|
| `DB_HOST` | Yes* | Database hostname |
| `DB_PORT` | No | Database port (default: 5432 for PostgreSQL, 3306 for MySQL) |
| `DB_NAME` | Yes* | Database name |
| `DB_USER` | Yes* | Database username |
| `DB_PASSWORD` | Yes* | Database password |
| `DB_ENGINE` | Yes* | `postgresql` or `mysql` |
| `DB_SSL` | No | Enable SSL (default: true) |
| `DB_SECRET_ARN` | No | AWS Secrets Manager ARN (alternative to direct credentials) |
| `DB_WRITE_MODE` | No | `disabled` (default), `enabled`, or `auto` |
| `DB_IS_READ_REPLICA` | No | Set to `true` to block writes |
| `DB_DEFAULT_LIMIT` | No | Default query row limit (default: 1000) |
| `DB_DEFAULT_TIMEOUT` | No | Default query timeout in ms (default: 30000) |

*Required unless using `DB_SECRET_ARN`

### AWS Configuration (Optional)

AWS credentials are only needed if using:
- `DB_SECRET_ARN` (Secrets Manager)
- `db_list_instances` tool (RDS API)

| Variable | Description |
|----------|-------------|
| `AWS_REGION` | AWS region (required for AWS features) |
| `AWS_PROFILE` | AWS credentials profile |
| `AWS_ACCESS_KEY_ID` | AWS access key |
| `AWS_SECRET_ACCESS_KEY` | AWS secret key |
| `AWS_SESSION_TOKEN` | Session token (for temporary credentials) |

## Usage Examples

Once configured, you can use natural language to interact with your database:

- "Show me the database schema"
- "List all tables"
- "Get sample data from the users table"
- "Run a query to find all orders from last week"
- "Insert a new record into the settings table"
- "Show me the columns in the products table"

## Available Tools

### `db_list_instances`

List RDS database instances (requires AWS credentials).

| Parameter | Required | Description |
|-----------|----------|-------------|
| `identifier` | No | Filter by instance identifier |
| `engine` | No | Filter by engine (postgres, mysql) |

### `db_test_connection`

Test database connection and return status, latency, and version.

| Parameter | Required | Description |
|-----------|----------|-------------|
| (none) | - | Uses configured connection |

### `db_execute_query`

Execute read-only SQL queries.

| Parameter | Required | Description |
|-----------|----------|-------------|
| `sql` | Yes | SELECT, SHOW, DESCRIBE, or EXPLAIN query |
| `limit` | No | Max rows (default: 1000, max: 10000) |
| `timeout` | No | Query timeout in ms (default: 30000) |

### `db_execute_write`

Execute write operations (INSERT/UPDATE only).

| Parameter | Required | Description |
|-----------|----------|-------------|
| `sql` | Yes | INSERT or UPDATE query |
| `timeout` | No | Query timeout in ms (default: 30000) |

**Note**: DELETE, DROP, TRUNCATE, and ALTER are blocked. Fails gracefully on read replicas.

### `db_get_schema`

Get full database schema including tables, views, and columns.

| Parameter | Required | Description |
|-----------|----------|-------------|
| `schemaName` | No | Filter by schema (e.g., "public") |

### `db_get_tables`

List tables and views.

| Parameter | Required | Description |
|-----------|----------|-------------|
| `schemaName` | No | Filter by schema |
| `type` | No | `table`, `view`, or `all` (default) |

### `db_get_columns`

Get column details for a specific table.

| Parameter | Required | Description |
|-----------|----------|-------------|
| `table` | Yes | Table name |
| `schemaName` | No | Schema name |

### `db_sample_data`

Get sample rows from a table.

| Parameter | Required | Description |
|-----------|----------|-------------|
| `table` | Yes | Table name |
| `schemaName` | No | Schema name |
| `limit` | No | Number of rows (default: 10, max: 100) |

## Write Protection

The server includes multiple safety mechanisms:

| Mode | Behavior |
|------|----------|
| `disabled` (default) | Blocks all write operations |
| `enabled` | Allows INSERT/UPDATE on any instance |
| `auto` | Allows INSERT/UPDATE except on read replicas |

**Always blocked**: DELETE, DROP, TRUNCATE, ALTER, CREATE, GRANT, REVOKE

Read replica detection works via:
1. `DB_IS_READ_REPLICA=true` environment variable
2. Automatic detection via RDS API (requires `DB_RDS_INSTANCE_IDENTIFIER` and AWS credentials)

## Development

```bash
# Watch mode for development
npm run dev

# Build for production
npm run build

# Test with MCP Inspector
npx @modelcontextprotocol/inspector node build/index.js
```

## License

MIT