PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/schema.ts:428-579 (handler)

  Full implementation of the 'pg_manage_schema' tool handler. Includes input schema definition, parameter extraction, operation dispatching (get_info, create_table, alter_table, get_enums, create_enum), calls to helper functions, error handling, and ToolOutput formatting.
  export const manageSchemaTools: PostgresTool = { name: 'pg_manage_schema', description: 'Manage PostgreSQL schema - get schema info, create/alter tables, manage enums. Examples: operation="get_info" for table lists, operation="create_table" with tableName and columns, operation="get_enums" to list enums, operation="create_enum" with enumName and values', inputSchema: z.object({ connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'), operation: z.enum(['get_info', 'create_table', 'alter_table', 'get_enums', 'create_enum']).describe('Operation: get_info (schema/table info), create_table (new table), alter_table (modify table), get_enums (list ENUMs), create_enum (new ENUM)'), // Common parameters tableName: z.string().optional().describe('Table name (optional for get_info to get specific table info, required for create_table/alter_table)'), schema: z.string().optional().describe('Schema name (defaults to public)'), // Create table parameters columns: z.array(z.object({ name: z.string(), type: z.string().describe("PostgreSQL data type"), nullable: z.boolean().optional(), default: z.string().optional().describe("Default value expression"), })).optional().describe('Column definitions (required for create_table)'), // Alter table parameters operations: z.array(z.object({ type: z.enum(['add', 'alter', 'drop']), columnName: z.string(), dataType: z.string().optional().describe("PostgreSQL data type (for add/alter)"), nullable: z.boolean().optional().describe("Whether the column can be NULL (for add/alter)"), default: z.string().optional().describe("Default value expression (for add/alter)"), })).optional().describe('Alter operations (required for alter_table)'), // Enum parameters enumName: z.string().optional().describe('ENUM name (optional for get_enums to filter, required for create_enum)'), values: z.array(z.string()).optional().describe('ENUM values (required for create_enum)'), ifNotExists: z.boolean().optional().describe('Include IF NOT EXISTS clause (for create_enum)') }), // biome-ignore lint/suspicious/noExplicitAny: <explanation> execute: async (args: any, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => { const { connectionString: connStringArg, operation, tableName, schema, columns, operations, enumName, values, ifNotExists } = args as { connectionString?: string; operation: 'get_info' | 'create_table' | 'alter_table' | 'get_enums' | 'create_enum'; tableName?: string; schema?: string; columns?: Array<{ name: string; type: string; nullable?: boolean; default?: string; }>; operations?: Array<{ type: 'add' | 'alter' | 'drop'; columnName: string; dataType?: string; nullable?: boolean; default?: string; }>; enumName?: string; values?: string[]; ifNotExists?: boolean; }; try { switch (operation) { case 'get_info': { const result = await executeGetSchemaInfo({ connectionString: connStringArg, tableName }, getConnectionStringVal); const message = tableName ? `Schema information for table ${tableName}` : 'List of tables in database'; return { content: [{ type: 'text', text: message }, { type: 'text', text: JSON.stringify(result, null, 2) }] }; } case 'create_table': { if (!tableName || !columns || columns.length === 0) { return { content: [{ type: 'text', text: 'Error: tableName and columns are required for create_table operation' }], isError: true }; } const result = await executeCreateTable({ connectionString: connStringArg, tableName, columns }, getConnectionStringVal); return { content: [{ type: 'text', text: `Table ${result.tableName} created successfully (if not exists).` }, { type: 'text', text: JSON.stringify(result, null, 2) }] }; } case 'alter_table': { if (!tableName || !operations || operations.length === 0) { return { content: [{ type: 'text', text: 'Error: tableName and operations are required for alter_table operation' }], isError: true }; } const result = await executeAlterTable({ connectionString: connStringArg, tableName, operations }, getConnectionStringVal); return { content: [{ type: 'text', text: `Table ${result.tableName} altered successfully.` }, { type: 'text', text: JSON.stringify(result, null, 2) }] }; } case 'get_enums': { const result = await executeGetEnumsInSchema( connStringArg || '', schema || 'public', enumName, getConnectionStringVal ); return { content: [{ type: 'text', text: `Fetched ${result.length} ENUM(s).` }, { type: 'text', text: JSON.stringify(result, null, 2) }] }; } case 'create_enum': { if (!enumName || !values || values.length === 0) { return { content: [{ type: 'text', text: 'Error: enumName and values are required for create_enum operation' }], isError: true }; } const result = await executeCreateEnumInSchema( connStringArg || '', enumName, values, schema || 'public', ifNotExists || false, getConnectionStringVal ); return { content: [{ type: 'text', text: `ENUM type ${result.schema ? `${result.schema}.` : ''}${result.enumName} created successfully.` }, { type: 'text', text: JSON.stringify(result, null, 2) }] }; } default: return { content: [{ type: 'text', text: `Error: Unknown operation "${operation}". Supported operations: get_info, create_table, alter_table, get_enums, create_enum` }], isError: true }; } } catch (error) { const errorMessage = error instanceof McpError ? error.message : (error instanceof Error ? error.message : String(error)); return { content: [{ type: 'text', text: `Error executing ${operation} operation: ${errorMessage}` }], isError: true }; } } };
• src/tools/schema.ts:431-460 (schema)

  Zod input schema for the pg_manage_schema tool, defining parameters for connection, operation type, and operation-specific fields like tableName, columns, operations, enum details.
  inputSchema: z.object({ connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'), operation: z.enum(['get_info', 'create_table', 'alter_table', 'get_enums', 'create_enum']).describe('Operation: get_info (schema/table info), create_table (new table), alter_table (modify table), get_enums (list ENUMs), create_enum (new ENUM)'), // Common parameters tableName: z.string().optional().describe('Table name (optional for get_info to get specific table info, required for create_table/alter_table)'), schema: z.string().optional().describe('Schema name (defaults to public)'), // Create table parameters columns: z.array(z.object({ name: z.string(), type: z.string().describe("PostgreSQL data type"), nullable: z.boolean().optional(), default: z.string().optional().describe("Default value expression"), })).optional().describe('Column definitions (required for create_table)'), // Alter table parameters operations: z.array(z.object({ type: z.enum(['add', 'alter', 'drop']), columnName: z.string(), dataType: z.string().optional().describe("PostgreSQL data type (for add/alter)"), nullable: z.boolean().optional().describe("Whether the column can be NULL (for add/alter)"), default: z.string().optional().describe("Default value expression (for add/alter)"), })).optional().describe('Alter operations (required for alter_table)'), // Enum parameters enumName: z.string().optional().describe('ENUM name (optional for get_enums to filter, required for create_enum)'), values: z.array(z.string()).optional().describe('ENUM values (required for create_enum)'), ifNotExists: z.boolean().optional().describe('Include IF NOT EXISTS clause (for create_enum)') }),
• src/index.ts:225-257 (registration)

  Registration of pg_manage_schema by including manageSchemaTools in the allTools array, which is passed to PostgreSQLServer constructor to populate availableToolsList and MCP capabilities.tools.
  const allTools: PostgresTool[] = [ // Core Analysis & Debugging analyzeDatabaseTool, debugDatabaseTool, // Schema & Structure Management (Meta-Tools) manageSchemaTools, manageFunctionsTool, manageTriggersTools, manageIndexesTool, manageConstraintsTool, manageRLSTool, // User & Security Management manageUsersTool, // Query & Performance Management manageQueryTool, // Data Operations (Enhancement Tools) executeQueryTool, executeMutationTool, executeSqlTool, // Documentation & Metadata manageCommentsTool, // Data Migration & Monitoring exportTableDataTool, importTableDataTool, copyBetweenDatabasesTool, monitorDatabaseTool ];
• src/index.ts:24-24 (registration)

  Import of the manageSchemaTools from src/tools/schema.ts for registration in the main server index.
  import { manageSchemaTools } from './tools/schema.js';
• src/tools/schema.ts:52-80 (helper)

  Helper function executeGetSchemaInfo used by pg_manage_schema for 'get_info' operation to retrieve schema or table information.
  async function executeGetSchemaInfo( input: GetSchemaInfoInput, getConnectionString: GetConnectionStringFn ): Promise<TableInfo | string[]> { // Return type depends on whether tableName is provided const resolvedConnectionString = getConnectionString(input.connectionString); const db = DatabaseConnection.getInstance(); const { tableName } = input; try { await db.connect(resolvedConnectionString); if (tableName) { return await getTableInfo(db, tableName); } const tables = await db.query<{ table_name: string }>( `SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' -- Ensure only base tables ORDER BY table_name` ); return tables.map(t => t.table_name); } catch (error) { throw new McpError(ErrorCode.InternalError, `Failed to get schema information: ${error instanceof Error ? error.message : String(error)}`); } finally { await db.disconnect(); } }