Saurav Pant
CRUD Operations with MySQL+Express+React

CRUD Operations with MySQL+Express+React

Saurav Pant's photo
Saurav Pant
·

6 min read

Table of contents

Introduction

In this Blog, we will create a simple CRUD application using MySQL, Express, and React

To Follow along with this Blog Create a folder named Learning_mySQL run this command for creating a React app and enter all the things you want in this project.

npm create vite@latest

Now your Client is ready, let's go to create a Server, Firstly Create a folder Server in the root directory so that the Client and Server code get distinguished.

inside the Server folder run this command npm init -y then install Express and MySQL for the project, for updating every change install nodemon

Run this command:-

npm i nodemon express mysql

Now Let's first create an Express server and connect it to MySQL

Server and Database Setup

Firstly For creating DataBases in MySQL -

  1. Go to MySQL WorkBench

  2. Create a Schema

  3. Create a table with 3 things

    1. id - Primary key, auto-increment

    2. title - not null

    3. desc - not null

import express from "express";
import mysql from "mysql";

const app = express();  // Initializes an Express application.
app.use(express.json()); // Configures the application to parse incoming JSON data.
app.use(cors()); // Enables(CORS) to allow cross-origin HTTP requests from the client-side.

const db = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "Add your Password Here!",
  database: "Add your DataBase Name Here!",
  insecureAuth: true,
}); 

app.listen(8080, () => {
  console.log("Server listening on port 8080");
});

if you are encountering any "Fatal Error" or something run this command in the workbench and save :)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Your_Password';

GET Request

Now you created database schemas and set the server let's create a GET Request.

app.get("/notes", (req, res) => {
  const q = "SELECT * FROM learning.notes;";
  db.query(q, (err, data) => {
    if (err) return res.json(err);
    return res.json(data);
  });
});

In Backend Routes, if you go to /notes you will get a response, if in tables you added something you will get a JSON object in http://localhost:8080/notes

Now Let's understand what this Code is saying

The Second Line is for Defining an SQL query to select all columns (*) from the "notes" table in the "learning" database.

and after the third line, we are taking this query and if this works fine we will get a response JSON but if the error comes it will show the error response

In this Blog we also see how to use all these operations in Frontend, so stay with me.

POST Request

Now Let's Understand the POST Request

app.post("/notes", (req, res) => {
  const { title, desc } = req.body;
  const q = "INSERT INTO learning.notes (`title`, `desc`) VALUES (?, ?)";
  db.query(q, [title, desc], (err, data) => {
    if (err) return res.json(err);

    return res.json("Note has been created successfully");
  });
});

  • First Line: Set up a route to handle HTTP POST requests at the /notes endpoint.

  • Second Line: Extracts the title and desc properties from the request body.

  • Third Line: Defines a SQL query to insert a new record into the notes table with the provided title desc values.

  • Fourth Line: Executes the SQL query using the MySQL connection (db). The results are handled in a callback function that takes two parameters: err for error and data for the query results.

PUT Request

Now Let's Understand the PUT Request

app.put("/notes/:id", (req, res) => {
  const noteId = req.params.id;
  const { title, desc } = req.body;

  const q = "UPDATE learning.notes SET title = ?, `desc` = ? WHERE id = ?";
  db.query(q, [title, desc, noteId], (err, data) => {
    if (err) return res.json(err);

    return res.json("Note has been updated successfully");
  });
});

  • Second Line: Retrieves the noteId from the request parameters (req.params.id), representing the unique identifier of the note to be updated.

  • Third Line: Extracts the title and desc properties from the request body.

  • Fourth Line: Defines a SQL query to update a record in the notes table with the provided title and desc values based on the noteId.

DELETE Request

Now Let's Understand the DELETE Request

app.delete("/notes/:id", (req, res) => {
  const noteId = req.params.id;

  const q = "DELETE FROM learning.notes WHERE id = ?";
  db.query(q, [noteId], (err, data) => {
    if (err) return res.json(err);

    return res.json("Note has been deleted successfully");
  });
});

  • Second Line: Retrieves the noteId from the request parameters (req.params.id), representing the unique identifier of the note to be deleted.

  • Third Line: Defines a SQL query to delete a record from the "notes" table where the id matches the provided noteId.

  • Fourth Line: Executes the SQL query using the MySQL connection (db). The results are handled in a callback function that takes two parameters: err for error and data for the query results.

That's it your Backend work is done, Now Let's use this in the Client

Frontend Part

I am using simple UI for this, just to show it is working fine

import { useState } from "react";

