PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides tools to interact with PostgreSQL databases. Built using the official Kotlin MCP SDK for robust and standardized protocol compliance.

Features

• 🔒 Safe Query Execution: Only SELECT queries are allowed for security
• 🗄️ Schema Inspection: Get detailed table schemas and column information
• 📋 Table Listing: List all tables in the database
• 🔗 Relationship Discovery: Discover foreign keys, primary keys, and table relationships
• 🔄 JOIN Suggestions: Get intelligent JOIN query suggestions based on relationships
• 🌍 Multi-Environment Support: Connect to staging, release, and production databases
• ⚡ HikariCP Connection Pooling: Enterprise-grade connection management
• 📊 Real-time Monitoring: Built-in connection pool statistics and health monitoring

Prerequisites

• Java 17 or higher
• PostgreSQL database(s)
• Database configuration file (database.properties)

Available Tools

Safety Features

• 🔒 Read-Only Access: Only SELECT queries are permitted
• 🛡️ SQL Injection Protection: Uses parameterized queries where possible
• 📏 Row Limiting: Configurable limits prevent overwhelming responses
• ✅ Connection Validation: Built-in connection testing and validation

Configuration

Create a database.properties file in src/main/resources/ with your database connection details:

# PostgreSQL Database Configuration
# All sensitive information should be provided via environment variables

# Staging Environment
database.staging.jdbc-url=${POSTGRES_STAGING_JDBC_URL}
database.staging.username=${POSTGRES_STAGING_USERNAME}
database.staging.password=${POSTGRES_STAGING_PASSWORD}

# Release Environment
database.release.jdbc-url=${POSTGRES_RELEASE_JDBC_URL}
database.release.username=${POSTGRES_RELEASE_USERNAME}
database.release.password=${POSTGRES_RELEASE_PASSWORD}

# Production Environment
database.production.jdbc-url=${POSTGRES_PRODUCTION_JDBC_URL}
database.production.username=${POSTGRES_PRODUCTION_USERNAME}
database.production.password=${POSTGRES_PRODUCTION_PASSWORD}

# HikariCP Connection Pool Configuration (Optional)
# Uses sensible defaults if not specified

# Optional: Override default pool sizes per environment
hikari.staging.maximum-pool-size=5
hikari.staging.minimum-idle=1

hikari.release.maximum-pool-size=8
hikari.release.minimum-idle=2

hikari.production.maximum-pool-size=15
hikari.production.minimum-idle=3

Environment Variables Setup

Set the required environment variables for your database connections:

# Staging Environment
export POSTGRES_STAGING_JDBC_URL="jdbc:postgresql://localhost:5432/mydb_staging"
export POSTGRES_STAGING_USERNAME="your_staging_username"
export POSTGRES_STAGING_PASSWORD="your_staging_password"

# Release Environment
export POSTGRES_RELEASE_JDBC_URL="jdbc:postgresql://localhost:5432/mydb_release"
export POSTGRES_RELEASE_USERNAME="your_release_username"
export POSTGRES_RELEASE_PASSWORD="your_release_password"

# Production Environment
export POSTGRES_PRODUCTION_JDBC_URL="jdbc:postgresql://prod-host:5432/mydb_production"
export POSTGRES_PRODUCTION_USERNAME="your_production_username"
export POSTGRES_PRODUCTION_PASSWORD="your_production_password"

For Windows (PowerShell):

$env:POSTGRES_STAGING_JDBC_URL="jdbc:postgresql://localhost:5432/mydb_staging"
$env:POSTGRES_STAGING_USERNAME="your_staging_username"
$env:POSTGRES_STAGING_PASSWORD="your_staging_password"
# ... repeat for release and production

For Docker/Container environments:

environment:
  - POSTGRES_STAGING_JDBC_URL=jdbc:postgresql://localhost:5432/mydb_staging
  - POSTGRES_STAGING_USERNAME=your_staging_username
  - POSTGRES_STAGING_PASSWORD=your_staging_password

Building

The build system requires a jarSuffix parameter to create database-specific JAR files:

# Build JAR for specific database system
./gradlew shadowJar -PjarSuffix=incidents
./gradlew shadowJar -PjarSuffix=users
./gradlew shadowJar -PjarSuffix=analytics
./gradlew shadowJar -PjarSuffix=payroll

This creates JAR files with descriptive names:

• build/libs/postgres-mcp-tool-incidents.jar
• build/libs/postgres-mcp-tool-users.jar
• build/libs/postgres-mcp-tool-analytics.jar
• build/libs/postgres-mcp-tool-payroll.jar

