PostgreSQL MCP Server

# PostgreSQL MCP Server A Model Context Protocol (MCP) server that provides access to PostgreSQL database schemas and metadata. This server allows Cursor and other MCP-compatible tools to explore your database structure, inspect table schemas, and understand relationships. ## Features - **List Tables**: Get all tables in a schema with basic information - **Describe Table**: Get detailed column information for any table - **Table Relationships**: Explore foreign key relationships - **List Schemas**: See all available schemas in your database - **Index Information**: View indexes for specific tables ## Prerequisites - Node.js 18+ - PostgreSQL database (local or remote) - npm or yarn package manager ## Installation 1. **Clone or download this repository** 2. **Install dependencies**: ```bash npm install ``` 3. **Configure database connection**: ```bash cp config.env.example .env ``` Edit the `.env` file with your PostgreSQL connection details: ```env DB_HOST=localhost DB_PORT=5432 DB_NAME=your_database_name DB_USER=your_username DB_PASSWORD=your_password ``` 4. **Test the server** (optional): ```bash npm start ``` ## Cursor Integration To use this MCP server with Cursor, you need to add it to your Cursor configuration. ### Step 1: Add to Cursor MCP Configuration Open Cursor and go to Settings → Features → Model Context Protocol, or edit your MCP configuration file directly. Add the following configuration: ```json { "mcpServers": { "postgres-schema": { "command": "node", "args": ["/absolute/path/to/pg_mcp/src/index.js"], "env": { "DB_HOST": "localhost", "DB_PORT": "5432", "DB_NAME": "your_database_name", "DB_USER": "your_username", "DB_PASSWORD": "your_password" } } } } ``` **Important**: Replace `/absolute/path/to/pg_mcp/src/index.js` with the actual absolute path to your `src/index.js` file. ### Step 2: Alternative Configuration (Using .env file) If you prefer to use the `.env` file for configuration: ```json { "mcpServers": { "postgres-schema": { "command": "node", "args": ["/absolute/path/to/pg_mcp/src/index.js"], "cwd": "/absolute/path/to/pg_mcp" } } } ``` ### Step 3: Restart Cursor After adding the configuration, restart Cursor to load the MCP server. ## Available Tools Once configured, you'll have access to these tools in Cursor: ### `list_tables` Lists all tables in a specified schema (defaults to 'public') ``` Parameters: - schema (optional): Schema name (default: "public") ``` ### `describe_table` Gets detailed schema information for a specific table ``` Parameters: - table_name (required): Name of the table to describe - schema (optional): Schema name (default: "public") ``` ### `get_table_relationships` Shows foreign key relationships for a table ``` Parameters: - table_name (required): Name of the table - schema (optional): Schema name (default: "public") ``` ### `list_schemas` Lists all available schemas in the database ``` No parameters required ``` ### `get_indexes` Shows indexes for a specific table ``` Parameters: - table_name (required): Name of the table - schema (optional): Schema name (default: "public") ``` ## Usage Examples Once configured in Cursor, you can ask questions like: - "What tables are in my database?" - "Show me the schema for the users table" - "What are the foreign key relationships for the orders table?" - "List all indexes on the products table" - "What schemas are available in my database?" ## Troubleshooting ### Connection Issues 1. **Database connection fails**: - Verify your database credentials in `.env` - Ensure PostgreSQL is running - Check if the database allows connections from your host 2. **MCP server not loading in Cursor**: - Verify the absolute path to `index.js` is correct - Check that Node.js is installed and accessible - Review Cursor's MCP logs for error messages 3. **Permission denied errors**: - Ensure the database user has read permissions on `information_schema` - Grant necessary SELECT permissions on system tables ### Common Error Messages - `Database connection failed`: Check your connection parameters - `Table not found`: Verify the table name and schema - `Permission denied`: Ensure your database user has appropriate read permissions ## Security Considerations - This server only reads database metadata (schema information) - No actual table data is accessed or exposed - Uses parameterized queries to prevent SQL injection - Database credentials are stored locally in your environment ## Development To run in development mode with auto-reload: ```bash npm run dev ``` ## License MIT License - see LICENSE file for details.