Introduction

In today's web development landscape, combining the power of Next.js with the reliability of MySQL is a game-changer for building performant, scalable applications. While many tutorials cover basic setups, this guide dives deep into production-ready implementations, covering everything from initial setup to advanced optimization techniques.

 Why MySQL with Next.js?

 The Perfect Synergy

Next.js provides server-side rendering, static generation, and API routes, while MySQL offers ACID compliance and robust data integrity. Together, they enable:

• SEO-friendly dynamic content

• Real-time data with server components

• Type-safe database interactions

• Scalable architecture patterns

 When to Choose MySQL

• Complex transactions requiring ACID compliance

• Applications with structured, relational data

• Teams familiar with SQL

• Need for mature tooling and community support

Architecture Overview

Setting Up the Database

Prerequisites Installation

# Install required packages
npm install mysql2
npm install drizzle-orm mysql2  # For ORM option
npm install @types/mysql2 --save-dev  # TypeScript types

# Optional: Prisma ORM
npm install prisma @prisma/client
npx prisma init

Database Configuration File

Create lib/db.ts:

import mysql from 'mysql2/promise';
import { config } from 'dotenv';

config();

// Define connection interface
interface DBConfig {
  host: string;
  user: string;
  password: string;
  database: string;
  port: number;
  waitForConnections: boolean;
  connectionLimit: number;
  queueLimit: number;
  enableKeepAlive: boolean;
  keepAliveInitialDelay: number;
}

// Configuration with environment variables
const dbConfig: DBConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'nextjs_app',
  port: parseInt(process.env.DB_PORT || '3306'),
  waitForConnections: true,
  connectionLimit: parseInt(process.env.DB_CONNECTION_LIMIT || '10'),
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
};

// Create connection pool
class Database {
  private static instance: mysql.Pool;
  
  private constructor() {}
  
  public static getInstance(): mysql.Pool {
    if (!Database.instance) {
      Database.instance = mysql.createPool(dbConfig);
      
      // Add event listeners for debugging
      Database.instance.on('connection', (connection) => {
        console.log('New database connection established');
      });
      
      Database.instance.on('acquire', (connection) => {
        console.log('Connection %d acquired', connection.threadId);
      });
      
      Database.instance.on('release', (connection) => {
        console.log('Connection %d released', connection.threadId);
      });
    }
    
    return Database.instance;
  }
  
  public static async healthCheck(): Promise<boolean> {
    try {
      const pool = Database.getInstance();
      const [rows] = await pool.query('SELECT 1 as health_check');
      return true;
    } catch (error) {
      console.error('Database health check failed:', error);
      return false;
    }
  }
}

export default Database;

Environment Variables (.env.local)

# Database Configuration
DB_HOST=localhost
DB_PORT=3306
DB_USER=your_username
DB_PASSWORD=your_secure_password
DB_NAME=nextjs_database
DB_CONNECTION_LIMIT=10

# For Production (example)
# DB_HOST=mysql-production.cluster-123456.us-east-1.rds.amazonaws.com
# DB_USER=production_user
# DB_PASSWORD=${DATABASE_PASSWORD}

Connection Strategies

Strategy 1: Direct Connection Pool (Recommended)

// lib/db/queries.ts
import Database from '../db';

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

export class UserRepository {
  private pool = Database.getInstance();
  
