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