Chapter 59: Node.js MySQL Delete
DELETE operations in MySQL using Node.js.
I will explain it as if I am sitting next to you right now:
- I open MySQL Workbench / terminal / DBeaver on one screen
- I open VS Code on the other screen
- I type every SQL line and every line of Node.js code live
- I explain why we write it this way (security, performance, data integrity)
- I show what 90% of beginners do wrong (and why it causes serious problems)
- I show what intermediate developers often forget (and how it leads to bugs / data loss)
- I show what real production code actually looks like in 2025–2026
Goal of this lesson
Learn how to write safe, clean, atomic, recoverable, logged DELETE operations from Node.js to MySQL.
We will cover:
- Basic DELETE
- DELETE with WHERE (single & multiple conditions)
- DELETE with JOIN
- DELETE with foreign key constraints (ON DELETE CASCADE vs RESTRICT)
- Soft delete vs hard delete (real production choice)
- Transaction usage (very important for safety)
- Logging & auditing deleted records
- Error handling & rollback
- Performance & index considerations
Step 1 – Project setup (if you don’t have it yet)
Bash
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mkdir task-api-mysql-delete cd task-api-mysql-delete npm init -y npm pkg set type=module npm install express dotenv mysql2 zod npm install -D typescript tsx nodemon @types/express @types/node |
tsconfig.json (strict & modern)
JSON
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
{ "compilerOptions": { "target": "ES2022", "module": "NodeNext", "moduleResolution": "NodeNext", "esModuleInterop": true, "forceConsistentCasingInFileNames": true, "strict": true, "skipLibCheck": true, "outDir": "./dist", "rootDir": "./src" }, "include": ["src/**/*"] } |
package.json scripts
JSON
|
0 1 2 3 4 5 6 7 8 9 |
"scripts": { "dev": "tsx watch src/index.ts", "start": "node dist/index.js" } |
src/config/database.ts (connection pool – must-have)
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import mysql from 'mysql2/promise' export const pool = mysql.createPool({ host: 'localhost', user: 'root', password: 'your-root-password', database: 'task_app', waitForConnections: true, connectionLimit: 10, queueLimit: 0, timezone: '+00:00', namedPlaceholders: true, multipleStatements: false }) async function checkConnection() { try { const conn = await pool.getConnection() console.log('MySQL pool ready') conn.release() } catch (err) { console.error('MySQL connection failed:', err) process.exit(1) } } checkConnection() |
Step 2 – Create test tables (run this once in MySQL)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
CREATE DATABASE IF NOT EXISTS task_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE task_app; -- Users table CREATE TABLE IF NOT EXISTS users ( id CHAR(36) PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Tasks table with foreign key CREATE TABLE IF NOT EXISTS tasks ( id CHAR(36) PRIMARY KEY, user_id CHAR(36) NOT NULL, title VARCHAR(150) NOT NULL, description TEXT, priority ENUM('low', 'medium', 'high') DEFAULT 'medium', due_date DATETIME, completed BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); -- Soft-delete table (for auditing) CREATE TABLE IF NOT EXISTS deleted_tasks ( id CHAR(36), user_id CHAR(36), title VARCHAR(150), deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, deleted_by VARCHAR(100) -- who deleted it (optional) ); |
Important foreign key behavior
- ON DELETE CASCADE → when user is deleted → all his tasks are automatically deleted
- ON DELETE RESTRICT → prevents deleting user if he has tasks (safer in some cases)
Step 3 – Basic DELETE – delete one task
src/controllers/task.controller.ts
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
import { Request, Response } from 'express' import pool from '../config/database.js' import { AppError } from '../middleware/error.middleware.js' export const deleteTask = async (req: Request<{ id: string }>, res: Response) => { try { const taskId = req.params.id if (!taskId) { throw new AppError(400, 'Task ID is required') } // Check if task exists (good practice) const [existing] = await pool.execute( 'SELECT id FROM tasks WHERE id = :id', { id: taskId } ) if ((existing as any[]).length === 0) { throw new AppError(404, 'Task not found') } // Real DELETE const [result] = await pool.execute( 'DELETE FROM tasks WHERE id = :id', { id: taskId } ) const affectedRows = (result as any).affectedRows if (affectedRows === 0) { throw new AppError(404, 'Task not found') } res.json({ success: true, message: 'Task deleted successfully', deletedId: taskId }) } catch (err) { next(err) } } |
Route
TypeScript
|
0 1 2 3 4 5 6 |
router.delete('/:id', deleteTask) |
Test it
Bash
|
0 1 2 3 4 5 6 |
curl -X DELETE http://localhost:5000/api/tasks/123e4567-e89b-12d3-a456-426614174000 |
Common beginner mistake
TypeScript
|
0 1 2 3 4 5 6 7 |
// DANGEROUS – SQL injection possible! await pool.query(`DELETE FROM tasks WHERE id = '${taskId}'`) |
Correct → always use parameters
TypeScript
|
0 1 2 3 4 5 6 |
'DELETE FROM tasks WHERE id = :id' |
Step 4 – DELETE with ownership check (very important – security)
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
export const deleteMyTask = async (req: Request<{ id: string }>, res: Response) => { try { const taskId = req.params.id const userId = req.user!.userId // from auth middleware const [existing] = await pool.execute( 'SELECT id FROM tasks WHERE id = :id AND user_id = :userId', { id: taskId, userId } ) if ((existing as any[]).length === 0) { throw new AppError(404, 'Task not found or does not belong to you') } await pool.execute( 'DELETE FROM tasks WHERE id = :id AND user_id = :userId', { id: taskId, userId } ) res.json({ success: true, message: 'Your task was deleted' }) } catch (err) { next(err) } } |
Why double-check ownership?
- Prevents users from deleting other people’s tasks
- Prevents IDOR (Insecure Direct Object Reference) attacks
- Very common security bug in APIs
Step 5 – Soft delete instead of hard delete (production favorite)
Why soft delete?
- You can recover deleted data
- You can audit who deleted what and when
- Easier compliance (GDPR, data retention policies)
Add deleted_at column
SQL
|
0 1 2 3 4 5 6 7 |
ALTER TABLE tasks ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL; |
Soft delete controller
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
export const softDeleteTask = async (req: Request<{ id: string }>, res: Response) => { try { const taskId = req.params.id const userId = req.user!.userId const [existing] = await pool.execute( 'SELECT id FROM tasks WHERE id = :id AND user_id = :userId AND deleted_at IS NULL', { id: taskId, userId } ) if ((existing as any[]).length === 0) { throw new AppError(404, 'Task not found or already deleted') } await pool.execute( 'UPDATE tasks SET deleted_at = NOW() WHERE id = :id AND user_id = :userId', { id: taskId, userId } ) // Optional: log deletion await pool.execute( 'INSERT INTO deleted_tasks (id, user_id, title, deleted_at, deleted_by) SELECT id, user_id, title, NOW(), :deletedBy FROM tasks WHERE id = :id', { id: taskId, deletedBy: userId } ) res.json({ success: true, message: 'Task soft-deleted successfully' }) } catch (err) { next(err) } } |
Select only active tasks
TypeScript
|
0 1 2 3 4 5 6 7 8 9 |
const [rows] = await pool.execute( 'SELECT * FROM tasks WHERE user_id = :userId AND deleted_at IS NULL', { userId } ) |
Why soft delete is preferred in production
- You can restore data if user deletes by mistake
- You can audit deletions (who, when)
- Easier to comply with data retention laws
- No need to worry about foreign key cascades when deleting users
Step 6 – Summary – DELETE / soft-delete best practices in Node.js + MySQL 2025–2026
| Best Practice | Why it matters | Code pattern example |
|---|---|---|
| Use named placeholders :name | Prevents SQL injection | DELETE FROM tasks WHERE id = :id |
| Always check ownership | Prevents IDOR / unauthorized deletion | WHERE id = :id AND user_id = :userId |
| Prefer soft delete (deleted_at) | Recoverable, auditable, GDPR-friendly | UPDATE tasks SET deleted_at = NOW() |
| Use prepared statements | Security – never concatenate values | execute(sql, { id, userId }) |
| Log deletions (audit trail) | Compliance & debugging | Insert into deleted_tasks table |
| Use transaction for multi-step delete | Atomicity (all or nothing) | pool.getConnection() + beginTransaction() |
| Use connection pool | Performance + safety | mysql2.createPool() |
| Add indexes on WHERE columns | 10×–1000× faster DELETE / SELECT | CREATE INDEX idx_user_id ON tasks(user_id) |
Which part would you like to go much deeper into next?
- Login + JWT authentication with MySQL
- Full task CRUD (create/read/update/delete + ownership)
- Add pagination + filtering + sorting + search
- Replace raw SQL with Prisma (most popular choice today)
- Replace raw SQL with Drizzle ORM (type-safe SQL)
- Add Docker + docker-compose with real MySQL container
- Soft-delete + restore + audit log complete system
Just tell me what you want to build or understand next — I’ll continue with complete, secure, production-ready code and explanations. 😊
