Node JS Express MYSQL CRUD Example (2024) | TechGeekNext


Node JS Express MYSQL CRUD Example (2024)

In this article, we'll demonstrate how to create a Node.js Express MYSQL CRUD example by using routes and create below RESTful API endpoints for POST, GET, PUT, DELETE.

Node JS Express MYSQL POST, GET, PUT, DELETE Rest Api's

Node JS Express MYSQL Stack
  1. POST - Create Employee Record
  2. GET - List all employees
  3. GET - Get employees by it's id
  4. PUT - Update/Edit selected employee details
  5. DELETE - Remove selected employee record
  6. DELETE - RemoveAll employees.

Refer node js installation steps from Node.js Installation and NPM Tutorial

Create Express Crud Project

Create Express application skeleton instantly by using the express-generator application generator tool using below commands: 

npm install -g express-generator
express
Express Crud Example

Now install dependencies. 

npm install

Install cors dependency

Install cor package available in Node.js NPM registry. It would be useful if some other application wants to fetch any data from our RESTful API endpoints like from front end app. 

npm i cors

Install MySql dependency

npm install mysql

Create MYSQL Database Schema and table

Execute below SQL Script to create MYSQL database schema and table required for this project. 

create database employeetestdb;

use employeetestdb;
create table employees(
id BIGINT(20) primary key auto_increment,
name VARCHAR(255),
role VARCHAR(255)
);

Project Structure

According to the project's use case or requirements, we can create files and folders. Express Crud Example

Checkout our related posts :
Angular 9 features
Angular Spring Boot Example
Spring Boot - JWT Angular Example
RxJS Interview Questions
RxJS Observable
RxJS Higher-Order Observable Mapping
RxJS Tutorial
Typescript Collections

Add MySql connection details

Create the new folder config and new file database-config.js. 

const mysql = require('mysql')
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'employeetestdb'
});

connection.connect((err)=>{
  if (err) {
    console.log(err);
  }else{
    console.log("Database connected!");
  }
})
module.exports = connection;

Test MySql Connection

