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();
    }
  }