React, Node.js, Express and MySQL CRUD app

This tutorial teaches you how to create a basic CRUD app using React in the frontend and Express in the backend with a MySQL database.

In this comprehensive tutorial, you'll learn how to create a full-stack CRUD (Create, Read, Update, Delete) application using React for the front end and Node.js with Express for the back end, all powered by a MySQL database. We'll utilize technologies such as React Router, Axios, and TailwindCSS to enhance the user experience.

1. Project Overview#

We'll develop a full-stack application where users can manage tutorials. Each tutorial will have the following attributes:

ID: Unique identifier
Title: Name of the tutorial
Description: Detailed information
Published Status: Indicates if the tutorial is published

Users can perform the following actions:

Create a new tutorial
Retrieve all tutorials or a specific one by ID
Update existing tutorials
Delete tutorials
Search tutorials by title

1.1 Example Screenshots#

1.1.1 Add a New Tutorial#

Add Item

1.1.2 Display All Tutorials#

Show All Items

1.1.3 Edit a Tutorial#

Edit Item

1.1.4 Search Tutorials by Title#

Search Tutorials

Subscribe to our Passkeys Substack for the latest news and insights.

2. Architecture#

The application follows a client-server architecture:

Backend: Node.js with Express handles RESTful APIs and interacts with the MySQL database using Sequelize ORM.
Frontend: React.js communicates with the backend via Axios for HTTP requests and uses React Router for navigation.

react nodejs express mysql architecture

3. Backend Implementation#

3.1 Setup Node.js Application#

Create Project Directory:

mkdir react-node-express-mysql-crud
cd react-node-express-mysql-crud

Initialize Node.js App:
```
npm init -y
```

Install Dependencies:

npm install express sequelize mysql2 cors --save

Use ESModule Syntax Add the following line to your package.json file:
```
{
   "type": "module"
   // ...
}
```

3.2 Configure MySQL & Sequelize#

Create Configuration File (app/config/db.config.js):

export default {
   HOST: "localhost",
   USER: "root",
   PASSWORD: "root",
   DB: "db",
   PORT: 8081,
   dialect: "mysql",
   pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
   }
};

Initialize Sequelize (app/models/index.js):

import dbConfig from "../config/db.config.js"
import Sequelize from "sequelize";
import Tutorial from "./tutorial.model.js";
 
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
   host: dbConfig.HOST,
   dialect: dbConfig.dialect,
   pool: dbConfig.pool,
   port: dbConfig.PORT,
});
 
const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;
db.tutorials = Tutorial(sequelize, Sequelize);
 
export default db;

Define Tutorial Model (app/models/tutorial.model.js):

export default (sequelize, Sequelize) => {
   const Tutorial = sequelize.define("tutorial", {
      title: {
         type: Sequelize.STRING
      },
      description: {
         type: Sequelize.STRING
      },
      published: {
         type: Sequelize.BOOLEAN
      }
   });
   return Tutorial;
};

If you don't hava a MySQL database for local development, you can use Docker to create a temporary MySQL container:

docker run --rm -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=root \
  -e MYSQL_DATABASE=db \
  mysql:8

3.3 Define Routes and Controllers#

Create Controller (app/controllers/tutorial.controller.js):

import db from "../models/index.js";
 
const Op = db.Sequelize.Op;
const Tutorial = db.tutorials;
 
// Create and Save a new Tutorial
export const create = (req, res) => {
   // Validate request
   if (!req.body.title) {
      res.status(400).send({
         message: "Content can not be empty!"
      });
      return;
   }
 
   // Create a Tutorial
   const tutorial = {
      title: req.body.title,
      description: req.body.description,
      published: req.body.published ? req.body.published : false
   };
 
   // Save Tutorial in the database
   Tutorial.create(tutorial)
       .then(data => {
          res.send(data);
       })
       .catch(err => {
          res.status(500).send({
             message:
                 err.message || "Some error occurred while creating the Tutorial."
          });
       });
};
 