  // Create user with transaction
  async createUser(userData: Omit<User, 'id' | 'created_at' | 'updated_at'>): Promise<User> {
    const connection = await this.pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      const [result] = await connection.execute(
        `INSERT INTO users (email, name) VALUES (?, ?)`,
        [userData.email, userData.name]
      );
      
      const insertId = (result as any).insertId;
      
      const [rows] = await connection.execute(
        `SELECT * FROM users WHERE id = ?`,
        [insertId]
      );
      
      await connection.commit();
      
      return (rows as User[])[0];
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
  
  // Read with pagination
  async getUsers(page: number = 1, limit: number = 10): Promise<{ users: User[]; total: number }> {
    const offset = (page - 1) * limit;
    
    const [users] = await this.pool.execute(
      `SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?`,
      [limit, offset]
    );
    
    const [[{ total }]] = await this.pool.execute(
      `SELECT COUNT(*) as total FROM users`
    );
    
    return {
      users: users as User[],
      total: (total as any).total,
    };
  }
  
  // Update with optimistic locking
  async updateUser(id: number, updates: Partial<User>): Promise<User> {
    const setClauses = [];
    const values = [];
    
    if (updates.email) {
      setClauses.push('email = ?');
      values.push(updates.email);
    }
    if (updates.name) {
      setClauses.push('name = ?');
      values.push(updates.name);
    }
    
    setClauses.push('updated_at = NOW()');
    values.push(id);
    
    await this.pool.execute(
      `UPDATE users SET ${setClauses.join(', ')} WHERE id = ?`,
      values
    );
    
    const [rows] = await this.pool.execute(
      `SELECT * FROM users WHERE id = ?`,
      [id]
    );
    
    return (rows as User[])[0];
  }
  
  // Soft delete
  async deleteUser(id: number): Promise<void> {
    await this.pool.execute(
      `UPDATE users SET deleted_at = NOW() WHERE id = ?`,
      [id]
    );
  }
}

Strategy 2: Using Prisma ORM

// prisma/schema.prisma
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  deletedAt DateTime? @map("deleted_at")
  
  @@map("users")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now()) @map("created_at")
  
  @@map("posts")
  @@index([authorId])
}

// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development' 
    ? ['query', 'error', 'warn']
    : ['error'],
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
});

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

// Connection health check
export async function checkDatabaseConnection() {
  try {
    await prisma.$queryRaw`SELECT 1`;
    return true;
  } catch (error) {
    console.error('Database connection failed:', error);
    return false;
  }
}

CRUD Operations with Examples

API Route Implementation

// app/api/users/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { UserRepository } from '@/lib/db/queries';
import { z } from 'zod';

const userSchema = z.object({
  email: z.string().email(),
  name: z.string().min(2).max(100),
});

export async function GET(request: NextRequest) {
  try {
    const searchParams = request.nextUrl.searchParams;
    const page = parseInt(searchParams.get('page') || '1');
    const limit = parseInt(searchParams.get('limit') || '10');
    
    const repository = new UserRepository();
    const { users, total } = await repository.getUsers(page, limit);
    
    return NextResponse.json({
      success: true,
      data: users,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit),
      },
    });
  } catch (error) {
    console.error('Error fetching users:', error);
    return NextResponse.json(
      { success: false, error: 'Failed to fetch users' },
      { status: 500 }
    );
  }
}

export async function POST(request: NextRequest) {
  try {
    const body = await request.json();
    const validatedData = userSchema.parse(body);
    
    const repository = new UserRepository();
    const user = await repository.createUser(validatedData);
    
    return NextResponse.json({
      success: true,
      data: user,
      message: 'User created successfully',
    }, { status: 201 });
  } catch (error) {
    if (error instanceof z.ZodError) {
      return NextResponse.json(
        { success: false, errors: error.errors },
        { status: 400 }
      );
    }
    
    console.error('Error creating user:', error);
    return NextResponse.json(
      { success: false, error: 'Failed to create user' },
      { status: 500 }
    );
  }
}

Server Component with MySQL

// app/users/page.tsx
import { UserRepository } from '@/lib/db/queries';
import { UserTable } from '@/components/UserTable';
import { Pagination } from '@/components/Pagination';
import { Suspense } from 'react';
import { Skeleton } from '@/components/ui/skeleton';

interface UsersPageProps {
  searchParams: {
    page?: string;
    limit?: string;
  };
}

