Create RESTful Api With MySQL Database, Node.js, Expressjs

Create RESTful API With MySQL Database Node.js – Today we will learn how to create node js APIs with MySQL, using node js framework express. How to create rest API in node js with MySQL.

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

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

How To Create RESTful Api With MySQL Database, Node.js, Expressjs ?
Create Project Folder

First of all, We need to new express js application Create a new express js application.

Now install MySQL driver with NPM. Go to the terminal and use the below commands :

npm install mysql --save


npm install body-parser --save
Create Database And Table

Next, we need to create a database and table to perform crud operation of node js restful API with MySQL.

-- 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, 'ajay', 'ajay@g.co', '2020-02-02 13:20:20'),
  (2, 'abhay', 'abhay@g.co', '2020-02-02 13:20:20'),
  (3, 'nirbhay', 'nirbhay@g.co', '2020-02-02 13:20:20'),
  (4, 'vijay', 'vijay@g.co', '2020-02-02 13:20:20'),
  (5, 'santosh', 'santosh@g.co', '2020-02-02 13:20:20');

Add this code to your index.js

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

 app.use(bodyParser.json());
 app.use(bodyParser.urlencoded({
     extended: true
 }));
Create Database Connection

This Step, We need to required a database connection for fetching or update data into the database see below :

var mysql      = require('mysql');

// 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

| 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 in node js

Now we will first implement the route of users list API with we will Fetch all users into MySQL 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: 'All 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 in node js

Now we will implement a single-user API. This API will return a single user record in the MySQL database.

// 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: 'Singht User List.' });
     });
 });

Call this API to use this URL http://127.0.0.1:3000/user/2.

Add User API in node js express js

Now We will implement add a new user route so that when a user sends a post method request with required data, the app will add a new user 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 requests and inserts records in your database. To call this API use this URL http://127.0.0.1:3000/user

Update User API in node js

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

Delete User API in node js

We are going to create a new route so that when a user sends a delete request in nodejs, 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 index.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: 'Welcome to Php Coding Stuff website in phpcodingstuff.com' })
});
// 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;
Conclustion

In this tutorial, we saw above how to create API using node.js express MySQL database and very easily we can create even harder API via node.js

I hope it can help you...

Node Js Express Js
Auther Image phpcodingstuff.com

Robert look

Author, Web Developer (Front-end & Back-end)

My Name is Robert look from New york, America. I'm a Web Application Developer with 6 years of experience. Currently, I'm working on front-end & back-end web development using popular web technology Languages. That's why I love to share my coding knowledge in a smart way.

Add Remove Multiple Input Fields Dynamically With Jquery - Step By Step Prev Add Remove Multiple Input Fields Dynamically With Jquery

Leave a Reply

Your privacy will not be published. Required fields are marked *

We'll share your Website Only Trusted.!!

close