// Retrieve all Tutorials
export const findAll = (req, res) => {
   // Allow a filter condition via query parameter
   const title = req.query.title;
   const condition = title ? {title: {[Op.like]: `%${title}%`}} : null;
 
   Tutorial.findAll({where: condition})
       .then(data => {
          res.send(data);
       })
       .catch(err => {
          res.status(500).send({
             message:
                 err.message || "Some error occurred while retrieving tutorials."
          });
       });
};
 
// Find a single Tutorial by ID
export const findOne = (req, res) => {
   const id = req.params.id;
 
   // Find Tutorial by primary key
   Tutorial.findByPk(id)
       .then(data => {
          if (data) {
             res.send(data);
          } else {
             res.status(404).send({
                message: `Cannot find Tutorial with id=${id}.`
             });
          }
       })
       .catch(err => {
          res.status(500).send({
             message: "Error retrieving Tutorial with id=" + id
          });
       });
};
 
// Update a Tutorial by ID
export const update = (req, res) => {
   const id = req.params.id;
 
   // Update the Tutorial with the specified ID
   Tutorial.update(req.body, {
      where: {id: id}
   })
       .then(num => {
          if (num === 1) {
             res.send({
                message: "Tutorial was updated successfully."
             });
          } else {
             res.send({
                message: `Cannot update Tutorial with id=${id}. Maybe Tutorial was not found or req.body is empty!`
             });
          }
       })
       .catch(err => {
          res.status(500).send({
             message: "Error updating Tutorial with id=" + id
          });
       });
};
 
// Delete a Tutorial by ID
export const deleteOne = (req, res) => {
   const id = req.params.id;
 
   // Delete the Tutorial with the specified ID
   Tutorial.destroy({
      where: {id: id}
   })
       .then(num => {
          if (num === 1) {
             res.send({
                message: "Tutorial was deleted successfully!"
             });
          } else {
             res.send({
                message: `Cannot delete Tutorial with id=${id}. Maybe Tutorial was not found!`
             });
          }
       })
       .catch(err => {
          res.status(500).send({
             message: "Could not delete Tutorial with id=" + id
          });
       });
};
 
// Delete all Tutorials
export const deleteAll = (req, res) => {
   // Delete all Tutorials
   Tutorial.destroy({
      where: {},
      truncate: false
   })
       .then(nums => {
          res.send({message: `${nums} Tutorials were deleted successfully!`});
       })
       .catch(err => {
          res.status(500).send({
             message:
                 err.message || "Some error occurred while removing all tutorials."
          });
       });
};
 
// Find all published Tutorials
export const findAllPublished = (req, res) => {
   // Find all Tutorials with published = true
   Tutorial.findAll({where: {published: true}})
       .then(data => {
          res.send(data);
       })
       .catch(err => {
          res.status(500).send({
             message:
                 err.message || "Some error occurred while retrieving tutorials."
          });
       });
};

Set Up Routes (app/routes/tutorial.routes.js):

import * as tutorials from "../controllers/tutorial.controller.js";
import express from "express";
 
export default (app) => {
   let router = express.Router();
 
   // Create a new Tutorial
   router.post("/", tutorials.create);
 
   // Retrieve all Tutorials
   router.get("/", tutorials.findAll);
 
   // Retrieve a single Tutorial with id
   router.get("/:id", tutorials.findOne);
 
   // Update a Tutorial with id
   router.put("/:id", tutorials.update);
 
   // Delete a Tutorial with id
   router.delete("/:id", tutorials.deleteOne);
 
   // Delete all Tutorials
   router.delete("/", tutorials.deleteAll);
 
   // Find all published Tutorials
   router.get("/published", tutorials.findAllPublished);
 
   app.use('/api/tutorials', router);
};

Want to try passkeys yourself? Check our Passkeys Demo.

Try Passkeys

