PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/constraints.ts:415-598 (handler)

  Main handler implementation for the pg_manage_constraints tool. Defines the tool object with name, description, input schema, and execute function that dispatches to specific operations (get, create_fk, drop_fk, create, drop) using helper functions.
  export const manageConstraintsTool: PostgresTool = { name: 'pg_manage_constraints', description: 'Manage PostgreSQL constraints - get, create foreign keys, drop foreign keys, create constraints, drop constraints. Examples: operation="get" to list constraints, operation="create_fk" with constraintName, tableName, columnNames, referencedTable, referencedColumns', 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)') }), // biome-ignore lint/suspicious/noExplicitAny: <explanation> 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)

  Input schema using Zod that validates parameters for all supported 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:225-257 (registration)

  The pg_manage_constraints tool (imported as manageConstraintsTool) is registered in the allTools array, which is passed to the PostgreSQLServer constructor to make it available.
  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/constraints.ts:27-89 (helper)

  Helper function to list constraints from information_schema, used by the 'get' operation.
  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(); } }
• src/tools/constraints.ts:130-183 (helper)

  Helper function to create foreign key constraints, used by the 'create_fk' operation.
  async function executeCreateForeignKey( input: CreateForeignKeyInput, getConnectionString: GetConnectionStringFn ): Promise<{ constraintName: string; tableName: string; created: true }> { const resolvedConnectionString = getConnectionString(input.connectionString); const db = DatabaseConnection.getInstance(); const { constraintName, tableName, columnNames, referencedTable, referencedColumns, schema, referencedSchema, onUpdate, onDelete, deferrable, initiallyDeferred } = input; try { await db.connect(resolvedConnectionString); if (columnNames.length !== referencedColumns.length) { throw new McpError(ErrorCode.InvalidParams, 'Number of columns must match number of referenced columns'); } const schemaPrefix = schema !== 'public' ? `"${schema}".` : ''; const refSchemaPrefix = (referencedSchema || schema) !== 'public' ? `"${referencedSchema || schema}".` : ''; const columnsClause = columnNames.map(col => `"${col}"`).join(', '); const referencedColumnsClause = referencedColumns.map(col => `"${col}"`).join(', '); const deferrableClause = deferrable ? ' DEFERRABLE' : ''; const initiallyDeferredClause = initiallyDeferred ? ' INITIALLY DEFERRED' : ''; const createFkSQL = ` ALTER TABLE ${schemaPrefix}"${tableName}" ADD CONSTRAINT "${constraintName}" FOREIGN KEY (${columnsClause}) REFERENCES ${refSchemaPrefix}"${referencedTable}" (${referencedColumnsClause}) ON UPDATE ${onUpdate} ON DELETE ${onDelete}${deferrableClause}${initiallyDeferredClause} `; await db.query(createFkSQL); return { constraintName, tableName, created: true }; } catch (error) { throw new McpError(ErrorCode.InternalError, `Failed to create foreign key: ${error instanceof Error ? error.message : String(error)}`); } finally { await db.disconnect(); } }