Chapter 54: Node.js MySQL Create Table
Creating tables in MySQL from a Node.js application.
I will explain it as if we are sitting together right now:
- I open MySQL Workbench / terminal / DBeaver on one screen
- I open VS Code on the other screen
- I type every command and every line of code live
- I explain why we do each step 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 in 2025–2026
We will create a realistic database schema for a Task Management Application — including users, tasks, and proper relationships.
Step 1 – Understand why we care so much about table creation
When you create tables in MySQL from Node.js, you have three main paths:
| Approach | When teams use it in 2025–2026 | Pros | Cons | Recommended for you right now? |
|---|---|---|---|---|
| Manual SQL scripts | Small projects, learning, scripts | You understand exactly what happens | No versioning, error-prone | Yes – for learning |
| Prisma migrate | Most modern TypeScript projects | Automatic migrations, type-safe | Some magic, Prisma lock-in | Very popular |
| Drizzle-kit push / migrate | Teams that want full SQL control | Type-safe SQL, zero magic | Slightly more code | Fastest growing |
| Knex.js migrations | Teams that like query builder | Very flexible | Older style | Still used |
| TypeORM migrations | Enterprise / NestJS projects | Decorator-based | Heavy, performance concerns | Common in large companies |
Today we will do two versions:
- Manual SQL + Node.js execution (learning & full control)
- Prisma migrate (most popular modern way)
Both are used in real companies — you should understand both.
Step 2 – Manual SQL + Node.js (learning & control path)
2.1 Create database connection pool
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 || 'task_app', waitForConnections: true, connectionLimit: 10, queueLimit: 0, namedPlaceholders: true, // allows :name syntax timezone: '+00:00', // store everything in UTC multipleStatements: true // important for running CREATE scripts }) // Test connection on startup async function testConnection() { try { const conn = await pool.getConnection() console.log('MySQL pool ready') conn.release() } catch (err) { console.error('MySQL connection failed:', err) process.exit(1) } } testConnection() |
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) |
2.2 Create database + tables from Node.js (one-time setup script)
src/setup/create-tables.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 |
import pool from '../config/database.js' async function createDatabaseAndTables() { const connection = await pool.getConnection() try { // 1. Create database if not exists await connection.query(` CREATE DATABASE IF NOT EXISTS task_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci `) console.log('Database "task_app" ready') // 2. Switch to it await connection.query('USE task_app') // 3. Create users table await connection.query(` 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 ) `) // 4. Create tasks table with foreign key await connection.query(` CREATE TABLE IF NOT EXISTS 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. Add index on email for fast login await connection.query(` CREATE INDEX IF NOT EXISTS idx_users_email ON users(email) `) console.log('Tables created successfully') } catch (err) { console.error('Database setup failed:', err) process.exit(1) } finally { connection.release() } } createDatabaseAndTables() |
Run it once:
Bash
|
0 1 2 3 4 5 6 |
npx tsx src/setup/create-tables.ts |
Why we do it this way?
- IF NOT EXISTS → safe to run multiple times
- ON DELETE CASCADE → automatic cleanup when user is deleted
- utf8mb4_unicode_ci → supports emojis, all languages
- Index on email → login becomes fast
- Connection pool → safe for concurrent use
Common beginner mistake
Running CREATE TABLE without IF NOT EXISTS → error on second run
Intermediate mistake
Not using prepared statements (?) → SQL injection risk
Step 5 – Create user via API (real endpoint)
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 |
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 { const { email, password, name } = registerSchema.parse(req.body) // Check if email exists const [rows] = await pool.execute( 'SELECT id FROM users WHERE email = ?', [email] ) if ((rows as any[]).length > 0) { throw new AppError(409, 'Email already registered') } 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 } }) } 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 14 15 |
import { Router } from 'express' import { registerUser } from '../controllers/auth.controller.js' import { validateBody } from '../middleware/validate.middleware.js' import { registerSchema } from '../schemas/auth.schema.js' const router = Router() router.post('/register', validateBody(registerSchema), registerUser) export default router |
src/index.ts (connect routes)
TypeScript
|
0 1 2 3 4 5 6 |
app.use('/api/auth', authRoutes) |
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":"strongpass123","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", "email": "test@example.com", "name": "Test User" } } |
Summary – MySQL table creation + Node.js in 2025–2026
You now have:
- Safe connection pool (mysql2/promise)
- Environment validation (Zod)
- Database + tables created from Node.js (IF NOT EXISTS)
- UUID primary keys
- Proper foreign keys + indexes
- Prepared statements (no SQL injection)
- Realistic user registration endpoint
This is very close to how many real Node.js + MySQL projects start.
Which direction would you like to go next?
- Add login + JWT authentication
- Add task CRUD with user ownership
- Replace raw SQL with Prisma (most popular choice)
- Replace raw SQL with Drizzle ORM (type-safe SQL)
- Add Docker + docker-compose with 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. 😊
