Node Js Express Mysql Crud Tutorial

In this tutorial, I will show you how to build full-stack React + Node.js + Express + MySQL example with a CRUD Application. The back-end server uses Node.js + Express for REST APIs, front-end side is a React.js client with React Router, Axios & Bootstrap.

Related Posts:
– React Redux + Node.js + Express + MySQL example: Build a CRUD App
– React + Node.js Express: Login example with JWT
– React File Upload with Axios and Progress Bar to Rest API

Run both projects in one place:
How to integrate React with Node.js Express on same Server/Port

Dockerize: Docker Compose: React, Node.js, MySQL example

Contents

  • React + Node.js + Express + MySQL example Overview
  • React, Node.js Express, MySQL Architecture
  • Video
  • Node.js Express Back-end
    • Overview
    • Project Structure
    • Implementation
      • Create Node.js App
      • Setup Express web server
      • Configure MySQL database & Sequelize
      • Initialize Sequelize
      • Define the Sequelize Kamil
      • Create the Controller
      • Run the Node.js Express Server
  • React.js Front-end
    • Overview
    • Technology
    • Project Structure
    • Implementation
      • Setup React.js Project
      • Import Bootstrap to React CRUD App
      • Add React Router to React CRUD App
      • Add Navbar to React CRUD App
      • Initialize Axios for React CRUD HTTP Client
      • Create Data Service
      • Create React Components/Pages
      • Run React CRUD App
  • Source Code
  • Conclusion
  • Further Reading

React + Node.js + Express + MySQL example Overview

We will build a full-stack Tutorial Application in that:

  • Cak bimbingan has id, title, description, published status.
  • User can create, retrieve, update, delete Tutorials.
  • There is a search box for finding Tutorials by title.

Here are screenshots of the example.

– Add an item:

react-node-express-mysql-crud-example-demo-create

– Show all items:

react-node-express-mysql-crud-example-demo-retrieve

– Click on
Edit
button to view details of an item:

react-node-express-mysql-crud-example-demo-retrieve-one

On this Page, you can:

  • change harga diri to
    Published/Pending
    using
    Publish/UnPublished
    button
  • remove the object from MySQL Database using
    Delete
    button
  • update this object’s details on Database with
    Update
    button

react-node-express-mysql-crud-example-demo-update

– Search objects by field ‘title’:

react-node-express-mysql-crud-example-demo-search

– Check MySQL database:

react-node-express-mysql-crud-example-demo-mysql-database

React, Node.js Express, MySQL Architecture

We’re gonna build the application with following architecture:

react-node-express-mysql-crud-example-architecture

– Node.js Express exports REST APIs & interacts with MySQL Database using Sequelize ORM.
– React Client sends HTTP Requests and retrieves HTTP Responses using
Axios, consume data on the components. React Router is used for navigating to pages.

Video

This is our React Node.js Express Sequelize application demo (with brief instruction) running with MySQL database.

Node.js Express Back-end

Overview

These are APIs that Node.js Express App will export:

Methods Urls Actions
GET api/tutorials get all Tutorials
GET api/tutorials/:id get Tutorial by
id
POST api/tutorials add new Tutorial
PUT api/tutorials/:id update Latihan by
id
DELETE api/tutorials/:id remove Pelajaran by
id
DELETE api/tutorials remove all Tutorials
GET api/tutorials?title=[kw] find all Tutorials which title contains
'kw'

Project Structure

react-node-js-express-mysql-example-node-server-project-structure


db.config.js
exports configuring parameters for MySQL connection & Sequelize.

Express
web server in
server.js
where we configure CORS, initialize & run Express REST APIs.
– Next, we add configuration for MySQL database in
models/index.js, create
Sequelize
data arketipe in
models/tutorial.paradigma.js.
– Tutorial controller in
controllers.
– Routes for handling all CRUD operations (including custom finder) in
tutorial.routes.js.

If you want to use raw SQL (without Sequelize), kindly visit:
Build Node.js Rest APIs with Express & MySQL

This backend works well with frontend in this tutorial.

Implementation

Create Node.js App

First, we create a folder:

        $ mkdir nodejs-express-sequelize-mysql $ cd nodejs-express-sequelize-mysql
        
      

