Chapter 53: Node.js MySQL Create Database
MySQL database in a Node.js application.
I will explain it as if I am sitting next to you right now — I open MySQL Workbench / terminal / VS Code, I show every single command, every decision, every line of code, and I explain why we do things this way, what most beginners get wrong, what intermediate developers often forget, and what production-grade code actually looks like in 2025–2026.
We will build everything from absolute zero — no skipping steps.
Goal of this lesson
We will:
- Install and run MySQL locally (or on cloud)
- Create a real database and tables with proper structure
- Connect Node.js to MySQL using mysql2/promise (the modern & recommended way)
- Create a complete mini-API that creates, reads, updates and deletes data
- Add validation, error handling, security basics, and best practices
Step 1 – Install and run MySQL locally (Windows / macOS / Linux)
Windows
- Download MySQL Community Server from official site → https://dev.mysql.com/downloads/mysql/ → Choose MySQL Installer for Windows → Developer Default
- Install → choose Server Only or Full → set root password (remember it!)
- After install → open MySQL Workbench (comes with installer) or use command line:
Bash
|
0 1 2 3 4 5 6 7 |
mysql -u root -p # enter your root password |
macOS (recommended way – Homebrew)
Bash
|
0 1 2 3 4 5 6 7 8 |
brew install mysql brew services start mysql mysql_secure_installation # set root password, remove anonymous users, etc. |
Ubuntu / Debian
Bash
|
0 1 2 3 4 5 6 7 8 |
sudo apt update sudo apt install mysql-server sudo mysql_secure_installation |
Cloud alternative (fastest for learning)
Use PlanetScale free tier, Neon, Railway, Supabase or Aiven → they give you a ready DATABASE_URL — no local install needed
For this tutorial we’ll assume local MySQL — but the code works exactly the same with cloud.
Step 2 – Create the database and tables (with good practices)
Open MySQL Workbench or terminal and run these commands one by one.
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
-- 1. Create database (use utf8mb4 for full emoji & international support) CREATE DATABASE task_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 2. Switch to it USE task_app; -- 3. Create users table CREATE TABLE users ( id CHAR(36) PRIMARY KEY, -- UUID 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 ); -- 4. Create tasks table (with foreign key) CREATE TABLE tasks ( id CHAR(36) PRIMARY KEY, user_id CHAR(36) NOT NULL, title VARCHAR(150) NOT NULL, description TEXT, priority ENUM('low', 'medium', 'high') DEFAULT 'medium', due_date DATETIME, completed BOOLEAN DEFAULT FALSE, 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 ); -- 5. Create index for faster email lookup CREATE INDEX idx_users_email ON users(email); -- 6. Optional: create a test user manually INSERT INTO users (id, email, password_hash, name) VALUES ( UUID(), 'test@example.com', '$2a$12$examplehashgoeshere', -- real hash later via bcrypt 'Test User' ); |
Why these choices?
- CHAR(36) for UUID → no auto-increment collision issues in distributed systems
- utf8mb4_unicode_ci → supports emojis, all languages, modern standard
- ON DELETE CASCADE → when user is deleted → his tasks are automatically deleted
- TIMESTAMP with ON UPDATE → auto-maintains created/updated times
- Index on email → login becomes fast
Step 3 – Connect Node.js to MySQL (mysql2/promise – best choice)
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 35 36 37 |
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 || 'task_app', waitForConnections: true, connectionLimit: 10, // adjust based on traffic queueLimit: 0, namedPlaceholders: true, // allows :name syntax (safer) timezone: '+00:00', // store everything in UTC // Very important security flags ssl: env.NODE_ENV === 'production' ? { rejectUnauthorized: true } : undefined }) // Test connection on startup async function initializeDatabase() { try { const connection = await pool.getConnection() console.log('MySQL connection pool initialized successfully') connection.release() } catch (err) { console.error('Failed to connect to MySQL:', err) process.exit(1) // fail fast in development } } initializeDatabase() export default pool |
src/config/env.ts (Zod validation)
TypeScript
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
import { z } from 'zod' import 'dotenv/config' const envSchema = z.object({ PORT: z.coerce.number().default(5000), NODE_ENV: z.enum(['development', 'production', 'test']).default('development'), DB_HOST: z.string().default('localhost'), DB_USER: z.string().default('root'), DB_PASSWORD: z.string().optional(), DB_NAME: z.string().default('task_app'), JWT_SECRET: z.string().min(32) }) export const env = envSchema.parse(process.env) |
Step 4 – Create first API – Register 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 |
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(), password: z.string().min(8, 'Password must be at least 8 characters'), name: z.string().min(2).max(100).optional() }) export const register = async (req: Request, res: Response) => { const { email, password, name } = registerSchema.parse(req.body) // Check if email already exists const [existing] = await pool.execute( 'SELECT id FROM users WHERE email = ?', [email] ) if ((existing as any[]).length > 0) { throw new AppError(409, 'Email already in use') } const passwordHash = await bcrypt.hash(password, 12) const id = randomUUID() await pool.execute( `INSERT INTO users (id, email, password_hash, name) VALUES (?, ?, ?, ?)`, [id, email, passwordHash, name || null] ) res.status(201).json({ success: true, message: 'User registered successfully', user: { id, email, name: name || null } }) } |
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 { register } from '../controllers/auth.controller.js' const router = Router() router.post('/register', register) export default router |
src/index.ts (connect everything)
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 |
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.get('/health', (req, res) => { res.json({ status: 'ok', database: 'connected' }) }) app.listen(env.PORT, () => { console.log(`Server running → http://localhost:${env.PORT}`) }) |
Test it:
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":"password123","name":"Test User"}' |
You should get:
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 5 – Summary – MySQL + Node.js in 2025–2026 feels like this
You now have:
- Secure connection pool (mysql2/promise)
- Environment + database validation (Zod)
- Raw SQL with prepared statements (prevents SQL injection)
- UUID primary keys (scalable)
- Proper error handling
- TypeScript safety
- Modern ESM project
This is very close to how many real Node.js + MySQL backends are built today (before adding Prisma/Drizzle/TypeORM).
Which direction would you like to go next?
- Add login + JWT authentication
- Add task CRUD endpoints with user ownership
- Replace raw SQL with Prisma (most popular choice)
- Replace raw SQL with Drizzle ORM (type-safe SQL)
- Add connection pooling tuning, slow query logging, monitoring
- Add Docker + docker-compose with MySQL container
Just tell me what you want to build or understand next — I’ll continue with complete, secure, production-ready code and explanations. 😊
