Next.js MySQL Integration

Introduction

MySQL is one of the world's most popular relational database management systems, and integrating it with Next.js allows you to build robust, data-driven applications. Whether you're developing a blog, e-commerce platform, or administrative dashboard, knowing how to connect Next.js with MySQL is an essential skill for modern web developers.

In this guide, we'll explore how to integrate MySQL with Next.js applications, covering everything from establishing database connections to performing CRUD (Create, Read, Update, Delete) operations through Next.js API routes.

Prerequisites

Before getting started, make sure you have:

A basic understanding of Next.js
Node.js installed on your machine
MySQL server installed or access to a MySQL database
Basic knowledge of SQL queries

Setting Up MySQL for Next.js

1. Install Required Dependencies

First, let's install the necessary packages:

bash
npm install mysql2
# or
yarn add mysql2

We're using mysql2 instead of the original mysql package because it offers better performance, supports promises, and is actively maintained.

2. Creating a Database Connection Utility

Create a utility file to manage your database connections. This approach helps keep your code organized and reusable.

Create a new file at lib/db.js:

javascript
import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

export default async function executeQuery({ query, values = [] }) {
  try {
    const [results] = await pool.execute(query, values);
    return results;
  } catch (error) {
    console.error('Database query error:', error);
    throw error;
  }
}

3. Setting Up Environment Variables

Create a .env.local file in the root of your Next.js project to securely store your database credentials:

MYSQL_HOST=localhost
MYSQL_USER=yourusername
MYSQL_PASSWORD=yourpassword
MYSQL_DATABASE=yourdatabase

Make sure to add .env.local to your .gitignore file to avoid exposing sensitive information.

Basic CRUD Operations

Let's implement API routes for basic CRUD operations using our MySQL connection.

Creating API Routes

1. Reading Data from MySQL

Create a file at pages/api/users/index.js:

javascript
import executeQuery from '../../../lib/db';

export default async function handler(req, res) {
  if (req.method === 'GET') {
    try {
      const users = await executeQuery({
        query: 'SELECT * FROM users LIMIT 10',
        values: [],
      });
      
      res.status(200).json({ users });
    } catch (error) {
      res.status(500).json({ error: 'Error fetching users' });
    }
  } else {
    res.status(405).json({ error: 'Method not allowed' });
  }
}

2. Creating Data in MySQL

Add the POST method handling to the same file:

javascript
import executeQuery from '../../../lib/db';

export default async function handler(req, res) {
  // GET method handling from previous example
  
  if (req.method === 'POST') {
    const { name, email } = req.body;
    
    // Basic validation
    if (!name || !email) {
      return res.status(400).json({ error: 'Name and email are required' });
    }
    
    try {
      const result = await executeQuery({
        query: 'INSERT INTO users (name, email) VALUES (?, ?)',
        values: [name, email],
      });
      
      res.status(201).json({ 
        message: 'User created successfully', 
        userId: result.insertId 
      });
    } catch (error) {
      res.status(500).json({ error: 'Error creating user' });
    }
  }
}

3. Reading a Single Record

Create a file at pages/api/users/[id].js:

javascript
import executeQuery from '../../../lib/db';

export default async function handler(req, res) {
  const { id } = req.query;
  
  if (req.method === 'GET') {
    try {
      const users = await executeQuery({
        query: 'SELECT * FROM users WHERE id = ?',
        values: [id],
      });
      
      if (users.length === 0) {
        return res.status(404).json({ error: 'User not found' });
      }
      
      res.status(200).json({ user: users[0] });
    } catch (error) {
      res.status(500).json({ error: 'Error fetching user' });
    }
  }
  
  // Other methods (PUT, DELETE) will be added here
}

4. Updating Data

Add the PUT method to pages/api/users/[id].js:

