How to REST API CRUD Using NodeJS Express MySQL?

Node.js · 15-Dec-2022

Teams applying “How to REST API CRUD Using NodeJS Express MySQL?” in a production project can use salary survey to record implementation, review and debugging time, then compare that effort with tests and shipped functionality instead of treating activity as performance by itself.

Before copying the example into a live application, confirm version-specific behaviour with the official MySQL website and test it against the project’s actual database and runtime.

Hello Friends,

In this tutorial we will demonstrate how to rest API crud using nodejs express MySQL. you can see building a rest API with node.js. if you have a question about MySQL then I will give a simple example with a solution. we will help you to give examples of and express. Follow the bellow tutorial step of build node.js rest APIs with express & MySQL.

CRUD stands for Create, Read, Update, and Delete

Create — To insert any record into the database.

Read — To retrieve records from the database.

Update — To update a record in the database.

Delete — To delete a record in the database

This node.js express crud restful APIs with MySQL example will show you a very easy and straightforward way for building a restful crud APIs with node.js express + MySQL database.

Step 1: Create Node JS Express App

Use the below command and create your express project with the name expressfirst

 mkdir node-rest-crud-api
 cd node-rest-crud-api

After successfully created node-rest-crud-API folder in your system. Next follow the below commands and install node js express in your project:

npm init --yes
npm install

Now install express js framework and MySQL driver with NPM. go to the terminal and use the below commands:

 npm install express --save
 npm install mysql --save
 npm install body-parser --save

Step 2: Create Database and table For this App

Next you need to create a database and table to perform crud operation of node js restful API.

-- Table structure for users
CREATE TABLE IF NOT EXISTS users (
    id int(11) NOT NULL,
    name varchar(200) NOT NULL,
    email varchar(200) NOT NULL,
    created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE users ADD PRIMARY KEY (id);
ALTER TABLE users MODIFY id int(11) NOT NULL AUTO_INCREMENT;

Insert data into database :

INSERT INTO users (id, name, email, created_at) VALUES
    (1, 'Test', '[email protected]', '2019-02-28 13:20:20'),
    (2, 'john', '[email protected]', '2019-02-28 13:20:20'),
    (3, 'nicenippets', '[email protected]', '2019-02-28 13:20:20'),
    (4, 'tut', '[email protected]', '2019-02-28 13:20:20'),
    (5, 'mhd', '[email protected]', '2019-02-28 13:20:20');

Step 3: Connect App to database

In this step, you need to required database connection for fetching or updating data into database:

// connection configurations
var dbConn = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'node_js_api'
});
// connect to database
dbConn.connect();

Step 4: Create Rest Apis and Add in server.js

Next, you need to create server.js file inside of node-rest-crud-api directory and add the following code into it:

var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var mysql = require('mysql');
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: true
}));
// default route
app.get('/', function (req, res) {
    return res.send({ error: true, message: 'hello' })
});
// connection configurations
var dbConn = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'node_js_api'
});
// connect to database
dbConn.connect(); 
// Retrieve all users 
app.get('/users', function (req, res) {
    dbConn.query('SELECT * FROM users', function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'users list.' });
    });
});
// Retrieve user with id 
app.get('/user/:id', function (req, res) {
    let user_id = req.params.id;
    if (!user_id) {
        return res.status(400).send({ error: true, message: 'Please provide user_id' });
    }
    dbConn.query('SELECT * FROM users where id=?', user_id, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results[0], message: 'users list.' });
    });
});
// Add a new user  
app.post('/user', function (req, res) {
    let user = req.body.user;
    if (!user) {
        return res.status(400).send({ error:true, message: 'Please provide user' });
    }
    dbConn.query("INSERT INTO users SET ? ", { user: user }, function (error, results, fields) {
    if (error) throw error;
        return res.send({ error: false, data: results, message: 'New user has been created successfully.' });
    });
});
//  Update user with id
app.put('/user', function (req, res) {
    let user_id = req.body.user_id;
    let user = req.body.user;
    if (!user_id || !user) {
        return res.status(400).send({ error: user, message: 'Please provide user and user_id' });
    }
    dbConn.query("UPDATE users SET user = ? WHERE id = ?", [user, user_id], function (error, results, fields) {
    if (error) throw error;
            return res.send({ error: false, data: results, message: 'user has been updated successfully.' });
    });
});
//  Delete user
app.delete('/user', function (req, res) {
    let user_id = req.body.user_id;
    if (!user_id) {
        return res.status(400).send({ error: true, message: 'Please provide user_id' });
    }
    dbConn.query('DELETE FROM users WHERE id = ?', [user_id], function (error, results, fields) {
    if (error) throw error;
        return res.send({ error: false, data: results, message: 'User has been updated successfully.' });
    });
}); 
// set port
app.listen(3000, function () {
    console.log('Node app is running on port 3000');
});
module.exports = app;

