Chapter 52: Node.js MySQL
MySQL with Node.js — explained as if I’m sitting next to you right now, building a real project together.
We will go step by step, slowly, with lots of reasoning, copy-paste-ready code, common beginner traps, intermediate mistakes, production-grade patterns and 2025–2026 best practices.
Goal of this tutorial
We will build a realistic Task Management REST API that uses MySQL as the database, including:
- User registration & login (JWT)
- CRUD operations on personal tasks
- Input validation (Zod)
- Proper error handling
- Connection pooling
- Environment variables + validation
- Logging
- Security headers
- TypeScript + ESM
This is the kind of foundation many real Node.js backends use today.
Step 1 – Project setup (modern & realistic)
Bash
|
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 |
mkdir task-api-mysql cd task-api-mysql # Initialize with ESM npm init -y npm pkg set type=module # Core runtime dependencies npm install \ express \ cors \ helmet \ compression \ dotenv \ zod \ jsonwebtoken \ bcryptjs \ mysql2 \ express-rate-limit # Development dependencies npm install -D \ typescript \ @types/node \ @types/express \ @types/cors \ @types/jsonwebtoken \ @types/bcryptjs \ tsx \ nodemon \ eslint \ prettier \ eslint-config-standard-with-typescript \ @typescript-eslint/parser \ @typescript-eslint/eslint-plugin |
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 20 21 22 23 |
{ "compilerOptions": { "target": "ES2022", "module": "NodeNext", "moduleResolution": "NodeNext", "esModuleInterop": true, "forceConsistentCasingInFileNames": true, "strict": true, "noImplicitAny": true, "skipLibCheck": true, "outDir": "./dist", "rootDir": "./src", "sourceMap": true, "noEmitOnError": true }, "include": ["src/**/*"], "exclude": ["node_modules", "dist"] } |
package.json scripts
JSON
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
"scripts": { "dev": "tsx watch src/index.ts", "start": "node dist/index.js", "build": "tsc", "lint": "eslint . --ext .ts", "lint:fix": "eslint . --ext .ts --fix", "format": "prettier --write .", "typecheck": "tsc --noEmit" } |
Step 2 – Folder structure (what most real teams use)
text
|
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 |
task-api-mysql/ ├── src/ │ ├── config/ ← env + db connection │ │ ├── env.ts │ │ └── database.ts │ ├── controllers/ ← HTTP handlers (thin) │ │ └── task.controller.ts │ ├── middleware/ ← auth, validation, error, rate-limit │ │ ├── auth.middleware.ts │ │ ├── error.middleware.ts │ │ ├── rate-limit.middleware.ts │ │ └── validate.middleware.ts │ ├── models/ ← DB queries / repository layer │ │ └── task.model.ts │ ├── routes/ ← route definitions │ │ └── task.routes.ts │ ├── schemas/ ← Zod schemas │ │ └── task.schema.ts │ ├── types/ ← shared types │ │ └── index.ts │ └── index.ts ← entry point ├── .env ├── .env.example ├── tsconfig.json └── package.json |
Step 3 – Database connection with mysql2 (pooling – production must-have)
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 34 |
import mysql from 'mysql2/promise' import env from './env.js' export const pool = mysql.createPool({ host: env.DB_HOST || 'localhost', user: env.DB_USER || 'root', password: env.DB_PASSWORD || '', database: env.DB_NAME || 'taskdb', waitForConnections: true, connectionLimit: 10, queueLimit: 0, enableKeepAlive: true, keepAliveInitialDelay: 0, namedPlaceholders: true }) // Optional: test connection on startup async function testConnection() { try { const connection = await pool.getConnection() console.log('MySQL connection pool ready') connection.release() } catch (err) { console.error('MySQL connection failed:', err) process.exit(1) } } testConnection() |
.env.example (give this to your team)
text
|
0 1 2 3 4 5 6 7 8 9 10 11 |
DB_HOST=localhost DB_USER=root DB_PASSWORD= DB_NAME=taskdb JWT_SECRET=your-very-long-random-secret-here PORT=5000 |
Important security note
- Never commit .env
- Use secret managers (AWS Secrets Manager, Doppler, Infisical, 1Password, HashiCorp Vault) in production
- Use connection pooling (10–30 connections) — never create new connection per request
Step 4 – First real database table (manual SQL – no ORM yet)
Run this in MySQL (via MySQL Workbench, DBeaver, phpMyAdmin, or terminal)
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 26 27 28 29 30 |
CREATE DATABASE taskdb; USE taskdb; CREATE TABLE users ( id CHAR(36) PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, name VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); CREATE TABLE tasks ( id CHAR(36) PRIMARY KEY, user_id CHAR(36) NOT NULL, title VARCHAR(100) NOT NULL, description TEXT, completed BOOLEAN DEFAULT FALSE, due_date DATETIME, priority ENUM('low', 'medium', 'high') DEFAULT 'medium', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); |
Why manual SQL here?
- You understand exactly what is happening
- No magic → easier to debug
- Later we can switch to Prisma/Drizzle without rewriting logic
Step 5 – User registration & login (real production style)
src/controllers/auth.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 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
import { Request, Response } from 'express' import { z } from 'zod' import bcrypt from 'bcryptjs' import jwt from 'jsonwebtoken' import { pool } from '../config/database.js' import env from '../config/env.js' import { AppError } from '../middleware/error.middleware.js' const registerSchema = z.object({ email: z.string().email(), password: z.string().min(8), name: z.string().min(2).optional() }) const loginSchema = z.object({ email: z.string().email(), password: z.string().min(8) }) export const register = async (req: Request, res: Response) => { const { email, password, name } = registerSchema.parse(req.body) const [existing] = await pool.execute( 'SELECT id FROM users WHERE email = ?', [email] ) if ((existing as any[]).length > 0) { throw new AppError(409, 'Email already exists') } const passwordHash = await bcrypt.hash(password, 12) const id = crypto.randomUUID() await pool.execute( 'INSERT INTO users (id, email, password_hash, name) VALUES (?, ?, ?, ?)', [id, email, passwordHash, name || null] ) const token = jwt.sign({ userId: id, email }, env.JWT_SECRET, { expiresIn: '7d' }) res.status(201).json({ success: true, user: { id, email, name: name || null }, token }) } export const login = async (req: Request, res: Response) => { const { email, password } = loginSchema.parse(req.body) const [rows] = await pool.execute( 'SELECT id, email, password_hash, name FROM users WHERE email = ?', [email] ) const user = (rows as any[])[0] if (!user) throw new AppError(401, 'Invalid credentials') const valid = await bcrypt.compare(password, user.password_hash) if (!valid) throw new AppError(401, 'Invalid credentials') const token = jwt.sign({ userId: user.id, email: user.email }, env.JWT_SECRET, { expiresIn: '7d' }) res.json({ success: true, user: { id: user.id, email: user.email, name: user.name }, token }) } |
Step 6 – Protected route middleware
src/middleware/auth.middleware.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 |
import { Request, Response, NextFunction } from 'express' import jwt from 'jsonwebtoken' import env from '../config/env.js' import { AppError } from './error.middleware.js' interface AuthPayload { userId: string email: string } declare global { namespace Express { interface Request { user?: AuthPayload } } } export async function authenticate(req: Request, res: Response, next: NextFunction) { const authHeader = req.headers.authorization if (!authHeader || !authHeader.startsWith('Bearer ')) { throw new AppError(401, 'No token provided') } const token = authHeader.split(' ')[1] try { const decoded = jwt.verify(token, env.JWT_SECRET) as AuthPayload req.user = decoded next() } catch (err) { throw new AppError(401, 'Invalid or expired token') } } |
Step 7 – Task CRUD with MySQL
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 |
import { Request, Response } from 'express' import { pool } from '../config/database.js' import { createTaskSchema } from '../schemas/task.schema.js' import { AppError } from '../middleware/error.middleware.js' export const createTask = async (req: Request, res: Response) => { const input = createTaskSchema.parse(req.body) const userId = req.user!.userId const id = crypto.randomUUID() await pool.execute( `INSERT INTO tasks (id, user_id, title, description, priority, due_date) VALUES (?, ?, ?, ?, ?, ?)`, [id, userId, input.title, input.description || null, input.priority, input.dueDate || null] ) const [rows] = await pool.execute('SELECT * FROM tasks WHERE id = ?', [id]) const task = (rows as any[])[0] res.status(201).json(task) } export const getMyTasks = async (req: Request, res: Response) => { const userId = req.user!.userId const [rows] = await pool.execute( 'SELECT * FROM tasks WHERE user_id = ? ORDER BY created_at DESC', [userId] ) res.json(rows) } |
Summary – MySQL + Node.js in 2025–2026 feels like this
You now have:
- Connection pooling (mysql2/promise)
- Type-safe environment variables (Zod)
- Custom errors + global handler
- JWT authentication
- Secure password hashing (bcrypt)
- Raw SQL queries with prepared statements
- Input validation
- Security headers & compression
This is very close to what many real Node.js backends look like today (with Prisma/Drizzle added later for convenience).
Which direction would you like to go next?
- Add Prisma instead of raw SQL (most popular choice)
- Add Drizzle ORM (type-safe SQL – very fast growing)
- Implement refresh tokens + cookie-based auth
- Add pagination, filtering, sorting for tasks
- Add unit & integration tests with Vitest
- Docker + production deployment checklist
Just tell me what you want to build or understand next — I’ll continue with complete, secure, production-ready code. 😊