javascript
// Continuing from the previous [id].js file
if (req.method === 'PUT') {
  const { name, email } = req.body;
  
  try {
    const result = await executeQuery({
      query: 'UPDATE users SET name = ?, email = ? WHERE id = ?',
      values: [name, email, id],
    });
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'User not found' });
    }
    
    res.status(200).json({ message: 'User updated successfully' });
  } catch (error) {
    res.status(500).json({ error: 'Error updating user' });
  }
}

5. Deleting Data

Add the DELETE method to pages/api/users/[id].js:

javascript
// Continuing from the previous [id].js file
if (req.method === 'DELETE') {
  try {
    const result = await executeQuery({
      query: 'DELETE FROM users WHERE id = ?',
      values: [id],
    });
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'User not found' });
    }
    
    res.status(200).json({ message: 'User deleted successfully' });
  } catch (error) {
    res.status(500).json({ error: 'Error deleting user' });
  }
}

Using MySQL Data in Next.js Pages

Client-Side Data Fetching

Here's a simple example of fetching users from our API and displaying them on a page:

jsx
import { useState, useEffect } from 'react';

export default function Users() {
  const [users, setUsers] = useState([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    async function fetchUsers() {
      try {
        const response = await fetch('/api/users');
        const data = await response.json();
        setUsers(data.users);
      } catch (error) {
        console.error('Error fetching users:', error);
      } finally {
        setLoading(false);
      }
    }

    fetchUsers();
  }, []);

  if (loading) return <p>Loading users...</p>;

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

Server-Side Rendering with MySQL

For better performance and SEO, you may want to fetch data on the server using getServerSideProps:

jsx
import executeQuery from '../lib/db';

export default function Users({ users }) {
  return (
    <div>
      <h1>Users</h1>
      <ul>
        {users.map((user) => (
          <li key={user.id}>
            {user.name} ({user.email})
          </li>
        ))}
      </ul>
    </div>
  );
}

export async function getServerSideProps() {
  try {
    const users = await executeQuery({
      query: 'SELECT * FROM users LIMIT 10',
      values: [],
    });

    return {
      props: {
        users: JSON.parse(JSON.stringify(users)), // Serialize dates and other non-JSON values
      },
    };
  } catch (error) {
    console.error('Error fetching users:', error);
    return {
      props: {
        users: [],
      },
    };
  }
}

Note: We use JSON.parse(JSON.stringify()) to handle Date objects and other values that can't be directly serialized as JSON.

Real-World Example: Building a Todo List Application

Let's implement a practical example by building a simple todo list application with MySQL integration.

1. Create the Database Table

First, create a todos table in your MySQL database:

sql
CREATE TABLE todos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  completed BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Create API Routes for Todo Management

Create a file at pages/api/todos/index.js:

javascript
import executeQuery from '../../../lib/db';

export default async function handler(req, res) {
  // Get all todos
  if (req.method === 'GET') {
    try {
      const todos = await executeQuery({
        query: 'SELECT * FROM todos ORDER BY created_at DESC',
      });
      
      return res.status(200).json({ todos });
    } catch (error) {
      return res.status(500).json({ error: 'Error fetching todos' });
    }
  }
  
  // Create a new todo
  if (req.method === 'POST') {
    const { title } = req.body;
    
    if (!title) {
      return res.status(400).json({ error: 'Title is required' });
    }
    
    try {
      const result = await executeQuery({
        query: 'INSERT INTO todos (title) VALUES (?)',
        values: [title],
      });
      
      return res.status(201).json({
        message: 'Todo created successfully',
        todoId: result.insertId,
      });
    } catch (error) {
      return res.status(500).json({ error: 'Error creating todo' });
    }
  }
  
  return res.status(405).json({ error: 'Method not allowed' });
}

Create a file at pages/api/todos/[id].js:

javascript
import executeQuery from '../../../lib/db';

export default async function handler(req, res) {
  const { id } = req.query;
  
  // Toggle todo completion status
  if (req.method === 'PATCH') {
    try {
      const [todo] = await executeQuery({
        query: 'SELECT completed FROM todos WHERE id = ?',
        values: [id],
      });
      
      if (!todo) {
        return res.status(404).json({ error: 'Todo not found' });
      }
      
      const newStatus = !todo.completed;
      
      await executeQuery({
        query: 'UPDATE todos SET completed = ? WHERE id = ?',
        values: [newStatus, id],
      });
      
      return res.status(200).json({
        message: 'Todo status updated',
        completed: newStatus,
      });
    } catch (error) {
      return res.status(500).json({ error: 'Error updating todo' });
    }
  }
  
  // Delete a todo
  if (req.method === 'DELETE') {
    try {
      const result = await executeQuery({
        query: 'DELETE FROM todos WHERE id = ?',
        values: [id],
      });
      
      if (result.affectedRows === 0) {
        return res.status(404).json({ error: 'Todo not found' });
      }
      
      return res.status(200).json({ message: 'Todo deleted successfully' });
    } catch (error) {
      return res.status(500).json({ error: 'Error deleting todo' });
    }
  }
  
  return res.status(405).json({ error: 'Method not allowed' });
}

3. Create a Todo List Page

Create a file at pages/todos.js:

jsx
import { useState, useEffect } from 'react';
import styles from '../styles/Todos.module.css';

export default function TodoList() {
  const [todos, setTodos] = useState([]);
  const [newTodo, setNewTodo] = useState('');
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    fetchTodos();
  }, []);

  async function fetchTodos() {
    try {
      const res = await fetch('/api/todos');
      const data = await res.json();
      setTodos(data.todos);
    } catch (error) {
      console.error('Error fetching todos:', error);
    } finally {
      setLoading(false);
    }
  }

  async function addTodo(e) {
    e.preventDefault();
    if (!newTodo.trim()) return;

    try {
      const res = await fetch('/api/todos', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify({ title: newTodo }),
      });

      if (res.ok) {
        setNewTodo('');
        fetchTodos();
      }
    } catch (error) {
      console.error('Error adding todo:', error);
    }
  }

  async function toggleTodoStatus(id) {
    try {
      await fetch(`/api/todos/${id}`, {
        method: 'PATCH',
      });
      fetchTodos();
    } catch (error) {
      console.error('Error toggling todo status:', error);
    }
  }

  async function deleteTodo(id) {
    try {
      await fetch(`/api/todos/${id}`, {
        method: 'DELETE',
      });
      fetchTodos();
    } catch (error) {
      console.error('Error deleting todo:', error);
    }
  }

  if (loading) return <div>Loading todos...</div>;

  return (
    <div className={styles.container}>
      <h1>Todo List</h1>
      
      <form onSubmit={addTodo} className={styles.addForm}>
        <input
          type="text"
          value={newTodo}
          onChange={(e) => setNewTodo(e.target.value)}
          placeholder="Add a new todo"
          className={styles.input}
        />
        <button type="submit" className={styles.addButton}>Add</button>
      </form>
      
      <ul className={styles.todoList}>
        {todos.length === 0 ? (
          <li className={styles.emptyList}>No todos yet! Add one above.</li>
        ) : (
          todos.map((todo) => (
            <li key={todo.id} className={styles.todoItem}>
              <span 
                className={`${styles.todoTitle} ${todo.completed ? styles.completed : ''}`}
                onClick={() => toggleTodoStatus(todo.id)}
              >
                {todo.title}
              </span>
              <button 
                onClick={() => deleteTodo(todo.id)}
                className={styles.deleteButton}
              >
                Delete
              </button>
            </li>
          ))
        )}
      </ul>
    </div>
  );
}