Explanation of node.js express crud MySQL rest API example as shown below:

Implement following apis with methods name

Method	Url	Action
GET	/users	fetch all users
GET	user/1	fetch user with id ==1
POST	user	add new user
PUT	user	update user by id == 1
DELETE	user	delete user by id == 1

Create users list api

This method Fetch all users into the database:

// Retrieve all users 
app.get('/users', function (req, res) {
    dbConn.query('SELECT * FROM users', function (error, results, fields) {
    if (error) throw error;
        return res.send({ error: false, data: results, message: 'users list.' });
    });
});

This function simply returns all user's information as you can see in this query, to call this API use this URL http://127.0.0.1:3000/users.

Get Single User Api

This method gets a single user record:

// Retrieve user with id 
app.get('/user/:id', function (req, res) {
    let user_id = req.params.id;
    if (!user_id) {
        return res.status(400).send({ error: true, message: 'Please provide user_id' });
    }
    dbConn.query('SELECT * FROM users where id=?', user_id, function (error, results, fields) {
    if (error) throw error;
        return res.send({ error: false, data: results[0], message: 'users list.' });
    });
});

Call this API use this URL http://127.0.0.1/user/1.

Add User Api

This method will add a new record to the database:

// Add a new user  
app.post('/user', function (req, res) {
    let user = req.body.user;
    if (!user) {
        return res.status(400).send({ error:true, message: 'Please provide user' });
    }
    dbConn.query("INSERT INTO users SET ? ", { user: user }, function (error, results, fields) {
    if (error) throw error;
        return res.send({ error: false, data: results, message: 'New user has been created successfully.' });
    });
});

This API function accepts post request and insert record in your database. To call this API use this URL http://127.0.0.1:3000/add

Update User Api

This method will update the record to the database:

//  Update user with id
app.put('/user', function (req, res) {
    let user_id = req.body.user_id;
    let user = req.body.user;
    if (!user_id || !user) {
        return res.status(400).send({ error: user, message: 'Please provide user and user_id' });
    }
    dbConn.query("UPDATE users SET user = ? WHERE id = ?", [user, user_id], function (error, results, fields) {
    if (error) throw error;
        return res.send({ error: false, data: results, message: 'user has been updated successfully.' });
    });
});

This API accept puts request and updates record in your database. To call this API use this URL http://127.0.0.1/user/{id}

Delete User Api

This method will delete a record from the database:

//  Delete user
 app.delete('/user', function (req, res) {
    let user_id = req.body.user_id;
    if (!user_id) {
        return res.status(400).send({ error: true, message: 'Please provide user_id' });
    }
    dbConn.query('DELETE FROM users WHERE id = ?', [user_id], function (error, results, fields) {
    if (error) throw error;
        return res.send({ error: false, data: results, message: 'User has been updated successfully.' });
    });
});

Step 5: Start Development Server

Execute the following command on the terminal to run the development server:

//run the below command
npm start

After run this command open your browser and hit

http://127.0.0.1:3000
OR
http://localhost:3000