Note: The jarSuffix parameter is required. Running ./gradlew shadowJar without it will fail with a clear error message.

Multi-Database Workflow

This naming convention enables you to manage multiple database systems efficiently:

1. Configure your database.properties for the target database system
2. Build the JAR with a descriptive suffix: ./gradlew shadowJar -PjarSuffix=incidents
3. Repeat for other database systems (users, analytics, payroll, etc.)
4. Deploy multiple MCP servers, each with its own JAR and database configuration
5. Distinguish easily between different database connections in your AI agent

Example Workflow:

# Configure database.properties for incidents database
# Build incidents JAR
./gradlew shadowJar -PjarSuffix=incidents

# Update database.properties for users database
# Build users JAR
./gradlew shadowJar -PjarSuffix=users

# Update database.properties for analytics database
# Build analytics JAR
./gradlew shadowJar -PjarSuffix=analytics

Environment-Based Database Routing

All tools support environment-based database routing with an optional environment parameter:

• staging (default) - Routes to staging database
• release - Routes to release database
• production - Routes to production database

Natural Language Support

AI agents automatically extract environment information from user prompts:

• "Query the production database for user statistics" → environment: "production"
• "List tables in staging" → environment: "staging"
• "Show me the schema for users table in release" → environment: "release"

Usage with AI Agents

Add this configuration to your AI agent's MCP configuration file:

Claude Desktop Configuration

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "postgres-incidents": {
      "command": "java",
      "args": ["-jar", "/absolute/path/to/postgres-mcp-tool-incidents.jar"]
    },
    "postgres-users": {
      "command": "java",
      "args": ["-jar", "/absolute/path/to/postgres-mcp-tool-users.jar"]
    },
    "postgres-analytics": {
      "command": "java",
      "args": ["-jar", "/absolute/path/to/postgres-mcp-tool-analytics.jar"]
    }
  }
}

Single Database Setup:

{
  "mcpServers": {
    "postgres-mcp-tool": {
      "command": "java",
      "args": ["-jar", "/absolute/path/to/postgres-mcp-tool-incidents.jar"]
    }
  }
}

Augment IntelliJ Plugin Configuration

In the Augment IntelliJ plugin, add MCP servers for each database system:

For Incidents Database:

• Name: postgres-incidents
• Command: java -jar /absolute/path/to/postgres-mcp-tool-incidents.jar

For Users Database:

• Name: postgres-users
• Command: java -jar /absolute/path/to/postgres-mcp-tool-users.jar

For Analytics Database:

• Name: postgres-analytics
• Command: java -jar /absolute/path/to/postgres-mcp-tool-analytics.jar

Other AI Agents

For other MCP-compatible AI agents, use the standard MCP server configuration format:

Multiple Database Systems:

[
  {
    "name": "postgres-incidents",
    "command": "java",
    "args": ["-jar", "/absolute/path/to/postgres-mcp-tool-incidents.jar"],
    "env": {}
  },
  {
    "name": "postgres-users",
    "command": "java",
    "args": ["-jar", "/absolute/path/to/postgres-mcp-tool-users.jar"],
    "env": {}
  }
]

Single Database System:

{
  "name": "postgres-mcp-tool",
  "command": "java",
  "args": ["-jar", "/absolute/path/to/postgres-mcp-tool-incidents.jar"],
  "env": {}
}

Prerequisites

Before using the MCP server, ensure you have:

1. Java 17+ installed and available in your PATH
2. Built the JAR file using ./gradlew shadowJar -PjarSuffix=<database-name>
3. Set environment variables for your database connections (see Environment Variables Setup above)
4. Database permissions - the configured user must have SELECT permissions on the target databases

Database-Specific JAR Management

Since you can create multiple JAR files for different database systems, you can:

1. Build separate JARs for each database system (incidents, users, analytics, etc.)
2. Configure different database.properties for each system before building
3. Deploy multiple MCP servers simultaneously, each connecting to different databases
4. Easily distinguish between database connections using descriptive JAR names

Architecture

This server is built using:

• Kotlin MCP SDK v0.5.0: Official Model Context Protocol implementation
• HikariCP: High-performance JDBC connection pooling
• PostgreSQL JDBC Driver: Database connectivity
• Kotlinx Serialization: JSON handling
• Kotlinx Coroutines: Asynchronous operations

Connection Management (HikariCP)

• Enterprise-Grade Pooling: Battle-tested connection management
• Automatic Health Monitoring: Built-in connection validation and health checks
• Connection Leak Detection: Automatically detects and reports connection leaks
• Optimized Performance: Fastest connection pool available for Java/Kotlin
• Thread-Safe Operations: Concurrent access is properly managed

