Next.js 14 Node Express CRUD (Create Read Update and Delete) | MySQL

Next.js 14 Node Express CRUD (Create Read Update and Delete) | MySQL

https://expressjs.com/
Express JS
Fast, unopinionated, minimalist web framework for Node.js

Install
$ npm install express --savev PS C:\nodeproject> npm install express --save
https://expressjs.com/en/starter/hello-world.html

mysql
$ npm install mysql
PS C:\nodeproject>npm install mysql

cors
CORS is a node.js package for providing a Connect/Express middleware that can be used to enable CORS with various options.
https://www.npmjs.com/package/cors
PS C:\nodeproject>npm i cors

bcrypt
https://www.npmjs.com/package/bcrypt
A library to help you hash passwords.
npm install bcrypt

run PS C:\nodeproject> node index.js
index.js

//index.js
const express = require('express')
const bodyParser = require('body-parser');
const cors = require('cors');
const bycrypt = require('bcrypt'); //npm install bcrypt
const db = require('./db');

const app = express()

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

app.use(cors({
      origin: 'http://localhost:3000',
      credentials: true
}));

app.get('/', (req, res) => {
  res.send('Hello World!')
})

app.post("/api/adduser", async (req, res) => {
  const sql ="INSERT INTO users (name,email,username,password) VALUES (?, ?, ?, ?)";

  const hashedPassword =  await bycrypt.hash(req.body.password, 10);

  const values = [req.body.name, req.body.email, req.body.username, hashedPassword];
  db.query(sql, values, (err, result) => {
    if (err)
      return res.json({ message: "Something unexpected has occured" + err });
    return res.json({ success: "New User added successfully" });
  });
});

app.get("/api/users", (req, res) => {
  const sql = "SELECT * FROM users";
  db.query(sql, (err, result) => {
    if (err) res.json({ message: "Server error" });
    return res.json(result);
  });
});

app.get("/api/getuser/:id", (req, res) => {
  const id = req.params.id;
  const sql = "SELECT * FROM users WHERE id= ?";
  db.query(sql, [id], (err, result) => {
    if (err) res.json({ message: "Server error" });
    return res.json(result);
  });
});

app.put("/api/edit/:id", async (req, res) => {
  const id = req.params.id;
  const sql ="UPDATE users SET name=?, email=?, username=? WHERE id=?";
  
  const values = [
    req.body.name,
    req.body.email,
    req.body.username,
    id,
  ];
  db.query(sql, values, (err, result) => {
    if (err)
      return res.json({ message: "Something unexpected has occured" + err });
    return res.json({ success: "User updated successfully" });
  });
});

app.delete("/api/delete/:id", (req, res) => {
  const id = req.params.id;
  const sql = "DELETE FROM users WHERE id=?";
  const values = [id];
  db.query(sql, values, (err, result) => {
    if (err)
      return res.json({ message: "Something unexpected has occured" + err });
    return res.json({ success: "Student successfully Deleted" });
  });
});

app.listen(3001, () => {console.log('Server started on port 3001')});

db.js

//db.js
const mysql = require("mysql");

const db = mysql.createPool({
    socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock',
    connectionLimit: 10,
    host: "localhost",
    user: "root",
    password: "root",
    database: "nodeexpressDB"
});

// Ping database to check for common exception errors.
db.getConnection((err, connection) => {
    if (err) {
        if (err.code === 'PROTOCOL_CONNECTION_LOST') {
            console.error('Database connection was closed.');
        }
        if (err.code === 'ER_CON_COUNT_ERROR') {
            console.error('Database has too many connections.');
        }
        if (err.code === 'ECONNREFUSED') {
            console.error('Database connection was refused.');
        }
    }
 
    if (connection) connection.release();
 
    return;
});

module.exports = db;

Next.js

Install requirements
npm install axios
https://www.npmjs.com/package/axios

app\page.js

//app\page.js
import ListUser from '@/components/ListUser'
import { Suspense } from "react";
import Link from "next/link";
 