Start the application by using the npm start command after providing the MySql connection information. If everything goes smoothly, carry on with the rest endpoints; however, if the following exception occurs, follow the steps from Error: ER_NOT_SUPPORTED_AUTH_MODE and make the required changes. 

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at Handshake.Sequence._packetToError (D:\express-crud-mysql\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Handshake.ErrorPacket (D:\express-crud-mysql\node_modules\mysql\lib\protocol\sequences\Handshake.js:123:18)
    at Protocol._parsePacket (D:\express-crud-mysql\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (D:\express-crud-mysql\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (D:\express-crud-mysql\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (D:\express-crud-mysql\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (D:\express-crud-mysql\node_modules\mysql\lib\Connection.js:88:28)
    at Socket.<anonymous> (D:\express-crud-mysql\node_modules\mysql\lib\Connection.js:526:10)
    at Socket.emit (node:events:527:28)
    at addChunk (node:internal/streams/readable:315:12)

As explained above in Error: ER_NOT_SUPPORTED_AUTH_MODE will use mysql2 dependency.

Express Crud Dependencies

In Express, all of the necessary dependencies are listed in the package.json file. It contains scripts to start, build, test and eject the Express JS application. 

{
  "name": "express-crud-mysql",
  "version": "0.0.0",
  "private": true,
  "scripts": {
    "start": "node ./bin/www"
  },
  "dependencies": {
    "body-parser": "^1.18.3",
    "cookie-parser": "~1.4.4",
    "cors": "^2.8.5",
    "debug": "~2.6.9",
    "express": "~4.16.1",
    "http-errors": "~1.6.3",
    "jade": "~1.11.0",
    "morgan": "~1.9.1",
    "mysql2": "^2.3.3"
  }
}

Change Default Node js Port as per requirement

From the generated bin/www file, change the default port from 3000 to 8080.

Express MYSQL CRUD App - App component

  1. The App component (App.js) is the application's root container.
  2. Default Code base with error handling will be generated by the express application generator tool.
  3. Change the code for usersRouter as given below: 
var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
const cors = require('cors');


var indexRouter = require('./routes/index');
var usersRouter = require('./routes/employee');

var app = express();

app.disable('etag');

app.use(cors({
  origin: '*'
}));

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/', usersRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;

Express MYSQL CRUD Example - Create Route rest endpoints

  1. Create the page employee.js under routes folder.
  2. Create CRUD rest endpoints.
var express = require('express');
const connection = require('../config/database-config');
var router = express.Router();

/* GET all employees listing. */
router.get('/employees', function(req, resp, next) {

  connection.query('SELECT * FROM employees', (err, res) => {
    if (err) return next(err);

    resp.json(res);
  });

});

/* Get employee based on id*/
router.get('/employee/:id', (req, resp, next) => {
  connection.query('SELECT * FROM employees WHERE id = ?', [req.params.id], (err, res) => {
    if (err) return next(err);

    resp.json(
      {
        id:res[0].id,
        name: res[0].name,
        role: res[0].role
      }
    );
  });
});

/* Add new employee*/
router.post('/employee', (req, resp, next) => {
  const { name, role } = req.body;

  connection.query(
    'INSERT INTO employees(name, role) VALUES(?, ?)',
    [name, role],
    (err, res) => {
      if (err) return next(err);

      resp.json(res);
    }
  );
});

/* Edit existing employee based on id*/
router.put('/employee/:id', (req, resp, next) => {
  const employees = ['name', 'role'];
  const records = [];

  employees.forEach(emp => {
    if (req.body[emp]) records.push(emp);
  });

  records.forEach((rec, index) => {
    connection.query(
      `UPDATE employees SET ${rec}=? WHERE id=?`,
      [req.body[rec], req.params.id],
      (err, res) => {
        if (err) return next(err);

        if (index === records.length - 1)
        resp.json(res);
      }
    )
  });
});

/* Delete employee based on id*/
router.delete('/employee/:id', (req, resp, next) => {

  connection.query('DELETE FROM employees WHERE id=?', [req.params.id], (err, res) => {
    if (err) return next(err);

    resp.json(res);
  });
});

/* Delete all employees*/
router.delete('/employees', (req, resp, next) => {

  connection.query('DELETE FROM employees ', (err, res) => {
    if (err) return next(err);

    resp.json(res);
  });
});
module.exports = router;

Test Express MYSQL CRUD Example

  1. Use below command to install all the dependencies required for the project. 
    npm install
  2. Run the application by using below command: 
    npm start
  3. The application will open in browser at our configured port http://localhost:8080/ url.
  4. Create New Employee
    Open Postman, use POST method with end point http://localhost:8080/employee and provide Employee details to create new employee record. Express CRUD Example - Post Output
  5. List Employees
    Use GET method with end point http://localhost:8080/employees to get all employees. Express CRUD Example - List Employee Output
  6. Get Employee
    Use GET method with end point http://localhost:8080/employee/1 to get employee by id. Express CRUD Example - Get Output
  7. Edit Employee
    Use PUT method with end point http://localhost:8080/employee/2 where 2 is the id of the employee and provide employee details as body in Postman to edit. Express CRUD Example - Put Output
  8. Delete Employee
    Use DELETE method with end point http://localhost:8080/employee/2 where 2 is the id of the employee. Express CRUD Example - Delete Output
  9. Delete All Employees
    Use DELETE method with end point http://localhost:8080/employees to delete all employees records from database.

Download Source Code

The full source code for this article can be found on below.
Download it here - Express MYSQL Crud Example







Recommendation for Top Popular Post :

Spring Boot - Session Management
Spring Boot - Transaction Management
Spring Boot - Security Tutorial
Java Lombok Tutorial
PCF Tutorial
Spring Cloud Tutorial
RxJS Tutorial
Spring Boot - JWT Example
Spring Boot Complete CRUD example
Spring Boot - RabbitMQ Example
Angular Spring Boot Example
Spring Boot JPA Rest
Java Z Garbage Collector (ZGC)
Java 8 Programming Interview Questions
Angular - RxJS Interview Questions
RxJS switchMap
CI CD DevOps Interview Questions
Top Spring Batch Interview Questions
RxJS Higher-Order Observable Mapping