A Kotlin-based MCP server for interacting with PostgreSQL databases.
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.
database.properties)| Tool Name | Description | Required Parameters | Optional Parameters | Returns |
|---|---|---|---|---|
postgres_query | Execute SELECT queries against the database | sql (string) | environment (staging/release/production) | Query results in table format |
postgres_list_tables | List all tables in the database | None | environment (staging/release/production) | List of table names |
postgres_get_table_schema | Get detailed schema information for a table with PII analysis in production | table_name (string) | environment (staging/release/production) | Column details with data types, constraints, relationships, and accessibility status (production only) |
postgres_suggest_joins | Suggest JOIN queries based on relationships | table_name (string) | environment (staging/release/production) | Suggested JOIN conditions and example queries |
postgres_connection_stats | Get connection pool statistics and health info | None | None | HikariCP pool status, metrics, and health information |
| postgres_explain_query | Get PostgreSQL query execution plan with performance analysis | sql (string) | environment (staging/release/production) | Detailed execution plan with timing, costs, and optimization insights |
| postgres_reconnect_database | Reconnect to database(s) - useful when VPN connection is restored | None | environment (staging/release/production), test_connection (boolean) | Reconnection status and results for specified or all environments |
The MCP server now starts successfully even when no database connections can be established initially. This is particularly useful for VPN-dependent environments where:
Previous Behavior: Server would fail to start if no connections could be established New Behavior: Server starts with a warning and provides reconnection tools
The postgres_reconnect_database tool provides robust reconnection capabilities for handling VPN disconnections and network issues:
postgres_connection_stats to see current statusenvironment (optional): Specific environment to reconnect (staging/release/production)test_connection (optional): Whether to test connection health first (default: true)If no environment is specified, the tool will check and reconnect all configured environments as needed.
postgres_reconnect_database when ready to connectCreate 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
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
This MCP server includes enhanced PII (Personally Identifiable Information) analysis integrated into the table schema tool:
postgres_get_table_schema tool automatically includes PII information in production environmentsprivacy field in column comments to determine PII status"privacy": "personal" are treated as PII, "privacy": "non-personal" are safepostgres_get_table_schema tool with production environmentThe 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.jarbuild/libs/postgres-mcp-tool-users.jarbuild/libs/postgres-mcp-tool-analytics.jarbuild/libs/postgres-mcp-tool-payroll.jarNote: The jarSuffix parameter is required. Running ./gradlew shadowJar without it will fail with a clear error message.
This naming convention enables you to manage multiple database systems efficiently:
database.properties for the target database system./gradlew shadowJar -PjarSuffix=incidentsExample 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
All tools support environment-based database routing with an optional environment parameter:
staging (default) - Routes to staging databaserelease - Routes to release databaseproduction - Routes to production databaseAI agents automatically extract environment information from user prompts:
environment: "production"environment: "staging"environment: "release"Add this configuration to your AI agent's MCP configuration file:
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"]
}
}
}
In the Augment IntelliJ plugin, add MCP servers for each database system:
For Incidents Database:
postgres-incidentsjava -jar /absolute/path/to/postgres-mcp-tool-incidents.jarFor Users Database:
postgres-usersjava -jar /absolute/path/to/postgres-mcp-tool-users.jarFor Analytics Database:
postgres-analyticsjava -jar /absolute/path/to/postgres-mcp-tool-analytics.jarFor 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": {}
}
Before using the MCP server, ensure you have:
./gradlew shadowJar -PjarSuffix=<database-name>Since you can create multiple JAR files for different database systems, you can:
This server is built using:
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
No Database Connections at Startup:
postgres_reconnect_database tool to establish connections when readyConnection Failed During Operation:
postgres_connection_stats to check current connection statuspostgres_reconnect_database to restore connectionsjdbc:postgresql://host:port/databaseVPN Connection Issues:
postgres_reconnect_databaseEnvironment Variables Not Found:
Permission Denied: Ensure the database user has SELECT permissions
Tool Not Showing: Verify the JAR path in your AI agent's MCP configuration
Java Not Found: Ensure Java 17+ is installed and in your PATH
Build Failed - Missing jarSuffix:
./gradlew shadowJar -PjarSuffix=<database-name> instead of ./gradlew shadowJarWrong Database Connection:
postgres-mcp-tool-incidents.jar for incidents database)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:
š Updated: This server has been migrated from a custom JSON-RPC implementation to use the official Kotlin MCP SDK, providing:
Server class from the official SDKserver.addTool() methodStdioServerTransport with proper kotlinx.io integrationdatabase.properties with simplified formatlistChanged supportThis 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.
Immutable ledger database with live synchronization
Interact with the data stored in Couchbase clusters using natural language.
Real-time PostgreSQL & Supabase database schema access for AI-IDEs via Model Context Protocol. Provides live database context through secure SSE connections with three powerful tools: get_schema, analyze_database, and check_schema_alignment.
Provides real-time financial data using the Tushare API.
A persistent memory system for MCP using libSQL, providing vector search and efficient knowledge storage.
Access the NFTGo Developer API for comprehensive NFT data and analytics. Requires an NFTGo API key.
MySQL database integration with configurable access controls and schema inspection
Query a hybrid graph (Neo4j) and vector (Qdrant) database for powerful semantic and graph-based document retrieval.
A production-ready MCP server for Customer Relationship Management (CRM) functionality, built with TypeScript and SQLite.
An MCP server for Firebird SQL databases, enabling LLMs to securely access, analyze, and manipulate database content.