# PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides PostgreSQL database access to Claude Desktop. This server enables Claude to interact with your local PostgreSQL database through a secure, well-defined interface.

## Features

- **Query Execution**: Execute custom SQL queries with parameterization for security
- **Schema Inspection**: List tables, describe table structures, and get schema overviews
- **CRUD Operations**: Create, read, update, and delete records with structured interfaces
- **Security Features**: Input validation, parameterized queries, and connection pooling
- **Type Safety**: Full TypeScript implementation with Zod validation

## Available Tools

1. **postgres_query** - Execute custom SQL queries
2. **postgres_list_tables** - List all tables in the database
3. **postgres_describe_table** - Get detailed table information
4. **postgres_schema_overview** - Get complete database schema
5. **postgres_select** - Select data with filtering and pagination
6. **postgres_insert** - Insert new records
7. **postgres_update** - Update existing records
8. **postgres_delete** - Delete records

## Installation

1. Clone this repository:
   ```bash
   git clone <repository-url>
   cd postgres-mcp
   ```

2. Install dependencies:
   ```bash
   npm install
   ```

3. Build the project:
   ```bash
   npm run build
   ```

## Configuration

### PostgreSQL Setup

Ensure your PostgreSQL server is running on localhost:5432 with a database named `postgres`. The default configuration assumes you can connect without username/password (trust authentication).

### Claude Desktop Configuration

Add the following to your Claude Desktop configuration file:

**macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`
**Windows**: `%APPDATA%\\Claude\\claude_desktop_config.json`

```json
{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/path/to/postgres-mcp/dist/index.js"]
    }
  }
}
```

Replace `/path/to/postgres-mcp` with the actual path to your project directory.

## Usage

1. Start Claude Desktop
2. The PostgreSQL MCP server will automatically connect to your database
3. Use the available tools through Claude's interface

### Example Queries

- "Show me all tables in the database"
- "Describe the users table"
- "Select all users where age > 25"
- "Insert a new user with name 'John' and email 'john@example.com'"

## Security Considerations

- All queries use parameterized statements to prevent SQL injection
- Input validation using Zod schemas
- Connection pooling with timeouts
- No hardcoded credentials (uses environment/default connection)

## Development

```bash
# Development mode (watch for changes)
npm run dev

# Build
npm run build

# Lint
npm run lint

# Test
npm test
```

## Troubleshooting

1. **Connection Issues**: Ensure PostgreSQL is running and accessible
2. **Permission Errors**: Check database user permissions
3. **Tool Not Found**: Verify Claude Desktop configuration path

## License

MIT