delete-data
Remove records from PostgreSQL tables using WHERE conditions for targeted deletion, with confirmation prompts for large operations to prevent accidental data loss.
Instructions
Delete records from a table. Requires WHERE conditions for safety. Includes confirmation prompt for large deletions.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| table | Yes | ||
| where | Yes | ||
| confirm_delete | No | ||
| returning | No |
Implementation Reference
- tools/deleteData.ts:25-134 (handler)The main handler function for the 'delete-data' tool. It validates input, builds a safe DELETE query with parameterized WHERE conditions supporting equality, NULL, IN arrays, and LIKE patterns. Includes safety checks like requiring WHERE clause, estimating row count, and confirmation for large deletions (>100 rows). Executes the query and returns deleted count and optionally returned rows.export async function deleteData( rawParams: any ): McpToolResponse { try { // Validate and parse parameters const params = deleteDataSchema.parse(rawParams); // Check database connection status const connectionStatus = getConnectionStatus(); if (connectionStatus.status !== 'connected') { return createDatabaseUnavailableResponse("delete data"); } const { table, where, confirm_delete, returning } = params; // Validate table name const sanitizedTable = sanitizeIdentifier(table); // Validate that we have WHERE conditions (safety check) if (!where || Object.keys(where).length === 0) { throw new Error("WHERE clause is required for DELETE operations for safety"); } // Build WHERE clause const whereConditions: string[] = []; const queryParams: any[] = []; let paramIndex = 1; for (const [column, value] of Object.entries(where)) { const sanitizedColumn = sanitizeIdentifier(column); if (value === null) { whereConditions.push(`${sanitizedColumn} IS NULL`); } else if (Array.isArray(value)) { // Handle IN operator for arrays const placeholders = value.map(() => `$${paramIndex++}`).join(", "); whereConditions.push(`${sanitizedColumn} IN (${placeholders})`); queryParams.push(...value); } else if (typeof value === 'string' && value.includes('%')) { // Handle LIKE operator for strings with wildcards whereConditions.push(`${sanitizedColumn} LIKE $${paramIndex}`); queryParams.push(value); paramIndex++; } else { // Handle equality whereConditions.push(`${sanitizedColumn} = $${paramIndex}`); queryParams.push(value); paramIndex++; } } const whereClause = whereConditions.join(" AND "); // Safety check: estimate impact before deletion if (!confirm_delete) { const countQuery = `SELECT COUNT(*) as count FROM ${sanitizedTable} WHERE ${whereClause}`; const countResult = await executePostgresQuery(countQuery, queryParams); const affectedRows = parseInt(countResult[0].count); if (affectedRows > 100) { throw new Error( `This operation would delete ${affectedRows} rows. ` + `If you're sure you want to proceed, set confirm_delete to true.` ); } if (affectedRows === 0) { return createMcpSuccessResponse({ table: sanitizedTable, deleted_count: 0, message: "No rows match the WHERE conditions", deleted_at: new Date().toISOString(), }); } } // Build DELETE query let deleteQuery = `DELETE FROM ${sanitizedTable} WHERE ${whereClause}`; // Add RETURNING clause if specified let hasEmptyReturning = false; if (returning !== undefined) { if (returning.length > 0) { const sanitizedReturning = returning.map((col: string) => col === "*" ? "*" : sanitizeIdentifier(col) ); deleteQuery += ` RETURNING ${sanitizedReturning.join(", ")}`; } else { hasEmptyReturning = true; } } const result = await executePostgresModification(deleteQuery, queryParams); const response = { table: sanitizedTable, deleted_count: result.affectedCount, ...(returning !== undefined && { data: hasEmptyReturning ? Array(result.affectedCount).fill({}) : result.rows }), deleted_at: new Date().toISOString(), }; return createMcpSuccessResponse(response); } catch (error) { return createMcpErrorResponse("delete data", error); } }
- tools/deleteData.ts:15-22 (schema)Zod schema definition for input validation of the delete-data tool, including table name, where conditions, confirmation flag, and optional returning columns.export const deleteDataShape: ZodRawShape = { table: z.string().min(1, "Table name is required"), where: z.record(z.any()), confirm_delete: z.boolean().optional().default(false), returning: z.array(z.string()).optional(), }; export const deleteDataSchema = z.object(deleteDataShape);
- index.ts:62-67 (registration)Registration of the 'delete-data' tool on the MCP server, linking the name, description, input schema, and handler function.server.tool( "delete-data", "Delete records from a table. Requires WHERE conditions for safety. Includes confirmation prompt for large deletions.", deleteDataShape, deleteData );