Create a file at styles/Todos.module.css for styling:

css
.container {
  max-width: 600px;
  margin: 0 auto;
  padding: 20px;
}

.addForm {
  display: flex;
  margin-bottom: 20px;
}

.input {
  flex-grow: 1;
  padding: 8px;
  border: 1px solid #ddd;
  border-radius: 4px 0 0 4px;
}

.addButton {
  padding: 8px 16px;
  background-color: #0070f3;
  color: white;
  border: none;
  border-radius: 0 4px 4px 0;
  cursor: pointer;
}

.todoList {
  list-style: none;
  padding: 0;
}

.todoItem {
  display: flex;
  align-items: center;
  justify-content: space-between;
  padding: 12px;
  border-bottom: 1px solid #eee;
}

.todoTitle {
  cursor: pointer;
  flex-grow: 1;
}

.completed {
  text-decoration: line-through;
  color: #999;
}

.deleteButton {
  padding: 5px 10px;
  background-color: #ff6b6b;
  color: white;
  border: none;
  border-radius: 4px;
  cursor: pointer;
}

.emptyList {
  text-align: center;
  color: #999;
  padding: 20px 0;
}

Best Practices for MySQL Integration in Next.js

Use Connection Pooling: As we did in our examples, use connection pooling instead of creating a new connection for each request.
Parameterized Queries: Always use prepared statements with parameters (the ? placeholders) to prevent SQL injection attacks.
Error Handling: Implement comprehensive error handling for database operations.
Environment Variables: Store database credentials in environment variables, not directly in your code.
Separation of Concerns: Keep your database logic separate from your API route handling.
Data Validation: Always validate user input before inserting it into your database.
Connection Limits: Be mindful of your database connection limits, especially in serverless environments like Vercel.

