Chaptert 58: Node.js MySQL Order By
ORDER BY in MySQL queries from Node.js — explained as if I’m sitting next to you right now.
We will go extremely slowly, step by step, with:
- Live code you can copy-paste and run
- Clear reasoning — why we do things this way
- Common beginner traps (and how they cause bugs)
- Intermediate-level mistakes (even experienced developers make them)
- Production-grade patterns used in real Node.js + MySQL apps in 2025–2026
- Security, performance, and readability focus
Goal of this lesson
Master ORDER BY in MySQL from Node.js so that your API endpoints return data in a predictable, user-friendly, performant way.
We will cover:
- Basic ORDER BY (single column)
- Multiple columns + ASC/DESC
- ORDER BY with JOIN
- ORDER BY with dynamic user input (safe & safe from injection)
- ORDER BY with pagination (LIMIT + OFFSET)
- ORDER BY with CASE / conditional sorting
- Production logging & timing
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-orderby cd task-api-mysql-orderby 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 – production 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 – Test data (run this once in MySQL)
Open MySQL Workbench / terminal and run:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE task_app; -- Clear old data TRUNCATE TABLE tasks; -- Insert sample tasks (different priorities, due dates, completion status) INSERT INTO tasks (id, user_id, title, priority, due_date, completed, created_at) VALUES (UUID(), 'user-uuid-1', 'Finish quarterly report', 'high', '2025-03-05 18:00:00', false, '2025-02-01 10:00:00'), (UUID(), 'user-uuid-1', 'Call client', 'medium', null, true, '2025-02-02 14:30:00'), (UUID(), 'user-uuid-2', 'Design logo', 'high', '2025-02-28 23:59:00', false, '2025-02-03 09:15:00'), (UUID(), 'user-uuid-2', 'Update website', 'low', null, false, '2025-02-04 16:45:00'), (UUID(), 'user-uuid-3', 'Prepare presentation', 'high', '2025-02-25 10:00:00', false, '2025-02-05 11:20:00'); |
Now we have real data to sort!
Step 3 – Basic ORDER BY (single column)
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 |
import { Request, Response } from 'express' import pool from '../config/database.js' export const getTasksSortedByCreatedAt = async (_req: Request, res: Response) => { try { // Newest first (DESC) const [rows] = await pool.query( 'SELECT * FROM tasks ORDER BY created_at DESC' ) res.json({ success: true, count: (rows as any[]).length, data: rows }) } catch (err) { console.error('Database error:', err) res.status(500).json({ success: false, message: 'Failed to fetch tasks' }) } } |
Route in src/routes/task.routes.ts
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
import { Router } from 'express' import { getTasksSortedByCreatedAt } from '../controllers/task.controller.js' const router = Router() router.get('/sorted-by-date', getTasksSortedByCreatedAt) export default router |
Add to index.ts
TypeScript
|
0 1 2 3 4 5 6 |
app.use('/api/tasks', taskRoutes) |
Visit: http://localhost:5000/api/tasks/sorted-by-date
Result Tasks ordered from newest to oldest.
Common beginner mistake
TypeScript
|
0 1 2 3 4 5 6 7 |
// Wrong – no ORDER BY const [rows] = await pool.query('SELECT * FROM tasks') |
→ Order is undefined — MySQL may return rows in any order (often insertion order, but not guaranteed).
Fix: always explicitly write ORDER BY when order matters.
Step 4 – ORDER BY with multiple columns
Newest tasks first, then by priority (high → low)
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
export const getTasksMultiSort = async (_req: Request, res: Response) => { try { const [rows] = await pool.query(` SELECT * FROM tasks ORDER BY created_at DESC, FIELD(priority, 'high', 'medium', 'low') `) res.json({ success: true, data: rows }) } catch (err) { res.status(500).json({ success: false, message: 'Failed to fetch tasks' }) } } |
Explanation of FIELD()
FIELD(priority, ‘high’, ‘medium’, ‘low’) returns:
- ‘high’ → 1
- ‘medium’ → 2
- ‘low’ → 3
So sorting by FIELD(…) gives high priority first.
Alternative (more readable)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
ORDER BY created_at DESC, CASE priority WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 ELSE 4 END |
Step 5 – Dynamic ORDER BY (user-controlled sorting – very common in real APIs)
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 |
export const getTasksDynamicSort = async (req: Request, res: Response) => { try { const sortBy = (req.query.sortBy as string) || 'created_at' const sortOrder = (req.query.sortOrder as string || 'desc').toLowerCase() // Whitelist safe columns (prevents SQL injection) const allowedColumns = ['created_at', 'title', 'priority', 'due_date', 'completed'] if (!allowedColumns.includes(sortBy)) { throw new AppError(400, 'Invalid sort column') } // Validate sort direction const direction = sortOrder === 'asc' ? 'ASC' : 'DESC' const [rows] = await pool.execute( `SELECT * FROM tasks ORDER BY ${sortBy} ${direction}` ) res.json({ success: true, data: rows }) } catch (err) { next(err) } } |
Security note
Never do this:
TypeScript
|
0 1 2 3 4 5 6 |
`ORDER BY ${sortBy} ${sortOrder}` // ← SQL injection possible! |
Correct way — whitelist columns and hardcode direction
Step 6 – SELECT with WHERE + ORDER BY + pagination (real API pattern)
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 52 53 54 55 56 57 58 59 60 61 62 63 |
export const getFilteredAndSortedTasks = async (req: Request, res: Response) => { try { const page = Math.max(1, Number(req.query.page) || 1) const limit = Math.min(50, Math.max(5, Number(req.query.limit) || 10)) const offset = (page - 1) * limit const priority = req.query.priority as string const completed = req.query.completed as string let sql = 'SELECT * FROM tasks WHERE 1=1' const params: Record<string, any> = {} if (priority) { sql += ' AND priority = :priority' params.priority = priority } if (completed !== undefined) { sql += ' AND completed = :completed' params.completed = completed === 'true' ? 1 : 0 } // Dynamic sorting const sortBy = (req.query.sortBy as string) || 'created_at' const sortOrder = (req.query.sortOrder as string || 'desc').toLowerCase() const allowedColumns = ['created_at', 'title', 'priority', 'due_date'] const direction = sortOrder === 'asc' ? 'ASC' : 'DESC' if (allowedColumns.includes(sortBy)) { sql += ` ORDER BY ${sortBy} ${direction}` } else { sql += ' ORDER BY created_at DESC' } sql += ' LIMIT :limit OFFSET :offset' const [rows] = await pool.execute(sql, { ...params, limit, offset }) // Get total count (for pagination) const [totalRows] = await pool.execute('SELECT COUNT(*) as count FROM tasks') const total = (totalRows as any[])[0].count res.json({ success: true, data: rows, pagination: { page, limit, total, totalPages: Math.ceil(total / limit), hasNext: page * limit < total, hasPrev: page > 1 } }) } catch (err) { next(err) } } |
Example URLs
- /api/tasks/filter?page=1&limit=10&priority=high&sortBy=title&sortOrder=asc
- /api/tasks/filter?completed=true&sortBy=priority
Security & performance notes
- Named placeholders → safe from injection
- Whitelisted columns → safe dynamic sorting
- LIMIT / OFFSET → prevents returning millions of rows
- COUNT(*) separate query → accurate total (fast with index)
Step 7 – Summary – SELECT FROM with WHERE / ORDER BY best practices
| Best Practice | Why it matters | Code pattern example |
|---|---|---|
| Use named placeholders :name | Security + readability | WHERE email = :email |
| Never concatenate values | Prevents SQL injection | execute(sql, { email }) |
| Whitelist dynamic columns | Prevents injection in ORDER BY / GROUP BY | if (allowedColumns.includes(sortBy)) |
| Always limit columns | Performance + security | SELECT id, title, priority |
| Use pagination (LIMIT + OFFSET) | Must-have for any list endpoint | LIMIT :limit OFFSET :offset |
| Add ORDER BY explicitly | Predictable order | ORDER BY created_at DESC |
| Use indexes on WHERE / ORDER BY columns | 10×–1000× faster queries | CREATE INDEX idx_user_id ON tasks(user_id) |
| Log slow queries in production | Find performance killers | MySQL slow query log + Node.js logging |
Which part would you like to go much deeper into next?
- Login + JWT 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
Just tell me what you want to build or understand next — I’ll continue with complete, secure, production-ready code and explanations. 😊
