PostgreSQL MCP Server by leixiaotian1 - MCP Server

PostgreSQL MCP Server

| English

A Model Context Protocol (MCP) server that provides tools for interacting with a PostgreSQL database. It enables AI assistants to execute SQL queries, explain statements, create tables, and list database tables via the MCP protocol.

✨ Features

Interact with Databases via AI: Enables LLMs to perform database operations through a structured protocol.
Secure Toolset: Separates read and write operations into distinct, authorizable tools (read_query, write_query).
Schema Management: Allows for table creation (create_table) and listing (list_tables).
Query Analysis: Provides a tool to analyze query execution plans (explain_query).
Multiple Transport Modes: Supports stdio, Server-Sent Events (sse), and streamableHttp for flexible client integration.
Environment-Based Configuration: Easily configurable using a .env file.

🛠️ Available Tools

The server exposes the following tools for MCP clients to invoke:

Tool Name	Description	Parameters
`read_query`	Executes a `SELECT` SQL query.	`query` (string, required): The `SELECT` statement to execute.
`write_query`	Executes an `INSERT`, `UPDATE`, or `DELETE` SQL query.	`query` (string, required): The `INSERT/UPDATE/DELETE` statement to execute.
`create_table`	Executes a `CREATE TABLE` SQL statement.	`schema` (string, required): The `CREATE TABLE` statement.
`list_tables`	Lists all user-created tables in the database.	`schema` (string, optional): The schema name to filter tables by.
`explain_query`	Returns the execution plan for a given SQL query.	`query` (string, required): The query to explain (must start with `EXPLAIN`).

🚀 Quick Start

Prerequisites

Go 1.23 or later
A PostgreSQL database server

Installation

Clone the repository:

git clone https://github.com/leixiaotian1/pgsql-mcp-server.git
cd pgsql-mcp-server

Install dependencies:
```
go mod download
```
Build the MCP server:
```
go build -o pgsql-mcp-server
```

Configuration

The pg-mcp-server requires database connection details to be provided via environment variables. Create a .env file in the project root with the following variables:

DB_HOST=localhost      # PostgreSQL server host
DB_PORT=5432           # PostgreSQL server port
DB_NAME=postgres       # Database name
DB_USER=your_username  # Database user
DB_PASSWORD=your_pass  # Database password
DB_SSLMODE=disable     # SSL mode (disable, require, verify-ca, verify-full)
SERVER_MODE=stdio      # Server mode (stdio, sse, streamableHttp)

Usage

Running the Server

./pgsql-mcp-server

MCP Configuration

To use this server with an MCP-enabled AI assistant, add the following to your MCP configuration:

{
  "mcpServers": {
    "pgsql-mcp-server": {
      "command": "/path/to/pgsql-mcp-server",
      "args": [],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_NAME": "postgres",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_SSLMODE": "disable",
        "SERVER_MODE": "stdio"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}

DOCKER DEPLOYMENT

Click to expand Docker Deployment Guide

Prerequisites

Docker installed

Deployment Steps

Clone the project

git clone https://github.com/leixiaotian1/pgsql-mcp-server.git
cd pgsql-mcp-server

Configure .env file

Create a .env file in the project root directory. This file stores database connection information. Ensure the DB_HOST value matches the database container name you'll start later.
```
DB_HOST=postgres
DB_PORT=5432
DB_NAME=postgres
DB_USER=user
DB_PASSWORD=password
DB_SSLMODE=disable
SERVER_MODE=sse
```
Create Docker network

To enable communication between the application container and database container, create a shared Docker network. This command only needs to run once.
```
docker network create sql-mcp-network
```
Start PostgreSQL database container

Use this command to start a PostgreSQL container and connect it to our network.
Note:
- --name postgres: Container name, must exactly match the DB_HOST in your .env file.
- --network sql-mcp-network: Connect to the shared network.
- -p 5432:5432: Maps host's 5432 port to container's 5432 port. This means you can connect from your computer (e.g., using DBeaver) via localhost:5432, while the app container will access 5432 port directly through the internal network.
```
docker run -d \
  --name postgres \
  --network sql-mcp-network \
  -e POSTGRES_USER=user \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=postgres \
  -p 5432:5432 \
  postgres
```
Build and run the application

Now you can use commands from the Makefile to manage the application.
- Build image and run container:
```
make build
make run
```
  This will automatically stop old containers, build a new image, and start a new container.
- View application logs:
```
make logs
```
  If you see Successfully connected to database, everything is working correctly.
- Stop the application:
```
make stop
```

🔌 Server Modes

You can select the transport protocol by setting the SERVER_MODE environment variable.

`stdio`

The server communicates over standard input and output. This is the default mode and is ideal for local testing or direct integration with command-line-based MCP clients.

`sse`

The server communicates using Server-Sent Events (SSE). When this mode is enabled, the server will start an HTTP service and listen for connections.

SSE Endpoint: http://localhost:8088/sse
Message Endpoint: http://localhost:8088/message

`streamableHttp`

The server uses the Streamable HTTP transport, a more modern and flexible HTTP-based transport for MCP.

Endpoint: http://localhost:8088/mcp

🤝 Contributing

Contributions are welcome! If you find any bugs, have feature requests, or suggestions for improvement, please feel free to submit a Pull Request or open an Issue.

Fork the Project.
Create your Feature Branch (git checkout -b feature/AmazingFeature).
Commit your Changes (git commit -m 'Add some AmazingFeature').
Push to the Branch (git push origin feature/AmazingFeature).
Open a Pull Request.

📄 License

This project is open source and is licensed under the MIT License.

leixiaotian1/pgsql-mcp-server