Next, we initialize the Node.js App with a
package.json
file:

        npm init name: (nodejs-express-sequelize-mysql)  version: (1.0.0)  description: Node.js Rest Apis with Express, Sequelize & MySQL. entry point: (index.js) server.js test command:  git repository:  keywords: nodejs, express, sequelize, mysql, rest, jago merah author: bezkoder license: (ISC) Is this ok? (yes) yes
        
      

We need to install necessary modules:
express,
sequelize,
mysql2
and
cors.
Run the command:

        npm install express sequelize mysql2 cors --save
        
      

Setup Express web server

In the root folder, let’s create a new
server.js
file:

        const express = require("express"); const cors = require("cors"); const app = express(); var corsOptions = {   origin: "http://localhost:8081" }; app.use(cors(corsOptions)); // parse requests of content-type - application/json app.use(express.json()); // parse requests of content-type - application/x-www-form-urlencoded app.use(express.urlencoded({ extended: true })); // simple route app.get("/", (req, res) => {   res.json({ message: "Welcome to bezkoder application." }); }); // set port, listen for requests const PORT = process.env.PORT || 8080; app.listen(PORT, () => {   console.gelondong(`Peladen is running on port ${PORT}.`); });
        
      

What we do are:
– import
express, and
cors
modules:

  • Express is for building the Rest apis
  • cors provides Express middleware to enable CORS with various options.

– create an Express app, then add body-parser (json
and
urlencoded) and
cors
middlewares using
app.use()
method. Notice that we set origin:
http://localhost:8081.
– define a GET route which is simple for test.
– listen on port 8080 for incoming requests.

Now let’s run the app with command:
node peladen.js.
Open your browser with url http://localhost:8080/, you will see:

node-js-express-sequelize-mysql-example-setup-server

Yeah, the first step is done. We’re gonna work with Sequelize in the next section.

Configure MySQL database & Sequelize

In the
app
folder, we create a separate
config
folder for configuration with
db.config.js
file like this:

        module.exports = {   HOST: "localhost",   USER: "root",   PASSWORD: "123456",   DB: "testdb",   dialect: "mysql",   pool: {     max: 5,     min: 0,     acquire: 30000,     idle: 10000   } };
        
      

First five parameters are for MySQL connection.
pool
is optional, it will be used for Sequelize connection pool configuration:

  • max: maximum number of connection in pool
  • min: minimal number of connection in pool
  • idle: maximum time, in milliseconds, that a connection can be idle before being released
  • acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit Jago merah Reference for the Sequelize constructor.

Initialize Sequelize

We’re gonna initialize Sequelize in
app/models
folder that will contain model in the next step.

Now create
app/models/index.js
with the following code:

        const dbConfig = require("../config/db.config.js"); const Sequelize = require("sequelize"); const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {   host: dbConfig.HOST,   dialect: dbConfig.dialect,   operatorsAliases: false,   pool: {     max: dbConfig.pool.max,     min: dbConfig.pool.min,     acquire: dbConfig.pool.acquire,     idle: dbConfig.pool.idle   } }); const db = {}; db.Sequelize = Sequelize; db.sequelize = sequelize; db.tutorials = require("./tutorial.ideal.js")(sequelize, Sequelize); module.exports = db;
        
      

Don’t forget to call
sync()
method in
server.js:

        ... const app = express(); app.use(...); const db = require("./app/models"); db.sequelize.sync(); ...
        
      

In development, you may need to drop existing tables and re-sync database. Just use
force: true
as following code:

        
          db.sequelize.sync({ force: true }).then(() => {   console.log("Drop and re-sync db."); });
        
      

Define the Sequelize Model

In
models
folder, create
tutorial.model.js
file like this:

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

This Sequelize Contoh represents
tutorials
table in MySQL database. These columns will be generated automatically:
id,
title,
description,
published,
createdAt,
updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them:

  • create a new Kursus:
    create(object)
  • find a Tutorial by id:
    findByPk(id)
  • get all Tutorials:
    findAll()
  • update a Cak bimbingan by id:
    update(data, where: { id: id })
  • remove a Tutorial:
    destroy(where: { id: id })
  • remove all Tutorials:
    destroy(where: {})
  • find all Tutorials by title:
    findAll({ where: { title: ... } })

