@devmels/postgres-db-mcp

A Model Context Protocol (MCP) server that provides PostgreSQL database operations for Large Language Models (LLMs). This server allows LLMs to fetch database schema information, execute read queries, and perform data modifications safely.

Features

• Database Schema Discovery: Retrieve tables, columns, and foreign key relationships
• Safe Query Execution: Execute SELECT queries for data retrieval
• Data Modification: Perform INSERT, UPDATE, and DELETE operations
• Type Safety: Built with TypeScript and Zod validation
• Environment Configuration: Secure database connection management

Installation

From npm (Recommended)

npm install -g @devmels/postgres-db-mcp

From source

git clone https://github.com/devtitus/postgres-db-mcp.git
cd postgres-db-mcp
npm install
npm run build

Configuration

Environment Variables

Create a .env file in your project root or set environment variables:

DATABASE_URL=postgresql://username:password@localhost:5432/your_database

Required Environment Variables:

• DATABASE_URL: PostgreSQL connection string

MCP Server Configuration

Add the server to your MCP settings configuration file:

For npm-installed package:

{
  "mcpServers": {
    "postgres-db": {
      "command": "npx",
      "args": ["-y", "@devmels/postgres-db-mcp"],
      "env": {
        "DATABASE_URL": "postgresql://username:password@localhost:5432/your_database"
      }
    }
  }
}

For local development:

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

Available Tools

1. get_database_context

Returns comprehensive database schema information including tables, columns, and relationships.

Parameters: None

Example Usage:

Get the database schema and relationships

2. run_read_query

Executes SELECT queries to retrieve data from the database.

Parameters:

• sql (string): The SELECT SQL query to execute

Example Usage:

SELECT * FROM users WHERE active = true LIMIT 10;

3. run_write_query

Executes INSERT, UPDATE, or DELETE queries to modify database data.

Parameters:

• sql (string): The SQL query to execute (INSERT, UPDATE, DELETE only)

Example Usage:

INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');

Usage Examples

Getting Database Context

Can you show me the structure of my PostgreSQL database?
What tables and relationships exist in my database?

Reading Data

Show me the first 10 users from the users table
Get all active orders from the orders table

Modifying Data

Add a new user with name 'Jane Smith' and email '[email protected]'
Update the status of order #123 to 'completed'
Delete inactive user accounts older than 1 year

Security Considerations

• Read-Only Operations: Use run_read_query for safe data retrieval
• Write Operations: run_write_query should be used carefully as it modifies data
• Input Validation: All queries are validated before execution
• Connection Security: Use secure connection strings with proper authentication

Development

Building from Source

npm run build

Running Locally

npm start

Testing

npm test

Requirements

• Node.js >= 18.0.0
• PostgreSQL database
• MCP-compatible client (Claude Desktop, VS Code extensions, etc.)

License

ISC License - see LICENSE file for details

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Submit a pull request

Support

For issues and questions:

• GitHub Issues: https://github.com/devtitus/postgres-db-mcp/issues
• Email: [email protected]

Changelog

v1.0.0

• Initial release
• Database schema discovery
• Read and write query execution
• TypeScript support
• MCP SDK integration