techslaves/postgresql-mcp-server

Spring AI MCP Server Walkthrough

I have successfully converted the project to a Spring AI MCP Server with PostgreSQL integration.

Changes Made

• pom.xml: Updated to use Spring Boot 3.3.0 and Spring AI 1.0.0-SNAPSHOT (experimental support for MCP).
• PostgresMcpServerApplication.java: Created the main Spring Boot application class.
• DatabaseService.java: Implemented the database service and annotated methods with @Tool to expose them as MCP tools.
• application.properties: Configured database connection settings with environment variable overrides.
• Deleted: Removed the legacy PostgresMcpServer.java.

How to Run

Prerequisites

• Java 17+
• Maven
• PostgreSQL Database

Running the Server

You can run the server using Maven. Make sure to set the environment variables for your database connection.

$env:DB_URL="jdbc:postgresql://localhost:5433/your_db"
$env:DB_USER="your_user"
$env:DB_PASSWORD="your_password"
mvn spring-boot:run

Troubleshooting & Fixes

1. Server Disconnects Immediately

Issue: The server exits immediately when connected to Claude Desktop. Cause: Port conflict (port 8080 already in use) or missing server.port=0 configuration. Fix:

• Ensure no other process is using port 8080.
• Add -Dserver.port=0 to the Claude Desktop configuration to use a random port.
• Ensure logging.level.root=INFO (not VERBOSE) in application.properties.

2. Tools Not Appearing

Issue: The server connects but shows no tools. Cause: Missing spring-ai-core dependency and incorrect tool registration. Fix:

• Added spring-ai-core dependency to pom.xml.
• Created McpConfig.java to explicitly register tools using ToolCallbacks.from(databaseService).

Verification

1. Build: mvn clean package -DskipTests
2. Run: java -Dspring.main.banner-mode=off -Dserver.port=0 -jar target/postgres-mcp-server-0.0.1-SNAPSHOT.jar
3. Check Logs: Verify mcp-server.log shows Registered tools: 2.

MCP Integration

The server exposes the following tools:

• listTables: Lists all public tables in the database.
• executeQuery: Executes a read-only SQL query (SELECT only).

These tools are automatically registered by Spring AI's MCP auto-configuration.

Testing the Postgres MCP Server

This guide provides instructions on how to test your Spring AI MCP Server.

1. Automated Unit Tests

Run the included JUnit tests to verify the application context and bean configuration.

mvn test

2. Testing with MCP Inspector

The MCP Inspector is the best way to interactively test your server.

Prerequisites

• Node.js installed (npx command available)

Steps

1. Start the Server: Make sure your database is running and environment variables are set.

   $env:DB_URL="jdbc:postgresql://localhost:5433/postgres"
   $env:DB_USER="admin"
   $env:DB_PASSWORD="password"
   mvn spring-boot:run
   

   The server will start on port 8080.

2. Run Inspector: Open a new terminal and run:

   npx @modelcontextprotocol/inspector
   

3. Connect:

   • In the Inspector UI (usually opens in browser), select SSE (Server-Sent Events) as the transport.
   • Enter the URL: http://localhost:8080/sse (or http://localhost:8080/mcp/sse depending on auto-configuration, check logs).
   • Click Connect.

4. Interact:

   • You should see listTables and executeQuery in the Tools list.
   • Try running listTables.

3. Testing with Claude Desktop (Stdio Mode)

To use this server with Claude Desktop, you need to run it in a way that it communicates via Standard Input/Output (Stdio).

Configuration

1. Build the JAR:

   mvn clean package -DskipTests
   

2. Locate the JAR in target/postgres-mcp-server-0.0.1-SNAPSHOT.jar.

Claude Desktop Config

Edit your %APPDATA%\Claude\claude_desktop_config.json:

{
  "mcpServers": {
    "postgres": {
      "command": "java",
      "args": [
        "-Dspring.main.banner-mode=off",
        "-Dlogging.pattern.console=",
        "-Dserver.port=0",
        "-jar",
        "D:/DevOps/GenAI/postgres-mcp-server/target/postgres-mcp-server-0.0.1-SNAPSHOT.jar"
      ],
      "env": {
        "DB_URL": "jdbc:postgresql://localhost:5433/postgres",
        "DB_USER": "admin",
        "DB_PASSWORD": "password"
      }
    }
  }
}

Note: We disable the web application and console logging to ensure clean JSON communication on stdout.

4. Simple Connectivity Check (SSE)

You can use curl to verify the server is listening.

curl -v http://localhost:8080/sse

You should see a connection established.