NodeJS Express MySQL REST API CRUD Example

Here I am going to show you how to create REST API CRUD example using Nodejs Express and MySQL server. CRUD means Create, Read, Update and Delete operations which are performed through REST (Representational State Transfer) resources that works on http/https protocol.

REST resources or APIs work on verbs, such as, GET, POST, PUT, DELETE, etc. So it is obvious that GET means you need to fetch data from the server endpoint, POST means you need to create the new resource on server endpoint, PUT means you need to update the existing resource on server and DELETE means you need to delete resource on server endpoint.

Though you have meaning verbs for working with REST resources but it again depends on the implementation on the server side. So for example, for fetching data from server you can use POST verb. So for creating a new resource also you can use GET verb, but remember whether you should really use request body parameter on GET endpoint.

Here I am going to use Nodejs and Express to create REST endpoints with MySQL as a persistent system for storing data.

Prerequisites

Nodejs v12.18.3, npm 6.14.6, Express 4.17.1, MySQL 8.0.17

Project Setup

Choose your project root directory, let’s say, the name of the directory is nodejs_mysql_rest_api_crud. I may not mention this project root directory name but I will assume that I will create any file or folder with respect to this project root directory.

Now execute the command npm init on your project root directory. This command will create package.json file with basic information, such, as app name, description, version, author, license, any dependencies, etc.

Next you need to execute command npm install and this command will create the file package-lock.json where you will find all your dependencies details. These dependencies are downloaded into the folder node_modules under your project root directory.

Next is to install the Express using the command npm install express in the same way you executed other two commands above.

You need to install mysql driver also to connect to your MySQL server by executing the following command.

npm install mysql

Well, you are done with the project setup. Now let’s move on to implementation part.

CRUD Resources

Now I am going to implement CRUD operations using REST resources. I will segregate the layer into different files.

For database operations I will create model file, for handling client request/response I will create controller file, for handling routes I will create route file, for creating server and server configuration I will create app file, for database configurations I will create db config file.

Database Configuration

Let’s start with database configuration. Create a file called db.js under folder config to establish the database connection. Make sure to change the database settings according to yours.

In this file I have defined the Item object then performing required CRUD operations. I am fetching record based on given id or name. I am also fetching all records from the database. The other write operations such as create/save, update and delete are also performed.

'use strict';

const mysql = require('mysql');

var con = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: 'root',
	database: 'roytuts'
});

con.connect(function(err) {
	if (err) throw err;
	console.log("Connected to MySQL!");
});

module.exports = con;

MySQL table and sample data is given below which I am going to use for this application.

