How to REST API CRUD Using NodeJS Express MySQL?

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

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', 'test@g.co', '2019-02-28 13:20:20'),
    (2, 'john', 'john@g.co', '2019-02-28 13:20:20'),
    (3, 'nicenippets', 'tuts@g.co', '2019-02-28 13:20:20'),
    (4, 'tut', 'tut@g.co', '2019-02-28 13:20:20'),
    (5, 'mhd', 'mhd@g.co', '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

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

I hope it can help you...