# MCP SQL Server
A Model Context Protocol (MCP) server for querying PostgreSQL and MySQL databases.
## Quick Start
**Recommended:** Use npx to run without installation:
```bash
npx postgres-mysql-mcp-server
```
For MCP client configuration (Cursor, Windsurf, etc.), use:
```json
{
"mcpServers": {
"sql": {
"command": "npx",
"args": ["-y", "postgres-mysql-mcp-server"],
"env": {
"DB_TYPE": "postgresql",
"DB_HOST": "localhost",
"DB_PORT": "5432",
"DB_DATABASE": "mydb",
"DB_USER": "postgres",
"DB_PASSWORD": "password"
}
}
}
}
```
## What is MCP and Why Use It?
**Model Context Protocol (MCP)** is a standardized protocol that enables AI assistants in code editors like [Cursor](https://cursor.sh/), [Windsurf](https://codeium.com/windsurf), and other AI-powered development tools to securely interact with external systems and data sources.
This MCP server bridges the gap between your AI coding assistant and your databases, allowing the AI to:
- **Understand your database schema** - The AI can explore tables, columns, and relationships
- **Write accurate SQL queries** - Generate queries based on your actual database structure
- **Debug database issues** - Query data to understand problems and verify fixes
- **Generate database-aware code** - Create application code that matches your database schema
- **Answer questions about your data** - Query the database to provide accurate information
### Perfect for AI-Powered Editors
When integrated with AI editors like **Cursor** or **Windsurf**, this MCP server transforms your AI assistant into a database-aware coding companion:
**Example Use Cases:**
1. **Schema-Aware Code Generation**
- *You:* "Create a user registration API endpoint"
- *AI:* Automatically queries your database schema, understands the `users` table structure, and generates code that matches your exact column names and types
2. **Intelligent Query Writing**
- *You:* "Show me all active users from the last 30 days"
- *AI:* Connects to your database, checks the schema, and writes a correct SQL query using your actual table and column names
3. **Database Debugging**
- *You:* "Why is my user login failing?"
- *AI:* Queries your database to check user records, verify table structures, and identify potential issues
4. **Data-Driven Development**
- *You:* "Create a dashboard showing user statistics"
- *AI:* Explores your database schema, understands relationships, and generates accurate queries and code
5. **Migration and Refactoring**
- *You:* "Refactor this code to use the new database schema"
- *AI:* Compares your code with the actual database schema and suggests accurate changes
### How It Works
1. **Configure** the MCP server in your AI editor (Cursor, Windsurf, etc.)
2. **Connect** to your PostgreSQL or MySQL database
3. **Ask** your AI assistant questions or request code generation
4. **AI uses** the MCP server to query your database schema and data
5. **Get** accurate, database-aware responses and code
The AI assistant can now "see" your database structure and data, making it much more helpful and accurate in generating database-related code.
## Features
- Connect to PostgreSQL and MySQL databases
- Execute SQL queries
- List database tables
- Describe table schemas
- Parameterized query support
- Connection pooling for better performance
- Secure credential management via environment variables
- Auto-connect on startup when environment variables are set
## Installation
### Option 1: Use with npx (Recommended - No installation required)
**Recommended:** Run the server directly with npx without any installation. This is the simplest and most convenient method:
```bash
npx postgres-mysql-mcp-server
```
The `-y` flag is automatically handled by npx, so it will download and run the latest version without prompts.
### Option 2: Install via npm
If you prefer to install the package:
**Global installation:**
```bash
npm install -g postgres-mysql-mcp-server
```
**Local installation in your project:**
```bash
npm install postgres-mysql-mcp-server
```
### Option 3: Development Installation
For local development or contributing:
```bash
git clone https://github.com/TranChiHuu/postgres-mysql-mcp-server.git
cd postgres-mysql-mcp-server
npm install
```
## Usage
### Running the Server
The server runs on stdio and communicates via the MCP protocol.
**Recommended: Using npx (no installation required)**
```bash
npx postgres-mysql-mcp-server
```
This is the recommended way to run the server. npx will automatically download and run the latest version.
**Alternative: Using globally installed package**
```bash
postgres-mysql-mcp-server
```
**For local development:**
```bash
npm start
```
### Available Tools
#### 1. `connect_database`
Connect to a PostgreSQL or MySQL database. Parameters can be provided directly, loaded from environment variables, or a combination of both. If environment variables are set, the server will auto-connect on startup.
**Parameters (all optional if using environment variables):**
- `type` (string, optional): Database type - "postgresql" or "mysql"
- `host` (string, optional): Database host
- `port` (number, optional): Database port
- `database` (string, optional): Database name
- `user` (string, optional): Database user
- `password` (string, optional): Database password
- `ssl` (boolean, optional): Use SSL connection (default: false)
**Examples:**
Using parameters:
```json
{
"type": "postgresql",
"host": "localhost",
"port": 5432,
"database": "mydb",
"user": "postgres",
"password": "password"
}
```
Using environment variables (call without parameters):
```json
{}
```
Mixing parameters with environment variables:
```json
{
"type": "postgresql",
"host": "custom-host"
}
```
#### 2. `execute_query`
Execute a SQL query on the connected database.
**Parameters:**
- `query` (string, required): SQL query to execute
- `params` (array, optional): Query parameters for parameterized queries
**Example:**
```json
{
"query": "SELECT * FROM users WHERE id = $1",
"params": [123]
}
```
#### 3. `list_tables`
List all tables in the connected database.
**Parameters:** None
#### 4. `describe_table`
Get schema information for a specific table.
**Parameters:**
- `tableName` (string, required): Name of the table to describe
**Example:**
```json
{
"tableName": "users"
}
```
#### 5. `disconnect_database`
Disconnect from the current database.
**Parameters:** None
## Configuration
### Environment Variables
You can configure database connection using environment variables. Create a `.env` file in the project root or set environment variables:
#### Option 1: Generic Environment Variables (works for both PostgreSQL and MySQL)
```bash
DB_TYPE=postgresql # or "mysql"
DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=mydb
DB_USER=postgres
DB_PASSWORD=password
DB_SSL=false # optional, set to "true" for SSL
```
#### Option 2: PostgreSQL-Specific Environment Variables
```bash
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=mydb
POSTGRES_USER=postgres
POSTGRES_PASSWORD=password
POSTGRES_SSL=false # optional
```
#### Option 3: MySQL-Specific Environment Variables
```bash
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_DATABASE=mydb
MYSQL_USER=root
MYSQL_PASSWORD=password
MYSQL_SSL=false # optional
```
**Note:** If environment variables are set, the server will automatically connect on startup. You can also call `connect_database` without parameters to use environment variables, or provide partial parameters that will be merged with environment variables.
### MCP Client Configuration
This MCP server integrates seamlessly with AI-powered code editors. Add it to your MCP client configuration to enable database-aware AI assistance.
#### Supported Editors
- **[Cursor](https://cursor.sh/)** - AI-powered code editor
- **[Windsurf](https://codeium.com/windsurf)** - AI-first IDE
- Any editor that supports the Model Context Protocol
#### Configuration Steps
**For Cursor:**
1. Open Cursor Settings
2. Navigate to Features → Model Context Protocol
3. Add the server configuration below
**For Windsurf:**
1. Open Settings
2. Navigate to MCP Servers
3. Add the server configuration below
**For other MCP-compatible editors:**
Add the configuration to your MCP settings file (typically `~/.config/mcp/settings.json` or editor-specific location)
#### Configuration Options
#### Option 1: Using npx (Recommended - No installation required)
**This is the recommended configuration.** npx automatically downloads and runs the latest version without requiring any installation:
```json
{
"mcpServers": {
"sql": {
"command": "npx",
"args": ["-y", "postgres-mysql-mcp-server"],
"env": {
"DB_TYPE": "postgresql",
"DB_HOST": "localhost",
"DB_PORT": "5432",
"DB_DATABASE": "mydb",
"DB_USER": "postgres",
"DB_PASSWORD": "password"
}
}
}
}
```
**Benefits of using npx:**
- ✅ No installation required
- ✅ Always uses the latest version
- ✅ No manual updates needed
- ✅ Works across different projects without conflicts
- ✅ The `-y` flag automatically answers "yes" to install prompts
#### Option 2: Using globally installed package
If you've installed the package globally (`npm install -g postgres-mysql-mcp-server`):
```json
{
"mcpServers": {
"sql": {
"command": "postgres-mysql-mcp-server",
"env": {
"DB_TYPE": "postgresql",
"DB_HOST": "localhost",
"DB_PORT": "5432",
"DB_DATABASE": "mydb",
"DB_USER": "postgres",
"DB_PASSWORD": "password"
}
}
}
}
```
#### Option 3: Using local installation
If you've installed the package locally in your project (`npm install postgres-mysql-mcp-server`):
```json
{
"mcpServers": {
"sql": {
"command": "node",
"args": ["./node_modules/postgres-mysql-mcp-server/index.js"],
"env": {
"DB_TYPE": "postgresql",
"DB_HOST": "localhost",
"DB_PORT": "5432",
"DB_DATABASE": "mydb",
"DB_USER": "postgres",
"DB_PASSWORD": "password"
}
}
}
}
```
#### Option 4: Development setup (for local development)
If you're developing locally and have cloned the repository:
```json
{
"mcpServers": {
"sql": {
"command": "npm",
"args": ["start"],
"cwd": "/path-to-source/postgres-mysql-mcp-server",
"env": {
"DB_TYPE": "postgresql",
"DB_HOST": "localhost",
"DB_PORT": "5432",
"DB_DATABASE": "mydb",
"DB_USER": "postgres",
"DB_PASSWORD": "password"
}
}
}
}
```
### Example: Using with Cursor AI
Once configured, you can interact with your database through natural language:
**Example Conversation:**
```
You: "What tables are in my database?"
AI: [Uses list_tables tool] "Your database contains: users, orders, products, categories"
You: "Show me the structure of the users table"
AI: [Uses describe_table tool] "The users table has: id (integer), email (varchar), created_at (timestamp)..."
You: "Create an API endpoint to get user by ID"
AI: [Uses describe_table to understand schema, then generates code]
"Here's the endpoint matching your users table structure..."
```
The AI assistant automatically uses the appropriate MCP tools to query your database and provide accurate, schema-aware responses.
## Development
The project uses plain JavaScript (ES modules), so no build step is required. Just edit `index.js` and run `npm start`.
## Security Notes
- Never commit database credentials to version control
- Use environment variables or secure credential management
- The server supports SSL connections for secure database access
- Always validate and sanitize SQL queries in production environments
## Requirements
- Node.js 18+
- PostgreSQL or MySQL database access
## Contributing
Contributions are welcome! Please feel free to submit a Pull Request.
## License
MIT
