Chapter 57: Node.js MySQL Where
WHERE Clauses (and all related filtering) when working with MySQL in Node.js.
I will explain it as if I am sitting next to you right now:
- We open MySQL Workbench / terminal / DBeaver on one side
- We open VS Code on the other side
- We type every SQL line and every line of Node.js code live
- I explain why we write it this way (security, performance, readability)
- I show what 90% of beginners do wrong
- I show what intermediate developers often forget
- I show what real production code looks like in 2025–2026
Goal of this lesson
Learn how to write safe, fast, readable, maintainable WHERE conditions from Node.js to MySQL.
We will cover:
- Basic WHERE
- WHERE with parameters (prevents SQL injection – very important)
- WHERE with multiple conditions (AND / OR)
- WHERE with LIKE (search)
- WHERE with IN (multiple values)
- WHERE with JOIN
- WHERE with pagination (LIMIT + OFFSET)
- WHERE with dynamic filters (user-controlled search/sort)
- Error handling & empty result handling
- Production logging & timing
Step 1 – Starting project (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-where cd task-api-mysql-where 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 – Test data (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 |
USE task_app; -- Clear old data TRUNCATE TABLE tasks; TRUNCATE TABLE users; -- Insert users INSERT INTO users (id, email, password_hash, name) VALUES (UUID(), 'aman@example.com', 'hash1', 'Aman'), (UUID(), 'priya@example.com', 'hash2', 'Priya'), (UUID(), 'rahul@example.com', 'hash3', 'Rahul'), (UUID(), 'sara@example.com', 'hash4', 'Sara'); -- Insert tasks (some completed, some not) INSERT INTO tasks (id, user_id, title, description, priority, due_date, completed) VALUES (UUID(), (SELECT id FROM users WHERE email = 'aman@example.com'), 'Finish quarterly report', 'Include sales figures', 'high', '2025-03-05 18:00:00', false), (UUID(), (SELECT id FROM users WHERE email = 'aman@example.com'), 'Team meeting notes', null, 'medium', null, true), (UUID(), (SELECT id FROM users WHERE email = 'priya@example.com'), 'Design new logo', 'Client wants minimalist style', 'high', '2025-03-01 23:59:00', false), (UUID(), (SELECT id FROM users WHERE email = 'rahul@example.com'), 'Update website footer', 'Add new contact info', 'low', null, false), (UUID(), (SELECT id FROM users WHERE email = 'sara@example.com'), 'Prepare presentation', 'For Monday board meeting', 'high', '2025-02-28 10:00:00', false); |
Step 3 – Basic SELECT FROM (get all tasks)
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 |
import { Request, Response } from 'express' import pool from '../config/database.js' export const getAllTasks = async (_req: Request, res: Response) => { try { // Simple SELECT – returns all rows const [rows] = await pool.query('SELECT * FROM tasks') 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' }) } } |
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 { getAllTasks } from '../controllers/task.controller.js' const router = Router() router.get('/', getAllTasks) 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
You should see all 5 tasks.
Common beginner mistake
TypeScript
|
0 1 2 3 4 5 6 7 |
const [rows] = await pool.query('SELECT * FROM tasks') res.send(rows) // ← wrong: sends raw array without wrapper |
Better: always wrap response
TypeScript
|
0 1 2 3 4 5 6 |
res.json({ success: true, data: rows }) |
Step 4 – SELECT with WHERE (single condition)
Get tasks for a specific user
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 |
export const getTasksByUser = async (req: Request<{ userId: string }>, res: Response) => { try { const userId = req.params.userId // SAFE: named placeholder const [rows] = await pool.execute( 'SELECT * FROM tasks WHERE user_id = :userId ORDER BY created_at DESC', { userId } ) res.json({ success: true, count: (rows as any[]).length, data: rows }) } catch (err) { res.status(500).json({ success: false, message: 'Failed to fetch user tasks' }) } } |
Route
TypeScript
|
0 1 2 3 4 5 6 |
router.get('/user/:userId', getTasksByUser) |
Why :userId is safer than ?
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 |
// SAFE & readable 'WHERE user_id = :userId' // Still safe, but harder to read when many parameters 'WHERE user_id = ? AND title LIKE ? AND completed = ?' |
Step 5 – SELECT with multiple conditions (AND / OR)
Filter tasks by priority & completed status
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 |
export const getFilteredTasks = async (req: Request, res: Response) => { try { const { priority, completed } = req.query 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 } sql += ' ORDER BY created_at DESC' const [rows] = await pool.execute(sql, params) res.json({ success: true, count: (rows as any[]).length, data: rows }) } catch (err) { res.status(500).json({ success: false, message: 'Failed to fetch tasks' }) } } |
Example URLs
- /api/tasks/filter?priority=high
- /api/tasks/filter?completed=true
- /api/tasks/filter?priority=medium&completed=false
Dynamic WHERE pattern (very common in real APIs)
Always start with WHERE 1=1 — makes adding AND conditions safe.
Step 6 – SELECT with JOIN (user info + tasks)
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 |
export const getTasksWithUserInfo = async (_req: Request, res: Response) => { try { const [rows] = await pool.execute(` SELECT t.id AS task_id, t.title, t.priority, t.completed, u.id AS user_id, u.email, u.name FROM tasks t INNER JOIN users u ON t.user_id = u.id ORDER BY t.created_at DESC `) res.json({ success: true, count: (rows as any[]).length, data: rows }) } catch (err) { res.status(500).json({ success: false, message: 'Failed to fetch tasks' }) } } |
INNER JOIN vs LEFT JOIN
- INNER JOIN → only tasks that have matching user
- LEFT JOIN → all tasks, even if user is deleted (user fields will be NULL)
Use INNER JOIN unless you have a very good reason for LEFT.
Step 7 – SELECT with pagination (must-have for real APIs)
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 |
export const getPaginatedTasks = 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 [rows] = await pool.execute( `SELECT * FROM tasks ORDER BY created_at DESC LIMIT :limit OFFSET :offset`, { limit, offset } ) 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) { res.status(500).json({ success: false, message: 'Failed to fetch tasks' }) } } |
Security note
Always parameterize :limit and :offset Never concatenate:
TypeScript
|
0 1 2 3 4 5 6 7 |
// DANGEROUS `LIMIT ${limit} OFFSET ${offset}` |
Step 8 – Summary – SELECT best practices in Node.js + MySQL 2025–2026
| 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 }) not sql + email |
| Always limit columns | Performance + security | SELECT id, email, name not SELECT * |
| Use LEFT JOIN when data may be missing | Avoid losing rows | LEFT JOIN tasks t ON u.id = t.user_id |
| Use pagination (LIMIT + OFFSET) | Must-have for any list endpoint | LIMIT :limit OFFSET :offset |
| Add ORDER BY | Consistent results | ORDER BY created_at DESC |
| Use indexes on WHERE / JOIN / ORDER BY | 10×–1000× faster queries | CREATE INDEX idx_user_id ON tasks(user_id) |
| Handle empty result gracefully | Good API UX | if (!rows.length) return res.json([]) |
| 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)
- 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. 😊
