Create Restful CRUD API with Node js MySQL and express – onlinecode

Create Restful CRUD API with Node js MySQL and express – onlinecode

In this post we will give you information about Create Restful CRUD API with Node js MySQL and express – onlinecode. Hear we will give you detail about Create Restful CRUD API with Node js MySQL and express – onlinecodeAnd how to use it also give you demo for it if it is necessary.

In this article, we will show how to create restful CRUD API with node js MySQL and express. here we will create a simple restful API using node js. so we can easily manage the HTTP request (such as GET, POST, PUT, DELETE, etc.).

so you can follow the below steps for create restful restful crud api with node.js express and mysql.

Overview

Step 1: Create the Application Directory.

Step 2: Create an application using npm init

Step 3: Install helpful Packages

Step 4: Create database and table using MySql

Step 5: Setting Database Configuration

Step 6: Create an app.js file

Step 7: Run Application

Building a Restful crud API with node.js

Step 1: Create an Application Directory

First, we will open the command prompt and create the application directory in our directory. for this, you can follow the below command.

PHP
mkdir nodejs_api_crud_mysql
cd nodejs_api_crud_mysql

Step 2: Create an application using npm init

The “npm init” command through we can create a new package.json file in our application directory. when we build a new app at that time we use this command. Node.js will need to have a package.json file because includes the application related packages and application data.

Here below command helps to initialization our application.

PHP
npm init

This utility will walk you through creating a package.json file.
It only covers the most common items and tries to guess sensible defaults.

See 'npm help json' for definitive documentation on these fields
and exactly what they do.

Use 'npm install ' afterwards to install a package and
save it as a dependency in the package.json file.

Press ^C at any time to quit.
package name: (myapp) node_crud_mysql_ejs
version: (1.0.0)
description: node_crud_mysql_ejs
entry point: (index.js)
test command:
git repository:
keywords:
author:
license: (ISC)
About to write to E:test_macmyapppackage.json:

{
  "name": "node_crud_mysql_ejs",
  "version": "1.0.0",
  "description": "nodejs_api_crud_mysql",
  "main": "index.js",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1"
  },
  "author": "",
  "license": "ISC"
}

Is this OK? (yes) yes

Step 3: Install helpful Packages

Expess.js is a free and open-source framework and it’s a web application framework for Node.js. we can manage routes, create restful API, handle the request and response, and also create MVC structure using the express.js. Npm stands for the node package manager. npm, help to install node packages and manage the types of dependencies.Here below command help to install express package.

PHP
npm install express -save

This body-parser module parses JSON request and that allows you to access req.body from within your routes.

PHP
npm install body-parser -save

below following command using install the MySQL module

PHP
npm install mysql -save

The node-datetime npm package is used for creating date and time.

npm install node-datetime

Step 4: Create database and table using MySql

Create MySQL table

we can use the below command to create a database, otherwise, we can directly create a database on PHPMyAdmin.

PHP
create database nodejs_api_crud_mysql;
use nodejs_api_crud_mysql;
 
CREATE TABLE students (
id int(11) NOT NULL auto_increment,
first_name varchar(100) NOT NULL,
last_name varchar(100) NOT NULL,
email varchar(100) NOT NULL,
mobile varchar(100) NOT NULL,
created_at datetime NOT NULL,
PRIMARY KEY (id)
);

Step 5: Setting Database Configuration

Node js database connection with MySQL

After complete the installation of packages. we will database configuration. now we will create config.js in the application directory and paste the below code in this file.

PHP
var mysql = require('mysql');
const dbConn = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'nodejs_api_crud_mysql'
});
dbConn.connect(function(err) {
  if (err) throw err;
  console.log("Database Connected!");
});
module.exports = dbConn;

Step 6: Create a server.js file

Now, we will take a new file and paste follow below code in this file and save as server.js.

node js server setup

var express = require('express');
var app = express();
var dbConn = require('./config'); 
var bodyParser = require('body-parser');
var dateTime = require('node-datetime');
var dt = dateTime.create();
var curdate = dt.format('Y-m-d H:M:S');

app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
 
app.get('/', function (req, res) {
    console.log('hello world');
});
 
app.listen(3000, function(){
    console.log('Server running at port 3000: ');
});

Rest API Post Example

// Create the student data
app.post('/student', function (req, res) {
  
  var student = {
		first_name: req.body.first_name,
		last_name: req.body.last_name,
		email: req.body.email,
		mobile: req.body.mobile,
		created_at: curdate
	}
  
    if (!student) {
        return res.status(400).send({ status:false, message: 'Please Provide Student Data' });
    }
  
    dbConn.query("INSERT INTO students SET ? ", student, function (err, results) {
		if(err) {
			throw err;
		} else {
			return res.send({ status: true, data: results, message: 'New student has been created successfully.' });
		}
    });
});

Rest API Get Example

// Retrieve all data
app.get('/students', function (req, res) {
    dbConn.query('SELECT * FROM students', function (err, results) {
		if(err) {
			throw err;
		} else {
			return res.send({ status: true, data: results});
		}
    });
});

Rest API Get Example

// Retrieve single data 
app.get('/student/:id', function (req, res) {
  
    let student_id = req.params.id;
  
    if(!student_id) {
        return res.status(400).send({ status: false, message: 'Please provide student id' });
    }
  
    dbConn.query('SELECT * FROM students where id=?', student_id, function (err, result) {
		if(err) {
			throw err;
		} else {
			return res.send({ status: true, data: result[0]});
		}
    });
  
});

Rest API Put Example

app.put('/student', function (req, res) {
  
    var student_id = req.body.id;
    var student = {
		first_name: req.body.first_name,
		last_name: req.body.last_name,
		email: req.body.email,
		mobile: req.body.mobile,
		created_at: curdate
	}
  
    if (!student_id || !student) {
        return res.status(400).send({ status: false, message: 'Please provide student data and student id' });
    }
  
    dbConn.query("UPDATE students SET ? WHERE id = ?", [student, student_id], function (err, results) {
		if(err) {
			throw err;
		} else {
			return res.send({ status: true, data: results, message: 'Student has been updated successfully.' });
		}
    });
});

Rest API Delete Example

app.delete('/student', function (req, res) {
  
    let student_id = req.body.id;
  
    if (!student_id) {
        return res.status(400).send({ status: false, message: 'Please provide student id' });
    }
    dbConn.query('DELETE FROM students WHERE id = ?', [student_id], function (err, results) {
		if(err) {
			throw err;
		} else {
			 return res.send({ status: true, data: results, message: 'Student has been deleted successfully.' });
		}       
    });
}); 

Step 7: Run Application

Now, you can run your application using the following command.

node server.js

So, you can following URL


If you liked this article, you can also like and share it and download it through download url.

Please follow and like us:

Hope this code and post will helped you for implement Create Restful CRUD API with Node js MySQL and express – onlinecode. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs

For More Info See :: laravel And github

Leave a Comment

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

  +  3  =  9

We're accepting well-written guest posts and this is a great opportunity to collaborate : Contact US