Connect Next.js to MySQL

CREATE DATABASE edusite CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE edusite;

CREATE TABLE courses (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  slug       VARCHAR(80)  NOT NULL UNIQUE,
  title      VARCHAR(120) NOT NULL,
  level      ENUM('Beginner','Intermediate','Advanced') DEFAULT 'Beginner',
  fee        INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO courses (slug, title, level, fee) VALUES
('c-programming', 'C Programming', 'Beginner', 2499),
('python-ai', 'Python + AI', 'Beginner', 3499),
('nextjs', 'Next.js', 'Intermediate', 4499);

npm install mysql2 server-only

# .env.local  (never commit this file)
DB_HOST=localhost
DB_USER=edusite_user
DB_PASSWORD=Str0ng#Pass
DB_NAME=edusite

// lib/db.js
import 'server-only'                     // build error if a client file imports this
import mysql from 'mysql2/promise'

// Re-use one pool across hot reloads in development
const globalForDb = globalThis
export const pool =
  globalForDb.mysqlPool ??
  mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 5,
  })
if (process.env.NODE_ENV !== 'production') globalForDb.mysqlPool = pool

// Small helper: const rows = await query('SELECT ...', [params])
export async function query(sql, params = []) {
  const [rows] = await pool.execute(sql, params)
  return rows
}

// app/admin/courses/page.jsx  (Server Component)
import Link from 'next/link'
import { query } from '@/lib/db'
import { deleteCourse } from './actions'

export const dynamic = 'force-dynamic'   // always show the latest rows

export default async function AdminCourses() {
  const courses = await query('SELECT id, slug, title, level, fee FROM courses ORDER BY id DESC')
  return (
    <main className="container">
      <h1>Courses ({courses.length})</h1>
      <Link href="/admin/courses/new" className="btn">+ Add course</Link>
      <table>
        <thead><tr><th>Title</th><th>Level</th><th>Fee</th><th></th></tr></thead>
        <tbody>
          {courses.map((c) => (
            <tr key={c.id}>
              <td>{c.title}</td><td>{c.level}</td><td>₹{c.fee}</td>
              <td>
                <Link href={`/admin/courses/${c.id}/edit`}>Edit</Link>{' '}
                <form action={deleteCourse} style={{ display: 'inline' }}>
                  <input type="hidden" name="id" value={c.id} />
                  <button>Delete</button>
                </form>
              </td>
            </tr>
          ))}
        </tbody>
      </table>
    </main>
  )
}

// app/admin/courses/actions.js
'use server'
import { revalidatePath } from 'next/cache'
import { redirect } from 'next/navigation'
import { query } from '@/lib/db'

function readCourse(formData) {
  const title = String(formData.get('title') || '').trim()
  const level = String(formData.get('level') || 'Beginner')
  const fee = Number(formData.get('fee'))
  const slug = title.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/(^-|-$)/g, '')
  const errors = {}
  if (title.length < 2) errors.title = 'Title is required'
  if (!['Beginner', 'Intermediate', 'Advanced'].includes(level)) errors.level = 'Invalid level'
  if (!Number.isInteger(fee) || fee < 0) errors.fee = 'Fee must be a whole number'
  return { data: { title, level, fee, slug }, errors }
}

export async function createCourse(prev, formData) {
  const { data, errors } = readCourse(formData)
  if (Object.keys(errors).length) return { errors }
  try {
    await query('INSERT INTO courses (slug, title, level, fee) VALUES (?, ?, ?, ?)',
                [data.slug, data.title, data.level, data.fee])
  } catch (e) {
    if (e.code === 'ER_DUP_ENTRY') return { errors: { title: 'A course with this name exists' } }
    throw e
  }
  revalidatePath('/admin/courses')
  redirect('/admin/courses')
}

export async function updateCourse(id, prev, formData) {   // id bound with .bind()
  const { data, errors } = readCourse(formData)
  if (Object.keys(errors).length) return { errors }
  await query('UPDATE courses SET title = ?, level = ?, fee = ? WHERE id = ?',
              [data.title, data.level, data.fee, id])
  revalidatePath('/admin/courses')
  redirect('/admin/courses')
}

export async function deleteCourse(formData) {
  await query('DELETE FROM courses WHERE id = ?', [Number(formData.get('id'))])
  revalidatePath('/admin/courses')
}

// app/admin/courses/new/page.jsx
'use client'
import { useActionState } from 'react'
import { createCourse } from '../actions'

export default function NewCourse() {
  const [state, action, pending] = useActionState(createCourse, { errors: {} })
  const e = state.errors || {}
  return (
    <form action={action} className="container">
      <h1>Add course</h1>
      <input name="title" placeholder="Course title" />  {e.title && <small>{e.title}</small>}
      <select name="level">
        <option>Beginner</option><option>Intermediate</option><option>Advanced</option>
      </select>
      <input name="fee" type="number" placeholder="Fee in ₹" />  {e.fee && <small>{e.fee}</small>}
      <button disabled={pending}>{pending ? 'Saving…' : 'Save course'}</button>
    </form>
  )
}

// app/admin/courses/[id]/edit/page.jsx  (Server Component)
import { notFound } from 'next/navigation'
import { query } from '@/lib/db'
import { updateCourse } from '../../actions'

export default async function EditCourse({ params }) {
  const { id } = await params
  const [course] = await query('SELECT * FROM courses WHERE id = ?', [Number(id)])
  if (!course) notFound()
  // bind() pre-fills (id, prevState) so the form only passes formData
  const action = updateCourse.bind(null, course.id, {})
  return (
    <form action={action}>
      <input name="title" defaultValue={course.title} />
      <select name="level" defaultValue={course.level}>
        <option>Beginner</option><option>Intermediate</option><option>Advanced</option>
      </select>
      <input name="fee" type="number" defaultValue={course.fee} />
      <button>Update</button>
    </form>
  )
}

// ❌ NEVER – user input glued into SQL
query(`SELECT * FROM users WHERE email = '${email}'`)
// email = "' OR '1'='1"  → returns ALL users!

// ✅ ALWAYS – placeholders; mysql2 escapes the values
query('SELECT * FROM users WHERE email = ?', [email])