PostgreSQL MCP Server

PostgreSQL MCP Server

A TypeScript-based Model Context Protocol (MCP) server that provides AI assistants with secure, structured access to PostgreSQL databases.

Features

• Safe Database Operations: All queries use parameterized statements to prevent SQL injection
• Comprehensive Tools: Query, insert, update, delete data with full schema introspection
• Flexible Querying: Support for filtering, pagination, sorting, and complex WHERE conditions
• Schema Discovery: Get detailed information about tables, columns, constraints, and indexes
• Connection Pooling: Efficient database connection management
• Error Handling: Comprehensive error reporting without exposing sensitive information
• Safety Checks: Required WHERE clauses for updates/deletes, confirmation for large operations

Tools Available

query-table

Query data from a specific table with filtering, pagination, and sorting.

Parameters:

• table (string, required): Table name to query
• columns (string[], optional): Specific columns to select (default: all)
• where (object, optional): WHERE conditions (supports equality, arrays for IN, wildcards for LIKE)
• pagination (object, optional): {limit: number, offset: number}
• sort (object, optional): {column: string, direction: "ASC"|"DESC"}

get-schema

Get database schema information including tables, columns, and constraints.

Parameters:

• schema_name (string, optional): Schema to inspect (default: "public")
• table_pattern (string, optional): LIKE pattern for table names
• include_columns (boolean, optional): Include column details (default: true)
• include_constraints (boolean, optional): Include constraint details (default: false)

execute-query

Execute a parameterized SQL query with safety checks.

Parameters:

• query (string, required): SQL query with parameter placeholders ($1, $2, etc.)
• params (any[], optional): Parameters for the query
• explain (boolean, optional): Include execution plan (default: false)

insert-data

Insert new records into a table.

Parameters:

• table (string, required): Target table name
• data (object|object[], required): Data to insert (single record or array)
• on_conflict (string, optional): Conflict resolution: "error", "ignore", "update" (default: "error")
• conflict_columns (string[], optional): Columns to check for conflicts
• returning (string[], optional): Columns to return (default: ["*"])

update-data

Update existing records in a table.

Parameters:

• table (string, required): Target table name
• data (object, required): Data to update
• where (object, required): WHERE conditions (required for safety)
• returning (string[], optional): Columns to return (default: ["*"])

delete-data

Delete records from a table.

Parameters:

• table (string, required): Target table name
• where (object, required): WHERE conditions (required for safety)
• confirm_delete (boolean, optional): Bypass confirmation for large deletes
• returning (string[], optional): Columns to return from deleted records

get-table-info

Get detailed information about a specific table.

Parameters:

• table (string, required): Table name
• schema_name (string, optional): Schema name (default: "public")
• include_statistics (boolean, optional): Include size and row count stats (default: true)

connection-status

Check database connection status, view error details, and retry connection.

Parameters:

• retry (boolean, optional): Attempt to reconnect if connection is currently failed (default: false)

Returns:

• Current connection status ("connected", "failed", or "unknown")
• Error details if connection failed
• Last connection attempt timestamp
• Troubleshooting information for failed connections
• Result of retry attempt if retry was requested

Installation

Configuration

Create a .env file based on .env.example:

Set your PostgreSQL connection details:

Usage

Development

Production

As an MCP Server

Add to your MCP client configuration (e.g., Claude Desktop):

Security Considerations

1. Parameterized Queries: All SQL operations use parameter binding to prevent injection attacks
2. Identifier Validation: Table and column names are validated against PostgreSQL naming rules
3. Required WHERE Clauses: UPDATE and DELETE operations require WHERE conditions for safety
4. Large Operation Warnings: Confirmation required for operations affecting >100 rows
5. Connection Security: Use SSL connections in production environments
6. Access Control: Configure database-level permissions appropriately

Database Permissions

The database user should have appropriate permissions for the operations you want to allow:

Example Usage

Once connected through an MCP client:

Development

Testing

This project includes comprehensive tests using Vitest and Testcontainers for real PostgreSQL database testing.

Prerequisites:

• Docker must be installed and running (for testcontainers)

Run all tests:

Run tests in watch mode:

Type Checking

Building

Architecture

• index.ts: Main server entry point and tool registration
• tools/utils.ts: Shared utilities, database connection, and helper functions
• tools/*.ts: Individual tool implementations
• tests/: Comprehensive test suite
• tsup.config.ts: Build configuration
• tsconfig.json: TypeScript configuration
• vitest.config.ts: Test configuration

Error Handling

All tools include comprehensive error handling:

• Input validation with Zod schemas
• Database connection error handling
• SQL execution error handling
• Graceful error responses to MCP clients
• Graceful startup: Server starts even if database is unavailable
• Connection recovery: Ability to retry connections without restarting

Connection Troubleshooting

If the database connection fails at startup or during operation, the server will continue running and provide helpful error information through the connection-status tool.

Common connection issues:

1. Database server not running: Ensure PostgreSQL is running and accessible
2. Invalid credentials: Check username, password, and database name in your configuration
3. Network connectivity: Verify host, port, and firewall settings
4. SSL/TLS issues: Check SSL configuration for production environments
5. Connection string format: Ensure DATABASE_URL follows the correct format

To diagnose and fix connection issues:

1. Check connection status:
   Use the connection-status tool to see current status and error details
2. Verify configuration:
   # Check your .env file or environment variables echo $DATABASE_URL # Should look like: postgresql://username:password@host:port/database
3. Test manually:
   # Test connection with psql psql $DATABASE_URL -c "SELECT 1;"
4. Retry connection:
   Use the connection-status tool with retry: true to attempt reconnection

Graceful degradation:

• If database connection fails, all database tools will return helpful error messages
• Error messages include specific troubleshooting steps
• Tools automatically guide users to use connection-status for diagnosis and retry
• Once connection is restored (via retry), all tools resume normal operation

Contributing

1. Follow the existing code patterns
2. Add proper TypeScript types
3. Include error handling
4. Test with a real PostgreSQL database
5. Update documentation as needed

License

MIT License