Creating RESTful API in Node js express MySQL. Here you will learn how to create crud API 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', 'test@g.co', '2019-02-28 13:20:20'),
  (2, 'john', 'john@g.co', '2019-02-28 13:20:20'),
  (3, 'tutsmake', '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');

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

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.

//  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.' });
 });
 });