Example Usage

Basic Database Exploration

• "What database am I connected to?"
• "What tables are in my database?"
• "Show me the schema for the users table"
• "Query the first 10 rows from the products table"

Environment-Specific Queries

• "What tables are in the production database?"
• "Query the staging database for user statistics"
• "Show me the schema for the orders table in release environment"
• "Get relationships for the users table in staging"

Relationship Discovery

• "What are the relationships for the orders table?"
• "Show me all foreign keys in the customers table"
• "What tables reference the users table?"
• "What JOIN queries can I write with the orders table?"

Advanced Schema Analysis

• "Show me the complete schema with relationships for the products table"
• "What are the primary keys and foreign keys for all my tables?"
• "Help me understand how my tables are connected"

Project Structure

postgres-mcp-tool/
├── src/
│   ├── main/
│   │   ├── kotlin/
│   │   │   ├── PostgreSqlMcpServer.kt         # Main MCP server implementation
│   │   │   ├── PostgreSqlRepository.kt        # Database operations and queries
│   │   │   ├── HikariConnectionManager.kt     # Connection pool management
│   │   │   └── DatabaseConnectionConfig.kt    # Database configuration DTO
│   │   └── resources/
│   │       └── database.properties            # Database configuration
│   └── test/kotlin/
│       ├── PostgreSqlMcpServerTest.kt         # Integration tests
│       ├── DatabaseConnectionConfigTest.kt    # DTO unit tests
│       └── DatabaseConfigurationTest.kt       # Configuration integration tests
├── build.gradle.kts                           # Build configuration
├── docker-compose.yml                         # Docker setup for testing
├── init.sql                                   # Sample database schema
└── README.md                                 # This documentation

Troubleshooting

Common Issues

1. Connection Failed:
   • Check that all required environment variables are set
   • Verify JDBC URL format: jdbc:postgresql://host:port/database
   • Ensure database credentials are correct
2. Environment Variables Not Found:
   • Verify environment variables are exported in your shell
   • For AI agents, ensure environment variables are available to the Java process
3. Permission Denied: Ensure the database user has SELECT permissions
4. Tool Not Showing: Verify the JAR path in your AI agent's MCP configuration
5. Java Not Found: Ensure Java 17+ is installed and in your PATH
6. Build Failed - Missing jarSuffix:
   • Use ./gradlew shadowJar -PjarSuffix=<database-name> instead of ./gradlew shadowJar
   • The jarSuffix parameter is required to create descriptive JAR names
7. Wrong Database Connection:
   • Verify you're using the correct JAR file for the intended database system
   • Check the JAR filename matches your database system (e.g., postgres-mcp-tool-incidents.jar for incidents database)

Debugging

Check your AI agent's logs for errors. For example:

Claude Desktop:

# macOS/Linux
tail -f ~/Library/Logs/Claude/mcp*.log

# Windows
# Check %APPDATA%\Claude\Logs\

Other AI Agents:

• Check your specific AI agent's documentation for log locations
• Look for MCP-related error messages in the agent's console or log files

Migration to Kotlin MCP SDK

🎉 Updated: This server has been migrated from a custom JSON-RPC implementation to use the official Kotlin MCP SDK, providing:

Benefits of the Migration

• Better Protocol Compliance: Full adherence to the MCP specification
• Improved Error Handling: Standardized error responses and better debugging
• Cleaner Code Structure: More maintainable and readable codebase
• Future-Proof Compatibility: Automatic updates with MCP protocol changes
• Enhanced Performance: Optimized transport layer and message handling

What Changed

• Server Implementation: Now uses Server class from the official SDK
• Tool Registration: Tools are registered using server.addTool() method
• Transport Layer: Uses StdioServerTransport with proper kotlinx.io integration
• Message Handling: Automatic JSON-RPC protocol handling by the SDK

What Stayed the Same

• All existing functionality: Every tool and feature has been preserved
• Database operations: HikariCP connection management unchanged
• Configuration: Clean database.properties with simplified format
• API compatibility: All tool parameters and responses remain identical

Technical Details

• SDK Version: Using Kotlin MCP SDK v0.5.0
• Transport: STDIO transport with buffered kotlinx.io streams
• Capabilities: Tools with listChanged support
• Error Handling: Standardized MCP error responses

This migration ensures the server remains compatible with all MCP clients while benefiting from the official SDK's improvements and future updates.

License

This project is licensed under the MIT License.