export default async function UsersPage({ searchParams }: UsersPageProps) {
  const page = parseInt(searchParams.page || '1');
  const limit = parseInt(searchParams.limit || '10');
  
  return (
    <div className="container mx-auto px-4 py-8">
      <h1 className="text-3xl font-bold mb-6">Users</h1>
      
      <Suspense fallback={<UsersTableSkeleton />}>
        <UsersList page={page} limit={limit} />
      </Suspense>
    </div>
  );
}

async function UsersList({ page, limit }: { page: number; limit: number }) {
  const repository = new UserRepository();
  const { users, total } = await repository.getUsers(page, limit);
  
  return (
    <>
      <UserTable users={users} />
      <Pagination
        currentPage={page}
        totalPages={Math.ceil(total / limit)}
        basePath="/users"
      />
    </>
  );
}

function UsersTableSkeleton() {
  return (
    <div className="space-y-4">
      <Skeleton className="h-10 w-full" />
      <Skeleton className="h-20 w-full" />
      <Skeleton className="h-20 w-full" />
      <Skeleton className="h-20 w-full" />
    </div>
  );
}

Dynamic Route with Database

// app/api/users/[id]/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { UserRepository } from '@/lib/db/queries';

interface Params {
  params: {
    id: string;
  };
}

export async function GET(request: NextRequest, { params }: Params) {
  try {
    const repository = new UserRepository();
    const [rows] = await repository.pool.execute(
      `SELECT * FROM users WHERE id = ? AND deleted_at IS NULL`,
      [params.id]
    );
    
    const users = rows as any[];
    
    if (users.length === 0) {
      return NextResponse.json(
        { success: false, error: 'User not found' },
        { status: 404 }
      );
    }
    
    return NextResponse.json({
      success: true,
      data: users[0],
    });
  } catch (error) {
    console.error('Error fetching user:', error);
    return NextResponse.json(
      { success: false, error: 'Failed to fetch user' },
      { status: 500 }
    );
  }
}

export async function PUT(request: NextRequest, { params }: Params) {
  try {
    const body = await request.json();
    const repository = new UserRepository();
    
    const updatedUser = await repository.updateUser(
      parseInt(params.id),
      body
    );
    
    return NextResponse.json({
      success: true,
      data: updatedUser,
      message: 'User updated successfully',
    });
  } catch (error) {
    console.error('Error updating user:', error);
    return NextResponse.json(
      { success: false, error: 'Failed to update user' },
      { status: 500 }
    );
  }
}

Optimization & Best Practices

Connection Pool Management

// lib/db/optimized-pool.ts
import mysql from 'mysql2/promise';

export class OptimizedConnectionPool {
  private static pools: Map<string, mysql.Pool> = new Map();
  
  static getPool(databaseName: string): mysql.Pool {
    if (!this.pools.has(databaseName)) {
      const pool = mysql.createPool({
        host: process.env.DB_HOST,
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
        database: databaseName,
        waitForConnections: true,
        connectionLimit: 20,
        maxIdle: 10,
        idleTimeout: 60000,
        queueLimit: 0,
        enableKeepAlive: true,
        keepAliveInitialDelay: 0,
      });
      
      // Monitor pool metrics
      setInterval(async () => {
        const poolInfo = {
          total: pool.pool?.connectionLimit,
          active: (pool as any)._allConnections.length,
          idle: (pool as any)._freeConnections.length,
        };
        console.log(`Pool ${databaseName} metrics:`, poolInfo);
      }, 30000);
      
      this.pools.set(databaseName, pool);
    }
    
    return this.pools.get(databaseName)!;
  }
  
  static async cleanup() {
    for (const [name, pool] of this.pools) {
      await pool.end();
      console.log(`Closed pool for database: ${name}`);
    }
    this.pools.clear();
  }
}

Query Optimization with Indexing

-- Create optimized indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at DESC);
CREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;

-- Composite index for common queries
CREATE INDEX idx_posts_author_published ON posts(author_id, published, created_at DESC);

