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

npm install

Configuration

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

cp .env.example .env

Set your PostgreSQL connection details:

# Required: PostgreSQL connection string
DATABASE_URL=postgresql://username:password@localhost:5432/database_name

# Optional: Individual connection parameters
# POSTGRES_HOST=localhost
# POSTGRES_PORT=5432
# POSTGRES_DB=database_name
# POSTGRES_USER=username
# POSTGRES_PASSWORD=password

# Optional: Environment and debugging
NODE_ENV=development
DEBUG=postgres-mcp*

# Optional: Connection pool settings
MAX_CONNECTIONS=20
QUERY_TIMEOUT=30000

Usage

Development

# Start in development mode with auto-reload
npm run dev

# Or start normally
npm start

Production

# Build the project
npm run build

# Run the built version
node dist/index.js

As an MCP Server

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

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/path/to/postgres-mcp/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://username:password@localhost:5432/database_name"
      }
    }
  }
}

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:

-- For read-only access
GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;

-- For full access
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_user;

-- For schema introspection
GRANT USAGE ON SCHEMA information_schema TO your_user;
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO your_user;

Example Usage

Once connected through an MCP client:

AI: Can you show me the structure of the users table?
Assistant: I'll get the table information for you.

[Uses get-table-info tool]

The users table has the following structure:
- id (integer, primary key)
- email (varchar, unique, not null)
- name (varchar)
- created_at (timestamp with time zone)
...

AI: Find all users created in the last 7 days
Assistant: I'll query the users table for recent records.

[Uses query-table tool with WHERE condition]

Found 15 users created in the last 7 days:
...

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:

npm test

Run tests in watch mode:

# Watch mode for development
npm run test:watch

Type Checking

npm run typecheck

Building

npm run build

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