Welcome to my personal blog

Simple Mysql Next.js Tutorial

Published on
4 min read
← Back to the blog
Authors

Simple MySQL with Next.js Tutorial

This guide shows you how to connect a Next.js application to a MySQL database, securely handling passwords with bcrypt.

What You Need

  • Node.js installed
  • MySQL server running
  • A basic understanding of Next.js and why you should never store plaintext passwords 🔐

Step 1: Install Dependencies

You'll need mysql2 for the database connection and bcrypt for secure password hashing.

npm install mysql2 bcrypt

Step 2: Environment Variables

Create a .env.local file in your project's root directory to store your database connection string securely. Never hardcode your credentials.

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

Example:

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

Step 3: Database Connection

Create a file at lib/db.js. This handles the direct connection to MySQL using a createConnection function. This file is not a React component, so it doesn't need to be in the app directory.

import mysql from 'mysql2/promise';

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

Step 4: Create API Route

In the App Router, API routes are created by making a route.js file inside a new directory within the app/api folder. Create a new file at app/api/users/route.js.

This file handles GET and POST requests and securely hashes the password before storing it. It uses NextResponse to return JSON data.

import { NextResponse } from 'next/server';
import { createConnection } from '@/lib/db';
import bcrypt from 'bcrypt';

// Handle GET requests to fetch users
export async function GET() {
  let connection;
  try {
    connection = await createConnection();
    const [rows] = await connection.execute('SELECT id, name, email FROM users');
    return NextResponse.json(rows);
  } catch (error) {
    console.error('Database error:', error);
    return NextResponse.json({ error: 'Database error' }, { status: 500 });
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

// Handle POST requests to create a new user
export async function POST(req) {
  let connection;
  try {
    const { name, email, password } = await req.json();

    // Hash the password securely using bcrypt
    const hashedPassword = await bcrypt.hash(password, 10);

    connection = await createConnection();
    const [result] = await connection.execute(
      'INSERT INTO users (name, email, password) VALUES (?, ?, ?)',
      [name, email, hashedPassword]
    );

    return NextResponse.json({ id: result.insertId, name, email }, { status: 201 });
  } catch (error) {
    console.error('Database error:', error);
    return NextResponse.json({ error: 'Database error' }, { status: 500 });
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

Step 5: Create the Database Table

Run this SQL command in your MySQL client. The password column should be a VARCHAR(255) to accommodate the long hash generated by bcrypt.

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

Step 6: Create the Frontend Component

Create a new file at app/users/page.js. This component will display users and provide a form to add a new one. It must be a Client Component because it uses hooks like useState and useEffect. Add "use client"; at the top of the file.

"use client";

import { useState, useEffect } from 'react';

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

  // Fetch users when the component loads
  useEffect(() => {
    fetch('/api/users')
      .then(res => res.json())
      .then(data => setUsers(data))
      .catch(error => console.error('Error fetching users:', error));
  }, []);

  // Add a new user
  const addUser = async (e) => {
    e.preventDefault();

    try {
      const response = await fetch('/api/users', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ name, email, password })
      });

      if (response.ok) {
        const newUser = await response.json();
        setUsers([...users, newUser]);
        setName('');
        setEmail('');
        setPassword('');
      } else {
        console.error('Failed to add user:', await response.json());
      }
    } catch (error) {
      console.error('Error adding user:', error);
    }
  };

  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 
        />
        <input 
          type="password" 
          placeholder="Password" 
          value={password} 
          onChange={(e) => setPassword(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>
  );
}

Comments