PostgreSQL MCP Server

🚀 Features

Database Tools

Key Capabilities

🏗️ Architecture

src/
├── main.ts # MCP server entry point
├── config/
│ └── database.ts # Database configuration management
├── database/
│ ├── manager.ts # Connection pool and lifecycle management
│ └── query-service.ts # High-level database operations
├── tools/
│ └── database-tools.ts # MCP tool implementations
├── types/
│ └── database.ts # TypeScript type definitions
└── utils/
 └── query-utils.ts # Query formatting and utilities

Design Principles

🗄️ Database Configuration

# PostgreSQL Connection
DB_HOST=127.0.0.1
DB_PORT=5432
DB_NAME=your-database-name
DB_USER=your-username
DB_PASSWORD=your-password
# Google Cloud SQL (optional)
INSTANCE_CONNECTION_NAME=your-project:region:instance-name
# SSL Configuration (recommended for production)
DB_SSL=false
# Google Cloud Project (for IAM authentication)
GOOGLE_CLOUD_PROJECT=your-project-id

💻 Usage Examples

#### 2. Execute SQL Queries

#### 3. Natural Language Queries

#### 4. AI-Generated SQL

### Supported Output Formats
- **table** (default) - Formatted table output
- **json** - JSON array format
- **csv** - Comma-separated values
### Database Features
- **Schema Discovery** - Automatically discover all schemas, tables, and columns
- **Multiple Output Formats** - Table, JSON, or CSV output
- **Natural Language Processing** - Convert questions to SQL queries
- **Google Cloud SQL Support** - Native support for Cloud SQL with private IP
- **Connection Pooling** - Efficient database connection management
- **Error Handling** - Comprehensive error handling and reporting
## � Installation & Setup
### Prerequisites
- **Node.js** 18+ 
- **PostgreSQL** database (local or cloud)
- **TypeScript** (installed globally or via npm)
### Quick Start
1. **Clone and install dependencies:**
```bash
git clone <repository-url>
cd mcp-test
npm install

# Create .env file with your database configuration
touch .env
# Edit .env with your PostgreSQL connection details

# Build the project
npm run build
# Test database connection
npm run test:db
# Start the server
npm start

# Watch mode with auto-rebuild
npm run dev

Environment Configuration

# PostgreSQL Configuration
DB_HOST=localhost
DB_PORT=5432
DB_NAME=your-database-name
DB_USER=your-username
DB_PASSWORD=your-password
# Connection Pool Settings
DB_MAX_CONNECTIONS=5
# SSL Configuration (recommended for production)
DB_SSL=false
# Google Cloud SQL Configuration (optional)
INSTANCE_CONNECTION_NAME=your-project:region:instance-name
GOOGLE_CLOUD_PROJECT=your-project-id

🧪 Testing

Database Tests

# Test database connection
npm run test:db
# Test database schema discovery
npm run test:schema
# Test MCP client integration
npm run test:client
# Run all tests
npm test

Manual Testing Scripts

# Direct database connection test
node scripts/test-direct-db.js
# Schema validation
node scripts/test-db-schema.js
# Get schema statistics
node scripts/get-schema-count.js

🔧 Key Benefits & Features

Architecture Excellence

Database Capabilities

Developer Experience

Production Ready

📡 Using with MCP Clients

Configuration for Claude Desktop

{
 "mcpServers": {
 "sql-assistant-server": {
 "command": "node",
 "args": ["/absolute/path/to/mcp-test/build/main.js"]
 }
 }
}

VS Code Integration

Available Tasks:

Project Structure

mcp-test/
├── .vscode/ # VS Code configuration
│ ├── mcp.json # MCP server config
│ ├── tasks.json # Build tasks
│ └── launch.json # Debug config
├── src/ # Source code (TypeScript)
│ ├── main.ts # Main server entry point
│ ├── config/ # Configuration management
│ │ └── database.ts # Database configuration
│ ├── database/ # Database layer
│ │ ├── manager.ts # Connection management
│ │ └── query-service.ts # Query operations
│ ├── tools/ # MCP tools implementation
│ │ └── database-tools.ts # Database tools
│ ├── types/ # TypeScript type definitions
│ │ └── database.ts # Database-related types
│ └── utils/ # Utility functions
│ └── query-utils.ts # Query formatting and analysis
├── build/ # Compiled JavaScript output
├── scripts/ # Utility scripts
│ ├── setup-dev.sh # Development setup
│ ├── test-direct-db.js # Database connection test
│ ├── test-db-schema.js # Database schema testing
│ └── test-client.js # MCP client testing
├── .env # Environment variables (create from template above)
├── Dockerfile # Docker configuration
├── package.json
├── tsconfig.json
└── README.md

🧑‍💻 Development

Testing

# Test database connection
npm run test:db
# Test database schema
npm run test:schema
# Test MCP client integration
npm run test:client
# Run all tests
npm test

Adding New Database Tools

// In src/tools/database-tools.ts
server.tool(
 "your_database_tool",
 {
 query: z.string().describe("SQL query or natural language request"),
 format: z.enum(["table", "json", "csv"]).default("table")
 },
 {
 title: "Your Database Tool",
 description: "Custom database operation"
 },
 async (args) => {
 const queryService = DatabaseManager.getInstance().getQueryService();
 const results = await queryService.executeQuery(args.query);
 
 return {
 content: [{
 type: "text", 
 text: formatResults(results, args.format)
 }]
 };
 }
);

Environment Configuration

# PostgreSQL Configuration
DB_HOST=localhost
DB_PORT=5432
DB_NAME=your-database-name
DB_USER=your-username
DB_PASSWORD=your-password
# Connection Pool Settings
DB_MAX_CONNECTIONS=5
# SSL Configuration (recommended for production)
DB_SSL=false
# Google Cloud SQL Configuration (optional)
INSTANCE_CONNECTION_NAME=your-project:region:instance-name
GOOGLE_CLOUD_PROJECT=your-project-id

Docker Support

# Build image
docker build -t mcp-postgres-server .
# Run container
docker run -p 3000:3000 --env-file .env mcp-postgres-server

📚 Learn More

📄 License