Chapter 61: Node.js MySQL Update
UPDATE queries in MySQL from Node.js (2025–2026 style).
I will explain everything as if we are sitting together right now:
- I open MySQL Workbench / terminal / DBeaver on one side
- I open VS Code on the other side
- We type every SQL statement and every line of Node.js code live
- I explain why we write it this way (security, performance, data integrity, auditability)
- I show what most beginners do wrong (very dangerous mistakes)
- I show what intermediate developers often forget (leads to subtle bugs or data corruption)
- I show what real production code looks like in serious Node.js applications today
Goal of this lesson
Learn how to write safe, atomic, auditable, performant, recoverable UPDATE statements from Node.js to MySQL.
We will cover:
- Basic UPDATE (single row)
- UPDATE with WHERE (very important for safety)
- UPDATE multiple rows
- UPDATE with JOIN
- Partial update (only some columns)
- Soft update (add updated_at, changed_by)
- Transaction usage (critical for multi-statement updates)
- Dynamic UPDATE (user-controlled fields – safe way)
- Error handling & optimistic concurrency
- Production logging & audit trail
Step 1 – Project setup (minimal but complete)
If you don’t have the project yet:
Bash
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mkdir task-api-mysql-update cd task-api-mysql-update 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 – non-negotiable)
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 – Test data (run once in MySQL)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE task_app; -- Clear old data TRUNCATE TABLE tasks; -- Insert some sample tasks INSERT INTO tasks (id, user_id, title, description, priority, due_date, completed, created_at) VALUES (UUID(), 'user-uuid-1', 'Finish report', 'Q4 sales report', 'high', '2025-03-05 18:00:00', false, '2025-02-01 10:00:00'), (UUID(), 'user-uuid-1', 'Call client', null, 'medium', null, true, '2025-02-02 14:30:00'), (UUID(), 'user-uuid-2', 'Design logo', 'New client logo', 'high', '2025-02-28 23:59:00', false, '2025-02-03 09:15:00'), (UUID(), 'user-uuid-2', 'Update website', 'Fix mobile responsiveness', 'low', null, false, '2025-02-04 16:45:00'); |
Now we have 4 tasks to update.
Step 3 – Basic UPDATE – mark task as completed
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 49 50 51 |
import { Request, Response } from 'express' import pool from '../config/database.js' import { AppError } from '../middleware/error.middleware.js' export const markTaskCompleted = 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') } // Perform the update const [result] = await pool.execute( `UPDATE tasks SET completed = TRUE, updated_at = NOW() 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 marked as completed', updatedId: taskId }) } catch (err) { next(err) } } |
Route
TypeScript
|
0 1 2 3 4 5 6 |
router.patch('/:id/complete', markTaskCompleted) |
Test:
Bash
|
0 1 2 3 4 5 6 |
curl -X PATCH http://localhost:5000/api/tasks/uuid-here/complete |
Common beginner mistake
TypeScript
|
0 1 2 3 4 5 6 |
await pool.query(`UPDATE tasks SET completed = 1 WHERE id = '${taskId}'`) |
→ SQL injection possible — never concatenate strings!
Correct → always use parameters
TypeScript
|
0 1 2 3 4 5 6 |
'WHERE id = :id' |
Step 4 – UPDATE with ownership check (security 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 33 34 35 36 37 38 39 40 41 |
export const updateTaskTitle = async (req: Request<{ id: string }>, res: Response) => { try { const taskId = req.params.id const userId = req.user!.userId // from auth middleware const { title } = req.body if (!title || typeof title !== 'string') { throw new AppError(400, 'Title is required') } 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( `UPDATE tasks SET title = :title, updated_at = NOW() WHERE id = :id AND user_id = :userId`, { title, id: taskId, userId } ) res.json({ success: true, message: 'Task title updated', updatedId: taskId }) } catch (err) { next(err) } } |
Why double-check ownership?
- Prevents IDOR (Insecure Direct Object Reference) attacks
- Users can only update their own tasks
- Extremely common security vulnerability in REST APIs
Step 5 – UPDATE multiple rows (batch update)
Mark all overdue tasks as high priority
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 |
export const prioritizeOverdueTasks = async (req: Request, res: Response) => { try { const [result] = await pool.execute(` UPDATE tasks SET priority = 'high', updated_at = NOW() WHERE due_date < NOW() AND completed = FALSE AND priority != 'high' `) const affectedRows = (result as any).affectedRows res.json({ success: true, message: 'Overdue tasks prioritized', updatedCount: affectedRows }) } catch (err) { next(err) } } |
When to use batch UPDATE
- Status changes (mark all old tasks as archived)
- Bulk price updates
- Reset flags
- Compliance updates (GDPR delete requests)
Step 6 – Transactional UPDATE (critical when multiple tables)
Mark task completed + update user points
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 49 |
export const completeTaskWithPoints = async (req: Request<{ id: string }>, res: Response) => { const connection = await pool.getConnection() try { await connection.beginTransaction() const taskId = req.params.id const userId = req.user!.userId // 1. Update task const [taskResult] = await connection.execute( `UPDATE tasks SET completed = TRUE, updated_at = NOW() WHERE id = :id AND user_id = :userId`, { id: taskId, userId } ) if ((taskResult as any).affectedRows === 0) { throw new AppError(404, 'Task not found or not yours') } // 2. Give user 10 points await connection.execute( `UPDATE users SET points = points + 10, updated_at = NOW() WHERE id = :userId`, { userId } ) await connection.commit() res.json({ success: true, message: 'Task completed & points awarded' }) } catch (err) { await connection.rollback() next(err) } finally { connection.release() } } |
Why transaction?
- Either both updates succeed or neither does
- Prevents inconsistent state (task completed but no points awarded)
Step 7 – Summary – UPDATE best practices in Node.js + MySQL 2025–2026
| Best Practice | Why it matters | Code pattern example |
|---|---|---|
| Use named placeholders :name | Prevents SQL injection | SET title = :title |
| Always check ownership | Prevents IDOR / unauthorized updates | WHERE id = :id AND user_id = :userId |
| Prefer soft update (updated_at) | Track changes, auditability | SET updated_at = NOW() |
| Use prepared statements | Security – never concatenate values | execute(sql, { id, title }) |
| Use transactions for multi-table | Atomicity (all or nothing) | beginTransaction() + commit() / rollback() |
| Log who changed what | Compliance & debugging | Add updated_by column or audit table |
| Use indexes on WHERE columns | 10×–1000× faster updates | CREATE INDEX idx_user_id ON tasks(user_id) |
| Validate input before UPDATE | Prevents bad data + better UX | zod.parse() |
Which direction 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 system
Just tell me what you want to build or understand next — I’ll continue with complete, secure, production-ready code and explanations. 😊
