PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/constraints.ts:449-597 (handler)

  Main handler logic for the pg_manage_constraints tool. Dispatches based on 'operation' parameter ('get', 'create_fk', 'drop_fk', 'create', 'drop') to specialized helper functions.

  execute: async (args: any, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => {
    const { 
      connectionString: connStringArg,
      operation,
      schema,
      constraintName,
      tableName,
      constraintType,
      columnNames,
      referencedTable,
      referencedColumns,
      referencedSchema,
      onUpdate,
      onDelete,
      constraintTypeCreate,
      checkExpression,
      deferrable,
      initiallyDeferred,
      ifExists,
      cascade
    } = args as {
      connectionString?: string;
      operation: 'get' | 'create_fk' | 'drop_fk' | 'create' | 'drop';
      schema?: string;
      constraintName?: string;
      tableName?: string;
      constraintType?: 'PRIMARY KEY' | 'FOREIGN KEY' | 'UNIQUE' | 'CHECK';
      columnNames?: string[];
      referencedTable?: string;
      referencedColumns?: string[];
      referencedSchema?: string;
      onUpdate?: 'NO ACTION' | 'RESTRICT' | 'CASCADE' | 'SET NULL' | 'SET DEFAULT';
      onDelete?: 'NO ACTION' | 'RESTRICT' | 'CASCADE' | 'SET NULL' | 'SET DEFAULT';
      constraintTypeCreate?: 'unique' | 'check' | 'primary_key';
      checkExpression?: string;
      deferrable?: boolean;
      initiallyDeferred?: boolean;
      ifExists?: boolean;
      cascade?: boolean;
    };
  
    try {
      switch (operation) {
        case 'get': {
          const result = await executeGetConstraints({
            connectionString: connStringArg,
            schema: schema || 'public',
            tableName,
            constraintType
          }, getConnectionStringVal);
          const message = tableName 
            ? `Constraints for table ${tableName}` 
            : `All constraints in schema ${schema || 'public'}`;
          return { content: [{ type: 'text', text: message }, { type: 'text', text: JSON.stringify(result, null, 2) }] };
        }
  
        case 'create_fk': {
          if (!constraintName || !tableName || !columnNames || !referencedTable || !referencedColumns) {
            return { 
              content: [{ type: 'text', text: 'Error: constraintName, tableName, columnNames, referencedTable, and referencedColumns are required for create_fk operation' }], 
              isError: true 
            };
          }
          const result = await executeCreateForeignKey({
            connectionString: connStringArg,
            constraintName,
            tableName,
            columnNames,
            referencedTable,
            referencedColumns,
            schema: schema || 'public',
            referencedSchema,
            onUpdate: onUpdate || 'NO ACTION',
            onDelete: onDelete || 'NO ACTION',
            deferrable: deferrable || false,
            initiallyDeferred: initiallyDeferred || false
          }, getConnectionStringVal);
          return { content: [{ type: 'text', text: `Foreign key ${result.constraintName} created successfully.` }, { type: 'text', text: JSON.stringify(result, null, 2) }] };
        }
  
        case 'drop_fk': {
          if (!constraintName || !tableName) {
            return { 
              content: [{ type: 'text', text: 'Error: constraintName and tableName are required for drop_fk operation' }], 
              isError: true 
            };
          }
          const result = await executeDropForeignKey({
            connectionString: connStringArg,
            constraintName,
            tableName,
            schema: schema || 'public',
            ifExists: ifExists !== undefined ? ifExists : true,
            cascade: cascade || false
          }, getConnectionStringVal);
          return { content: [{ type: 'text', text: `Foreign key ${result.constraintName} dropped successfully.` }, { type: 'text', text: JSON.stringify(result, null, 2) }] };
        }
  
        case 'create': {
          if (!constraintName || !tableName || !constraintTypeCreate) {
            return { 
              content: [{ type: 'text', text: 'Error: constraintName, tableName, and constraintTypeCreate are required for create operation' }], 
              isError: true 
            };
          }
          const result = await executeCreateConstraint({
            connectionString: connStringArg,
            constraintName,
            tableName,
            constraintType: constraintTypeCreate,
            columnNames,
            checkExpression,
            schema: schema || 'public',
            deferrable: deferrable || false,
            initiallyDeferred: initiallyDeferred || false
          }, getConnectionStringVal);
          return { content: [{ type: 'text', text: `${result.constraintType} constraint ${result.constraintName} created successfully.` }, { type: 'text', text: JSON.stringify(result, null, 2) }] };
        }
  
        case 'drop': {
          if (!constraintName || !tableName) {
            return { 
              content: [{ type: 'text', text: 'Error: constraintName and tableName are required for drop operation' }], 
              isError: true 
            };
          }
          const result = await executeDropConstraint({
            connectionString: connStringArg,
            constraintName,
            tableName,
            schema: schema || 'public',
            ifExists: ifExists !== undefined ? ifExists : true,
            cascade: cascade || false
          }, getConnectionStringVal);
          return { content: [{ type: 'text', text: `Constraint ${result.constraintName} dropped successfully.` }, { type: 'text', text: JSON.stringify(result, null, 2) }] };
        }
  
        default:
          return { 
            content: [{ type: 'text', text: `Error: Unknown operation "${operation}". Supported operations: get, create_fk, drop_fk, create, drop` }], 
            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/constraints.ts:418-447 (schema)

  Zod schema for input validation, supporting parameters for all constraint management operations.

  inputSchema: z.object({
    connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'),
    operation: z.enum(['get', 'create_fk', 'drop_fk', 'create', 'drop']).describe('Operation: get (list constraints), create_fk (foreign key), drop_fk (drop foreign key), create (constraint), drop (constraint)'),
    
    // Common parameters
    schema: z.string().optional().describe('Schema name (defaults to public)'),
    constraintName: z.string().optional().describe('Constraint name (required for create_fk/drop_fk/create/drop)'),
    tableName: z.string().optional().describe('Table name (optional filter for get, required for create_fk/drop_fk/create/drop)'),
    
    // Get operation parameters
    constraintType: z.enum(['PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE', 'CHECK']).optional().describe('Filter by constraint type (for get operation)'),
    
    // Foreign key specific parameters
    columnNames: z.array(z.string()).optional().describe('Column names in the table (required for create_fk)'),
    referencedTable: z.string().optional().describe('Referenced table name (required for create_fk)'),
    referencedColumns: z.array(z.string()).optional().describe('Referenced column names (required for create_fk)'),
    referencedSchema: z.string().optional().describe('Referenced table schema (for create_fk, defaults to same as table schema)'),
    onUpdate: z.enum(['NO ACTION', 'RESTRICT', 'CASCADE', 'SET NULL', 'SET DEFAULT']).optional().describe('ON UPDATE action (for create_fk)'),
    onDelete: z.enum(['NO ACTION', 'RESTRICT', 'CASCADE', 'SET NULL', 'SET DEFAULT']).optional().describe('ON DELETE action (for create_fk)'),
    
    // Constraint specific parameters  
    constraintTypeCreate: z.enum(['unique', 'check', 'primary_key']).optional().describe('Type of constraint to create (for create operation)'),
    checkExpression: z.string().optional().describe('Check expression (for create operation with check constraints)'),
    
    // Common options
    deferrable: z.boolean().optional().describe('Make constraint deferrable (for create_fk/create operations)'),
    initiallyDeferred: z.boolean().optional().describe('Initially deferred (for create_fk/create operations)'),
    ifExists: z.boolean().optional().describe('Include IF EXISTS clause (for drop_fk/drop operations)'),
    cascade: z.boolean().optional().describe('Include CASCADE clause (for drop_fk/drop operations)')
  }),

• src/index.ts:29-29 (registration)

  Import of the manageConstraintsTool from constraints.ts

  import { manageConstraintsTool } from './tools/constraints.js';

• src/index.ts:235-235 (registration)

  Inclusion of manageConstraintsTool in the allTools array passed to the MCP server constructor for registration.

  manageConstraintsTool,

• src/tools/constraints.ts:27-89 (helper)

  Helper function for the 'get' operation to retrieve constraint information from the database.

  async function executeGetConstraints(
    input: GetConstraintsInput,
    getConnectionString: GetConnectionStringFn
  ): Promise<ConstraintInfo[]> {
    const resolvedConnectionString = getConnectionString(input.connectionString);
    const db = DatabaseConnection.getInstance();
    const { schema, tableName, constraintType } = input;
    
    try {
      await db.connect(resolvedConnectionString);
      
      const whereConditions = ["tc.table_schema = $1"];
      const params: string[] = [schema];
      let paramIndex = 2;
      
      if (tableName) {
        whereConditions.push(`tc.table_name = $${paramIndex}`);
        params.push(tableName);
        paramIndex++;
      }
      
      if (constraintType) {
        whereConditions.push(`tc.constraint_type = $${paramIndex}`);
        params.push(constraintType);
        paramIndex++;
      }
      
      const whereClause = whereConditions.join(' AND ');
      
      const constraintsQuery = `
        SELECT 
          tc.constraint_name,
          tc.constraint_type,
          tc.table_name,
          kcu.column_name,
          ccu.table_name as foreign_table_name,
          ccu.column_name as foreign_column_name,
          cc.check_clause,
          tc.is_deferrable,
          tc.initially_deferred
        FROM information_schema.table_constraints tc
        LEFT JOIN information_schema.key_column_usage kcu 
          ON tc.constraint_name = kcu.constraint_name 
          AND tc.table_schema = kcu.table_schema
        LEFT JOIN information_schema.constraint_column_usage ccu
          ON tc.constraint_name = ccu.constraint_name
          AND tc.table_schema = ccu.table_schema
        LEFT JOIN information_schema.check_constraints cc
          ON tc.constraint_name = cc.constraint_name
          AND tc.table_schema = cc.constraint_schema
        WHERE ${whereClause}
        ORDER BY tc.table_name, tc.constraint_type, tc.constraint_name
      `;
      
      const result = await db.query<ConstraintInfo>(constraintsQuery, params);
      return result;
      
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to get constraints: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      await db.disconnect();
    }
  }