These functions will be used in our Controller.

We can improve the example by adding Comments for each Tutorial. It is the One-to-Many Relationship and I write a tutorial for this at:
Sequelize Associations: One-to-Many example – Node.js, MySQL

Or you can add Tags for each Tutorial and add Tutorials to Tag (Many-to-Many Relationship):
Sequelize Many-to-Many Association example with Node.js & MySQL

Create the Controller

Inside
app/controllers
folder, let’s create
latihan.controller.js
with these CRUD functions:

  • create
  • findAll
  • findOne
  • update
  • delete
  • deleteAll
  • findAllPublished
        const db = require("../models"); const Tutorial = db.tutorials; const Op = db.Sequelize.Op; // Create and Save a new Tutorial exports.create = (req, res) => {    }; // Retrieve all Tutorials from the database. exports.findAll = (req, res) => {    }; // Find a single Cak bimbingan with an id exports.findOne = (req, res) => {    }; // Update a Tutorial by the id in the request exports.update = (req, res) => {    }; // Delete a Kursus with the specified id in the request exports.delete = (req, res) => {    }; // Delete all Tutorials from the database. exports.deleteAll = (req, res) => {    }; // Find all published Tutorials exports.findAllPublished = (req, res) => {    };
        
      

You can continue with step by step to implement this Node.js Express App in the post:
Node.js Rest APIs example with Express, Sequelize & MySQL

Run the Node.js Express Peladen