Caching Strategy with Redis

// lib/cache/database-cache.ts
import { createClient } from 'redis';
import { UserRepository } from '../db/queries';

const redisClient = createClient({
  url: process.env.REDIS_URL,
});

await redisClient.connect();

export class CachedUserRepository extends UserRepository {
  private readonly CACHE_TTL = 300; // 5 minutes
  
  async getUsersCached(page: number = 1, limit: number = 10) {
    const cacheKey = `users:page:${page}:limit:${limit}`;
    
    // Try to get from cache
    const cached = await redisClient.get(cacheKey);
    if (cached) {
      return JSON.parse(cached);
    }
    
    // Get from database
    const result = await super.getUsers(page, limit);
    
    // Store in cache
    await redisClient.setEx(
      cacheKey,
      this.CACHE_TTL,
      JSON.stringify(result)
    );
    
    return result;
  }
  
  async invalidateUsersCache() {
    const keys = await redisClient.keys('users:*');
    if (keys.length > 0) {
      await redisClient.del(keys);
    }
  }
}

Prepared Statements for Security

// lib/db/secure-queries.ts
export class SecureQueryBuilder {
  static buildInsertQuery(table: string, data: Record<string, any>) {
    const columns = Object.keys(data);
    const placeholders = columns.map(() => '?').join(', ');
    const values = columns.map(col => data[col]);
    
    return {
      sql: `INSERT INTO ${table} (${columns.join(', ')}) VALUES (${placeholders})`,
      values,
    };
  }
  
  static buildUpdateQuery(table: string, id: number, data: Record<string, any>) {
    const columns = Object.keys(data);
    const setClause = columns.map(col => `${col} = ?`).join(', ');
    const values = [...columns.map(col => data[col]), id];
    
    return {
      sql: `UPDATE ${table} SET ${setClause} WHERE id = ?`,
      values,
    };
  }
}

Security Considerations

SQL Injection Prevention

// Always use parameterized queries
// ❌ BAD - Vulnerable to SQL injection
const query = `SELECT * FROM users WHERE email = '${email}'`;

// ✅ GOOD - Parameterized query
const query = `SELECT * FROM users WHERE email = ?`;
const [rows] = await pool.execute(query, [email]);

Input Validation

// lib/validation/database.ts
import { z } from 'zod';

export const databaseSchemas = {
  user: z.object({
    email: z.string().email().max(255),
    name: z.string().min(2).max(100),
    role: z.enum(['user', 'admin']).default('user'),
  }),
  
  post: z.object({
    title: z.string().min(5).max(200),
    content: z.string().min(10).max(5000),
    published: z.boolean().default(false),
  }),
};

export function sanitizeInput(input: string): string {
  // Remove potentially dangerous characters
  return input
    .replace(/[<>]/g, '')
    .trim()
    .substring(0, 1000); // Limit length
}

Connection Security

// Production database configuration
const secureConfig = {
  ssl: {
    rejectUnauthorized: true,
    ca: process.env.DB_SSL_CA,
    cert: process.env.DB_SSL_CERT,
    key: process.env.DB_SSL_KEY,
  },
  supportBigNumbers: true,
  bigNumberStrings: true,
  timezone: 'Z', // UTC
  charset: 'utf8mb4',
  dateStrings: true,
};

Conclusion

Connecting MySQL with Next.js provides a robust foundation for building scalable, performant applications. By following the patterns and best practices outlined in this guide, you can:

1. Ensure production-ready database connections

2. Implement secure query patterns

3. Optimize performance with caching and indexing

4. Monitor and maintain database health

5. Scale effectively as your application grows

Remember that the key to successful database integration lies in:

• Proper connection pooling

• Comprehensive error handling

• Regular monitoring and optimization

• Security-first implementation

Whether you choose direct MySQL connections, Prisma ORM, or another abstraction layer, the principles remain the same: prioritize security, performance, and maintainability.