# Directory Structure
```
├── .gitignore
├── LICENSE
├── package-lock.json
├── package.json
├── README.md
├── smithery.yaml
├── src
│ └── index.ts
└── tsconfig.json
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | node_modules/
2 | build/
3 | .env
4 | *.log
5 | .DS_Store
6 | .idea/
7 | .vscode/
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # MCP PostgreSQL Server
2 |
3 | A Model Context Protocol server that provides PostgreSQL database operations. This server enables AI models to interact with PostgreSQL databases through a standardized interface.
4 |
5 | ## Installation
6 |
7 | ### Manual Installation
8 |
9 | ```bash
10 | npm install mcp-postgres-server
11 | ```
12 |
13 | Or run directly with:
14 |
15 | ```bash
16 | npx mcp-postgres-server
17 | ```
18 |
19 | ## Configuration
20 |
21 | The server requires the following environment variables:
22 |
23 | ```json
24 | {
25 | "mcpServers": {
26 | "postgres": {
27 | "type": "stdio",
28 | "command": "npx",
29 | "args": ["-y", "mcp-postgres-server"],
30 | "env": {
31 | "PG_HOST": "your_host",
32 | "PG_PORT": "5432",
33 | "PG_USER": "your_user",
34 | "PG_PASSWORD": "your_password",
35 | "PG_DATABASE": "your_database"
36 | }
37 | }
38 | }
39 | }
40 | ```
41 |
42 | ## Available Tools
43 |
44 | ### 1. connect_db
45 |
46 | Establish connection to PostgreSQL database using provided credentials.
47 |
48 | ```javascript
49 | use_mcp_tool({
50 | server_name: "postgres",
51 | tool_name: "connect_db",
52 | arguments: {
53 | host: "localhost",
54 | port: 5432,
55 | user: "your_user",
56 | password: "your_password",
57 | database: "your_database"
58 | }
59 | });
60 | ```
61 |
62 | ### 2. query
63 |
64 | Execute SELECT queries with optional prepared statement parameters. Supports both PostgreSQL-style ($1, $2) and MySQL-style (?) parameter placeholders.
65 |
66 | ```javascript
67 | use_mcp_tool({
68 | server_name: "postgres",
69 | tool_name: "query",
70 | arguments: {
71 | sql: "SELECT * FROM users WHERE id = $1",
72 | params: [1]
73 | }
74 | });
75 | ```
76 |
77 | ### 3. execute
78 |
79 | Execute INSERT, UPDATE, or DELETE queries with optional prepared statement parameters. Supports both PostgreSQL-style ($1, $2) and MySQL-style (?) parameter placeholders.
80 |
81 | ```javascript
82 | use_mcp_tool({
83 | server_name: "postgres",
84 | tool_name: "execute",
85 | arguments: {
86 | sql: "INSERT INTO users (name, email) VALUES ($1, $2)",
87 | params: ["John Doe", "[email protected]"]
88 | }
89 | });
90 | ```
91 |
92 | ### 4. list_schemas
93 |
94 | List all schemas in the connected database.
95 |
96 | ```javascript
97 | use_mcp_tool({
98 | server_name: "postgres",
99 | tool_name: "list_schemas",
100 | arguments: {}
101 | });
102 | ```
103 |
104 | ### 5. list_tables
105 |
106 | List tables in the connected database. Accepts an optional schema parameter (defaults to 'public').
107 |
108 | ```javascript
109 | // List tables in the 'public' schema (default)
110 | use_mcp_tool({
111 | server_name: "postgres",
112 | tool_name: "list_tables",
113 | arguments: {}
114 | });
115 |
116 | // List tables in a specific schema
117 | use_mcp_tool({
118 | server_name: "postgres",
119 | tool_name: "list_tables",
120 | arguments: {
121 | schema: "my_schema"
122 | }
123 | });
124 | ```
125 |
126 | ### 6. describe_table
127 |
128 | Get the structure of a specific table. Accepts an optional schema parameter (defaults to 'public').
129 |
130 | ```javascript
131 | // Describe a table in the 'public' schema (default)
132 | use_mcp_tool({
133 | server_name: "postgres",
134 | tool_name: "describe_table",
135 | arguments: {
136 | table: "users"
137 | }
138 | });
139 |
140 | // Describe a table in a specific schema
141 | use_mcp_tool({
142 | server_name: "postgres",
143 | tool_name: "describe_table",
144 | arguments: {
145 | table: "users",
146 | schema: "my_schema"
147 | }
148 | });
149 | ```
150 |
151 | ## Features
152 |
153 | * Secure connection handling with automatic cleanup
154 | * Prepared statement support for query parameters
155 | * Support for both PostgreSQL-style ($1, $2) and MySQL-style (?) parameter placeholders
156 | * Comprehensive error handling and validation
157 | * TypeScript support
158 | * Automatic connection management
159 | * Supports PostgreSQL-specific syntax and features
160 | * Multi-schema support for database operations
161 |
162 | ## Security
163 |
164 | * Uses prepared statements to prevent SQL injection
165 | * Supports secure password handling through environment variables
166 | * Validates queries before execution
167 | * Automatically closes connections when done
168 |
169 | ## Error Handling
170 |
171 | The server provides detailed error messages for common issues:
172 |
173 | * Connection failures
174 | * Invalid queries
175 | * Missing parameters
176 | * Database errors
177 |
178 | ## License
179 |
180 | MIT
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "compilerOptions": {
3 | "target": "ES2022",
4 | "module": "Node16",
5 | "moduleResolution": "Node16",
6 | "outDir": "./build",
7 | "rootDir": "./src",
8 | "strict": true,
9 | "esModuleInterop": true,
10 | "skipLibCheck": true,
11 | "forceConsistentCasingInFileNames": true
12 | },
13 | "include": ["src/**/*"],
14 | "exclude": ["node_modules"]
15 | }
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "name": "mcp-postgres-server",
3 | "version": "0.1.3",
4 | "description": "A Model Context Protocol server for PostgreSQL database operations",
5 | "type": "module",
6 | "bin": {
7 | "mcp-postgres": "./build/index.js"
8 | },
9 | "files": [
10 | "build"
11 | ],
12 | "scripts": {
13 | "build": "tsc && node -e \"require('fs').chmodSync('build/index.js', '755')\"",
14 | "prepare": "npm run build",
15 | "watch": "tsc --watch",
16 | "inspector": "npx @modelcontextprotocol/inspector build/index.js"
17 | },
18 | "keywords": [
19 | "mcp",
20 | "model-context-protocol",
21 | "postgres",
22 | "postgresql",
23 | "database",
24 | "claude",
25 | "anthropic"
26 | ],
27 | "author": "",
28 | "license": "MIT",
29 | "dependencies": {
30 | "@modelcontextprotocol/sdk": "0.6.0",
31 | "dotenv": "^16.4.7",
32 | "pg": "^8.11.3"
33 | },
34 | "devDependencies": {
35 | "@types/node": "^20.11.24",
36 | "@types/pg": "^8.10.7",
37 | "typescript": "^5.3.3"
38 | },
39 | "repository": {
40 | "type": "git",
41 | "url": "https://github.com/antonorlov/mcp-postgres-server.git"
42 | },
43 | "publishConfig": {
44 | "access": "public"
45 | }
46 | }
```
--------------------------------------------------------------------------------
/smithery.yaml:
--------------------------------------------------------------------------------
```yaml
1 | # Smithery configuration file: https://smithery.ai/docs/config#smitheryyaml
2 |
3 | startCommand:
4 | type: stdio
5 | configSchema:
6 | # JSON Schema defining the configuration options for the MCP.
7 | type: object
8 | required:
9 | - pgHost
10 | - pgUser
11 | - pgPassword
12 | - pgDatabase
13 | properties:
14 | pgHost:
15 | type: string
16 | description: Hostname for the PostgreSQL server
17 | pgPort:
18 | type: number
19 | description: Port for the PostgreSQL server
20 | default: 5432
21 | pgUser:
22 | type: string
23 | description: PostgreSQL user name
24 | pgPassword:
25 | type: string
26 | description: PostgreSQL user's password
27 | pgDatabase:
28 | type: string
29 | description: PostgreSQL database name
30 | commandFunction:
31 | # A JS function that produces the CLI command based on the given config to start the MCP on stdio.
32 | |-
33 | (config) => ({
34 | command: 'node',
35 | args: ['build/index.js'],
36 | env: {
37 | PG_HOST: config.pgHost,
38 | PG_PORT: config.pgPort || 5432,
39 | PG_USER: config.pgUser,
40 | PG_PASSWORD: config.pgPassword,
41 | PG_DATABASE: config.pgDatabase
42 | }
43 | })
44 | exampleConfig:
45 | pgHost: localhost
46 | pgPort: 5432
47 | pgUser: example_user
48 | pgPassword: example_password
49 | pgDatabase: example_db
```
--------------------------------------------------------------------------------
/src/index.ts:
--------------------------------------------------------------------------------
```typescript
1 | #!/usr/bin/env node
2 | import { Server } from '@modelcontextprotocol/sdk/server/index.js';
3 | import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
4 | import {
5 | CallToolRequestSchema,
6 | ErrorCode,
7 | ListToolsRequestSchema,
8 | McpError,
9 | } from '@modelcontextprotocol/sdk/types.js';
10 | import pg from 'pg';
11 | const { Client } = pg;
12 | import { config } from 'dotenv';
13 |
14 | // Load environment variables
15 | config();
16 |
17 | interface DatabaseConfig {
18 | host: string;
19 | port: number;
20 | user: string;
21 | password: string;
22 | database: string;
23 | }
24 |
25 | // Type guard for error objects
26 | function isErrorWithMessage(error: unknown): error is { message: string } {
27 | return (
28 | typeof error === 'object' &&
29 | error !== null &&
30 | 'message' in error &&
31 | typeof (error as Record<string, unknown>).message === 'string'
32 | );
33 | }
34 |
35 | // Helper to get error message
36 | function getErrorMessage(error: unknown): string {
37 | if (isErrorWithMessage(error)) {
38 | return error.message;
39 | }
40 | return String(error);
41 | }
42 |
43 | // Helper to convert ? parameters to $1, $2, etc.
44 | function convertToNamedParams(query: string): string {
45 | let paramIndex = 0;
46 | return query.replace(/\?/g, () => `$${++paramIndex}`);
47 | }
48 |
49 | class PostgresServer {
50 | private server: Server;
51 | private client: pg.Client | null = null;
52 | private config: DatabaseConfig | null = null;
53 |
54 | constructor() {
55 | this.server = new Server(
56 | {
57 | name: 'postgres-server',
58 | version: '1.0.0',
59 | },
60 | {
61 | capabilities: {
62 | tools: {},
63 | },
64 | }
65 | );
66 |
67 | this.setupToolHandlers();
68 |
69 | // Error handling
70 | this.server.onerror = (error) => console.error('[MCP Error]', error);
71 | const handleTermination = async () => {
72 | try {
73 | await this.cleanup();
74 | } catch (error) {
75 | console.error('Error during cleanup:', error);
76 | process.exit(1);
77 | }
78 | process.exit(0);
79 | };
80 | process.on('SIGINT', handleTermination);
81 | process.stdin.on('close', handleTermination);
82 | }
83 |
84 | private async cleanup() {
85 | if (this.client) {
86 | await this.client.end();
87 | }
88 | await this.server.close();
89 | }
90 |
91 | private async ensureConnection() {
92 | if (!this.config) {
93 | // Try to use environment variables if no explicit config was provided
94 | const envConfig = this.getEnvConfig();
95 |
96 | if (envConfig) {
97 | this.config = envConfig;
98 | console.error('[MCP Info] Using database config from environment variables');
99 | } else {
100 | throw new McpError(
101 | ErrorCode.InvalidRequest,
102 | 'Database configuration not set. Use connect_db tool first or set environment variables.'
103 | );
104 | }
105 | }
106 |
107 | if (!this.client) {
108 | try {
109 | this.client = new Client(this.config);
110 | await this.client.connect();
111 | } catch (error) {
112 | throw new McpError(
113 | ErrorCode.InternalError,
114 | `Failed to connect to database: ${getErrorMessage(error)}`
115 | );
116 | }
117 | }
118 | }
119 |
120 | private getEnvConfig(): DatabaseConfig | null {
121 | const { PG_HOST, PG_USER, PG_PASSWORD, PG_DATABASE, PG_PORT } = process.env;
122 |
123 | if (PG_HOST && PG_USER && PG_PASSWORD && PG_DATABASE) {
124 | return {
125 | host: PG_HOST,
126 | port: PG_PORT ? parseInt(PG_PORT, 10) : 5432,
127 | user: PG_USER,
128 | password: PG_PASSWORD,
129 | database: PG_DATABASE
130 | };
131 | }
132 |
133 | return null;
134 | }
135 |
136 | private setupToolHandlers() {
137 | this.server.setRequestHandler(ListToolsRequestSchema, async () => ({
138 | tools: [
139 | {
140 | name: 'connect_db',
141 | description: 'Connect to PostgreSQL database. NOTE: Default connection exists - only use when requested or if other commands fail',
142 | inputSchema: {
143 | type: 'object',
144 | properties: {
145 | host: {
146 | type: 'string',
147 | description: 'Database host',
148 | },
149 | port: {
150 | type: 'number',
151 | description: 'Database port (default: 5432)',
152 | },
153 | user: {
154 | type: 'string',
155 | description: 'Database user',
156 | },
157 | password: {
158 | type: 'string',
159 | description: 'Database password',
160 | },
161 | database: {
162 | type: 'string',
163 | description: 'Database name',
164 | },
165 | },
166 | required: ['host', 'user', 'password', 'database'],
167 | },
168 | },
169 | {
170 | name: 'query',
171 | description: 'Execute a SELECT query',
172 | inputSchema: {
173 | type: 'object',
174 | properties: {
175 | sql: {
176 | type: 'string',
177 | description: 'SQL SELECT query (use $1, $2, etc. for parameters)',
178 | },
179 | params: {
180 | type: 'array',
181 | items: {
182 | type: ['string', 'number', 'boolean', 'null'],
183 | },
184 | description: 'Query parameters (optional)',
185 | },
186 | },
187 | required: ['sql'],
188 | },
189 | },
190 | {
191 | name: 'execute',
192 | description: 'Execute an INSERT, UPDATE, or DELETE query',
193 | inputSchema: {
194 | type: 'object',
195 | properties: {
196 | sql: {
197 | type: 'string',
198 | description: 'SQL query (INSERT, UPDATE, DELETE) (use $1, $2, etc. for parameters)',
199 | },
200 | params: {
201 | type: 'array',
202 | items: {
203 | type: ['string', 'number', 'boolean', 'null'],
204 | },
205 | description: 'Query parameters (optional)',
206 | },
207 | },
208 | required: ['sql'],
209 | },
210 | },
211 | {
212 | name: 'list_schemas',
213 | description: 'List all schemas in the database',
214 | inputSchema: {
215 | type: 'object',
216 | properties: {},
217 | required: [],
218 | },
219 | },
220 | {
221 | name: 'list_tables',
222 | description: 'List tables in the database',
223 | inputSchema: {
224 | type: 'object',
225 | properties: {
226 | schema: {
227 | type: 'string',
228 | description: 'Schema name (default: public)',
229 | },
230 | },
231 | required: [],
232 | },
233 | },
234 | {
235 | name: 'describe_table',
236 | description: 'Get table structure',
237 | inputSchema: {
238 | type: 'object',
239 | properties: {
240 | table: {
241 | type: 'string',
242 | description: 'Table name',
243 | },
244 | schema: {
245 | type: 'string',
246 | description: 'Schema name (default: public)',
247 | },
248 | },
249 | required: ['table'],
250 | },
251 | },
252 | ],
253 | }));
254 |
255 | this.server.setRequestHandler(CallToolRequestSchema, async (request) => {
256 | switch (request.params.name) {
257 | case 'connect_db':
258 | return await this.handleConnectDb(request.params.arguments);
259 | case 'query':
260 | return await this.handleQuery(request.params.arguments);
261 | case 'execute':
262 | return await this.handleExecute(request.params.arguments);
263 | case 'list_schemas':
264 | return await this.handleListSchemas();
265 | case 'list_tables':
266 | return await this.handleListTables(request.params.arguments);
267 | case 'describe_table':
268 | return await this.handleDescribeTable(request.params.arguments);
269 | default:
270 | throw new McpError(
271 | ErrorCode.MethodNotFound,
272 | `Unknown tool: ${request.params.name}`
273 | );
274 | }
275 | });
276 | }
277 |
278 | private async handleConnectDb(args: any) {
279 | if (!args.host || !args.user || !args.password || !args.database) {
280 | throw new McpError(
281 | ErrorCode.InvalidParams,
282 | 'Missing required database configuration parameters'
283 | );
284 | }
285 |
286 | // Close existing connection if any
287 | if (this.client) {
288 | await this.client.end();
289 | this.client = null;
290 | }
291 |
292 | this.config = {
293 | host: args.host,
294 | port: args.port || 5432,
295 | user: args.user,
296 | password: args.password,
297 | database: args.database,
298 | };
299 |
300 | try {
301 | await this.ensureConnection();
302 | return {
303 | content: [
304 | {
305 | type: 'text',
306 | text: 'Successfully connected to PostgreSQL database',
307 | },
308 | ],
309 | };
310 | } catch (error) {
311 | throw new McpError(
312 | ErrorCode.InternalError,
313 | `Failed to connect to database: ${getErrorMessage(error)}`
314 | );
315 | }
316 | }
317 |
318 | private async handleQuery(args: any) {
319 | await this.ensureConnection();
320 |
321 | if (!args.sql) {
322 | throw new McpError(ErrorCode.InvalidParams, 'SQL query is required');
323 | }
324 |
325 | if (!args.sql.trim().toUpperCase().startsWith('SELECT')) {
326 | throw new McpError(
327 | ErrorCode.InvalidParams,
328 | 'Only SELECT queries are allowed with query tool'
329 | );
330 | }
331 |
332 | try {
333 | // Convert ? parameters to $1, $2, etc. if needed
334 | const sql = args.sql.includes('?') ? convertToNamedParams(args.sql) : args.sql;
335 | const result = await this.client!.query(sql, args.params || []);
336 |
337 | return {
338 | content: [
339 | {
340 | type: 'text',
341 | text: JSON.stringify(result.rows, null, 2),
342 | },
343 | ],
344 | };
345 | } catch (error) {
346 | throw new McpError(
347 | ErrorCode.InternalError,
348 | `Query execution failed: ${getErrorMessage(error)}`
349 | );
350 | }
351 | }
352 |
353 | private async handleExecute(args: any) {
354 | await this.ensureConnection();
355 |
356 | if (!args.sql) {
357 | throw new McpError(ErrorCode.InvalidParams, 'SQL query is required');
358 | }
359 |
360 | const sql = args.sql.trim().toUpperCase();
361 | if (sql.startsWith('SELECT')) {
362 | throw new McpError(
363 | ErrorCode.InvalidParams,
364 | 'Use query tool for SELECT statements'
365 | );
366 | }
367 |
368 | try {
369 | // Convert ? parameters to $1, $2, etc. if needed
370 | const preparedSql = args.sql.includes('?') ? convertToNamedParams(args.sql) : args.sql;
371 | const result = await this.client!.query(preparedSql, args.params || []);
372 |
373 | return {
374 | content: [
375 | {
376 | type: 'text',
377 | text: JSON.stringify({
378 | rowCount: result.rowCount,
379 | command: result.command,
380 | }, null, 2),
381 | },
382 | ],
383 | };
384 | } catch (error) {
385 | throw new McpError(
386 | ErrorCode.InternalError,
387 | `Query execution failed: ${getErrorMessage(error)}`
388 | );
389 | }
390 | }
391 |
392 | private async handleListSchemas() {
393 | await this.ensureConnection();
394 |
395 | try {
396 | const result = await this.client!.query(`
397 | SELECT schema_name
398 | FROM information_schema.schemata
399 | ORDER BY schema_name
400 | `);
401 |
402 | return {
403 | content: [
404 | {
405 | type: 'text',
406 | text: JSON.stringify(result.rows, null, 2),
407 | },
408 | ],
409 | };
410 | } catch (error) {
411 | throw new McpError(
412 | ErrorCode.InternalError,
413 | `Failed to list schemas: ${getErrorMessage(error)}`
414 | );
415 | }
416 | }
417 |
418 | private async handleListTables(args: any = {}) {
419 | await this.ensureConnection();
420 |
421 | const schema = args.schema || 'public';
422 |
423 | try {
424 | const result = await this.client!.query(`
425 | SELECT table_name
426 | FROM information_schema.tables
427 | WHERE table_schema = $1
428 | ORDER BY table_name
429 | `, [schema]);
430 |
431 | return {
432 | content: [
433 | {
434 | type: 'text',
435 | text: JSON.stringify(result.rows, null, 2),
436 | },
437 | ],
438 | };
439 | } catch (error) {
440 | throw new McpError(
441 | ErrorCode.InternalError,
442 | `Failed to list tables: ${getErrorMessage(error)}`
443 | );
444 | }
445 | }
446 |
447 | private async handleDescribeTable(args: any) {
448 | await this.ensureConnection();
449 |
450 | if (!args.table) {
451 | throw new McpError(ErrorCode.InvalidParams, 'Table name is required');
452 | }
453 |
454 | const schema = args.schema || 'public';
455 |
456 | try {
457 | const result = await this.client!.query(`
458 | SELECT
459 | c.column_name,
460 | c.data_type,
461 | c.is_nullable,
462 | c.column_default,
463 | CASE
464 | WHEN pk.constraint_type = 'PRIMARY KEY' THEN true
465 | ELSE false
466 | END AS is_primary_key,
467 | c.character_maximum_length
468 | FROM
469 | information_schema.columns c
470 | LEFT JOIN (
471 | SELECT
472 | tc.constraint_type,
473 | kcu.column_name,
474 | kcu.table_name,
475 | kcu.table_schema
476 | FROM
477 | information_schema.table_constraints tc
478 | JOIN
479 | information_schema.key_column_usage kcu
480 | ON
481 | tc.constraint_name = kcu.constraint_name
482 | WHERE
483 | tc.constraint_type = 'PRIMARY KEY'
484 | ) pk
485 | ON
486 | c.column_name = pk.column_name
487 | AND c.table_name = pk.table_name
488 | AND c.table_schema = pk.table_schema
489 | WHERE
490 | c.table_schema = $1
491 | AND c.table_name = $2
492 | ORDER BY
493 | c.ordinal_position
494 | `, [schema, args.table]);
495 |
496 | return {
497 | content: [
498 | {
499 | type: 'text',
500 | text: JSON.stringify(result.rows, null, 2),
501 | },
502 | ],
503 | };
504 | } catch (error) {
505 | throw new McpError(
506 | ErrorCode.InternalError,
507 | `Failed to describe table: ${getErrorMessage(error)}`
508 | );
509 | }
510 | }
511 |
512 | async run() {
513 | const transport = new StdioServerTransport();
514 | await this.server.connect(transport);
515 | console.error('PostgreSQL MCP server running on stdio');
516 | }
517 | }
518 |
519 | const server = new PostgresServer();
520 | server.run().catch(console.error);
```