PgSQL MCP Server

PgSQL MCP Server

English | 简体中文

A powerful tool server based on the Model Context Protocol (MCP), enabling interaction with PostgreSQL databases via MCP calls.

---

🚀 Overview

This project is built on the FastMCP framework and leverages the SQLAlchemy and asyncpg libraries to deliver high-performance asynchronous database operations, ensuring efficiency and responsiveness when handling database requests.

✨ Key Features

• Asynchronous & Efficient: Fully asynchronous implementation based on asyncio, utilizing asyncpg for a high-performance asynchronous PostgreSQL driver.
• Transactional Safety: DDL, DML, and DCL operations are executed within transactions with error handling and rollback mechanisms.
• Easy Deployment: Start the server with a simple command line interface.

📦 Installation

Ensure you have Python 3.10+ installed. Recommendation:

Using uv (Recommended)

uv tool install pgsql-mcp-server

Then run it:

pgsql-mcp-server --dsn "postgresql://user:password@localhost:5432/db"

Or run directly without installation:

uvx pgsql-mcp-server --dsn "postgresql://user:password@localhost:5432/db"

Using pip

pip install pgsql-mcp-server

🛠️ Available Tools

The server provides the following tools for database interaction:

• get_schema_names: List all schemas in the database.
• get_tables: List all tables in a specific schema (defaults to public).
• get_columns: Get detailed column information for a specific table.
• get_indexes: Get index details for a specific table.
• get_foreign_keys: Get foreign key constraints for a specific table.
• run_dql_query: Execute Data Query Language (DQL) statements like SELECT, SHOW, EXPLAIN.
• run_dml_query: Execute Data Manipulation Language (DML) statements like INSERT, UPDATE, DELETE.
• run_ddl_query: Execute Data Definition Language (DDL) statements like CREATE, ALTER, DROP.
• run_dcl_query: Execute Data Control Language (DCL) statements like GRANT, REVOKE.

🔍 Preview and Debugging

You can use the official MCP Inspector tool to visually inspect the tools provided by this server, view their parameters and descriptions, and perform test calls directly.

npx @modelcontextprotocol/inspector uvx pgsql-mcp-server --dsn "postgresql://user:password@host:port/database"

This will start a local web service. Open the provided URL in your browser to begin debugging.

🧪 Testing

This project uses pytest for testing.

Run all tests

uv run pytest

Run unit tests only

uv run pytest tests/test_app.py

Run integration tests only

Integration tests require a local PostgreSQL instance. They default to localhost:5432/postgres.

uv run pytest tests/test_integration.py

🤝 Contributing

Contributions are welcome! If you have suggestions for improvements, feature requests, or find any bugs, please feel free to:

1. Open an Issue to discuss.
2. Fork the repository and create your feature branch (git checkout -b feature/AmazingFeature).
3. Commit your changes (git commit -m 'Add some AmazingFeature').
4. Push to the branch (git push origin feature/AmazingFeature).
5. Open a Pull Request.