Model Context Protocol PostgreSQL Server

This project implements a Model Context Protocol (MCP) server that connects to a PostgreSQL database. It allows AI models to interact with your database through a standardized protocol.

Features

• Connects to a PostgreSQL database using connection pooling
• Implements the Model Context Protocol for AI model interaction
• Provides database schema information as resources
• Allows executing SQL queries with retry logic
• Handles connection errors gracefully

Prerequisites

• Node.js 20 or higher
• PostgreSQL database
• Access credentials for the database

Installation

1. Clone this repository

2. Install dependencies:

npm install

Configuration

The server reads database credentials from a `.env` file in the project root directory. You need to add your database credentials as a JSON string in the `DB_CREDENTIALS` environment variable:

1. Create a `.env` file in the project root:

touch .env

2. Add the following line with your actual database credentials:

export DB_CREDENTIALS='{"DB_USER":"your-username","DB_PASSWORD":"your-password","DB_HOST":"your-host","DB_PORT":"5433","DB_NAME":"your-database"}'

Fallback to Shell Config Files

If the `.env` file is not present or the credentials variable is not found, the server will automatically look for the credentials in your shell configuration files in the following order:

1. `~/.zshrc`

2. `~/.bashrc`

3. `~/.bash_profile`

4. `~/.profile`

This is especially useful in environments where shell config files are not automatically sourced, such as the Cursor MCP environment.

To set up credentials in any of your shell config files:

1. Open your preferred shell config file, for example:

nano ~/.zshrc
# or
nano ~/.bashrc

2. Add the following line with your actual database credentials:

export DB_CREDENTIALS='{"DB_USER":"your-username","DB_PASSWORD":"your-password","DB_HOST":"your-host","DB_PORT":"5433","DB_NAME":"your-database"}'

The server will automatically detect and use these credentials when the `.env` file is not available.

Custom Credentials Variable

You can also use a custom environment variable name instead of `DB_CREDENTIALS` by using the `--credentials-var` flag when starting the server:

node server.js --credentials-var MY_CUSTOM_DB_CREDS

In this case, you would define `MY_CUSTOM_DB_CREDS` in your `.env` file instead.

Combining Options

You can combine different command-line options as needed:

# Use custom credentials and enable verbose mode
node server.js --credentials-var MY_CUSTOM_DB_CREDS --verbose

# Short form also works
node server.js -c MY_CUSTOM_DB_CREDS -v

Usage

Start the MCP server:

# Directly with Node.js
node server.js

# Or with npm
npm start

Logging Options

By default, the server runs in silent mode, displaying only error messages. If you want to see all log messages, you can use the verbose flag:

# With verbose logging
node server.js --verbose

# Or with npm
npm start -- --verbose

You can also use the short flag `-v`:

node server.js -v

The server will:

1. Test the database connection

2. Start the MCP server using stdio transport

3. Handle requests from AI models

Integration with Cursor

This server supports the Model Context Protocol (MCP) and integrates with Cursor AI.

Automatic Configuration

This project includes a pre-configured `.cursor/mcp.json` file for automatic setup within Cursor.

Manual Configuration

To manually add this server to Cursor:

1. Go to Cursor Settings → Features → MCP

2. Click "+ Add New MCP Server"

3. Enter the following details:

For more information on MCP integration with Cursor, see the official documentation.

Available Tools

The server provides the following tools to AI models:

• `query`: Execute SQL queries with retry logic

Resources

The server exposes database tables as resources, allowing AI models to:

• List all tables in the database
• View schema information for each table

Error Handling

The server includes:

• Connection retry logic
• Detailed error logging
• Graceful shutdown handling

Troubleshooting

Connection Issues

1. Database Connection Failed

2. Environment Variable Problems

3. Node.js Version Issues

Cursor Integration

1. Server Not Showing in Cursor

2. "Failed to create client" Error

3. No Tools Available in Cursor

PostgreSQL Specific Issues

1. Permission Denied Errors

2. "Relation does not exist" Errors

3. Performance Issues

For additional help, you can run the server with verbose logging (`-v` flag) to see detailed error messages and operation logs.

License

MIT