index.js•11.3 kB
#!/usr/bin/env node
import { Server } from '@modelcontextprotocol/sdk/server/index.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import {
CallToolRequestSchema,
ListToolsRequestSchema,
} from '@modelcontextprotocol/sdk/types.js';
import pg from 'pg';
import dotenv from 'dotenv';
// Load environment variables
dotenv.config();
const { Client } = pg;
class PostgresMCPServer {
constructor() {
this.server = new Server(
{
name: 'postgres-mcp-server',
version: '1.0.0',
},
{
capabilities: {
tools: {},
},
}
);
this.setupToolHandlers();
this.setupErrorHandling();
}
async connectToDatabase() {
const client = new Client({
host: process.env.DB_HOST || 'localhost',
port: process.env.DB_PORT || 5432,
database: process.env.DB_NAME || 'postgres',
user: process.env.DB_USER || 'postgres',
password: process.env.DB_PASSWORD || '',
});
try {
await client.connect();
return client;
} catch (error) {
console.error('Database connection failed:', error.message);
throw error;
}
}
setupToolHandlers() {
this.server.setRequestHandler(ListToolsRequestSchema, async () => {
return {
tools: [
{
name: 'list_tables',
description: 'List all tables in the database with basic information',
inputSchema: {
type: 'object',
properties: {
schema: {
type: 'string',
description: 'Schema name (default: public)',
default: 'public'
}
},
},
},
{
name: 'describe_table',
description: 'Get detailed schema information for a specific table',
inputSchema: {
type: 'object',
properties: {
table_name: {
type: 'string',
description: 'Name of the table to describe',
},
schema: {
type: 'string',
description: 'Schema name (default: public)',
default: 'public'
}
},
required: ['table_name'],
},
},
{
name: 'get_table_relationships',
description: 'Get foreign key relationships for a table',
inputSchema: {
type: 'object',
properties: {
table_name: {
type: 'string',
description: 'Name of the table',
},
schema: {
type: 'string',
description: 'Schema name (default: public)',
default: 'public'
}
},
required: ['table_name'],
},
},
{
name: 'list_schemas',
description: 'List all schemas in the database',
inputSchema: {
type: 'object',
properties: {},
},
},
{
name: 'get_indexes',
description: 'Get indexes for a specific table',
inputSchema: {
type: 'object',
properties: {
table_name: {
type: 'string',
description: 'Name of the table',
},
schema: {
type: 'string',
description: 'Schema name (default: public)',
default: 'public'
}
},
required: ['table_name'],
},
},
],
};
});
this.server.setRequestHandler(CallToolRequestSchema, async (request) => {
const { name, arguments: args } = request.params;
try {
switch (name) {
case 'list_tables':
return await this.listTables(args?.schema || 'public');
case 'describe_table':
return await this.describeTable(args.table_name, args?.schema || 'public');
case 'get_table_relationships':
return await this.getTableRelationships(args.table_name, args?.schema || 'public');
case 'list_schemas':
return await this.listSchemas();
case 'get_indexes':
return await this.getIndexes(args.table_name, args?.schema || 'public');
default:
throw new Error(`Unknown tool: ${name}`);
}
} catch (error) {
return {
content: [
{
type: 'text',
text: `Error: ${error.message}`,
},
],
};
}
});
}
async listTables(schema = 'public') {
const client = await this.connectToDatabase();
try {
const query = `
SELECT
table_name,
table_type,
is_insertable_into,
is_typed
FROM information_schema.tables
WHERE table_schema = $1
ORDER BY table_name;
`;
const result = await client.query(query, [schema]);
return {
content: [
{
type: 'text',
text: `Tables in schema "${schema}":\n\n` +
result.rows.map(row =>
`• ${row.table_name} (${row.table_type})`
).join('\n'),
},
],
};
} finally {
await client.end();
}
}
async describeTable(tableName, schema = 'public') {
const client = await this.connectToDatabase();
try {
const query = `
SELECT
column_name,
data_type,
is_nullable,
column_default,
character_maximum_length,
numeric_precision,
numeric_scale,
ordinal_position
FROM information_schema.columns
WHERE table_schema = $1 AND table_name = $2
ORDER BY ordinal_position;
`;
const result = await client.query(query, [schema, tableName]);
if (result.rows.length === 0) {
throw new Error(`Table "${tableName}" not found in schema "${schema}"`);
}
const tableInfo = result.rows.map(row => ({
column: row.column_name,
type: row.data_type,
nullable: row.is_nullable === 'YES',
default: row.column_default,
maxLength: row.character_maximum_length,
precision: row.numeric_precision,
scale: row.numeric_scale,
position: row.ordinal_position
}));
return {
content: [
{
type: 'text',
text: `Schema for table "${schema}.${tableName}":\n\n` +
tableInfo.map(col =>
`${col.column} | ${col.type}${col.maxLength ? `(${col.maxLength})` : ''}${col.precision ? `(${col.precision}${col.scale ? `,${col.scale}` : ''})` : ''} | ${col.nullable ? 'NULL' : 'NOT NULL'}${col.default ? ` | DEFAULT ${col.default}` : ''}`
).join('\n'),
},
],
};
} finally {
await client.end();
}
}
async getTableRelationships(tableName, schema = 'public') {
const client = await this.connectToDatabase();
try {
const query = `
SELECT
kcu.column_name,
ccu.table_schema AS foreign_table_schema,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name,
rc.constraint_name,
rc.update_rule,
rc.delete_rule
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
JOIN information_schema.referential_constraints AS rc
ON tc.constraint_name = rc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_schema = $1
AND tc.table_name = $2;
`;
const result = await client.query(query, [schema, tableName]);
return {
content: [
{
type: 'text',
text: result.rows.length > 0 ?
`Foreign key relationships for "${schema}.${tableName}":\n\n` +
result.rows.map(row =>
`${row.column_name} → ${row.foreign_table_schema}.${row.foreign_table_name}.${row.foreign_column_name}\n` +
` Constraint: ${row.constraint_name}\n` +
` On Update: ${row.update_rule}, On Delete: ${row.delete_rule}`
).join('\n\n') :
`No foreign key relationships found for table "${schema}.${tableName}"`
},
],
};
} finally {
await client.end();
}
}
async listSchemas() {
const client = await this.connectToDatabase();
try {
const query = `
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
ORDER BY schema_name;
`;
const result = await client.query(query);
return {
content: [
{
type: 'text',
text: `Available schemas:\n\n` +
result.rows.map(row => `• ${row.schema_name}`).join('\n'),
},
],
};
} finally {
await client.end();
}
}
async getIndexes(tableName, schema = 'public') {
const client = await this.connectToDatabase();
try {
const query = `
SELECT
i.indexname,
i.indexdef,
am.amname AS index_type
FROM pg_indexes i
JOIN pg_class c ON c.relname = i.indexname
JOIN pg_am am ON am.oid = c.relam
WHERE i.schemaname = $1 AND i.tablename = $2
ORDER BY i.indexname;
`;
const result = await client.query(query, [schema, tableName]);
return {
content: [
{
type: 'text',
text: result.rows.length > 0 ?
`Indexes for table "${schema}.${tableName}":\n\n` +
result.rows.map(row =>
`${row.indexname} (${row.index_type}):\n ${row.indexdef}`
).join('\n\n') :
`No indexes found for table "${schema}.${tableName}"`
},
],
};
} finally {
await client.end();
}
}
setupErrorHandling() {
this.server.onerror = (error) => {
console.error('[MCP Error]', error);
};
process.on('SIGINT', async () => {
await this.server.close();
process.exit(0);
});
}
async run() {
const transport = new StdioServerTransport();
await this.server.connect(transport);
console.error('PostgreSQL MCP server running on stdio');
}
}
// Start the server
const server = new PostgresMCPServer();
server.run().catch(console.error);