MCP Server PostgreSQL

A Model Context Protocol (MCP) server for PostgreSQL built with Spring Boot and Spring AI. This server provides read-only access to PostgreSQL databases through dynamic connections.

✅ Project Status

COMPLETED: The MCP server is fully functional with the following features:

• ✅ Spring Boot 3.4.1 + Spring AI 1.0.0 + Java 21
• ✅ Dynamic PostgreSQL connections (no hardcoded credentials)
• ✅ Read-only query validation and security
• ✅ Complete test suite with Testcontainers
• ✅ Docker Compose setup with sample data
• ✅ Production-ready Dockerfile
• ✅ Comprehensive documentation

Features

• Dynamic Database Connections: Connect to any PostgreSQL database with runtime parameters
• Read-Only Operations: Ensures database safety by only allowing SELECT queries
• MCP Tools:
  • list_schemas: List all schemas in a database
  • list_tables: List all tables in a specific schema
  • select: Select rows from a table with optional conditions and ordering
  • execute_query: Execute read-only SQL queries
• Built with Modern Stack: Spring Boot 3.4.1, Spring AI 1.0.0, Java 21

Quick Start

Prerequisites

• Java 21
• Maven 3.9+
• Docker & Docker Compose (for testing)

Running the Server

1. Clone and build the project:

   git clone https://github.com/luanvuhlu/mcp-server-postgres.git
   cd mcp-server-postgres
   mvn clean compile
   

2. Start test PostgreSQL database:

   docker-compose up -d
   

3. Run the MCP server:

   mvn spring-boot:run
   

The MCP server will start and be ready to accept connections via the Model Context Protocol.

Testing with Sample Data

The Docker Compose setup includes a PostgreSQL instance with sample data:

• Host: localhost
• Port: 5432
• Database: postgres
• Username: postgres
• Password: your_name

Sample schemas and tables are automatically created:

• public schema: users, products
• sales schema: orders, order_items
• inventory schema: stock

Using with MCP Clients

This server can be used with any MCP-compatible client. Here are some examples:

Claude Desktop

Add the following to your Claude Desktop configuration file:

{
  "mcpServers": {
    "postgres": {
      "command": "java",
      "args": ["-jar", "target/mcp-server-postgres-1.0.0-SNAPSHOT.jar"]
    }
  }
}

Other MCP Clients

The server implements the standard MCP protocol and can be used with any compatible client by running:

java -jar target/mcp-server-postgres-1.0.0-SNAPSHOT.jar

MCP Tools Usage

List Schemas

List all schemas in a PostgreSQL database:

{
  "name": "list_schemas",
  "arguments": {
    "host": "localhost",
    "database": "postgres", 
    "username": "postgres",
    "password": "your_name"
  }
}

List Tables

List all tables in a specific schema:

{
  "name": "list_tables",
  "arguments": {
    "host": "localhost",
    "database": "postgres",
    "username": "postgres", 
    "password": "your_name",
    "schema": "public"
  }
}

Select

Select rows from a table with optional conditions and ordering:

{
  "name": "select",
  "arguments": {
    "host": "localhost",
    "database": "postgres",
    "username": "postgres", 
    "password": "your_name",
    "table": "users",
    "schema": "public",
    "conditions": "id > 1",
    "orderBy": "username ASC",
    "limit": 10
  }
}

Execute Query

Execute a read-only SQL query:

{
  "name": "execute_query",
 

... [View full README on GitHub](https://github.com/luanvuhlu/mcp-server-postgres#readme)