# SQLx MCP Server

A Model Context Protocol (MCP) server implementation in Rust that provides comprehensive database management tools using SQLx.

[![Rust](https://img.shields.io/badge/rust-1.70+-orange.svg)](https://www.rust-lang.org)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

## 🚀 Features

This server provides 6 powerful MCP tools for database management:

1. **🔍 get_database_info** - Get basic database information
2. **📋 list_tables** - List all tables with metadata (comments, row counts, etc.)
3. **🏗️ get_table_structure** - Get detailed table structure information  
4. **📜 get_table_ddl** - Export table DDL (CREATE TABLE statements)
5. **👁️ execute_readonly_query** - Execute read-only SQL queries safely
6. **✏️ execute_write_query** - Execute write SQL operations

### 🎯 Smart Configuration Management

- **Auto-Configuration Detection**: Server automatically detects and reports database configuration at startup
- **Client Notification**: Clients receive configuration status via MCP protocol initialization
- **Flexible URL Management**: Optional database URL parameters when server is pre-configured
- **Secure Display**: Database credentials are automatically masked for security

## 🗄️ Supported Databases

- **PostgreSQL** - Full support with native features
- **MySQL** - Complete functionality including engine-specific features  
- **SQLite** - Comprehensive support for embedded database operations

## 🔧 Installation

### Option 1: NPM (Recommended)

Install globally via npm:

```bash
npm install -g @sqlx-mcp/sqlx-mcp
```

Or use directly with npx (no installation needed):

```bash
npx @sqlx-mcp/sqlx-mcp --database-url "postgresql://user:pass@localhost/mydb"
```

### Option 2: Build from Source

#### Prerequisites

- Rust 1.70 or later
- Git

#### Build Steps

```bash
git clone https://github.com/lihongjie0209/sqlx-mcp.git
cd sqlx-mcp
cargo build --release
```

## 🚀 Quick Start

### 1. Run as Standalone Server

#### Using NPM/npx (Recommended):

```bash
# Install globally and run
npm install -g @sqlx-mcp/sqlx-mcp
sqlx-mcp --database-url "postgresql://user:pass@localhost/mydb"

# Or run directly with npx (no installation needed)
npx @sqlx-mcp/sqlx-mcp --database-url "postgresql://user:pass@localhost/mydb"

# With environment variable
export DATABASE_URL="postgresql://user:pass@localhost/mydb"
npx @sqlx-mcp/sqlx-mcp
```

#### Using Built Binary:

```bash
# With environment variable
export DATABASE_URL="postgresql://user:pass@localhost/mydb"
./target/release/sqlx-mcp

# With command line argument
./target/release/sqlx-mcp --database-url "postgresql://user:pass@localhost/mydb"
```

### 2. Integrate with Claude Desktop

Add to your Claude Desktop configuration. The server will automatically notify Claude about the current database configuration status.

#### Using NPM Package (Recommended):

**With Environment Variable (Recommended):**
```json
{
  "mcpServers": {
    "sqlx-mcp": {
      "command": "npx",
      "args": ["@sqlx-mcp/sqlx-mcp"],
      "env": {
        "DATABASE_URL": "postgresql://user:pass@localhost/mydb"
      }
    }
  }
}
```

**With Command Line Argument:**
```json
{
  "mcpServers": {
    "sqlx-mcp": {
      "command": "npx",
      "args": ["@sqlx-mcp/sqlx-mcp", "--database-url", "postgresql://user:pass@localhost/mydb"]
    }
  }
}
```

**Without Pre-configuration (require database_url in each tool call):**
```json
{
  "mcpServers": {
    "sqlx-mcp": {
      "command": "npx",
      "args": ["@sqlx-mcp/sqlx-mcp"]
    }
  }
}
```

#### Using Built Binary:

**Windows (`%APPDATA%\Claude\claude_desktop_config.json`):**
```json
{
  "mcpServers": {
    "sqlx-mcp": {
      "command": "D:\\path\\to\\sqlx-mcp\\target\\release\\sqlx-mcp.exe",
      "args": ["--database-url", "postgresql://user:pass@localhost/mydb"]
    }
  }
}
```

**macOS/Linux:**
```json
{
  "mcpServers": {
    "sqlx-mcp": {
      "command": "/path/to/sqlx-mcp/target/release/sqlx-mcp",
      "args": ["--database-url", "postgresql://user:pass@localhost/mydb"]
    }
  }
}
```

## 🔗 Database Configuration

The server provides intelligent database configuration management with automatic detection and client notification.

### Configuration Priority

The server resolves database connections with the following priority:

1. **Tool Parameter** (highest) - `database_url` parameter in individual tool calls
2. **Command Line** - `--database-url` argument at server startup
3. **Environment Variable** (lowest) - `DATABASE_URL` environment variable

### Auto-Detection & Client Notification

When clients connect to the MCP server, they automatically receive configuration information:

- **Configuration Status**: Whether a database is pre-configured
- **Connection Source**: How the database was configured (command line, environment, or none)
- **Masked URL**: Database connection string with credentials safely hidden
- **Usage Guidance**: Whether `database_url` parameter is required in tool calls

#### Example Client Notifications:

**No Configuration:**
```
Current database configuration: No database configured. Please provide database_url parameter in tool calls or set DATABASE_URL environment variable.
```

**Environment Variable Configuration:**
```
Current database configuration: Database configured via environment variable: postgresql://user:***@localhost:5432/mydb

If a database is already configured, you can omit the database_url parameter in tool calls.
```

**Command Line Configuration:**
```
Current database configuration: Database configured via command line: mysql://root:***@localhost:3306/testdb

If a database is already configured, you can omit the database_url parameter in tool calls.
```

### Flexible Usage Patterns

#### Pre-configured Server (Recommended)
```bash
# Set up database via environment variable
export DATABASE_URL="postgresql://user:pass@localhost/mydb"
npx @sqlx-mcp/sqlx-mcp

# Or via command line
npx @sqlx-mcp/sqlx-mcp --database-url "postgresql://user:pass@localhost/mydb"
```

Then use tools without database_url parameter:
```json
{
  "name": "list_tables",
  "arguments": {}
}
```

#### Per-Tool Database URLs
```json
{
  "name": "list_tables", 
  "arguments": {
    "database_url": "postgresql://user:pass@different-host/other-db"
  }
}
```

This allows flexible connection management for multiple databases.

## 🛠️ Tool Usage Examples

### Get Database Information
```json
{
  "name": "get_database_info",
  "arguments": {
    "database_url": "postgresql://user:pass@localhost/mydb"  // Optional if server is pre-configured
  }
}
```

### List Tables with Metadata
```json
{
  "name": "list_tables",
  "arguments": {
    "database_url": "postgresql://user:pass@localhost/mydb"  // Optional if server is pre-configured
  }
}
```

### Export Table DDL
```json
{
  "name": "get_table_ddl",
  "arguments": {
    "database_url": "postgresql://user:pass@localhost/mydb",  // Optional if server is pre-configured
    "table_name": "users"
  }
}
```

### Execute Safe Read-Only Queries
Supports SELECT, WITH (CTE), SHOW, DESCRIBE, EXPLAIN:
```json
{
  "name": "execute_readonly_query",
  "arguments": {
    "database_url": "postgresql://user:pass@localhost/mydb",  // Optional if server is pre-configured
    "query": "WITH recent_users AS (SELECT * FROM users WHERE created_at > '2024-01-01') SELECT count(*) FROM recent_users"
  }
}
```

**Note**: When the server is pre-configured with a database (via command line or environment variable), the `database_url` parameter becomes optional in all tool calls. The server will notify clients about the current configuration status during MCP initialization.

## 🔒 Security Features

- **Query Validation**: Strict read-only query enforcement for safe operations
- **SQL Injection Protection**: Parameterized queries where possible
- **Connection Security**: Supports SSL/TLS encrypted connections
- **Access Control**: Respects database user permissions
- **Credential Protection**: Database passwords automatically masked in logs and client notifications
- **Secure Configuration Display**: Connection strings shown with sensitive information hidden

## 🏗️ Architecture

Built with modern Rust ecosystem:

- **rmcp** - Rust MCP SDK for protocol compliance
- **SQLx** - Async SQL toolkit with compile-time checked queries
- **Tokio** - Async runtime for high performance
- **Serde** - Serialization framework for JSON handling
- **Tracing** - Structured logging and debugging

## 📚 Documentation

- [Detailed Usage Guide](README_USAGE.md)
- [Database Configuration Guide](DATABASE_CONFIG_VERIFICATION.md)
- [DDL Export Feature](DDL_EXPORT_FEATURE.md)
- [List Tables Feature](LIST_TABLES_FEATURE.md)
- [Improvements Summary](IMPROVEMENTS_SUMMARY.md)
- [Deployment Guide](DEPLOYMENT_SUMMARY.md)
- [PowerShell Environment Setup](scripts/POWERSHELL_ENV_SETUP.md)

## 🔧 Development & Release

This project includes an advanced automated release system powered by AI:

### Environment Setup

For Windows PowerShell users who want to use the automated release features:

```powershell
# Quick setup using .env file (recommended)
Copy-Item ".env.template" ".env"
notepad .env  # Add your OPENROUTER_API_KEY

# Or set environment variable for current session
$env:OPENROUTER_API_KEY = "your-api-key-here"
```

For detailed PowerShell environment setup, see [PowerShell Environment Setup Guide](scripts/POWERSHELL_ENV_SETUP.md).

### Release Management

The project includes a comprehensive release script with AI-powered commit message generation:

```bash
# Create automated release with AI-generated commit messages
npm run release

# The script will:
# - Bump version numbers
# - Generate intelligent commit messages using AI
# - Update package files
# - Create git commits and tags
# - Push to repository
```

**Features:**
- 🤖 AI-powered commit message generation using OpenRouter
- 📦 Automatic version management 
- 🔄 Multi-file synchronization (Cargo.toml ↔ package.json)
- 🚀 Git automation (commit, tag, push)
- 🖥️ Cross-platform support (Windows, macOS, Linux)

## 🤝 Contributing

1. Fork the repository
2. Create a feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add some amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request

## 📄 License

This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

## 🙏 Acknowledgments

- [Model Context Protocol](https://modelcontextprotocol.io/) for the protocol specification
- [SQLx](https://github.com/launchbadge/sqlx) for the excellent SQL toolkit
- [rmcp](https://github.com/modelcontextprotocol/rust-mcp-sdk) for the Rust MCP SDK

## 📞 Support

- Create an [Issue](https://github.com/lihongjie0209/sqlx-mcp/issues) for bug reports
- Start a [Discussion](https://github.com/lihongjie0209/sqlx-mcp/discussions) for questions
- Check the [Documentation](README_USAGE.md) for detailed guides

---

Made with ❤️ and 🦀 (Rust)