MySQL CRUD Operations using Express JS

In this article, we will use Express JS, PHP MyAdmin, React JS to add CRUD operations in a React-Form.

Since I will be using a custom Form UI, which I created using Bootstrap, so I would advise you to go through the following (if not gone though already):

1. Create React Form UI
2. Adding Backend Fuctionality to Form UI

Express JS

Express.js, or simply Express, is a Node.js back-end web application framework that is free and open-source software licensed under the MIT License. It is intended for the development of web applications and APIs.

Express, together with the MongoDB database software and a JavaScript front-end framework or library, is the back-end component of popular development stacks such as the MEAN, MERN, or MEVN stack.

You can install Express using

npm install --save express

SQL

Structured Query Language (SQL) is a programming language developed for managing data in a relational database management system (RDBMS) or stream processing in a relational data stream management system (RDSMS). It is especially beneficial when dealing with structured data, which includes relationships between entities and variables.

SQL is a data query language (DQL), a data definition language (DDL), a data control language (DCL), and a data manipulation language (DML). SQL was originally based on relational algebra and tuple relational calculus, and it comprises many different sorts of statements.

SQL’s scope includes data querying, data manipulation (insert, update, and delete), data definition (creation and updating of schemas), and data access control.

MySQL

MySQL is an open-source relational database management system (RDBMS). Its name is a mix of “My,” the name of co-founder Michael Widenius’s daughter, and “SQL,” the acronym for Structured Query Language.

In addition to relational databases and SQL, an RDBMS such as MySQL works with an operating system to implement a relational database in a computer’s storage system, manages users, provides network access and simplifies checking database integrity and backup creation.

MySQL is used by numerous database-driven online applications, including Drupal, Joomla, phpBB, and WordPress.

You can install MySQL using

npm install --save mysql

phpMyAdmin

phpMyAdmin is a free PHP software utility designed to manage MySQL administration via the Internet. phpMyAdmin offers a wide variety of MySQL and MariaDB operations. Frequently used activities (managing databases, tables, columns, relations, indexes, users, permissions, and so on) may be conducted using the user interface, yet any SQL expression can still be executed directly.

Cross-Origin Resource Sharing (CORS)

Cross-Origin Resource Sharing (CORS) is an HTTP-header-based method that allows a server to specify any origin (domain, scheme, or port) other than its own from which a browser should allow resources to be loaded. CORS also makes use of a method in which browsers send a “preflight” request to the server hosting the cross-origin resource to ensure that the server will allow the real request. The browser transmits headers indicating the HTTP method as well as headers that will be used in the actual request during that preflight.

You can install CORS using

npm install --save cors

AXIOS

Axios is a Javascript library that implements the Promise API that is native to JS ES6 and is used to make HTTP requests using node.js or XMLHttpRequests from the browser. It allows for client-side protection against XSRF(Cross-site request forgery) by intercepting HTTP requests and answers. It can also cancel requests if necessary.

You can install AXIOS using

npm install --save axios

Creating Node Project

We need to set up another project, which will work as the server. We cannot add the MySQL code in the same (React Form UI) project.

We will create a simple node project since we don’t need extra files.

Run the following command to create a new node project

mkdir server
cd server
npm init -y

We’ll use the body-parser module. Body-parser is the middleware for body parsing in Node.js. It is in charge of processing incoming request bodies in a middleware before you treat them.

Use the following to install body-parser

npm install --save body-parser

index.js

Create a new file named ‘index.js’.

We will include all the required file module

const mysql = require('mysql');
const express = require('express');
const cors=require("cors");
const bodyparser = require('body-parser');

Creating Database Connection

const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
});

We use createConnection() method from mysql module to create a database connection.

• host – address where the database reside. In our case it will be ‘localhost’
• user – name of the account used to access database. In our case it will be ‘root’, which has all the read, write and execute rights.
• password – password of the user, if set. In our case we dont have any password.
• database – name of the database that needs to be accessed.

db.connect((err) => {
    if (err) {
        throw err;
    }
    console.log('MySql Connected...');
});

After setting up the connection requirements, we will can call connect().

• err is the error that we get during the process of establishing connection.
• If we get an error ‘err’, we will throw it, to be shown. I have not done error handling. I suggest you to do error handling.
• If the connection is established without any error, we will log “MySQL Connected…”

Setting up Express.JS Server

const app = express();

we use express() constructor method to initialize the express js server.

app.use(bodyparser.json());

I have added bodyparser.json() so as to get the results in a JSON format.

const corsOptions ={
   origin:'*', 
   credentials:true,            //access-control-allow-credentials:true
   optionSuccessStatus:200,
}

app.use(cors(corsOptions)) // Use this after the variable declaration

Now, we set up the CORS, so that we are not affected by cross-domain errors.

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

After setting up everything, we will configure the express server to listen at port 5000 (You can use any of the ports you want).

CRUD Operations

Create Database

app.get('/createdb', (req, res) => {
    let sql = 'CREATE DATABASE IF NOT EXISTS nodejs';
    db.query(sql, (err, result) => {
        if (err) throw err;
        console.log(result);
        res.send('Database created...');
    });
});

Create Table

app.get('/createtable', (req, res) => {
    let sql = 'CREATE TABLE IF NOT EXISTS OrderDetails(id int AUTO_INCREMENT, Name VARCHAR(255), Mobile INT(10), Email VARCHAR(255), Menu VARCHAR(255), Extra VARCHAR(255), PRIMARY KEY(id))';
    db.query(sql, (err, result) => {
        if (err) throw err;
        console.log(result);
        res.send('table created...');
    });
});

Read Data

app.get('/getall', (req, res) => {
    let sql = 'SELECT * FROM OrderDetails';
    db.query(sql, (err, result) => {
        if (err) throw err;
        console.log(result);
        res.send(result);
    });
});

Insert Data

app.post('/insert', (req, res) => {
    console.log(req.body);
    let form = req.body;
    let sql = `INSERT INTO OrderDetails(Name, Mobile, Email, Menu, Extra) VALUES ('${form.name}', '${form.mobile}', '${form.email}', '${form.dropdown}', '${form.order}')`;
    db.query(sql, (err, result) => {
        if (err) throw err;
        console.log(result);
        res.send('Post added...');
    });
});

React Form UI changes

We are done setting up for the requirements, now we need to make calls from our Form.

To make calls we will use Axios. Install it, if not installed already.

Add the following code, outside your App()

import axios from 'axios'

const api = axios.create({
  baseURL: 'http://localhost:5000'
})

In the above code, we have imported Axios. After we initialized Axios to listen to localhost:5000.

Add the following code inside submitButton()

const db_request = await api.get('/createdb')
console.log(db_request)

We call the localhost:5000/createdb to check if the database exists, and if it does not exist it will create one. We have used ‘await’, as we want to wait until the query returns.

After that, we call

const table_request = await api.get('/createtable')
console.log(table_request)

const response = await api.post('/insert', request)
console.log(response)

• /createtable to check avaialblabilit of table, f it does not exists it will create one.
• /insert to add data in to the database.

Task

Its a task for you to write the code for

1. Showing specific data
2. Delete all the data
3. Delete specific data
4. Update a specific column

I have attached the code for both the server as well as the react form. You can download it from here:

Conclusion

Thanks for reading, hope you got answers to some of your questions and learned how to add CRUD operations in React Form UI.

Visit Let’s React to learn more about React.