Chapter 56: Node.js MySQL Select From
SELECT FROM (and all related SELECT queries) in MySQL using Node.js — explained as if I am sitting next to you right now.
We will go extremely slowly, step by step, with lots of reasoning, copy-paste-ready code, explanations of every line, common beginner traps, intermediate-level mistakes, production-grade patterns, and 2025–2026 best practices.
Goal of this chapter
We will learn how to write safe, clean, performant, readable, maintainable SELECT queries from Node.js to MySQL.
We will cover:
- Basic SELECT
- WHERE + parameters (very important for security)
- SELECT with JOIN
- SELECT with pagination + LIMIT + OFFSET
- SELECT with ORDER BY
- SELECT with COUNT + aggregate functions
- SELECT single row vs many rows
- Error handling & empty result handling
- Production patterns (connection pool, logging, timing)
Step 1 – Project setup (already have? skip to step 2)
Bash
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
mkdir task-api-mysql-select cd task-api-mysql-select 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" } |
Step 2 – Database connection pool (must-have in real apps)
src/config/database.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 |
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, // ← very important: pooling! queueLimit: 0, timezone: '+00:00', // ← store everything in UTC namedPlaceholders: true, // ← allows :name syntax (safer & readable) multipleStatements: false // ← security: prevent stacked queries }) // Test connection on startup async function initialize() { try { const conn = await pool.getConnection() console.log('MySQL pool initialized successfully') conn.release() } catch (err) { console.error('MySQL connection failed:', err) process.exit(1) // fail fast in development } } initialize() |
Why pooling is non-negotiable
- Creating new connection per request → extremely slow + can crash MySQL server
- Pool reuses existing connections → fast + safe
- connectionLimit: 10 is a good starting point (adjust later: 20–50 for medium app)
Step 3 – Create some 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 18 19 20 21 22 23 24 |
USE task_app; -- Clear previous data (optional) TRUNCATE TABLE tasks; TRUNCATE TABLE users; -- Insert 4 test users INSERT INTO users (id, email, password_hash, name) VALUES (UUID(), 'aman@example.com', 'hashedpass1', 'Aman'), (UUID(), 'priya@example.com', 'hashedpass2', 'Priya'), (UUID(), 'rahul@example.com', 'hashedpass3', 'Rahul'), (UUID(), 'sara@example.com', 'hashedpass4', 'Sara'); -- Insert 10 sample tasks (belonging to different users) INSERT INTO tasks (id, user_id, title, description, priority, due_date, completed) VALUES (UUID(), (SELECT id FROM users WHERE email = 'aman@example.com'), 'Finish report', 'Q4 sales report', 'high', '2025-03-01 18:00:00', false), (UUID(), (SELECT id FROM users WHERE email = 'aman@example.com'), 'Call client', null, 'medium', null, true), (UUID(), (SELECT id FROM users WHERE email = 'priya@example.com'), 'Design logo', 'New client logo', 'high', '2025-02-28 23:59:00', false), (UUID(), (SELECT id FROM users WHERE email = 'rahul@example.com'), 'Update website', 'Fix mobile responsiveness', 'low', null, false); |
Now we have real data to query!
Step 4 – Basic SELECT – Get all users
src/controllers/user.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 |
import { Request, Response } from 'express' import pool from '../config/database.js' export const getAllUsers = async (_req: Request, res: Response) => { try { // Simple SELECT – returns all columns const [rows] = await pool.query('SELECT * FROM users') // rows is array of objects 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: 'Database error' }) } } |
src/routes/user.routes.ts
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
import { Router } from 'express' import { getAllUsers } from '../controllers/user.controller.js' const router = Router() router.get('/users', getAllUsers) export default router |
Add to index.ts
TypeScript
|
0 1 2 3 4 5 6 |
app.use('/api', userRoutes) |
Now go to: http://localhost:5000/api/users
You should see all users!
Step 5 – SELECT with WHERE + named placeholders (very important – security)
Get single user by email
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 |
export const getUserByEmail = async (req: Request, res: Response) => { try { const email = req.query.email as string if (!email) { return res.status(400).json({ error: 'Email is required' }) } // SAFE: using named placeholder :email const [rows] = await pool.execute( 'SELECT id, email, name, created_at FROM users WHERE email = :email', { email } ) if ((rows as any[]).length === 0) { return res.status(404).json({ error: 'User not found' }) } res.json({ success: true, data: (rows as any[])[0] }) } catch (err) { res.status(500).json({ error: 'Database error' }) } } |
Why named placeholders are the best practice
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 |
// SAFE – impossible to do SQL injection 'WHERE email = :email' // ← correct // DANGEROUS – NEVER DO THIS 'WHERE email = "' + email + '"' // ← SQL injection possible! |
Even safer pattern – always limit columns
SQL
|
0 1 2 3 4 5 6 7 8 |
SELECT id, email, name, created_at -- never do SELECT * FROM users WHERE email = :email |
Step 6 – SELECT with JOIN (user + their tasks)
Get user with all his 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 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 |
export const getUserWithTasks = async (req: Request<{ id: string }>, res: Response) => { try { const userId = req.params.id const [rows] = await pool.execute( `SELECT u.id AS user_id, u.email, u.name, t.id AS task_id, t.title, t.description, t.completed, t.priority, t.due_date FROM users u LEFT JOIN tasks t ON u.id = t.user_id WHERE u.id = :userId ORDER BY t.created_at DESC`, { userId } ) if ((rows as any[]).length === 0) { throw new AppError(404, 'User not found') } // Group tasks under user (very common pattern) const user = { id: (rows as any)[0].user_id, email: (rows as any)[0].email, name: (rows as any)[0].name, tasks: (rows as any[]) .filter(row => row.task_id !== null) .map(row => ({ id: row.task_id, title: row.title, description: row.description, completed: row.completed, priority: row.priority, dueDate: row.due_date })) } res.json({ success: true, data: user }) } catch (err) { next(err) } } |
Result shape
JSON
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
{ "success": true, "data": { "id": "uuid-here", "email": "aman@example.com", "name": "Aman", "tasks": [ { "id": "...", "title": "Finish report", ... }, { "id": "...", "title": "Call client", ... } ] } } |
Step 7 – SELECT with pagination (very important 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 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) { next(err) } } |
URL examples
- GET /api/tasks?page=1&limit=10
- GET /api/tasks?page=3&limit=20
Very important security note
Always use parameters (:limit, :offset) Never concatenate strings:
TypeScript
|
0 1 2 3 4 5 6 7 |
// DANGEROUS – SQL injection possible `LIMIT ${limit} OFFSET ${offset}` |
Summary – SELECT FROM 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 |
| Always limit columns | Performance + security | SELECT id, email, name not SELECT * |
| Use prepared statements | Prevents SQL injection | pool.execute(sql, values) |
| Handle empty result properly | Avoid sending null / undefined to frontend | if (!rows.length) throw 404 |
| Use pagination (LIMIT + OFFSET) | Must-have for any real list endpoint | LIMIT :limit OFFSET :offset |
| Use connection pool | Performance + resource safety | mysql2.createPool() |
| Log slow queries in production | Find performance killers | MySQL slow query log + Node.js logging |
| Use indexes on WHERE / JOIN columns | 10×–1000× faster queries | CREATE INDEX idx_email ON users(email) |
Which part would you like to go much deeper into next?
- Login + JWT authentication with MySQL
- Task CRUD (full create/read/update/delete + ownership check)
- 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. 😊
