postgresql-mcp-server

# Spring boot Starter for Postgresql MCP Servers With Spring AI A Spring Boot application implementing an MCP (Model Context Protocol) server that provides read-only access to a PostgreSQL database via STDIO transport. ## Components ### Tools - query - Executes a read-only SQL query with paging - Input: - sql (string): SQL query. Must include a WHERE clause, must not use wildcard (SELECT *). - pageNumber (integer, optional, default = 0): Zero-based page index - pageSize (integer, optional, default = 10, max = 50): Number of rows per page - Behavior: - Validates that the query contains a WHERE clause - Prohibits SELECT * or alias wildcards - Executes query in a READ ONLY transaction with LIMIT/OFFSET - Returns JSON array of records ### Resources - Table Schemas (`postgres://<host>/<table>/schema`) - Automatically discovered from information_schema.columns - Each resource URI lists the table’s column metadata: - column_name (string) - data_type (string) - Exposed as JSON via MCP resource endpoints - Dynamic discovery on each request; no preloading at startup ## To create jar file ```shell mvn clean package ``` <div style="border: 1px solid #050505; padding: 10px; background-color: #050505;"> Note: To use docker, the podman command should be replaced by docker, </div> ## Podman Image There are two type Docker image for Spring MCP server for this projecy ### SSE Docker Image - To create SSE docker image use [Dockerfile](dockers/sse/Dockerfile) ```shell podman build -f dockers/sse/Dockerfile -t postgresql-mcp-server:latest . ``` ### STDIO Docker Image - To create STDIO docker image use [Dockerfile](dockers/stdio/Dockerfile) ```shell podman build -f dockers/stdio/Dockerfile -t postgresql-mcp-server:latest . ``` <div style="border: 1px solid #050505; padding: 10px; background-color: #132285;"> <ul> <li>In some cases, below command may not work. In that case, you can use following command to build the image.</li> <li>Dockerfile and jar file should be in the same directory.</li> <li>change this "COPY target/postgresql-mcp-server.jar app.jar" to "COPY postgresql-mcp-server.jar app.jar" in the Dockerfile.</li> </ul> </div> ```sh podman build -t postgresql-mcp-server . ``` ## Create Postgresql MCP container For SSE <div style="border: 1px solid #050505; padding: 10px; background-color: #050505;"> <ul> <li>to run container in sse mode, you need to set the environment variable SPRING_AI_MCP_SERVER_STDIO to false.</li> <li>SSE works only in async mode. So change SPRING_AI_MCP_SERVER_TYPE to ASYNC.</li> <li>To verify that SSE is working, run this command: curl -v -H "Accept: text/event-stream" http://localhost:8080/sse</li> </ul> </div> ```sh -- change host to database host, change 5432 to database port podman run -i --rm -e DATABASE_URL=jdbc:postgresql://host:5432/db -e DATABASE_USERNAME=user -e DATABASE_PASSWORD=pass -e APP_CURRENT_SCHEMA=public mcp-postgres-spring ``` ## Host / Client settings <div style="border: 1px solid #050505; padding: 10px; background-color: #050505;"> **Note:** To use podman, the podman command should be replaced by podman in the command property. </div> ### Claude Desktop this configuration should be added to claude_desktop_config.json ```json { "mcpServers": { "postgres": { "command": "docker", "args": [ "run", "-i", "--rm", "-e", "DATABASE_URL=${input:pg_url}", "-e", "DATABASE_USERNAME=${input:pg_user}", "-e", "DATABASE_PASSWORD=${input:pg_password}", "-e", "APP_CURRENT_SCHEMA=${input:pg_schema:public}", "mcp-postgres-spring" ] } } } ``` ### VS CODE: CODING ASSISTANTS Settings For manual installation, add the following JSON block to your Preferences (JSON) file in VS Code. You can do this by pressing Ctrl + Shift + P and typing Preferences: Open User Settings (JSON). Optionally, you can add it to a file called .vscode/mcp.json in your workspace. This allows you to share the configuration with others. Note that the mcp key is not required in the .vscode/mcp.json file. ```json { "mcp": { "inputs": [ { "type": "promptString", "id": "pg_url", "description": "PostgreSQL URL (e.g. jdbc:postgresql://host:5432/db)" }, { "type": "promptString", "id": "pg_user", "description": "Database username" }, { "type": "promptString", "id": "pg_password", "description": "Database password" }, { "type": "promptString", "id": "pg_schema", "description": "Database schema (default: public)", "default": "public" } ], "servers": { "postgres": { "command": "docker", "args": [ "run", "-i", "--rm", "-e", "DATABASE_URL=${input:pg_url}", "-e", "DATABASE_USERNAME=${input:pg_user}", "-e", "DATABASE_PASSWORD=${input:pg_password}", "mcp-postgres-spring" ] } } } } ``` ## TECH Stack - **Spring Boot**: 3.4.4 - **Spring AI**: 1.0.0-M7 - **Spring MCP Server (Stdio/Webflux)** - **Lombok**: 1.18.38 - **SpotBugs**: 4.8.6 - **Jackson Databind** - **CheckStyle**