export default function Home() {
  return (
    <div className="w-screen py-20 flex justify-center flex-col items-center">
      <div className="flex items-center justify-between gap-1 mb-5">
        <h1 className="text-4xl font-bold">Next.js 14 Node Express CRUD (Create Read Update and Delete) | MySQL</h1>
      </div>    
      <div className="overflow-x-auto">
          <div className="mb-2 w-full text-right">
            <Link
              href="/users/create"
              className="btn btn-primary">
              Add New User
            </Link>
          </div>
        <Suspense fallback="Loading...">
            <ListUser/>
          </Suspense>
      </div>  
    </div>
  );
}

app\users\create\page.jsx

//app\users\create\page.jsx
"use client";
 
import React, { useState } from "react";
import axios from 'axios' //npm install axios https://www.npmjs.com/package/axios
import { useRouter } from 'next/navigation';

const Addnewuser = () => {
    const [inputs, setInputs] = useState([]);
  
    const handleChange = (event) => {
        const name = event.target.name;
        const value = event.target.value;
        setInputs(values => ({...values, [name]: value}));
    }
 
    const router = useRouter();

    const handleSubmit = (event) => {
        event.preventDefault();
 
        axios.post('http://localhost:3001/api/adduser', inputs).then(function(response){
            console.log(response.data);
            router.push('/')
        });
    }
 
    return (
    <div className="max-w-md mx-auto mt-5">
        <h1 className="text-2xl text-center mb-2">Add New User</h1>
        <div>
        <form onSubmit={handleSubmit}>
        <div className="mb-5">
          <label htmlFor="name" className="block text-sm font-medium text-gray-900">
            Name
          </label>
          <input
            type="text"
            name="name"
            id="name"
            className="input input-bordered input-primary w-full max-w-xs"
            placeholder="Name..."
            onChange={handleChange}
          />
        </div>
        <div className="mb-5">
          <label htmlFor="email" className="block text-sm font-medium text-gray-900">
            Email
          </label>
          <input
            type="email"
            name="email"
            id="email"
            className="input input-bordered input-primary w-full max-w-xs"
            placeholder="email..."
            onChange={handleChange}
          />
        </div>
        <div className="mb-5">
          <label htmlFor="username" className="block text-sm font-medium text-gray-900">
            Username
          </label>
          <input
            type="text"
            name="username"
            id="username"
            className="input input-bordered input-primary w-full max-w-xs"
            placeholder="Username..."
            onChange={handleChange}
          />
        </div>
        <div className="mb-5">
          <label htmlFor="password" className="block text-sm font-medium text-gray-900">
            Password
          </label>
          <input
            type="password"
            name="password"
            id="password"
            className="input input-bordered input-primary w-full max-w-xs"
            placeholder="Password..."
            onChange={handleChange}
          />
        </div>
        <button type="submit" className="btn btn-primary">Add New User</button> 
      </form>
    </div>
    </div>
  );
};
    
export default Addnewuser;

app\users\edit\[id]\page.jsx

//app\users\edit\[id]\page.jsx
"use client";
   
import React, { useState, useEffect } from 'react';
import axios from 'axios' //npm install axios https://www.npmjs.com/package/axios
import { useParams, useRouter } from 'next/navigation'

export default function Edituser() {
    const [inputs, setInputs] = useState([]);
    const {id}=useParams();
    //console.log(id);
  
    useEffect(() => {
        getUser();
    }, []);
  
    function getUser() {
        axios.get(`http://localhost:3001/api/getuser/${id}`).then(function(response) {
            console.log(response.data[0]);
            setInputs(response.data[0]);
        });
    }
 
    const handleChange = (event) => {
        const name = event.target.name;
        const value = event.target.value;
        setInputs(values => ({...values, [name]: value}));
    }
 
    const router = useRouter();

    const handleSubmit = (event) => {
        event.preventDefault();
  
        axios.put(`http://localhost:3001/api/edit/${id}`, inputs).then(function(response){
            console.log(response.data);
            router.push('/')
        });
          
    }
    return (
    <div className="max-w-md mx-auto mt-5">
      <h1 className="text-2xl text-center mb-2">Edit Form</h1>
            <form onSubmit={handleSubmit}> 
                <div className="mb-3 mt-3">
                    <label className="block text-sm font-medium text-gray-900"> ID:</label>
                    <input type="text" id="id" name="id" value={id} disabled />
                </div>
                <div className="mb-3 mt-3">
                    <label className="block text-sm font-medium text-gray-900"> Full Name:</label>
                    <input type="text" className="input input-bordered input-primary w-full max-w-xs" placeholder="Enter Your Full Name" name="name"
                    value={inputs.name || ''} 
                    onChange={handleChange}/>
                </div>
                <div className="mb-3 mt-3">
                    <label className="block text-sm font-medium text-gray-900">Email:</label>
                    <input type="text" className="input input-bordered input-primary w-full max-w-xs" id="email" placeholder="Enter email" name="email"
                    value={inputs.email || ''} 
                    onChange={ handleChange}/>
                </div>
                <div className="mb-3 mt-3">
                    <label className="block text-sm font-medium text-gray-900">Username:</label>
                    <input type="text" className="input input-bordered input-primary w-full max-w-xs" id="username" placeholder="Enter username" name="username"
                    value={inputs.username || ''} 
                    onChange={ handleChange}/>
                </div>
                <button type="submit" name="update"  className="btn btn-primary">Update</button>
            </form>
    </div>
  );
}