Common Challenges and Solutions

1. Too Many Connections in Development

When using Next.js in development mode with hot reloading, you might encounter "too many connections" errors. This happens because each code change reestablishes database connections.

Solution: Implement a global connection pool that's reused across requests.

2. Date Serialization Issues

MySQL dates don't serialize well to JSON automatically.

Solution: Convert dates to ISO strings or use JSON.parse(JSON.stringify(data)) for serialization.

3. Serverless Function Timeouts

On platforms like Vercel, there are time limits for serverless functions.

Solution: Optimize your queries and consider caching for expensive operations.

Summary

In this guide, we've covered how to integrate MySQL with Next.js applications:

Setting up a MySQL connection with proper pooling
Creating API routes for CRUD operations
Using MySQL data in both client and server-rendered components
Building a practical todo application with MySQL backend
Best practices for production applications

MySQL integration opens up a world of possibilities for your Next.js applications, allowing you to build data-rich, dynamic web applications with a powerful relational database backend.

Additional Resources and Exercises

Further Learning

Exercises

User Authentication System: Extend the examples to create a complete authentication system with MySQL.
Add Pagination: Modify the todo list to include pagination for large datasets.
Search Functionality: Implement a search feature that queries the MySQL database.
Data Relationships: Create a blogging platform with related tables for posts, categories, and comments.

By mastering MySQL integration with Next.js, you'll have the skills to build robust, data-driven web applications that can scale to meet your users' needs.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Prerequisites​

Setting Up MySQL for Next.js​

1. Install Required Dependencies​

2. Creating a Database Connection Utility​

3. Setting Up Environment Variables​

Basic CRUD Operations​

Creating API Routes​

1. Reading Data from MySQL​

2. Creating Data in MySQL​

3. Reading a Single Record​

4. Updating Data​

5. Deleting Data​

Using MySQL Data in Next.js Pages​

Client-Side Data Fetching​

Server-Side Rendering with MySQL​

Real-World Example: Building a Todo List Application​

1. Create the Database Table​

2. Create API Routes for Todo Management​

3. Create a Todo List Page​

Best Practices for MySQL Integration in Next.js​

Common Challenges and Solutions​

1. Too Many Connections in Development​

2. Date Serialization Issues​

3. Serverless Function Timeouts​

Summary​

Additional Resources and Exercises​

Further Learning​

Exercises​