3.4 Run the Server#

Create Server File (server.js):

import express from "express";
import cors from "cors";
import db from "./app/models.js";
import tutorialRoutes from "./app/routes/tutorial.routes.js";
 
const app = express();
 
const corsOptions = {
   origin: "http://localhost:5173"
};
 
app.use(cors(corsOptions));
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
 
// Simple route
app.get("/", (req, res) => {
   res.json({ message: "Welcome to the Tutorial Application." });
});
 
// Routes
tutorialRoutes(app);
 
// Sync database
db.sequelize.sync().then(() => {
   console.log("Synced db.");
});
 
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
   console.log(`Server is running on port ${PORT}.`);
});

Start the Server:

node server.js

Output:

Server is running on port 8080.
Synced db.

4. Frontend Implementation#

This is the architecture:

react architecture

Alteratively, you can use Redux:

react redux architecture

4.1 File structure#

Your final file structure will look like this:

frontend/
├─ index.html
├─ package.json
├─ postcss.config.js
├─ tailwind.config.js
├─ vite.config.js
├─ src/
│  ├─ App.jsx
│  ├─ main.jsx
│  ├─ index.css
│  ├─ services/
│  │   └─ tutorial.service.js
│  └─ pages/
│      ├─ AddTutorial.jsx
│      ├─ Tutorial.jsx
│      └─ TutorialsList.jsx

Become part of our Passkeys Community for updates and support.

Join

4.2 Create the React App#

Run the following commands to set up a new React app using Vite:

npm create vite@latest frontend -- --template react
cd frontend
npm i
npm i react-router-dom axios
npm install -D tailwindcss autoprefixer
npx tailwindcss init -p

Finally, set up the tailwind content option in the configuration file tailwind.config.js:

/** @type {import('tailwindcss').Config} */
export default {
   content: [
      "./index.html",
      "./src/**/*.{js,ts,jsx,tsx}",
   ],
   theme: {
      extend: {},
   },
   plugins: [],
}

Then, open src/index.css (Vite has created this file for you) and add the Tailwind directives:

@tailwind base;
@tailwind components;
@tailwind utilities;

4.3 Initialize the App layout#

The App.jsx component configures React Router routes and sets up a basic Tailwind navbar. We’ll navigate between:

/tutorials – list of tutorials
/add – form to create new tutorial
/tutorials/:id – editing a single tutorial

import { Routes, Route, Link } from "react-router-dom";
import TutorialsList from "./pages/TutorialsList";
import AddTutorial from "./pages/AddTutorial";
import Tutorial from "./pages/Tutorial";
 
function App() {
   return (
       <BrowserRouter>
          <div>
             {/* NAVBAR */}
             <nav className="bg-blue-600 p-4 text-white">
                <div className="flex space-x-4">
                   <Link to="/tutorials" className="hover:text-gray-300 font-bold">
                      Tutorials
                   </Link>
                   <Link to="/add" className="hover:text-gray-300">
                      Add
                   </Link>
                </div>
             </nav>
 
             {/* ROUTES */}
             <div className="container mx-auto mt-8 px-4">
                <Routes>
                   <Route path="/" element={<TutorialsList/>}/>
                   <Route path="/tutorials" element={<TutorialsList/>}/>
                   <Route path="/add" element={<AddTutorial/>}/>
                   <Route path="/tutorials/:id" element={<Tutorial/>}/>
                </Routes>
             </div>
          </div>
       </BrowserRouter>
   );
}
 
export default App;

4.4 Create Data Service#