Run our Node.js application with command:
node peladen.js.
The console shows:

        Server is running on port 8080. Executing (default): DROP TABLE IF EXISTS `tutorials`; Executing (default): CREATE TABLE IF NOT EXISTS `tutorials` (`id` INTEGER Titinada NULL auto_increment , `title` VARCHAR(255), `description` VARCHAR(255), `published` TINYINT(1), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; Executing (default): SHOW INDEX FROM `tutorials` Drop and re-sync db.
        
      

React.js Front-end

Overview

react-node-express-mysql-crud-example-react-components-overview

– The
App
component is a container with React
Router. It has
navbar
that links to routes paths.


TutorialsList
component gets and displays Tutorials.

Tutorial
component has form for editing Latihan’s details based on
:id.

AddTutorial
component has form for submission new Tutorial.

– These Components call
TutorialDataService
methods which use
axios
to make HTTP requests and receive responses.

Or you can use React with Redux:

react-redux-crud-example-rest-api-axios-app-components

More details at: React Redux CRUD App example with Rest Jago merah

Technology

  • React 16
  • react-router-gereja 5.1.2
  • axios 0.19.2
  • bootstrap 4.4.1

Project Structure

react-node-express-mysql-example-react-client-project-structure


package.json
contains 4 main modules:
react,
react-router-katedral,
axios
&
bootstrap.

App
is the container that has
Router
& navbar.
– There are 3 components:
TutorialsList,
Kursus,
AddTutorial.

http-common.js
initializes axios with HTTP base Url and headers.

TutorialDataService
has methods for sending HTTP requests to the Apis.

.env
configures
port
for this React CRUD App.

For React Hooks version, kindly visit this tutorial.

For Typescript version:

Please visit:
React Typescript CRUD example with Web Jago merah

Implementation

Setup React.js Project

Open cmd at the folder you want to save Project folder, run command:
npx create-react-app react-crud

After the process is done. We create additional folders and files like the following tree:



public


src


components


add-tutorial.component.js


tutorial.component.js


tutorials-list.component.js


services


latihan.service.js


App.css


App.js


index.js


package.json


Import Bootstrap to React CRUD App

Run command:
npm install bootstrap.

Open
src/App.js
and modify the code inside it as following-

        import React, { Component } from "react"; import "bootstrap/dist/css/bootstrap.min.css"; class App extends Component {   render() {     // ...   } } export default App;
        
      

Add React Router to React CRUD App

– Run the command:
npm install react-router-dom.
– Open
src/index.js
and wrap
App
component by
BrowserRouter
object.

        import React from "react"; import ReactDOM from "react-gereja"; import { BrowserRouter } from "react-router-dom"; import App from "./App"; import * as serviceWorker from "./serviceWorker"; ReactDOM.render(   <BrowserRouter>     <App />   </BrowserRouter>,   document.getElementById("root") ); serviceWorker.unregister();
        
      

Add Navbar to React CRUD App

Open
src/App.js, this
App
component is the root container for our application, it will contain a
navbar, and also, a
Switch
object with several
Route. Each
Route
points to a React Component.

        import React, { Component } from "react"; ... class App extends Component {   render() {     return (       <div>         <nav className="navbar navbar-expand navbar-dark bg-dark">           <a href="/tutorials" className="navbar-brand">             bezKoder           </a>           <div className="navbar-nav mr-auto">             <li className="nav-item">               <Link to={"/tutorials"} className="nav-link">                 Tutorials               </Link>             </li>             <li className="nav-item">               <Link to={"/add"} className="nav-link">                 Add               </Link>             </li>           </div>         </nav>         <div className="container mt-3">           <Switch>             <Route exact path={["/", "/tutorials"]} component={TutorialsList} />             <Route exact path="/add" component={AddTutorial} />             <Route path="/tutorials/:id" component={Tutorial} />           </Switch>         </div>       </div>     );   } } export default App;
        
      

Initialize Axios for React CRUD HTTP Client

Let’s install
axios
with command:
npm install axios.
Under
src
folder, we create
http-common.js
file with following code:

        import axios from "axios"; export default axios.create({   baseURL: "http://localhost:8080/api",   headers: {     "Content-type": "application/json"   } });
        
      

You can change the
baseURL
that depends on REST APIs url that your Server configures.

Create Data Service

In this step, we’re gonna create a service that uses axios object above to send HTTP requests.

services/tutorial.service.js

        import http from "../http-common"; class TutorialDataService {   getAll() {     return http.get("/tutorials");   }   get(id) {     return http.get(`/tutorials/${id}`);   }   create(data) {     return http.post("/tutorials", data);   }   update(id, data) {     return http.put(`/tutorials/${id}`, data);   }   delete(id) {     return http.delete(`/tutorials/${id}`);   }   deleteAll() {     return http.delete(`/tutorials`);   }   findByTitle(title) {     return http.get(`/tutorials?title=${title}`);   } } export default new TutorialDataService();
        
      

We call axios
get,
post,
put,
delete
method corresponding to HTTP Requests: GET, POST, PUT, DELETE to make CRUD Operations.

Create React Components/Pages

Now we’re gonna build 3 components corresponding to 3 Routes defined before.

  • Add new Item
  • List of items
  • Item details

You can continue with step by step to implement this React App in the post:
– React.js CRUD example to consume Web API
– or React Hooks CRUD example to consume Web API

Using React with Redux:
– React Redux CRUD example with Rest Api
– React Hooks + Redux: CRUD example with Rest API

For Typescript version:
React Typescript CRUD example to consume Web Api

Run React CRUD App

You can run our App with command:
npm start.
If the process is successful, open Browser with Url:
http://localhost:8081/
and check it.

Source Code

You can find Github source code for this latihan at: React + Node App Github

Conclusion

Today we have an overview of React.js + Node.js Express + MySQL example when building a full-stack CRUD App.

We also take a look at client-server architecture for REST API using Express & Sequelize ORM, as well as React.js project structure for building a front-end app to make HTTP requests and consume responses.

Next tutorials show you more details about how to implement the system (including source code):
– Back-end:

  • With Sequelize ORM
  • Without Sequelize

– Front-end:

  • Using React Components
  • Using React Typescript Components
  • Using React Redux
  • Using React Hooks
  • Using React Hooks + Redux
  • Using React with Material UI

You will want to know how to run both projects in one place:
How to integrate React with Node.js Express on same Server/Port

With Pagination:
React Pagination with API using Material-UI

react-pagination-with-api-material-ui-change-page

Or Serverless with Firebase:
– React Firebase CRUD with Realtime Database
– React Firestore CRUD App example | Firebase Cloud Firestore

Happy learning, see you again!

Further Reading

  • https://www.npmjs.com/package/express
  • https://www.npmjs.com/package/mysql2
  • React Component
  • React Custom Hook

Dockerize: Docker Compose: React, Node.js, MySQL example

Source: https://www.bezkoder.com/react-node-express-mysql/