PostgreSQL MCP Server

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

- 🔒 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

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

- 🔒 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

Create adatabase.propertiesfile insrc/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

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"

$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

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

The build system requires ajarSuffixparameter 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: ThejarSuffixparameter is required. Running./gradlew shadowJarwithout it will fail with a clear error message.

This naming convention enables you to manage multiple database systems efficiently:
- Configureyourdatabase.propertiesfor the target database system
- Buildthe JAR with a descriptive suffix:./gradlew shadowJar -PjarSuffix=incidents
- Repeatfor other database systems (users, analytics, payroll, etc.)
- Deploymultiple MCP servers, each with its own JAR and database configuration
- Distinguisheasily between different database connections in your AI agent

# 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

All tools support environment-based database routing with an optionalenvironmentparameter:

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

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"

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

Add to yourclaude_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"] } } }

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

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

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

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

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

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

[ { "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": {} } ]

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

Before using the MCP server, ensure you have:
- Java 17+installed and available in your PATH
- Built the JAR fileusing./gradlew shadowJar -PjarSuffix=<database-name>
- Set environment variablesfor your database connections (see Environment Variables Setup above)
- Database permissions- the configured user must have SELECT permissions on the target databases

Since you can create multiple JAR files for different database systems, you can:
- Build separate JARsfor each database system (incidents, users, analytics, etc.)
- Configure different database.propertiesfor each system before building
- Deploy multiple MCP serverssimultaneously, each connecting to different databases
- Easily distinguishbetween database connections using descriptive JAR names

- 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

- 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

- "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"

- "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"

- "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?"

- "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"

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

- Connection Failed:

- Check that all required environment variables are set
- Verify JDBC URL format:jdbc:postgresql://host:port/database
- Ensure database credentials are correct

- Verify environment variables are exported in your shell
- For AI agents, ensure environment variables are available to the Java process

- Use./gradlew shadowJar -PjarSuffix=<database-name>instead of./gradlew shadowJar
- The jarSuffix parameter is required to create descriptive JAR names

- 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.jarfor incidents database)

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

# macOS/Linux tail -f ~/Library/Logs/Claude/mcp*.log # Windows # Check %APPDATA%\Claude\Logs\

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

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

- 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

- Server Implementation: Now usesServerclass from the official SDK
- Tool Registration: Tools are registered usingserver.addTool()method
- Transport Layer: UsesStdioServerTransportwith proper kotlinx.io integration
- Message Handling: Automatic JSON-RPC protocol handling by the SDK

- All existing functionality: Every tool and feature has been preserved
- Database operations: HikariCP connection management unchanged
- Configuration: Cleandatabase.propertieswith simplified format
- API compatibility: All tool parameters and responses remain identical

- SDK Version: Using Kotlin MCP SDK v0.5.0
- Transport: STDIO transport with buffered kotlinx.io streams
- Capabilities: Tools withlistChangedsupport
- 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.

This project is licensed under the MIT License.

Multi-database agent access (PostgreSQL, SQLite, MySQL, Oracle, SQL Server) with batch queries, pre-configured connections, and SQLGlot-enforced read-only safety

A read-only MCP server for querying AWS PostgreSQL databases.

A read-only MCP server for AlloyDB, enabling LLMs to query live data directly from AlloyDB databases.

A read-only MCP server by CData that enables LLMs to query live data from EnterpriseDB databases.

A server for direct interaction with CockroachDB databases.

Database MCP server for MySQL, MariaDB, PostgreSQL & SQLite

A single-binary MCP server for MySQL, MariaDB, PostgreSQL, and SQLite

A Model Context Protocol (MCP) server that provides multi-database query execution capabilities with support for SQLite, PostgreSQL, and MySQL databases. Includes a built-in Web UI for managing database connections.

Update various databases (PostgreSQL, MySQL, MongoDB, SQLite) using data from CSV and Excel files.

Expert-level PostgreSQL database analysis MCP server for AI assistants.