Chapter 62: Node.js MySQL Limit
LIMIT and OFFSET in MySQL queries from Node.js.
We will go through everything slowly and step by step, as if I am sitting next to you right now:
- I open the MySQL client (Workbench / terminal / DBeaver) on one side
- I 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, UX)
- I show what most beginners do wrong (very common & dangerous mistakes)
- I show what intermediate developers often forget (leads to bad APIs)
- I show what real production code looks like in 2025–2026
Goal of this lesson
Learn how to implement pagination (LIMIT + OFFSET) correctly and safely in Node.js + MySQL.
At the end, you will be able to build a production-ready paginated list endpoint that:
- Returns only 10–50 rows at a time
- Lets the frontend ask for page 1, page 2, page 3…
- Shows total count & total pages
- Is safe from SQL injection
- Is performant even with 1 million rows
- Gives good API UX (hasNext, hasPrev, etc.)
Step 1 – Quick reminder: What LIMIT and OFFSET actually do
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT * FROM tasks LIMIT 10 OFFSET 0; -- page 1 (rows 1–10) SELECT * FROM tasks LIMIT 10 OFFSET 10; -- page 2 (rows 11–20) SELECT * FROM tasks LIMIT 10 OFFSET 20; -- page 3 (rows 21–30) |
- LIMIT n = maximum number of rows to return
- OFFSET m = how many rows to skip from the beginning
Very important facts
- OFFSET starts from 0 (not 1)
- OFFSET can be very slow when large (e.g. OFFSET 1000000) — we’ll discuss solutions later
- MySQL does not guarantee order without ORDER BY → always add ORDER BY
Step 2 – 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-limit cd task-api-mysql-limit 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
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 3 – Test data (run once in MySQL)
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 20+ sample tasks (so we can paginate) INSERT INTO tasks (id, user_id, title, priority, created_at) VALUES (UUID(), 'user1', 'Task 1 - Urgent report', 'high', '2025-01-01 10:00:00'), (UUID(), 'user1', 'Task 2 - Call client', 'medium', '2025-01-02 11:00:00'), (UUID(), 'user1', 'Task 3 - Design logo', 'high', '2025-01-03 12:00:00'), -- ... repeat until 20+ rows ... (UUID(), 'user2', 'Task 20 - Review code', 'low', '2025-01-20 14:00:00'); |
Step 4 – First paginated endpoint (basic version)
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 |
import { Request, Response } from 'express' import pool from '../config/database.js' import { AppError } from '../middleware/error.middleware.js' export const getPaginatedTasks = async (req: Request, res: Response) => { try { // Get page & limit from query (with safe defaults) 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 // Main query with pagination const [rows] = await pool.execute( `SELECT id, title, priority, created_at FROM tasks ORDER BY created_at DESC LIMIT :limit OFFSET :offset`, { limit, offset } ) // Get total count (for pagination info) const [totalResult] = await pool.execute('SELECT COUNT(*) as total FROM tasks') const total = (totalResult as any[])[0].total res.json({ success: true, data: rows, pagination: { page, limit, total, totalPages: Math.ceil(total / limit), hasNext: page * limit < total, hasPrev: page > 1 } }) } catch (err) { console.error('Query error:', err) throw new AppError(500, 'Failed to fetch tasks') } } |
Route
TypeScript
|
0 1 2 3 4 5 6 |
router.get('/', getPaginatedTasks) |
Test URLs
- http://localhost:5000/api/tasks?page=1&limit=5
- http://localhost:5000/api/tasks?page=2&limit=5
- http://localhost:5000/api/tasks?page=3&limit=10
Very important security notes
Wrong & dangerous (SQL injection possible)
TypeScript
|
0 1 2 3 4 5 6 |
`LIMIT ${limit} OFFSET ${offset}` // NEVER DO THIS |
Correct & safe
TypeScript
|
0 1 2 3 4 5 6 7 |
'LIMIT :limit OFFSET :offset', { limit, offset } |
Why parameters are mandatory
MySQL treats :limit as a value, not code → impossible to inject malicious SQL.
Step 5 – Dynamic ORDER BY + WHERE + pagination (real API)
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 52 53 54 55 56 57 58 59 60 61 62 63 64 |
export const getAdvancedTasks = async (req: Request, res: Response) => { try { // Pagination 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 // Filters const completed = req.query.completed const priority = req.query.priority // Sorting const sortBy = (req.query.sortBy as string) || 'created_at' const sortOrder = (req.query.sortOrder as string || 'desc').toLowerCase() === 'asc' ? 'ASC' : 'DESC' // Whitelist safe columns for sorting const allowedSortColumns = ['created_at', 'title', 'priority', 'due_date'] const safeSortBy = allowedSortColumns.includes(sortBy) ? sortBy : 'created_at' // Build WHERE clause dynamically let sql = 'SELECT id, title, priority, completed, due_date, created_at FROM tasks WHERE 1=1' const params: Record<string, any> = {} if (completed !== undefined) { sql += ' AND completed = :completed' params.completed = completed === 'true' ? 1 : 0 } if (priority) { sql += ' AND priority = :priority' params.priority = priority } // Add sorting & pagination sql += ` ORDER BY ${safeSortBy} ${sortOrder}` sql += ' LIMIT :limit OFFSET :offset' const [rows] = await pool.execute(sql, { ...params, limit, offset }) // Total count (for pagination metadata) const [totalRows] = await pool.execute('SELECT COUNT(*) as total FROM tasks') const total = (totalRows as any[])[0].total 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?page=1&limit=10&completed=true&sortBy=priority&sortOrder=asc
- /api/tasks?page=2&limit=20&priority=high
Security & performance notes
- Named placeholders → safe
- Whitelisted sort columns → safe dynamic ORDER BY
- LIMIT / OFFSET parameterized → safe
- COUNT(*) separate query → accurate total (fast with index)
- WHERE 1=1 → makes adding AND conditions safe & clean
Step 6 – Summary – ORDER BY + LIMIT best practices in Node.js + MySQL 2025–2026
| Best Practice | Why it matters | Code pattern example |
|---|---|---|
| Always use ORDER BY | Predictable order – MySQL doesn’t guarantee anything | ORDER BY created_at DESC |
| Use named placeholders for LIMIT/OFFSET | Security + readability | LIMIT :limit OFFSET :offset |
| Whitelist dynamic sort columns | Prevents SQL injection in ORDER BY | if (allowedColumns.includes(sortBy)) |
| Use pagination metadata | Good API UX (totalPages, hasNext, hasPrev) | Math.ceil(total / limit) |
| Add indexes on ORDER BY / WHERE columns | 10×–1000× faster queries | CREATE INDEX idx_created_at ON tasks(created_at) |
| Use connection pool | Performance + safety | mysql2.createPool() |
| Separate COUNT query | Accurate total count | SELECT COUNT(*) as total FROM tasks |
| 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 authentication with MySQL
- Full task CRUD (create/read/update/delete + ownership)
- Add search, filtering, sorting, pagination all together
- 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. 😊
