Chapter 55: Node.js MySQL Insert Into
Node.js MySQL Insert Into
We are going to build this knowledge together step by step, as if I am sitting next to you right now:
- I open MySQL Workbench / terminal / DBeaver
- I open VS Code
- I type every command and every line of code live
- I explain why we do things this way
- I show what most beginners do wrong
- I show what intermediate developers often forget
- I show what production-grade code actually looks like
Step 1 – Quick reminder: Why INSERT INTO is so important
- Almost every real application starts with creating data
- In Node.js + MySQL, wrong INSERT →
- SQL injection vulnerabilities
- Duplicate entries
- Missing created_at / updated_at
- Wrong foreign key handling
- No error handling → silent failures
- No logging → impossible to debug in production
Goal today: learn how to write safe, clean, maintainable, observable INSERT code that real companies actually use.
Step 2 – Project setup (already have? skip to step 3)
Bash
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# If you don't have project yet mkdir task-api-mysql-insert cd task-api-mysql-insert 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 (minimal but strict)
JSON
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
{ "compilerOptions": { "target": "ES2022", "module": "NodeNext", "moduleResolution": "NodeNext", "esModuleInterop": 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 3 – 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) multipleStatements: false // ← security: prevent stacked queries }) // One-time connection test on startup async function checkConnection() { try { const conn = await pool.getConnection() console.log('MySQL connection pool is ready') conn.release() } catch (err) { console.error('Cannot connect to MySQL:', err) process.exit(1) // fail fast in development } } checkConnection() |
Why pooling is non-negotiable
- Creating new connection per request → very slow + exhausts server resources
- Pool reuses connections → fast + safe
- connectionLimit: 10 is good starting point (adjust later based on traffic)
Step 4 – Create a simple users table (if not already done)
Run this in MySQL Workbench / terminal once:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE DATABASE IF NOT EXISTS task_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE task_app; CREATE TABLE IF NOT EXISTS 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 ); |
Why these choices?
- CHAR(36) + UUID() → safe for distributed systems, no auto-increment collision
- utf8mb4_unicode_ci → supports emojis, all languages
- UNIQUE on email → prevents duplicate registrations
- TIMESTAMP with ON UPDATE → auto-maintains created/updated time
Step 5 – First realistic INSERT – Register a user
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 |
import { Request, Response } from 'express' import { z } from 'zod' import bcrypt from 'bcryptjs' import { randomUUID } from 'node:crypto' import pool from '../config/database.js' import { AppError } from '../middleware/error.middleware.js' const registerSchema = z.object({ email: z.string().email('Invalid email format'), password: z.string().min(8, 'Password must be at least 8 characters'), name: z.string().min(2).max(100).optional() }) export const registerUser = async (req: Request, res: Response) => { try { // 1. Validate input with Zod const { email, password, name } = registerSchema.parse(req.body) // 2. Check if email already exists (important!) const [existing] = await pool.execute( 'SELECT id FROM users WHERE email = :email', { email } ) if ((existing as any[]).length > 0) { throw new AppError(409, 'Email already registered') } // 3. Hash password (never store plain text!) const passwordHash = await bcrypt.hash(password, 12) // 4. Generate secure UUID const id = randomUUID() // 5. INSERT with named placeholders → prevents SQL injection await pool.execute( `INSERT INTO users (id, email, password_hash, name) VALUES (:id, :email, :passwordHash, :name)`, { id, email, passwordHash, name: name || null } ) // 6. Return clean response (never return password!) res.status(201).json({ success: true, message: 'User registered successfully', user: { id, email, name: name || null } }) } catch (err) { if (err instanceof z.ZodError) { throw new AppError(400, 'Validation failed', true) } throw err } } |
src/routes/auth.routes.ts
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
import { Router } from 'express' import { registerUser } from '../controllers/auth.controller.js' const router = Router() router.post('/register', registerUser) export default router |
src/index.ts (minimal server)
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
import express from 'express' import cors from 'cors' import helmet from 'helmet' import env from './config/env.js' import authRoutes from './routes/auth.routes.js' const app = express() app.use(helmet()) app.use(cors()) app.use(express.json()) app.use('/api/auth', authRoutes) app.listen(env.PORT, () => { console.log(`Server running → http://localhost:${env.PORT}`) }) |
Test it (run server first with npm run dev)
Bash
|
0 1 2 3 4 5 6 7 8 |
curl -X POST http://localhost:5000/api/auth/register \ -H "Content-Type: application/json" \ -d '{"email":"test@example.com","password":"strongpass123","name":"Test User"}' |
Expected response
JSON
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
{ "success": true, "message": "User registered successfully", "user": { "id": "some-uuid-here", "email": "test@example.com", "name": "Test User" } } |
Step 6 – Important production patterns for INSERT
Pattern 1 – Always use named placeholders (not ?)
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
// Safe (named placeholders) await pool.execute( 'INSERT INTO users (id, email) VALUES (:id, :email)', { id: randomUUID(), email: 'test@example.com' } ) // Dangerous (avoid) await pool.execute( 'INSERT INTO users (id, email) VALUES (?, ?)', [randomUUID(), 'test@example.com'] ) |
Why named placeholders are better
- More readable
- Order doesn’t matter
- Less chance of wrong parameter order
- Easier to debug
Pattern 2 – Insert + immediately return inserted row
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
const [result] = await pool.execute( 'INSERT INTO users (...) VALUES (...)', { ...data } ) const insertedId = (result as any).insertId // for auto-increment // or use UUID which we already know const [rows] = await pool.execute( 'SELECT * FROM users WHERE id = ?', [insertedId || uuidWeGenerated] ) const newUser = (rows as any[])[0] |
Pattern 3 – Bulk insert (when inserting many rows)
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
const usersToInsert = [ { id: randomUUID(), email: 'a@example.com', password_hash: 'hash1' }, { id: randomUUID(), email: 'b@example.com', password_hash: 'hash2' } ] const values = usersToInsert.map(u => [u.id, u.email, u.password_hash]) const placeholders = values.map(() => '(?, ?, ?)').join(', ') await pool.execute( `INSERT INTO users (id, email, password_hash) VALUES {placeholders}`, values.flat() ) |
Summary – MySQL INSERT best practices in Node.js 2025–2026
| Best Practice | Why it matters | Code pattern example |
|---|---|---|
| Use named placeholders (:name) | Safety + readability | VALUES (:id, :email) |
| Always hash passwords (bcrypt) | Security – never store plain text | bcrypt.hash(password, 12) |
| Use UUID instead of auto-increment | Safer for distributed systems | randomUUID() |
| Validate input before INSERT | Prevents bad data + better error messages | zod.parse() |
| Check existing before insert | Avoids duplicate key errors | SELECT … WHERE email = ? |
| Use connection pool | Performance + resource safety | mysql2/promise.createPool() |
| Handle errors properly | Fail gracefully, log meaningfully | try/catch + custom AppError |
| Return inserted row | Client gets full record immediately | SELECT * FROM … WHERE id = ? |
Which part would you like to go much deeper into next?
- Login + JWT authentication with MySQL
- Task CRUD (create, read, update, delete) with ownership check
- 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
- Add connection retry, slow query logging, monitoring
Just tell me what you want to build or understand next — I’ll continue with complete, secure, production-ready code and explanations. 😊
