PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/schema.ts:462-578 (handler)

  The main handler function for 'pg_manage_schema' tool, which dispatches to specific operations like get_info, create_table, alter_table, get_enums, create_enum using switch on 'operation' parameter.
  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 defining parameters for the pg_manage_schema tool, including operation type and specific params for each operation.
  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 manageSchemaTools (pg_manage_schema) in the allTools array, which is passed to PostgreSQLServer constructor to enable the tool.
  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/tools/schema.ts:121-155 (helper)

  Helper function for create_table operation: generates and executes CREATE TABLE statement.
  async function executeCreateTable( input: CreateTableInput, getConnectionString: GetConnectionStringFn ): Promise<{ tableName: string; columns: z.infer<typeof CreateTableColumnSchema>[] }> { const resolvedConnectionString = getConnectionString(input.connectionString); const db = DatabaseConnection.getInstance(); const { tableName, columns } = input; try { await db.connect(resolvedConnectionString); const columnDefs = columns.map(col => { let def = `"${col.name}" ${col.type}`; if (col.nullable === false) def += ' NOT NULL'; if (col.default !== undefined) def += ` DEFAULT ${col.default}`; // if (col.primaryKey) def += ' PRIMARY KEY'; // If using column-level PK return def; }).join(', '); // const primaryKeyDef = input.primaryKeyColumns && input.primaryKeyColumns.length > 0 // ? `, PRIMARY KEY (${input.primaryKeyColumns.map(pk => `"${pk}"`).join(', ')})` // : ''; // const createTableSQL = `CREATE TABLE IF NOT EXISTS "${tableName}" (${columnDefs}${primaryKeyDef})`; const createTableSQL = `CREATE TABLE IF NOT EXISTS "${tableName}" (${columnDefs})`; await db.query(createTableSQL); return { tableName, columns }; } catch (error) { throw new McpError(ErrorCode.InternalError, `Failed to create table: ${error instanceof Error ? error.message : String(error)}`); } finally { await db.disconnect(); } }
• src/tools/schema.ts:52-80 (helper)

  Helper function for get_info operation: retrieves list of tables or detailed schema for a table.
  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(); } }