Node Express Js Creating a RESTful Crud Api With Mysql

Node Express Js Creating a RESTful Crud Api With Mysql

Create RESTful api in Node js – Today we will learn how to create node js apis using node js framework express with mysql database.

If you want to create a RESTful API with Node Js framework Express js? Here is the rest api crud tutorial, by reading this tutorial you can create our own REST API application server.

We are going to use a very popular web node js framework called Express to create RESTful APIs using mysql database.

Let’s start create RESTful Crud Apis with Node.Js , Express, MySQL

Create Project Folder

Use the below command and create your express project with 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 terminal and use the below commands :

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

Create Database and table

Next we need to create database and table for 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, 'tutsmake', '[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');

Create server.js

Next we need to create server.js file inside of node-rest-crud-api folder and put the connection. server.js is entry point.

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

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
extended: true
}));
// default route
app.get('/', function (req, res) {
return res.send({ error: true, message: 'hello' })
});
// set port
app.listen(3000, function () {
console.log('Node app is running on port 3000');
});
module.exports = app;

Create database connection

We need to required database connection for fetching or update data into database :

// connection configurations
var dbConn = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'node_js_api'
});
// connect to database
dbConn.connect();
We will implement following apis with methods name
MethodUrlAction
GET /usersfetch all users
GET
user/1fetch user with id ==1
POSTuseradd new user
PUTuserupdate user by id == 1
DELETEuserdelete user by id == 1

Create users list api

Now we will first implement route of users list api with we will Fetch all users into 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 return all users 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

Now we will implement single user api. This api will return 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

Now We will implement add new user route so that when a user sends a post request with required data, app 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

Now We will implement update user route so that when a user sends a post request with required data, app will update 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 put request and updates record in your database. To call this API use this URL http://127.0.0.1/user/{id}

Delete User Api

We are going to create a new route so that when a user sends a delete request, the app 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.' });
});
});

Here is the complete server.js file.

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;

We have successfully implemented node js crud api using express js.

I hope you like this node js api crud post, Please feel free to comment below or your suggestion .

4 replies to Node Express Js Creating a RESTful Crud Api With Mysql

  1. Super genials your tutorial,

    Thanks you so much.

  2. thank you very much

  3. This just saved me a lot of hours to figure out how i can easily write an APi withExpress Js.

  4. could you tell how to test over postman especially add and delete methods

Leave a Reply

Your email address will not be published. Required fields are marked *