Uses Docker for running test containers, enabling comprehensive testing with real PostgreSQL database instances.
Supports configuration through environment variables using .env files, allowing users to easily configure database connection details and other settings.
Uses npm for package management, installation, and running scripts for development, testing, and production builds.
Provides AI assistants with secure, structured access to PostgreSQL databases. Includes tools for querying, inserting, updating, and deleting data with schema introspection, filtering, pagination, sorting, and safety features.
Built with TypeScript, providing type safety and better code organization for the MCP server implementation.
Includes comprehensive testing using Vitest in combination with Testcontainers for real PostgreSQL database testing.
Implements input validation with Zod schemas for comprehensive error handling and type safety.
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 querycolumns
(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 namesinclude_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 queryexplain
(boolean, optional): Include execution plan (default: false)
insert-data
Insert new records into a table.
Parameters:
table
(string, required): Target table namedata
(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 conflictsreturning
(string[], optional): Columns to return (default: ["*"])
update-data
Update existing records in a table.
Parameters:
table
(string, required): Target table namedata
(object, required): Data to updatewhere
(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 namewhere
(object, required): WHERE conditions (required for safety)confirm_delete
(boolean, optional): Bypass confirmation for large deletesreturning
(string[], optional): Columns to return from deleted records
get-table-info
Get detailed information about a specific table.
Parameters:
table
(string, required): Table nameschema_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
Parameterized Queries: All SQL operations use parameter binding to prevent injection attacks
Identifier Validation: Table and column names are validated against PostgreSQL naming rules
Required WHERE Clauses: UPDATE and DELETE operations require WHERE conditions for safety
Large Operation Warnings: Confirmation required for operations affecting >100 rows
Connection Security: Use SSL connections in production environments
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:
Database server not running: Ensure PostgreSQL is running and accessible
Invalid credentials: Check username, password, and database name in your configuration
Network connectivity: Verify host, port, and firewall settings
SSL/TLS issues: Check SSL configuration for production environments
Connection string format: Ensure DATABASE_URL follows the correct format
To diagnose and fix connection issues:
Check connection status:
Use the connection-status tool to see current status and error detailsVerify configuration:
# Check your .env file or environment variables echo $DATABASE_URL # Should look like: postgresql://username:password@host:port/databaseTest manually:
# Test connection with psql psql $DATABASE_URL -c "SELECT 1;"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 retryOnce connection is restored (via retry), all tools resume normal operation
Contributing
Follow the existing code patterns
Add proper TypeScript types
Include error handling
Test with a real PostgreSQL database
Update documentation as needed
License
MIT License
Tools
A TypeScript-based Model Context Protocol server that enables AI assistants to perform secure database operations on PostgreSQL databases through structured tool interfaces.
Related MCP Servers
- -securityAlicense-qualityA Model Context Protocol server implementation that enables AI assistants to execute SQL queries and interact with SQLite databases through a structured interface.Last updated -7MIT License
- -securityFlicense-qualityA TypeScript implementation of a Model Context Protocol server that enables language models to securely query PostgreSQL databases, including those behind SSH bastion tunnels.Last updated -2
- -securityAlicense-qualityA Model Context Protocol server that provides AI assistants with comprehensive access to SQL databases, enabling schema inspection, query execution, and database operations with enterprise-grade security.Last updated -12MIT License
- -securityFlicense-qualityA Model Context Protocol server that provides AI assistants with secure, read-only access to PostgreSQL databases while offering comprehensive tools for schema exploration, query validation, and performance optimization.Last updated -