Node.js MySQL 2026: mysql2 + Connection Pooling 🚀

Node.js MySQL 2026: Production mysql2 Patterns 🚀

Node.js + MySQL powers high-read scale apps (e-commerce, analytics) with mysql2/promise (async/await), connection pooling (500+ concurrent), prepared statements (SQL injection), PlanetScale (serverless), and Vercel v3 deployment. Perfect for read-heavy workloads where PostgreSQL joins hurt performance.

🎯 MySQL vs PostgreSQL (Node.js Context)

Feature	MySQL	PostgreSQL
Read QPS	18k	12k
Connection Pool	1000+	200-500
JSON	Good	Excellent
Full-Text	Native	Extensions
Serverless	PlanetScale	Neon
Write Scale	Horizontal	Vertical

🏗️ Production Connection Pool

// db/mysql.ts - Production mysql2 pool
import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

dotenv.config();

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'myapp',
  waitForConnections: true,
  connectionLimit: 50,        // Max concurrent
  queueLimit: 100,           // Queue overflow
  acquireTimeout: 60000,     // 60s timeout
  timeout: 60000,            // Query timeout
  reconnect: true,
  // SSL for production
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: true
  } : undefined
});

// Health check
export const healthCheck = async () => {
  const connection = await pool.getConnection();
  try {
    const [rows] = await connection.execute('SELECT 1 as healthy');
    return rows.healthy === 1;
  } finally {
    connection.release();
  }
};

pool.on('connection', () => {
  console.log('✅ MySQL connection created');
});

pool.on('error', (err) => {
  console.error('❌ MySQL pool error:', err);
});

export default pool;

🚀 Raw Queries + Prepared Statements

// services/userService.ts
import pool from '../db/mysql.js';

interface User {
  id: number;
  email: string;
  name: string;
  created_at: Date;
}

export const findUserByEmail = async (email: string): Promise<User | null> => {
  const connection = await pool.getConnection();
  
  try {
    // ✅ Prepared statement (SQL injection safe)
    const [rows] = await connection.execute<User[]>(
      'SELECT id, email, name, created_at FROM users WHERE email = ?',
      [email]
    );
    
    return rows || null;
  } finally {
    connection.release();
  }
};

