postgres_queryLABSCO SUMMARY
Read-only by construction, which is what makes pointing it at production reasonable.
Only SELECT is permitted, so the worst outcome is a slow query rather than a changed row. The relationship and JOIN-suggestion tools do the work that usually costs several failed attempts against an unfamiliar schema, and the environment argument keeps staging and production in the same conversation.
What it is
A PostgreSQL server built on the Kotlin MCP SDK, with read-only query execution, schema and relationship discovery, and HikariCP connection pooling.
What you get
- SELECT queries executed and returned in table format; anything else is refused (postgres_query)
- Every table in the database (postgres_list_tables)
- A table's columns with data types, constraints and relationship indicators (postgres_get_table_schema)
- Primary keys, foreign keys, what references a table, and its unique constraints (postgres_get_relationships)
- JOIN conditions and example queries suggested from those relationships (postgres_suggest_joins)
- Database name, version, driver and connection details (postgres_get_database_info), plus pool status and health metrics (postgres_connection_stats)
- An environment argument on most tools, choosing staging, release or production
Requirements
JDK 21 and Gradle 8.10 to build the jar, and a PostgreSQL user with SELECT permission. The database.properties already in the repository reads each environment's JDBC URL, username and password from POSTGRES_* environment variables when the server starts. Staging is the default, and at least one environment must be set.
Setup effort
Build from source — git clone https://github.com/chan00012/postgres-kotlin-mcp.git
USE CASES
01
Answering a question from production data safely
Run the SELECT against the production environment, knowing writes cannot go through.
02
Learning a schema you did not design
List the tables, then read the columns, types and constraints of the ones that matter.
03
Writing a join without guessing the keys
Read the real foreign keys, then take the suggested JOIN conditions as a starting point.
04
Comparing staging against production
Run the same query with a different environment argument and set the results side by side.
05
Checking whether the connection pool is healthy
Read the pool status and metrics when queries start to feel slow.
7 tools
Listed in project docs. Your client may show a different list.
postgres_list_tablespostgres_get_table_schemapostgres_get_relationshipspostgres_suggest_joinspostgres_get_database_infopostgres_connection_statsBEFORE YOU INSTALL
What to know first
Tools you get
7 tools named in the project's documentation. Only SELECT queries are allowed; the environment parameter (staging, release, production) chooses which database each tool hits.
Requires
- JDK 21
- Gradle 8.10
- A PostgreSQL user with SELECT permission
- POSTGRES_STAGING_JDBC_URL, POSTGRES_STAGING_USERNAME and POSTGRES_STAGING_PASSWORD (or the RELEASE / PRODUCTION trio) in the server's env
INSTALLATION
Connect your client
You clone and build this one yourself.
Server setup
TERMINAL
git clone https://github.com/chan00012/postgres-kotlin-mcp.git
cd postgres-kotlin-mcp
gradle shadowJar -PjarSuffix=incidentsThe suffix only names the jar, which lands in build/libs/postgres-mcp-tool-<YOUR_SUFFIX>.jar. incidents, users and analytics are the README's example databases: build one jar per database you connect — just this one if you have a single database — and give each jar its own entry in the client configuration.
MCP Clients
Claude Desktop
The README's example serves three databases (incidents, users, analytics), one jar and one entry each; keep one entry per jar you built. Each server reads its database from environment variables when it starts, and Claude Desktop does not pass your shell's variables on, so they go in env. Give each server the values for its own database. Staging is the default environment; add the POSTGRES_RELEASE_* or POSTGRES_PRODUCTION_* trio the same way to reach those.
CLAUDE DESKTOP · JSON
{
"mcpServers": {
"postgres-incidents": {
"command": "java",
"args": [
"-jar",
"<YOUR_PATH_TO_POSTGRES_MCP_TOOL_INCIDENTS>"
],
"env": {
"POSTGRES_STAGING_JDBC_URL": "jdbc:postgresql://localhost:5432/mydb_staging",
"POSTGRES_STAGING_USERNAME": "<YOUR_POSTGRES_STAGING_USERNAME>",
"POSTGRES_STAGING_PASSWORD": "<YOUR_POSTGRES_STAGING_PASSWORD>"
}
},
"postgres-users": {
"command": "java",
"args": [
"-jar",
"<YOUR_PATH_TO_POSTGRES_MCP_TOOL_USERS>"
],
"env": {
"POSTGRES_STAGING_JDBC_URL": "jdbc:postgresql://localhost:5432/mydb_staging",
"POSTGRES_STAGING_USERNAME": "<YOUR_POSTGRES_STAGING_USERNAME>",
"POSTGRES_STAGING_PASSWORD": "<YOUR_POSTGRES_STAGING_PASSWORD>"
}
},
"postgres-analytics": {
"command": "java",
"args": [
"-jar",
"<YOUR_PATH_TO_POSTGRES_MCP_TOOL_ANALYTICS>"
],
"env": {
"POSTGRES_STAGING_JDBC_URL": "jdbc:postgresql://localhost:5432/mydb_staging",
"POSTGRES_STAGING_USERNAME": "<YOUR_POSTGRES_STAGING_USERNAME>",
"POSTGRES_STAGING_PASSWORD": "<YOUR_POSTGRES_STAGING_PASSWORD>"
}
}
}
}Any MCP client
ANY MCP CLIENT · JSON
{
"mcpServers": {
"postgres-mcp-tool": {
"command": "java",
"args": [
"-jar",
"<YOUR_PATH_TO_POSTGRES_MCP_TOOL_INCIDENTS>"
],
"env": {
"POSTGRES_STAGING_JDBC_URL": "jdbc:postgresql://localhost:5432/mydb_staging",
"POSTGRES_STAGING_USERNAME": "<YOUR_POSTGRES_STAGING_USERNAME>",
"POSTGRES_STAGING_PASSWORD": "<YOUR_POSTGRES_STAGING_PASSWORD>"
}
}
}
}