app\users\read\[id]\page.jsx

//app\users\read\[id]\page.jsx
"use client";
   
import React, { useState, useEffect } from 'react';
import axios from 'axios' //npm install axios https://www.npmjs.com/package/axios
import { useParams } from 'next/navigation'
  
export default function ViewUser() {
    const {id}=useParams();
   
    console.log(id);
   
    const[user,setUser]=useState([]);
    
    useEffect(()=>{
        fetchUser();
    },[id]);
    
    const fetchUser=async()=>{
        try{
        const result=await axios.get("http://localhost:3001/api/getuser/"+id);
          console.log(result.data[0]);
          setUser(result.data[0])
    
        }catch(err){
            console.log("Something Wrong");
        }
    }
   
    return (
    <div className="max-w-2xl mx-auto mt-5">
      <h1 className="text-2xl text-center mb-2">View User</h1>
      <table className="table table-zebra">
          <thead className="text-sm text-gray-700 uppercase bg-gray-50">
            <tr>
              <th>S No.</th>
              <th>Name</th>
              <th>Email</th>         
              <th>Username</th>      
            </tr>
          </thead>
          <tbody>
            <tr>
                <td>{user.id}</td>
                <td>{user.name}</td>
                <td>{user.email}</td>
                <td>{user.username}</td>
            </tr>
          </tbody>
      </table>
    </div>
  );
}

app\users\components\ListUser.jsx

//app\users\components\ListUser.jsx
"use client";
 
import axios from 'axios';
import { useEffect, useState } from "react";
import Link from "next/link";
 
export default function ListUser() {
 
    const [users, setUsers] = useState([]);
 
    useEffect(() => {
        getUsers();
    }, []);
  
    function getUsers() { 
        axios.get('http://localhost:3001/api/users').then(function(response) {
            //console.log(response.data);
            setUsers(response.data);
        });
    }
 
    const deleteUser = (id) => {
        axios.delete(`http://localhost:3001/api/delete/${id}`).then(function(response){
            console.log(response.data);
            getUsers();
        });
    }
     
    return (
        <table className="table table-zebra">
            <thead className="text-sm text-gray-700 uppercase bg-gray-50">
                <tr>
                    <th className="py-3 px-6">#</th>
                    <th className="py-3 px-6">Name</th>
                    <th className="py-3 px-6">Email</th>
                    <th className="py-3 px-6">Username</th>
                    <th className="py-3 px-6 text-center">Actions</th>
                </tr>
            </thead>
            <tbody>
                {users.map((user, key) =>
                    <tr key={key} className="bg-white border-b">
                        <td className="py-3 px-6">{user.id}</td>
                        <td className="py-3 px-6">{user.name}</td>
                        <td className="py-3 px-6">{user.email}</td>
                        <td className="py-3 px-6">{user.username}</td>
                        <td className="flex justify-center gap-1 py-3">
                            <Link
                                href={`/users/read/${user.id}`} 
                                className="btn btn-success">
                                Read
                            </Link>
                            <Link className="btn btn-info" href={`users/edit/${user.id}/`}>
                                Edit
                            </Link>
                            <button onClick={() => deleteUser(user.id)} className="btn btn-error">Delete</button>
                        </td>
                    </tr>
                )}
            </tbody>
        </table>
    )
}

run C:\nextjs>npm run dev