function App() {
  const [notesTitle, setNotesTitle] = useState("");
  const [notesDesc, setNotesDesc] = useState("");

  const handleSubmit = async (e:any) => {
    e.preventDefault();

    try {
      const response = await fetch("http://localhost:8080/notes", {
        method: "POST",
        headers: {
          "Content-Type": "application/json",
        },
        body: JSON.stringify({ title: notesTitle, desc: notesDesc }),
      });

      if (response.ok) {
        console.log("Note added successfully");
        window.location.reload();
      } else {
        console.error("Failed to add note");
      }
    } catch (error) {
      console.error("Error adding note:", error);
    }
  };

  return (
    <div>
      <div className="NotesForm"> 
      <form onSubmit={handleSubmit}>
        <label htmlFor="notesTitle">Add Notes Title</label>
        <input
          type="text"
          id="notesTitle"
          value={notesTitle}
          onChange={(e) => setNotesTitle(e.target.value)}
        />
        <label htmlFor="notesDesc">Add Notes Description</label>
        <input
          type="text"
          id="notesDesc"
          value={notesDesc}
          onChange={(e) => setNotesDesc(e.target.value)}
        />
        <button type="submit" className="submit-btn">Submit</button>
      </form>
      </div>
    </div>
  );
}

export default App;

Here this code demonstrates the POST Request.

Now Let's use all other methods in Frontend:-

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

interface Note {
  id: number;
  title: string;
  desc: string;
}

const Notes: React.FC = () => {
  const [notes, setNotes] = useState<Note[]>([]);
  const [updateNoteId, setUpdateNoteId] = useState<number | null>(null);
  const [updateNoteTitle, setUpdateNoteTitle] = useState<string>('');
  const [updateNoteDesc, setUpdateNoteDesc] = useState<string>('');

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

  const fetchNotes = async () => {
    try {
      const response = await fetch('http://localhost:8080/notes');
      if (response.ok) {
        const data: Note[] = await response.json();
        setNotes(data);
      } else {
        console.error('Failed to fetch notes');
      }
    } catch (error) {
      console.error('Error fetching notes:', error);
    }
  };

  const handleUpdateNote = (noteId: number) => {
    setUpdateNoteId(noteId);

    const noteToUpdate = notes.find((note) => note.id === noteId);
    if (noteToUpdate) {
      setUpdateNoteTitle(noteToUpdate.title);
      setUpdateNoteDesc(noteToUpdate.desc);
    }
  };

  const handleUpdateNoteSubmit = async () => {
    try {
      const response = await fetch(`http://localhost:8080/notes/${updateNoteId}`, {
        method: 'PUT',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify({
          title: updateNoteTitle,
          desc: updateNoteDesc,
        }),
      });

      if (response.ok) {
        const updatedNotes = notes.map((note) =>
          note.id === updateNoteId ? { ...note, title: updateNoteTitle, desc: updateNoteDesc } : note
        );
        setNotes(updatedNotes);

        setUpdateNoteId(null);
      } else {
        console.error('Failed to update note');
      }
    } catch (error) {
      console.error('Error updating note:', error);
    }
  };

  const handleDeleteNote = async (noteId: number) => {
    try {
      const response = await fetch(`http://localhost:8080/notes/${noteId}`, {
        method: 'DELETE',
      });

      if (response.ok) {
        const updatedNotes = notes.filter((note) => note.id !== noteId);
        setNotes(updatedNotes);
      } else {
        console.error('Failed to delete note');
      }
    } catch (error) {
      console.error('Error deleting note:', error);
    }
  };

  return (
    <div className="notes-container">
      {notes.map((note) => (
        <div key={note.id} className="note">
          <h3>{note.title}</h3>
          <p>{note.desc}</p>
          <button className="update-btn" onClick={() => handleUpdateNote(note.id)}>Update</button>
          <button className="delete-btn" onClick={() => handleDeleteNote(note.id)}>Delete</button>
        </div>
      ))}

      {updateNoteId !== null && (
        <div className="modal">
          <div className="modal-content">
            <span className="close" onClick={() => setUpdateNoteId(null)}>
              &times;
            </span>
            <h2>Update Note</h2>
            <label>Title:</label>
            <input
              type="text"
              value={updateNoteTitle}
              onChange={(e) => setUpdateNoteTitle(e.target.value)}
              className="update-input"
            />
            <label>Description:</label>
            <textarea
              value={updateNoteDesc}
              onChange={(e) => setUpdateNoteDesc(e.target.value)}
              className="update-textarea"
            ></textarea>
            <button className="update-submit-btn" onClick={handleUpdateNoteSubmit}>Update Note</button>
          </div>
        </div>
      )}
    </div>
  );
};

export default Notes;

In this code snippet, you can see all methods like GET, PUT, and DELETE

if you want to check the whole code, Check this Repo and run this locally.

Thank you for Checking this, Happy Coding 🎉

Web DevelopmentMySQLBeginner DevelopersWeMakeDevsReact