# Directory Structure
```
├── .gitignore
├── LICENSE
├── package-lock.json
├── package.json
├── README.md
├── smithery.yaml
├── src
│ └── index.ts
└── tsconfig.json
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
node_modules/
build/
.env
*.log
.DS_Store
.idea/
.vscode/
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# MCP PostgreSQL Server
A Model Context Protocol server that provides PostgreSQL database operations. This server enables AI models to interact with PostgreSQL databases through a standardized interface.
## Installation
### Manual Installation
```bash
npm install mcp-postgres-server
```
Or run directly with:
```bash
npx mcp-postgres-server
```
## Configuration
The server requires the following environment variables:
```json
{
"mcpServers": {
"postgres": {
"type": "stdio",
"command": "npx",
"args": ["-y", "mcp-postgres-server"],
"env": {
"PG_HOST": "your_host",
"PG_PORT": "5432",
"PG_USER": "your_user",
"PG_PASSWORD": "your_password",
"PG_DATABASE": "your_database"
}
}
}
}
```
## Available Tools
### 1. connect_db
Establish connection to PostgreSQL database using provided credentials.
```javascript
use_mcp_tool({
server_name: "postgres",
tool_name: "connect_db",
arguments: {
host: "localhost",
port: 5432,
user: "your_user",
password: "your_password",
database: "your_database"
}
});
```
### 2. query
Execute SELECT queries with optional prepared statement parameters. Supports both PostgreSQL-style ($1, $2) and MySQL-style (?) parameter placeholders.
```javascript
use_mcp_tool({
server_name: "postgres",
tool_name: "query",
arguments: {
sql: "SELECT * FROM users WHERE id = $1",
params: [1]
}
});
```
### 3. execute
Execute INSERT, UPDATE, or DELETE queries with optional prepared statement parameters. Supports both PostgreSQL-style ($1, $2) and MySQL-style (?) parameter placeholders.
```javascript
use_mcp_tool({
server_name: "postgres",
tool_name: "execute",
arguments: {
sql: "INSERT INTO users (name, email) VALUES ($1, $2)",
params: ["John Doe", "[email protected]"]
}
});
```
### 4. list_schemas
List all schemas in the connected database.
```javascript
use_mcp_tool({
server_name: "postgres",
tool_name: "list_schemas",
arguments: {}
});
```
### 5. list_tables
List tables in the connected database. Accepts an optional schema parameter (defaults to 'public').
```javascript
// List tables in the 'public' schema (default)
use_mcp_tool({
server_name: "postgres",
tool_name: "list_tables",
arguments: {}
});
// List tables in a specific schema
use_mcp_tool({
server_name: "postgres",
tool_name: "list_tables",
arguments: {
schema: "my_schema"
}
});
```
### 6. describe_table
Get the structure of a specific table. Accepts an optional schema parameter (defaults to 'public').
```javascript
// Describe a table in the 'public' schema (default)
use_mcp_tool({
server_name: "postgres",
tool_name: "describe_table",
arguments: {
table: "users"
}
});
// Describe a table in a specific schema
use_mcp_tool({
server_name: "postgres",
tool_name: "describe_table",
arguments: {
table: "users",
schema: "my_schema"
}
});
```
## Features
* Secure connection handling with automatic cleanup
* Prepared statement support for query parameters
* Support for both PostgreSQL-style ($1, $2) and MySQL-style (?) parameter placeholders
* Comprehensive error handling and validation
* TypeScript support
* Automatic connection management
* Supports PostgreSQL-specific syntax and features
* Multi-schema support for database operations
## Security
* Uses prepared statements to prevent SQL injection
* Supports secure password handling through environment variables
* Validates queries before execution
* Automatically closes connections when done
## Error Handling
The server provides detailed error messages for common issues:
* Connection failures
* Invalid queries
* Missing parameters
* Database errors
## License
MIT
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
{
"compilerOptions": {
"target": "ES2022",
"module": "Node16",
"moduleResolution": "Node16",
"outDir": "./build",
"rootDir": "./src",
"strict": true,
"esModuleInterop": true,
"skipLibCheck": true,
"forceConsistentCasingInFileNames": true
},
"include": ["src/**/*"],
"exclude": ["node_modules"]
}
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
{
"name": "mcp-postgres-server",
"version": "0.1.3",
"description": "A Model Context Protocol server for PostgreSQL database operations",
"type": "module",
"bin": {
"mcp-postgres": "./build/index.js"
},
"files": [
"build"
],
"scripts": {
"build": "tsc && node -e \"require('fs').chmodSync('build/index.js', '755')\"",
"prepare": "npm run build",
"watch": "tsc --watch",
"inspector": "npx @modelcontextprotocol/inspector build/index.js"
},
"keywords": [
"mcp",
"model-context-protocol",
"postgres",
"postgresql",
"database",
"claude",
"anthropic"
],
"author": "",
"license": "MIT",
"dependencies": {
"@modelcontextprotocol/sdk": "0.6.0",
"dotenv": "^16.4.7",
"pg": "^8.11.3"
},
"devDependencies": {
"@types/node": "^20.11.24",
"@types/pg": "^8.10.7",
"typescript": "^5.3.3"
},
"repository": {
"type": "git",
"url": "https://github.com/antonorlov/mcp-postgres-server.git"
},
"publishConfig": {
"access": "public"
}
}
```
--------------------------------------------------------------------------------
/smithery.yaml:
--------------------------------------------------------------------------------
```yaml
# Smithery configuration file: https://smithery.ai/docs/config#smitheryyaml
startCommand:
type: stdio
configSchema:
# JSON Schema defining the configuration options for the MCP.
type: object
required:
- pgHost
- pgUser
- pgPassword
- pgDatabase
properties:
pgHost:
type: string
description: Hostname for the PostgreSQL server
pgPort:
type: number
description: Port for the PostgreSQL server
default: 5432
pgUser:
type: string
description: PostgreSQL user name
pgPassword:
type: string
description: PostgreSQL user's password
pgDatabase:
type: string
description: PostgreSQL database name
commandFunction:
# A JS function that produces the CLI command based on the given config to start the MCP on stdio.
|-
(config) => ({
command: 'node',
args: ['build/index.js'],
env: {
PG_HOST: config.pgHost,
PG_PORT: config.pgPort || 5432,
PG_USER: config.pgUser,
PG_PASSWORD: config.pgPassword,
PG_DATABASE: config.pgDatabase
}
})
exampleConfig:
pgHost: localhost
pgPort: 5432
pgUser: example_user
pgPassword: example_password
pgDatabase: example_db
```
--------------------------------------------------------------------------------
/src/index.ts:
--------------------------------------------------------------------------------
```typescript
#!/usr/bin/env node
import { Server } from '@modelcontextprotocol/sdk/server/index.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import {
CallToolRequestSchema,
ErrorCode,
ListToolsRequestSchema,
McpError,
} from '@modelcontextprotocol/sdk/types.js';
import pg from 'pg';
const { Client } = pg;
import { config } from 'dotenv';
// Load environment variables
config();
interface DatabaseConfig {
host: string;
port: number;
user: string;
password: string;
database: string;
}
// Type guard for error objects
function isErrorWithMessage(error: unknown): error is { message: string } {
return (
typeof error === 'object' &&
error !== null &&
'message' in error &&
typeof (error as Record<string, unknown>).message === 'string'
);
}
// Helper to get error message
function getErrorMessage(error: unknown): string {
if (isErrorWithMessage(error)) {
return error.message;
}
return String(error);
}
// Helper to convert ? parameters to $1, $2, etc.
function convertToNamedParams(query: string): string {
let paramIndex = 0;
return query.replace(/\?/g, () => `$${++paramIndex}`);
}
class PostgresServer {
private server: Server;
private client: pg.Client | null = null;
private config: DatabaseConfig | null = null;
constructor() {
this.server = new Server(
{
name: 'postgres-server',
version: '1.0.0',
},
{
capabilities: {
tools: {},
},
}
);
this.setupToolHandlers();
// Error handling
this.server.onerror = (error) => console.error('[MCP Error]', error);
const handleTermination = async () => {
try {
await this.cleanup();
} catch (error) {
console.error('Error during cleanup:', error);
process.exit(1);
}
process.exit(0);
};
process.on('SIGINT', handleTermination);
process.stdin.on('close', handleTermination);
}
private async cleanup() {
if (this.client) {
await this.client.end();
}
await this.server.close();
}
private async ensureConnection() {
if (!this.config) {
// Try to use environment variables if no explicit config was provided
const envConfig = this.getEnvConfig();
if (envConfig) {
this.config = envConfig;
console.error('[MCP Info] Using database config from environment variables');
} else {
throw new McpError(
ErrorCode.InvalidRequest,
'Database configuration not set. Use connect_db tool first or set environment variables.'
);
}
}
if (!this.client) {
try {
this.client = new Client(this.config);
await this.client.connect();
} catch (error) {
throw new McpError(
ErrorCode.InternalError,
`Failed to connect to database: ${getErrorMessage(error)}`
);
}
}
}
private getEnvConfig(): DatabaseConfig | null {
const { PG_HOST, PG_USER, PG_PASSWORD, PG_DATABASE, PG_PORT } = process.env;
if (PG_HOST && PG_USER && PG_PASSWORD && PG_DATABASE) {
return {
host: PG_HOST,
port: PG_PORT ? parseInt(PG_PORT, 10) : 5432,
user: PG_USER,
password: PG_PASSWORD,
database: PG_DATABASE
};
}
return null;
}
private setupToolHandlers() {
this.server.setRequestHandler(ListToolsRequestSchema, async () => ({
tools: [
{
name: 'connect_db',
description: 'Connect to PostgreSQL database. NOTE: Default connection exists - only use when requested or if other commands fail',
inputSchema: {
type: 'object',
properties: {
host: {
type: 'string',
description: 'Database host',
},
port: {
type: 'number',
description: 'Database port (default: 5432)',
},
user: {
type: 'string',
description: 'Database user',
},
password: {
type: 'string',
description: 'Database password',
},
database: {
type: 'string',
description: 'Database name',
},
},
required: ['host', 'user', 'password', 'database'],
},
},
{
name: 'query',
description: 'Execute a SELECT query',
inputSchema: {
type: 'object',
properties: {
sql: {
type: 'string',
description: 'SQL SELECT query (use $1, $2, etc. for parameters)',
},
params: {
type: 'array',
items: {
type: ['string', 'number', 'boolean', 'null'],
},
description: 'Query parameters (optional)',
},
},
required: ['sql'],
},
},
{
name: 'execute',
description: 'Execute an INSERT, UPDATE, or DELETE query',
inputSchema: {
type: 'object',
properties: {
sql: {
type: 'string',
description: 'SQL query (INSERT, UPDATE, DELETE) (use $1, $2, etc. for parameters)',
},
params: {
type: 'array',
items: {
type: ['string', 'number', 'boolean', 'null'],
},
description: 'Query parameters (optional)',
},
},
required: ['sql'],
},
},
{
name: 'list_schemas',
description: 'List all schemas in the database',
inputSchema: {
type: 'object',
properties: {},
required: [],
},
},
{
name: 'list_tables',
description: 'List tables in the database',
inputSchema: {
type: 'object',
properties: {
schema: {
type: 'string',
description: 'Schema name (default: public)',
},
},
required: [],
},
},
{
name: 'describe_table',
description: 'Get table structure',
inputSchema: {
type: 'object',
properties: {
table: {
type: 'string',
description: 'Table name',
},
schema: {
type: 'string',
description: 'Schema name (default: public)',
},
},
required: ['table'],
},
},
],
}));
this.server.setRequestHandler(CallToolRequestSchema, async (request) => {
switch (request.params.name) {
case 'connect_db':
return await this.handleConnectDb(request.params.arguments);
case 'query':
return await this.handleQuery(request.params.arguments);
case 'execute':
return await this.handleExecute(request.params.arguments);
case 'list_schemas':
return await this.handleListSchemas();
case 'list_tables':
return await this.handleListTables(request.params.arguments);
case 'describe_table':
return await this.handleDescribeTable(request.params.arguments);
default:
throw new McpError(
ErrorCode.MethodNotFound,
`Unknown tool: ${request.params.name}`
);
}
});
}
private async handleConnectDb(args: any) {
if (!args.host || !args.user || !args.password || !args.database) {
throw new McpError(
ErrorCode.InvalidParams,
'Missing required database configuration parameters'
);
}
// Close existing connection if any
if (this.client) {
await this.client.end();
this.client = null;
}
this.config = {
host: args.host,
port: args.port || 5432,
user: args.user,
password: args.password,
database: args.database,
};
try {
await this.ensureConnection();
return {
content: [
{
type: 'text',
text: 'Successfully connected to PostgreSQL database',
},
],
};
} catch (error) {
throw new McpError(
ErrorCode.InternalError,
`Failed to connect to database: ${getErrorMessage(error)}`
);
}
}
private async handleQuery(args: any) {
await this.ensureConnection();
if (!args.sql) {
throw new McpError(ErrorCode.InvalidParams, 'SQL query is required');
}
if (!args.sql.trim().toUpperCase().startsWith('SELECT')) {
throw new McpError(
ErrorCode.InvalidParams,
'Only SELECT queries are allowed with query tool'
);
}
try {
// Convert ? parameters to $1, $2, etc. if needed
const sql = args.sql.includes('?') ? convertToNamedParams(args.sql) : args.sql;
const result = await this.client!.query(sql, args.params || []);
return {
content: [
{
type: 'text',
text: JSON.stringify(result.rows, null, 2),
},
],
};
} catch (error) {
throw new McpError(
ErrorCode.InternalError,
`Query execution failed: ${getErrorMessage(error)}`
);
}
}
private async handleExecute(args: any) {
await this.ensureConnection();
if (!args.sql) {
throw new McpError(ErrorCode.InvalidParams, 'SQL query is required');
}
const sql = args.sql.trim().toUpperCase();
if (sql.startsWith('SELECT')) {
throw new McpError(
ErrorCode.InvalidParams,
'Use query tool for SELECT statements'
);
}
try {
// Convert ? parameters to $1, $2, etc. if needed
const preparedSql = args.sql.includes('?') ? convertToNamedParams(args.sql) : args.sql;
const result = await this.client!.query(preparedSql, args.params || []);
return {
content: [
{
type: 'text',
text: JSON.stringify({
rowCount: result.rowCount,
command: result.command,
}, null, 2),
},
],
};
} catch (error) {
throw new McpError(
ErrorCode.InternalError,
`Query execution failed: ${getErrorMessage(error)}`
);
}
}
private async handleListSchemas() {
await this.ensureConnection();
try {
const result = await this.client!.query(`
SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name
`);
return {
content: [
{
type: 'text',
text: JSON.stringify(result.rows, null, 2),
},
],
};
} catch (error) {
throw new McpError(
ErrorCode.InternalError,
`Failed to list schemas: ${getErrorMessage(error)}`
);
}
}
private async handleListTables(args: any = {}) {
await this.ensureConnection();
const schema = args.schema || 'public';
try {
const result = await this.client!.query(`
SELECT table_name
FROM information_schema.tables
WHERE table_schema = $1
ORDER BY table_name
`, [schema]);
return {
content: [
{
type: 'text',
text: JSON.stringify(result.rows, null, 2),
},
],
};
} catch (error) {
throw new McpError(
ErrorCode.InternalError,
`Failed to list tables: ${getErrorMessage(error)}`
);
}
}
private async handleDescribeTable(args: any) {
await this.ensureConnection();
if (!args.table) {
throw new McpError(ErrorCode.InvalidParams, 'Table name is required');
}
const schema = args.schema || 'public';
try {
const result = await this.client!.query(`
SELECT
c.column_name,
c.data_type,
c.is_nullable,
c.column_default,
CASE
WHEN pk.constraint_type = 'PRIMARY KEY' THEN true
ELSE false
END AS is_primary_key,
c.character_maximum_length
FROM
information_schema.columns c
LEFT JOIN (
SELECT
tc.constraint_type,
kcu.column_name,
kcu.table_name,
kcu.table_schema
FROM
information_schema.table_constraints tc
JOIN
information_schema.key_column_usage kcu
ON
tc.constraint_name = kcu.constraint_name
WHERE
tc.constraint_type = 'PRIMARY KEY'
) pk
ON
c.column_name = pk.column_name
AND c.table_name = pk.table_name
AND c.table_schema = pk.table_schema
WHERE
c.table_schema = $1
AND c.table_name = $2
ORDER BY
c.ordinal_position
`, [schema, args.table]);
return {
content: [
{
type: 'text',
text: JSON.stringify(result.rows, null, 2),
},
],
};
} catch (error) {
throw new McpError(
ErrorCode.InternalError,
`Failed to describe table: ${getErrorMessage(error)}`
);
}
}
async run() {
const transport = new StdioServerTransport();
await this.server.connect(transport);
console.error('PostgreSQL MCP server running on stdio');
}
}
const server = new PostgresServer();
server.run().catch(console.error);
```