export const createUser = async (email: string, name: string): Promise<User> => {
  const connection = await pool.getConnection();
  
  try {
    // Transaction for data consistency
    await connection.beginTransaction();
    
    const [result] = await connection.execute(
      'INSERT INTO users (email, name) VALUES (?, ?)',
      [email, name]
    );
    
    const userId = (result as any).insertId;
    await connection.commit();
    
    // Fetch complete user
    return await findUserByEmail(email)!;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
};

🌟 Express 6.0 + MySQL Integration

// routes/users.ts
import { Router } from 'express';
import { findUserByEmail, createUser, getUsersPaginated } from '../services/userService.js';
import { z } from 'zod';

const router = Router();

// Pagination + Search
router.get('/', async (req, res) => {
  try {
    const page = parseInt(req.query.page as string) || 1;
    const limit = parseInt(req.query.limit as string) || 20;
    const search = (req.query.search as string) || '';
    
    const users = await getUsersPaginated({ page, limit, search });
    res.json(users);
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch users' });
  }
});

// Single user
router.get('/:id', async (req, res) => {
  try {
    const user = await findUserByEmail(req.params.id);
    if (!user) return res.status(404).json({ error: 'User not found' });
    res.json(user);
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch user' });
  }
});

// Create user
router.post('/', async (req, res) => {
  try {
    const schema = z.object({
      email: z.string().email(),
      name: z.string().min(2).max(100)
    });
    
    const { email, name } = schema.parse(req.body);
    const user = await createUser(email, name);
    res.status(201).json(user);
  } catch (error) {
    if (error instanceof z.ZodError) {
      return res.status(400).json({ error: error.errors });
    }
    res.status(500).json({ error: 'User creation failed' });
  }
});

export default router;

🔥 TypeScript Types + Pagination

// types/mysql.ts
export interface User {
  id: number;
  email: string;
  name: string;
  created_at: Date;
}

export interface PaginationResponse<T> {
  data: T[];
  total: number;
  page: number;
  limit: number;
  totalPages: number;
}

// Optimized pagination query
export const getUsersPaginated = async ({
  page = 1,
  limit = 20,
  search = ''
}: {
  page?: number;
  limit?: number;
  search?: string;
}): Promise<PaginationResponse<User>> => {
  const offset = (page - 1) * limit;
  
  const connection = await pool.getConnection();
  try {
    // Count total (with search)
    const [[{ total }]] = await connection.execute(
      `SELECT COUNT(*) as total 
       FROM users 
       WHERE email LIKE ? OR name LIKE ?`,
      [`%${search}%`, `%${search}%`]
    );
    
    // Paginated results
    const [rows] = await connection.execute<User[]>(
      `SELECT id, email, name, created_at 
       FROM users 
       WHERE email LIKE ? OR name LIKE ?
       ORDER BY created_at DESC 
       LIMIT ? OFFSET ?`,
      [`%${search}%`, `%${search}%`, limit, offset]
    );
    
    return {
      data: rows,
      total: Number(total),
      page,
      limit,
      totalPages: Math.ceil(Number(total) / limit)
    };
  } finally {
    connection.release();
  }
};

🛠️ Server Setup (Production Ready)

// index.ts
import express from 'express';
import cors from 'cors';
import helmet from 'helmet';
import morgan from 'morgan';
import userRoutes from './routes/users.js';
import pool from './db/mysql.js';

const app = express();

// Production middleware
app.use(helmet({
  contentSecurityPolicy: process.env.NODE_ENV === 'production'
}));
app.use(cors({ origin: process.env.FRONTEND_URL }));
app.use(express.json({ limit: '10mb' }));
app.use(morgan('combined'));

// Health check
app.get('/health', async (req, res) => {
  const healthy = await healthCheck();
  res.json({ status: healthy ? 'OK' : 'ERROR', timestamp: new Date().toISOString() });
});

// Routes
app.use('/api/users', userRoutes);

// Graceful shutdown
process.on('SIGTERM', async () => {
  console.log('Shutting down gracefully...');
  await pool.end();
  process.exit(0);
});

const PORT = Number(process.env.PORT) || 3000;
app.listen(PORT, () => {
  console.log(`🚀 MySQL Server running on port ${PORT}`);
});

🐳 Docker + PlanetScale Production

# docker-compose.yml
version: '3.8'
services:
  mysql:
    image: mysql:8.0
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_DATABASE: myapp
      MYSQL_USER: appuser
      MYSQL_PASSWORD: apppass
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql

  app:
    build: .
    ports:
      - "3000:3000"
    environment:
      - DB_HOST=mysql
      - DB_USER=appuser
      - DB_PASSWORD=apppass
      - DB_NAME=myapp
    depends_on:
      - mysql

volumes:
  mysql_data:

# .env (PlanetScale)
DB_HOST=aws.connect.psdb.cloud
DB_USER=your_user
DB_PASSWORD=your_password
DB_NAME=your_database
SSL_CA=/path/to/planetscale-ca.pem

📊 MySQL vs PostgreSQL Performance

Metric	MySQL	PostgreSQL
Read QPS	18k	12k
Connection Limit	1000+	500
Full-Text Search	Native	Extensions
JSON Queries	Good	Excellent
Serverless	PlanetScale	Neon

🎯 Production Checklist

✅ [] mysql2/promise (async/await) ✅ [] Connection pooling (50-100) ✅ [] Prepared statements (?) ✅ [] Transaction support ✅ [] SSL/TLS encryption ✅ [] Health checks + monitoring ✅ [] Query timeouts (60s) ✅ [] Graceful shutdown ✅ [] PlanetScale/Vercel deploy ✅ [] Read replicas (scale reads)

🎯 Final Thoughts

Node.js + MySQL = Read-scale perfection. mysql2/promise delivers async/await, connection pooling handles 1000+ concurrent, prepared statements prevent injection, and PlanetScale offers serverless MySQL at global scale.

2026 Node.js MySQL Strategy: mysql2 → Raw performance (60%) Prisma → Rapid dev (30%) Drizzle → Lightweight TS (10%)

Build high-read APIs with Node.js + MySQL’s battle-tested ecosystem 🚀.