README - postgresql-mcp-server by thrkrdk

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

mvn clean package

Note: To use docker, the podman command should be replaced by docker,

Podman Image

There are two type Docker image for Spring MCP server for this projecy

SSE Docker Image

To create SSE docker image use

 podman build -f dockers/sse/Dockerfile   -t postgresql-mcp-server:latest  .

STDIO Docker Image

To create STDIO docker image use

  podman build   -f dockers/stdio/Dockerfile   -t postgresql-mcp-server:latest  .

In some cases, below command may not work. In that case, you can use following command to build the image.
Dockerfile and jar file should be in the same directory.
change this "COPY target/postgresql-mcp-server.jar app.jar" to "COPY postgresql-mcp-server.jar app.jar" in the Dockerfile.

 podman build -t postgresql-mcp-server  .

Create Postgresql MCP container For SSE

to run container in sse mode, you need to set the environment variable SPRING_AI_MCP_SERVER_STDIO to false.
SSE works only in async mode. So change SPRING_AI_MCP_SERVER_TYPE to ASYNC.
To verify that SSE is working, run this command: curl -v -H "Accept: text/event-stream" http://localhost:8080/sse

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

**Note:** To use podman, the podman command should be replaced by podman in the command property.

Claude Desktop

this configuration should be added to claude_desktop_config.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.

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