CREATE TABLE IF NOT EXISTS `items` (
  `item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_name` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_desc` text COLLATE utf8mb4_unicode_ci,
  `item_price` float NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `items` (`item_id`, `item_name`, `item_desc`, `item_price`) VALUES
	(1, 'CD', 'CD is a compact disk', 100),
	(2, 'DVD', 'DVD is larger than CD in size', 150),
	(3, 'ABC', 'ABC test description', 24),
	(4, 'XYZ', 'XYZ test description', 25.32),
	(5, 'CD Player', 'CD player is used to play CD', 30.02);

Server Configuration

The entry point for the Nodejs app is index.js file which you can find in package.json file. So you can write you code in this index.js file or you can write another file. I am going to write into a file called app.js. Make sure to update your package.json file with your new file name.

The app.js file has the following code:

var mysql = require('mysql');
var express = require('express');
var bodyParser = require('body-parser');

var app = express();

var server = require('http').Server(app);

var port = process.env.PORT || 4000;

// to support JSON-encoded bodies
app.use(bodyParser.json())

// to support URL-encoded bodies
app.use(bodyParser.urlencoded({ extended: true }))

// Require item routes
const routes = require('./src/route/item.route')

// using as middleware
app.use('/item', routes)

// root path
app.get("/", (req, res, next) => {
	res.json("What's up?");
});

server.listen(port, () => {
    console.log('Listening on port: ' + port);
});

The first line requires mysql and uses the mysql variable to represent it. The second line requires express and uses the express variable to represent it. The third line requires body-parser and uses the bodyParser variable to represent it. The fourth line initializes express server and puts the initialized server into the variable app.

Nodejs has a built-in module called HTTP, which allows Nodejs to transfer data over the Hyper Text Transfer Protocol (HTTP). To include the HTTP module, use the require() method: var http = require('http');

Next if there is any port specified through environment variable PORT then use it or by default use port 4000.

Then set your app to listen to port 4000 and create a callback function that says your server is running on port 4000 or so.

I have set bodyParser to handle content types json and application/x-www-form-urlencoded.

I have defined root path for this app. I have also used route and I will let you know later about this when I will create route for this application.

Your app will now be accessible at http://localhost:4000, and hitting that endpoint get you the following output.

nodejs mysql rest crud example

Model

I will create model to perform database operations. Create a file item.model.js under model folder.

'use strict';

var con = require('./../../config/db');

// Item Object

var Item = function(item){
  this.item_id = item.item_id;
  this.item_name = item.item_name;
  this.item_desc = item.item_desc;
  this.item_price = item.item_price;
};

// Define CRUD Operations Functions

Item.findById = function (id, result) {
	let sql = 'SELECT * FROM items WHERE item_id = ?';
	
	con.query(sql, id, (err, row, fields) => {
		console.log("error: ", err);
		if (err) result(err, null);
		
		console.log(row);
		result(null, row);
	});
};

Item.findByName = function (name, result) {
	let sql = 'SELECT * FROM items WHERE item_name = ?';
	
	con.query(sql, name, (err, rows, fields) => {
		console.log("error: ", err);
		if (err) result(err, null);
		
		console.log('rows: ', rows);
		result(null, rows);
	});
};

Item.findAll = function (result) {
	let sql = 'SELECT * FROM items';
	
	con.query(sql, (err, rows, fields) => {
		console.log("error: ", err);
		if (err) result(err, null);
		
		console.log(rows);
		result(null, rows);
	});
};

Item.create = function (newItem, result) {	
	let data = [newItem.item_name, newItem.item_desc, newItem.item_price];
	
	let sql = 'INSERT INTO items(item_name, item_desc, item_price) VALUES(?, ?, ?)';
	
	con.query(sql, data, (err, row, fields) => {
		console.log("error: ", err);
		if (err) result(err, null);
		
		console.log(row.insertId);
		result(null, row.insertId);
	});
};

Item.update = function(item, result){
	let data = [item.item_name, item.item_desc, item.item_price, item.item_id];
	
	let sql = 'UPDATE items SET item_name = ?, item_desc = ?, item_price = ? WHERE item_id = ?';
	
	con.query(sql, data, (err, row, fields) => {
		console.log("error: ", err);
		if (err) result(err, null);
		
		console.log(row.affectedRows);
		result(null, row.affectedRows);
	});
};

Item.delete = function(id, result){
	let sql = 'DELETE FROM items WHERE item_id = ?';
	
	con.query(sql, id, (err, row, fields) => {
		console.log("error: ", err);
		if (err) result(err, null);
		
		console.log(row.affectedRows);
		result(null, row.affectedRows);
	});
};

module.exports= Item;

Controller

I am creating controller file item.controller.js under controller folder to handle request/response from clients.

GET resource for REST endpoint is generally used to fetch or read data from server. So here I am going to create REST endpoints for fetching data from server.

The POST endpoint is used to create new record into the server. PUT is used to update the existing resource in the server and DELETE is used to delete existing resource into the server.

The callback accepts two parameters, req is the request body and carries information about the request. The res is the response body and is used to handle response functions. For your example, .json() function is used to return json data.

'use strict';

const Item = require('../model/item.model');

exports.findById = function(req, res) {
	const id = req.params.id;
	
	if (!id) {
		// 400 = bad request
		return res.status(400).send('The required path variable id is missing')
	}
	
	Item.findById(id, function(err, item) {
		if (err) return res.status(500).send('Error occured during fetching item for id ' + id);
		console.log('item: ', item);
		
		return res.send(item);
	});
};

exports.findByName = function(req, res) {
	const name = req.body.name
	
	// 400 = bad request
	if (!name) {
		return res.status(400).send('The required field name is missing')
	}
	
	Item.findByName(name, function(err, items) {
		if (err) return res.status(500).send('Error occured during fetching item for name ' + name);
		
		console.log('items: ', items);
		
		return res.send(items);
	});
};

exports.findAll = function(req, res) {
	Item.findAll(function(err, items) {
		if (err) return res.status(500).send('Error occured during fetching items');
		console.log('items: ', items);
		
		return res.send(items);
	});
};

exports.create = function(req, res) {
	const newItem = new Item(req.body);
	
	// 400 = bad request
	if(req.body.constructor === Object && Object.keys(req.body).length === 0){
		return res.status(400).send('One or more required fields are missing');
	} if (!newItem.item_name || !newItem.item_desc || !newItem.item_price) {		
		return res.status(400).send('One or more required fields are missing')
	} else {		
		Item.create(newItem, function(err, item_id) {
			console.log('err: ', err);
			//if (err === Object) res.status(500).send('Item already exist with name ' + err.item_name);
			
			if (err || item_id <= 0) return res.status(500).send('Error occured during saving item');
			
			return res.sendStatus(200);
		});
	}
};

exports.update = function(req, res) {
	const item = new Item(req.body);
	
	// 400 = bad request
	if(req.body.constructor === Object && Object.keys(req.body).length === 0) {
		return res.status(400).send('One or more required fields are missing');
	} if (!item.item_id || !item.item_name || !item.item_desc || !item.item_price) {
		return res.status(400).send('One or more required fields are missing');
	} else {
		Item.update(item, function(err, result) {
			if (err || result <= 0) return res.status(500).send('Error occured during updating item');
			
			return res.sendStatus(200);
		});
	}
};

exports.delete = function(req, res) {
	const id = req.params.id;
	
	if (!id) {
		// 400 = bad request
		return res.status(400).send('The required path variable id is missing');
	}
	
	Item.delete(id, function(err, employee) {
		if (err) return res.status(500).send('Error occured during deleting item');
		
		return res.sendStatus(200);
	});
};

Route

The item.route.js file under route defines the endpoints for REST APIs.

const express = require('express')

const router = express.Router()

const controller =   require('../controller/item.controller');

// Retrieve a single item with id
router.get('/:id', controller.findById);

// Retrieve a single item with id
router.post('/name', controller.findByName);

// Retrieve all items
router.get('/', controller.findAll);

// Create a new item
router.post('/', controller.create);

// Update an item
router.put('/', controller.update);

// Delete an item with id
router.delete('/:id', controller.delete);

module.exports = router

API End Points

  1. GET /item: will give you all items stored in database
  2. GET /item/<id>: will give a specific item with id
  3. POST /item/name: will give you all items for the given request body
  4. POST /item : create an item for the given request body
  5. PUT /item: update an item completely for the given request body
  6. DELETE /item/<id>: delete an item

Testing REST APIs

I am using Postman tool to test my REST APIs. you can use any REST client tool or CURL command to test your REST resources. When you call the APIs in the REST client you will also see the output in the console.

The following video shows how to test using Postman tool.

Source Code

Download

Thanks for reading.

Leave a Comment