FastPostgresMCP 🐘⚡️ (Full-Featured Multi-DB MCP Server)
This project implements a blazing fast, type-safe, and full-featured Model Context Protocol (MCP) Server designed for AI Agents (like Cursor, Claude Desktop) to interact with multiple PostgreSQL databases, including listing tables and inspecting schemas.
It is built with Bun, TypeScript, postgres, and leverages advanced features of the fastmcp framework for building robust MCP servers.
Purpose: An MCP Server for AI Agents
This is not a library to be imported into your code. It is a standalone server application. You run it as a process, and MCP clients (like AI agents) communicate with it using the JSON-based Model Context Protocol (v2.0), typically over a stdio connection managed by the client application (e.g., Cursor).
Troubleshooting and Development
Using the CLI for Testing
The package includes a built-in CLI command for testing the MCP server directly:
# From the project repository:
bun run cli
# This will start an interactive MCP CLI session where you can:
# - Call any of the PostgreSQL tools (query_tool, execute_tool, etc.)
# - View server capabilities
# - Test queries against your configured databases
Testing with Built-in MCP Inspector
You can also use the MCP Inspector to visually test and debug:
# From the project repository:
bun run inspect
Common Issues
If you see this error when running bunx postgres-mcp:
FastPostgresMCP started
[warning] FastMCP could not infer client capabilities
followed by ping messages, it means:
- The MCP server started successfully
- The client connected successfully
- But the client is only sending ping requests and not properly negotiating capabilities
This usually indicates you need to use a proper MCP client. Try:
- Using
bun run clito test with the MCP CLI - Configuring the MCP server in Cursor or Claude Desktop as described in the Installation section
If you're developing a custom MCP client, make sure it properly implements the MCP protocol including capabilities negotiation.
✨ Core Features
- 🚀 Blazing Fast: Built on Bun and
fastmcp. - 🔒 Type-Safe: End-to-end TypeScript with Zod schema validation.
- 🐘 Multi-Database Support: Connect to and manage interactions across several PostgreSQL instances defined in
.env. - 🛡️ Secure by Design: Parameterized queries via
postgresprevent SQL injection. - 🔑 Optional Authentication: Secure network-based connections (SSE/HTTP) using API Key validation (
fastmcp'sauthenticatehook). - 📄 Database Schema via MCP Resources:
- List Tables: Get a list of tables in a database via
db://{dbAlias}/schema/tables. - Inspect Table Schema: Get detailed column info for a specific table via
db://{dbAlias}/schema/{tableName}.
- List Tables: Get a list of tables in a database via
- 💬 Enhanced Tool Interaction:
- In-Tool Logging: Tools send detailed logs back to the client (
logcontext). - Progress Reporting: Long-running operations report progress (
reportProgresscontext).
- In-Tool Logging: Tools send detailed logs back to the client (
- 🧠 Session-Aware: Access session information within tool execution context (
sessioncontext). - 📡 Event-Driven: Uses
server.onandsession.onfor connection/session event handling. - 🔧 Modern Developer Experience (DX): Clear configuration, intuitive API, easy testing with
fastmcptools.
What's Included (fastmcp Features Leveraged)
FastMCPServer Coreserver.addTool(forquery_tool,execute_tool,schema_tool, andtransaction_tool)server.addResourceTemplate(for listing tables and inspecting table schemas)server.start(withstdiofocus, adaptable forsse/http)- Optional:
authenticateHook (for API Key validation) - Tool Execution
context(log,reportProgress,session) - Zod for Parameter Schema Validation
server.on(for connection logging)- (Potentially)
session.onfor session-specific logic
📋 Prerequisites
- Bun (v1.0 or later recommended): Installed and in PATH.
- PostgreSQL Database(s): Access credentials and connectivity. User needs permissions to query
information_schema.
⚙️ Installation
Option 1: NPM Package
# Install globally
npm install -g postgres-mcp
# Or install locally in your project
npm install postgres-mcp
The npm package is available at https://www.npmjs.com/package/postgres-mcp
Option 2: Clone Repository
-
Clone the repository:
# Replace with your actual repository URL git clone https://github.com/llm-graph/postgres-mcp.git cd postgres-mcp -
Install dependencies:
bun install
🔑 Configuration (Multi-Database & Optional Auth)
Configure via environment variables, loaded from appropriate .env files.
-
Create environment files:
- For production:
cp .env.example .env - For development:
cp .env.development.example .env.development
- For production:
-
Environment file loading order: The server loads environment variables from files in the following order of priority:
.env.<NODE_ENV>(e.g.,.env.development,.env.production,.env.staging).env.local(for local overrides, not version controlled).env(default fallback)
This allows different configurations for different environments.
-
Edit the environment files to define database connections and authentication:
DB_ALIASES- Comma-separated list of unique DB aliasesDEFAULT_DB_ALIAS- Default alias if 'dbAlias' is omitted in tool calls- Database connection details for each alias (e.g.,
DB_MAIN_HOST,DB_REPORTING_HOST) - Optional API Key authentication (
ENABLE_AUTH,MCP_API_KEY)
# Example .env file - Key Variables
# REQUIRED: Comma-separated list of unique DB aliases
DB_ALIASES=main,reporting
# REQUIRED: Default alias if 'dbAlias' is omitted in tool calls
DEFAULT_DB_ALIAS=main
# OPTIONAL: Enable API Key auth (primarily for network transports)
ENABLE_AUTH=false
MCP_API_KEY=your_super_secret_api_key_here # CHANGE THIS
# Define DB connection details for each alias (DB_MAIN_*, DB_REPORTING_*, etc.)
DB_MAIN_HOST=localhost
DB_MAIN_PORT=5432
DB_MAIN_NAME=app_prod_db
DB_MAIN_USER=app_user
DB_MAIN_PASSWORD=app_secret_password
DB_MAIN_SSL=disable
# Alternative: Use connection URLs
# DB_MAIN_URL=postgres://user:password@localhost:5432/database?sslmode=require
# --- Optional: Server Logging Level ---
# LOG_LEVEL=info # debug, info, warn, error (defaults to info)
🚀 Running the Server (as a Process)
Run this server directly using Bun. The AI Client (like Cursor) will typically start and manage this command for you.
Option 1: Using the globally installed package
- To run manually:
postgres-mcp
Option 2: Using the package in your project
- To run from your project:
npx postgres-mcp - Or import programmatically:
// server.js import { startServer } from 'postgres-mcp'; // Start the MCP server startServer();
Option 3: From cloned repository
- To run manually (for testing):
bun run src/index.ts - Manual Development Mode:
bun run --watch src/index.ts
Testing with fastmcp CLI Tools
- Interactive Terminal:
bunx fastmcp dev src/index.ts - Web UI Inspector:
bunx fastmcp inspect src/index.ts
💻 Using the Programmatic API (as a Library)
In addition to running as a standalone MCP server, postgres-mcp can also be used programmatically as a library in your Node.js/TypeScript applications.
Basic Usage
import { createPostgresMcp } from 'postgres-mcp';
// Create the PostgresMcp instance
const postgresMcp = createPostgresMcp();
// Start the server
postgresMcp.start();
// Direct database operations
const results = await postgresMcp.executeQuery(
'SELECT * FROM users WHERE role = $1',
['admin'],
'main' // optional database alias
);
// When done, stop the server and close connections
await postgresMcp.stop();
Direct Function Imports
For simpler use cases, you can import specific functions directly:
import {
initConnections,
closeConnections,
executeQuery,
executeCommand,
executeTransaction,
getTableSchema,
getAllTableSchemas
} from 'postgres-mcp';
// Configure database connections
const dbConfigs = {
main: {
host: 'localhost',
port: 5432,
database: 'my_db',
user: 'db_user',
password: 'db_password'
}
};
// Initialize connections
initConnections(dbConfigs);
// Execute a query
const results = await executeQuery(
'SELECT * FROM users WHERE role = $1',
['admin'],
'main'
);
// Get schema for a single table
const schema = await getTableSchema('users', 'main');
// Get schema for all tables in the database
const allSchemas = await getAllTableSchemas('main');
// Close connections when done
await closeConnections();
Configuration Options
const postgresMcp = createPostgresMcp({
// Custom database configurations (override .env)
databaseConfigs: {
main: {
host: 'localhost',
port: 5432,
database: 'app_db',
user: 'app_user',
password: 'password',
ssl: 'disable'
}
},
// Server configuration
serverConfig: {
name: 'Custom PostgresMCP',
defaultDbAlias: 'main'
},
// Transport options: 'stdio', 'sse', or 'http'
transport: 'http',
port: 3456
});
For complete documentation on the programmatic API, see docs/programmatic-api.md.
🔌 Connecting with AI Clients (Cursor, Claude Desktop)
Configure your AI Agent (MCP Client) to execute this server script via its command/args mechanism.
Cursor AI - Detailed Example
-
Open Cursor Settings/Preferences (Cmd+, or Ctrl+,).
-
Navigate to "Extensions" -> "MCP".
-
Click "Add MCP Server" or edit
settings.json. -
Add the following JSON configuration:
// In Cursor's settings.json or MCP configuration UI { "mcpServers": { "postgres-mcp": { // Unique name for Cursor "description": "MCP Server for PostgreSQL DBs (Main, Reporting)", "command": "bunx", // Use 'bun' or provide absolute path: "/Users/your_username/.bun/bin/bun" "args": [ "postgres-mcp" // or // *** ABSOLUTE PATH to your server's entry point *** // "/Users/your_username/projects/postgres-mcp/src/index.ts" / ], "env": { // .env file in project dir is loaded automatically by Bun. // Add overrides or Cursor-specific vars here if needed. }, "enabled": true } } } -
Save and Restart Cursor or "Reload MCP Servers".
-
Verify connection in Cursor's MCP status/logs.
Claude Desktop
- Locate and edit
config.json(see previous README for paths). - Add a similar entry under
mcpServers, using the absolute path inargs. - Restart Claude Desktop.
🛠️ MCP Capabilities Exposed
Authentication (Optional)
- Secures network transports (HTTP/SSE) via
X-API-Keyheader matchingMCP_API_KEYifENABLE_AUTH=true. stdioconnections (default for Cursor/Claude) generally bypass this check.
Resources
1. List Database Tables
- URI Template:
db://{dbAlias}/schema/tables - Description: Retrieves a list of user table names within the specified database alias (typically from the 'public' schema).
- Resource Definition (
addResourceTemplate):uriTemplate:"db://{dbAlias}/schema/tables"arguments:dbAlias: (string, required) - Alias of the database (from.env).
load({ dbAlias }): Connects to the database, queriesinformation_schema.tables(filtered for base tables in the public schema, customizable in implementation), formats the result as a JSON string array["table1", "table2", ...], and returns{ text: "..." }.
Example Usage (AI Prompt): "Get the resource db://main/schema/tables to list tables in the main database."
2. Inspect Table Schema
- URI Template:
db://{dbAlias}/schema/{tableName} - Description: Provides detailed schema information (columns, types, nullability, defaults) for a specific table.
- Resource Definition (
addResourceTemplate):uriTemplate:"db://{dbAlias}/schema/{tableName}"arguments:dbAlias: (string, required) - Database alias.tableName: (string, required) - Name of the table.
load({ dbAlias, tableName }): Connects, queriesinformation_schema.columnsfor the specific table, formats as JSON string array of column objects, returns{ text: "..." }.
Example Usage (AI Prompt): "Describe the resource db://reporting/schema/daily_sales."
Example Response Content (JSON String):
"[{\"column_name\":\"session_id\",\"data_type\":\"uuid\",\"is_nullable\":\"NO\",\"column_default\":\"gen_random_uuid()\"},{\"column_name\":\"user_id\",\"data_type\":\"integer\",\"is_nullable\":\"NO\",\"column_default\":null},{\"column_name\":\"created_at\",\"data_type\":\"timestamp with time zone\",\"is_nullable\":\"YES\",\"column_default\":\"now()\"},{\"column_name\":\"expires_at\",\"data_type\":\"timestamp with time zone\",\"is_nullable\":\"YES\",\"column_default\":null}]"
Tools
Tools receive context object (log, reportProgress, session).
1. query_tool
Executes read-only SQL queries.
- Description: Safely execute read-only SQL, get results, with execution logging/progress.
- Parameters:
statement(string),params(array, opt),dbAlias(string, opt). - Context Usage:
log.info/debug, optionalreportProgress, accesssession. - Returns: JSON string of the row array.
Example Request:
{
"tool_name": "query_tool",
"arguments": {
"statement": "SELECT product_id, name, price FROM products WHERE category = $1 AND price < $2 ORDER BY name LIMIT 10",
"params": ["electronics", 500],
"dbAlias": "main"
}
}
Example Response Content (JSON String):
"[{\"product_id\":123,\"name\":\"Example Gadget\",\"price\":499.99},{\"product_id\":456,\"name\":\"Another Device\",\"price\":350.00}]"
2. execute_tool
Executes data-modifying SQL statements.
- Description: Safely execute data-modifying SQL, with execution logging.
- Parameters:
statement(string),params(array, opt),dbAlias(string, opt). - Context Usage:
log.info/debug, accesssession. - Returns: String indicating rows affected.
Example Request:
{
"tool_name": "execute_tool",
"arguments": {
"statement": "UPDATE users SET last_login = NOW() WHERE user_id = $1",
"params": [54321]
// dbAlias omitted, uses DEFAULT_DB_ALIAS
}
}
Example Response Content (String):
"Rows affected: 1"
3. schema_tool
Retrieves detailed schema information for a specific table.
- Description: Get column definitions and details for a database table.
- Parameters:
tableName(string),dbAlias(string, opt). - Context Usage:
log.info, accesssession. - Returns: JSON string array of column information objects.
Example Request:
{
"tool_name": "schema_tool",
"arguments": {
"tableName": "user_sessions",
"dbAlias": "main"
}
}
Example Response Content (JSON String):
"[{\"column_name\":\"session_id\",\"data_type\":\"uuid\",\"is_nullable\":\"NO\",\"column_default\":\"gen_random_uuid()\"},{\"column_name\":\"user_id\",\"data_type\":\"integer\",\"is_nullable\":\"NO\",\"column_default\":null},{\"column_name\":\"created_at\",\"data_type\":\"timestamp with time zone\",\"is_nullable\":\"YES\",\"column_default\":\"now()\"},{\"column_name\":\"expires_at\",\"data_type\":\"timestamp with time zone\",\"is_nullable\":\"YES\",\"column_default\":null}]"
4. transaction_tool
Executes multiple SQL statements atomically.
- Description: Execute SQL sequence in a transaction, with step logging/progress.
- Parameters:
operations(array of {statement, params}),dbAlias(string, opt). - Context Usage:
log.info/debug/error,reportProgress, accesssession. - Returns: JSON string summarizing success/failure:
{"success": true, "results": [...]}or{"success": false, "error": ..., "failedOperationIndex": ...}.
Example Request:
{
"tool_name": "transaction_tool",
"arguments": {
"operations": [
{
"statement": "INSERT INTO orders (customer_id, order_date, status) VALUES ($1, NOW(), 'pending') RETURNING order_id",
"params": [101]
},
{
"statement": "INSERT INTO order_items (order_id, product_sku, quantity, price) VALUES ($1, $2, $3, $4)",
"params": [9999, "GADGET-X", 2, 49.99]
},
{
"statement": "UPDATE inventory SET stock_count = stock_count - $1 WHERE product_sku = $2 AND stock_count >= $1",
"params": [2, "GADGET-X"]
}
],
"dbAlias": "main"
}
}
Example Success Response Content (JSON String):
"{\"success\":true,\"results\":[{\"operation\":0,\"rowsAffected\":1},{\"operation\":1,\"rowsAffected\":1},{\"operation\":2,\"rowsAffected\":1}]}"
Example Error Response Content (JSON String):
"{\"success\":false,\"error\":\"Error executing operation 2: new row for relation \\\"inventory\\\" violates check constraint \\\"stock_count_non_negative\\\"\",\"failedOperationIndex\":2}"
Server & Session Events
- Uses
server.on('connect'/'disconnect')for logging client connections. - Can use
session.on(...)for more granular session event handling if needed.
🔒 Security Considerations
- SQL Injection: Mitigated via parameterized queries. No direct input concatenation.
- Database Permissions: Critical. Assign least privilege to each
DB_<ALIAS>_USER, including read access toinformation_schemafor schema/table listing resources. - SSL/TLS: Essential for production (
DB_<ALIAS>_SSL=requireor stricter). - Secrets Management: Protect
.envfile (add to.gitignore). Use secure secret management for production environments (Vault, Doppler, cloud secrets). - Authentication Scope:
authenticatehook primarily secures network transports.stdiosecurity relies on the execution environment. - Data Sensitivity: Be aware of data accessible via connections/tools.
- Resource Queries: The queries used for listing tables (
information_schema.tables) and schemas (information_schema.columns) are generally safe but rely on database permissions. Ensure the configured users have appropriate read access. Customize the table listing query (e.g., schema filtering) if needed for security or clarity.
📜 License
This project is licensed under the MIT License. See the LICENSE file for details.
📋 Changelog
1.0.0
- Initial release
- Full-featured MCP Server for PostgreSQL
- Support for multiple database connections
- Tools for queries, execution, schema inspection, and transactions
- Resources for schema introspection
- Comprehensive documentation and examples