Simple Mysql Next.js Tutorial

Published on: Sunday, September 14, 2025
4 min read

Authors

Name
Robin te Hofstee
Twitter
@Robin_teHofstee

Simple MySQL with Next.js Tutorial

A quick guide to get MySQL working with Next.js using direct connections.

What You Need

Node.js installed
MySQL server running
Basic Next.js knowledge

Step 1: Install Dependencies

npm install mysql2

Step 2: Environment Variables

Create .env.local file:

DATABASE_URL=mysql://username:password@localhost:3306/database_name

Example:

DATABASE_URL=mysql://root:mypassword@localhost:3306/myapp

Step 3: Database Connection

Create lib/db.js:

import mysql from 'mysql2/promise';

export async function createConnection() {
  const connection = await mysql.createConnection(process.env.DATABASE_URL);
  return connection;
}

What this does: Creates a direct connection to MySQL using the DATABASE_URL environment variable.

Step 4: Create API Route

Create pages/api/users.js:

import { createConnection } from '../../lib/db';

export default async function handler(req, res) {
  let connection;

  try {
    connection = await createConnection();

    if (req.method === 'GET') {
      const [rows] = await connection.execute('SELECT * FROM users');
      res.status(200).json(rows);
    }
    
    if (req.method === 'POST') {
      const { name, email } = req.body;
      const [result] = await connection.execute(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        [name, email]
      );
      res.status(201).json({ id: result.insertId, name, email });
    }
  } catch (error) {
    console.error('Database error:', error);
    res.status(500).json({ error: 'Database error' });
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

What this does:

Creates a new connection for each request
GET: Fetches all users from database
POST: Creates a new user with name and email
Uses ? placeholders to prevent SQL injection
Important: Always closes the connection in the finally block

Step 5: Create the Database Table

Run this SQL in your MySQL:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 6: Use in Your Component

Create pages/users.js:

import { useState, useEffect } from 'react';

export default function Users() {
  const [users, setUsers] = useState([]);
  const [name, setName] = useState('');
  const [email, setEmail] = useState('');

  // Fetch users when component loads
  useEffect(() => {
    fetch('/api/users')
      .then(res => res.json())
      .then(data => setUsers(data));
  }, []);

  // Add new user
  const addUser = async (e) => {
    e.preventDefault();
    
    const response = await fetch('/api/users', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ name, email })
    });

    if (response.ok) {
      const newUser = await response.json();
      setUsers([...users, newUser]);
      setName('');
      setEmail('');
    }
  };

  return (
    <div>
      <h1>Users</h1>
      
      {/* Add User Form */}
      <form onSubmit={addUser}>
        <input 
          type="text" 
          placeholder="Name" 
          value={name}
          onChange={(e) => setName(e.target.value)}
          required 
        />
        <input 
          type="email" 
          placeholder="Email" 
          value={email}
          onChange={(e) => setEmail(e.target.value)}
          required 
        />
        <button type="submit">Add User</button>
      </form>

      {/* Users List */}
      <ul>
        {users.map(user => (
          <li key={user.id}>
            {user.name} - {user.email}
          </li>
        ))}
      </ul>
    </div>
  );
}

What this does:

Displays all users from the database
Provides a form to add new users
Updates the list when new users are added

That's It!

You now have:

✅ MySQL connection using createConnection()
✅ API routes to read and write data
✅ A simple React component to interact with your data

Key Points to Remember

Always use environment variables for database credentials
Use parameterized queries (? placeholders) to prevent SQL injection
Always close connections in the finally block to prevent memory leaks
Handle errors properly in try/catch blocks
DATABASE_URL format: mysql://username:password@host:port/database

Connection vs Pool

This tutorial uses direct connections (createConnection). Each API request creates a new connection and closes it when done.

When to use direct connections:

Simple applications with low traffic
Learning/development purposes
Quick prototypes

When to use connection pools:

Production applications
High traffic websites
When you need better performance

Next Steps

Add update and delete functionality
Add form validation
Add loading states
Style your components
Consider using connection pools for production

Simple and ready to use! 🚀