This service handles Axios HTTP requests to our Node/Express backend (http://localhost:8080/api). Update the baseURL if your server runs on a different address or port.

import axios from "axios";
 
const http = axios.create({
   baseURL: "http://localhost:8080/api",
   headers: {
      "Content-Type": "application/json",
   },
});
 
const getAll = () => {
   return http.get("/tutorials");
};
 
const get = (id) => {
   return http.get(`/tutorials/${id}`);
};
 
const create = (data) => {
   return http.post("/tutorials", data);
};
 
const update = (id, data) => {
   return http.put(`/tutorials/${id}`, data);
};
 
const remove = (id) => {
   return http.delete(`/tutorials/${id}`);
};
 
const removeAll = () => {
   return http.delete("/tutorials");
};
 
const findByTitle = (title) => {
   return http.get(`/tutorials?title=${title}`);
};
 
export default {
   getAll,
   get,
   create,
   update,
   remove,
   removeAll,
   findByTitle,
};

4.5 Add item Component#

A component for creating new tutorials under src/pages/AddTutorial.jsx. It allows entering title and description, and then calls TutorialService.create().

import React, { useState } from "react";
import TutorialService from "../services/tutorial.service";
 
function AddTutorial() {
   const [title, setTitle] = useState("");
   const [description, setDescription] = useState("");
   const [submitted, setSubmitted] = useState(false);
 
   const saveTutorial = () => {
      const data = { title, description };
      TutorialService.create(data)
          .then((response) => {
             console.log(response.data);
             setSubmitted(true);
          })
          .catch((e) => {
             console.log(e);
          });
   };
 
   const newTutorial = () => {
      setTitle("");
      setDescription("");
      setSubmitted(false);
   };
 
   return (
       <div className="max-w-sm mx-auto p-4 bg-white rounded shadow">
          {submitted ? (
              <div>
                 <h4 className="font-bold text-green-600 mb-4">
                    Tutorial submitted successfully!
                 </h4>
                 <button className="bg-blue-500 text-white px-3 py-1 rounded" onClick={newTutorial}>
                    Add Another
                 </button>
              </div>
          ) : (
              <div>
                 <h4 className="font-bold text-xl mb-2">Add Tutorial</h4>
 
                 <div className="mb-2">
                    <label className="block mb-1 font-medium">Title</label>
                    <input
                        type="text"
                        className="border border-gray-300 rounded w-full px-2 py-1"
                        value={title}
                        onChange={(e) => setTitle(e.target.value)}
                    />
                 </div>
 
                 <div className="mb-2">
                    <label className="block mb-1 font-medium">Description</label>
                    <input
                        type="text"
                        className="border border-gray-300 rounded w-full px-2 py-1"
                        value={description}
                        onChange={(e) => setDescription(e.target.value)}
                    />
                 </div>
 
                 <button
                     className="bg-green-500 text-white px-3 py-1 rounded mt-2"
                     onClick={saveTutorial}
                 >
                    Submit
                 </button>
              </div>
          )}
       </div>
   );
}
 
export default AddTutorial;

Are your users passkey-ready?

Test Passkey-Readiness

4.6 Tutorial List Component#

A component under src/pages/TutorialsList.jsx that:

Displays a search bar to filter by tutorial title
Lists tutorials on the left
Shows the selected tutorial on the right
Provides a button to remove all tutorials

import { useState, useEffect } from "react";
import TutorialService from "../services/tutorial.service";
import { Link } from "react-router-dom";
 
function TutorialsList() {
   const [tutorials, setTutorials] = useState([]);
   const [currentTutorial, setCurrentTutorial] = useState(null);
   const [currentIndex, setCurrentIndex] = useState(-1);
   const [searchTitle, setSearchTitle] = useState("");
 
   useEffect(() => {
      retrieveTutorials();
   }, []);
 
   const onChangeSearchTitle = (e) => {
      setSearchTitle(e.target.value);
   };
 
   const retrieveTutorials = () => {
      TutorialService.getAll()
          .then((response) => {
             setTutorials(response.data);
             console.log(response.data);
          })
          .catch((e) => {
             console.log(e);
          });
   };
 
   const refreshList = () => {
      retrieveTutorials();
      setCurrentTutorial(null);
      setCurrentIndex(-1);
   };
 
   const setActiveTutorial = (tutorial, index) => {
      setCurrentTutorial(tutorial);
      setCurrentIndex(index);
   };
 
   const removeAllTutorials = () => {
      TutorialService.removeAll()
          .then((response) => {
             console.log(response.data);
             refreshList();
          })
          .catch((e) => {
             console.log(e);
          });
   };
 
   const findByTitle = () => {
      TutorialService.findByTitle(searchTitle)
          .then((response) => {
             setTutorials(response.data);
             setCurrentTutorial(null);
             setCurrentIndex(-1);
             console.log(response.data);
          })
          .catch((e) => {
             console.log(e);
          });
   };
 
   return (
       <div className="flex flex-col lg:flex-row gap-8">
          {/* LEFT COLUMN: SEARCH + LIST */}
          <div className="flex-1">
             <div className="flex mb-4">
                <input
                    type="text"
                    className="border border-gray-300 rounded-l px-2 py-1 w-full"
                    placeholder="Search by title"
                    value={searchTitle}
                    onChange={onChangeSearchTitle}
                />
                <button
                    className="bg-blue-500 text-white px-4 py-1 rounded-r"
                    onClick={findByTitle}
                >
                   Search
                </button>
             </div>
 
             <h4 className="font-bold text-lg mb-2">Tutorials List</h4>
             <ul className="divide-y divide-gray-200 border border-gray-200 rounded">
                {tutorials &&
                    tutorials.map((tutorial, index) => (
                        <li
                            className={
                                "px-4 py-2 cursor-pointer " +
                                (index === currentIndex ? "bg-blue-100" : "")
                            }
                            onClick={() => setActiveTutorial(tutorial, index)}
                            key={index}
                        >
                           {tutorial.title}
                        </li>
                    ))}
             </ul>
 
             <button
                 className="bg-red-500 text-white px-3 py-1 rounded mt-4"
                 onClick={removeAllTutorials}
             >
                Remove All
             </button>
          </div>
 
          {/* RIGHT COLUMN: DETAILS */}
          <div className="flex-1">
             {currentTutorial ? (
                 <div className="p-4 bg-white rounded shadow">
                    <h4 className="font-bold text-xl mb-2">Tutorial</h4>
                    <div className="mb-2">
                       <strong>Title: </strong>
                       {currentTutorial.title}
                    </div>
                    <div className="mb-2">
                       <strong>Description: </strong>
                       {currentTutorial.description}
                    </div>
                    <div className="mb-2">
                       <strong>Status: </strong>
                       {currentTutorial.published ? "Published" : "Pending"}
                    </div>
 
                    <Link
                        to={`/tutorials/${currentTutorial.id}`}
                        className="inline-block bg-yellow-400 text-black px-3 py-1 rounded"
                    >
                       Edit
                    </Link>
                 </div>
             ) : (
                 <div>
                    <p>Please click on a Tutorial...</p>
                 </div>
             )}
          </div>
       </div>
   );
}
 
export default TutorialsList;

Want to find out how many people can use passkeys?

View Adoption Data

4.7 Tutorial Component#

A function component under src/pages/Tutorial.jsx for viewing and editing a single tutorial. It uses:

useParams() to get :id from the URL
useNavigate() to redirect
TutorialService for get, update, and delete operations

import { useState, useEffect } from "react";
import { useParams, useNavigate } from "react-router-dom";
import TutorialService from "../services/tutorial.service";
 
function Tutorial() {
   const { id } = useParams();
   const navigate = useNavigate();
 
   const [currentTutorial, setCurrentTutorial] = useState({
      id: null,
      title: "",
      description: "",
      published: false,
   });
   const [message, setMessage] = useState("");
 
   const getTutorial = (id) => {
      TutorialService.get(id)
          .then((response) => {
             setCurrentTutorial(response.data);
             console.log(response.data);
          })
          .catch((e) => {
             console.log(e);
          });
   };
 
   useEffect(() => {
      if (id) getTutorial(id);
   }, [id]);
 
   const handleInputChange = (event) => {
      const { name, value } = event.target;
      setCurrentTutorial({ ...currentTutorial, [name]: value });
   };
 
   const updatePublished = (status) => {
      const data = {
         ...currentTutorial,
         published: status,
      };
 
      TutorialService.update(currentTutorial.id, data)
          .then((response) => {
             setCurrentTutorial({ ...currentTutorial, published: status });
             console.log(response.data);
          })
          .catch((e) => {
             console.log(e);
          });
   };
 
   const updateTutorial = () => {
      TutorialService.update(currentTutorial.id, currentTutorial)
          .then((response) => {
             console.log(response.data);
             setMessage("The tutorial was updated successfully!");
          })
          .catch((e) => {
             console.log(e);
          });
   };
 
   const deleteTutorial = () => {
      TutorialService.remove(currentTutorial.id)
          .then((response) => {
             console.log(response.data);
             navigate("/tutorials");
          })
          .catch((e) => {
             console.log(e);
          });
   };
 
   return (
       <div>
          {currentTutorial ? (
              <div className="max-w-sm mx-auto p-4 bg-white rounded shadow">
                 <h4 className="font-bold text-xl mb-2">Edit Tutorial</h4>
                 <div className="mb-2">
                    <label className="block font-medium" htmlFor="title">
                       Title
                    </label>
                    <input
                        type="text"
                        className="border border-gray-300 rounded w-full px-2 py-1"
                        id="title"
                        name="title"
                        value={currentTutorial.title}
                        onChange={handleInputChange}
                    />
                 </div>
 
                 <div className="mb-2">
                    <label className="block font-medium" htmlFor="description">
                       Description
                    </label>
                    <input
                        type="text"
                        className="border border-gray-300 rounded w-full px-2 py-1"
                        id="description"
                        name="description"
                        value={currentTutorial.description}
                        onChange={handleInputChange}
                    />
                 </div>
 
                 <div className="mb-2">
                    <strong>Status:</strong> {currentTutorial.published ? "Published" : "Pending"}
                 </div>
 
                 <div className="space-x-2 mt-2">
                    {currentTutorial.published ? (
                        <button
                            className="bg-blue-500 text-white px-3 py-1 rounded"
                            onClick={() => updatePublished(false)}
                        >
                           Unpublish
                        </button>
                    ) : (
                        <button
                            className="bg-blue-500 text-white px-3 py-1 rounded"
                            onClick={() => updatePublished(true)}
                        >
                           Publish
                        </button>
                    )}
 
                    <button
                        className="bg-red-500 text-white px-3 py-1 rounded"
                        onClick={deleteTutorial}
                    >
                       Delete
                    </button>
 
                    <button
                        className="bg-green-500 text-white px-3 py-1 rounded"
                        onClick={updateTutorial}
                    >
                       Update
                    </button>
                 </div>
 
                 {message && <p className="text-green-600 mt-2">{message}</p>}
              </div>
          ) : (
              <div>
                 <p>Loading tutorial...</p>
              </div>
          )}
       </div>
   );
}
 
export default Tutorial;

Want to experiment with passkey flows? Try our Passkeys Debugger.

Try for Free

4.8 Run the application#

npm run dev

Now you can access the application at http://localhost:5173. Open that URL in your browser. You can now navigate to:

/tutorials – see all tutorials
/add – add a tutorial
/tutorials/:id – edit a specific tutorial

(Make sure your Node/Express back end is running on http://localhost:8080 or update the baseURL in tutorial.service.js accordingly.)

5. Conclusion#

You've successfully built a full-stack CRUD application using React, Node.js, Express, and MySQL. This project showcases how to set up a RESTful API with Node.js and Express, manage data with Sequelize ORM, and create a responsive frontend with React and Bootstrap.

Happy coding and see you in the next tutorial!