Node js Express + MySQL + CRUD REST APIs Example

Building a restful CRUD api with Node js express and mysql example; In this tutorial i am going to show you how to you create crud restful apis usin node.js express and MySQL database.

A REST API (also known as RESTful API) is an application programming interface (API or web API) that conforms to the constraints of REST architectural style and allows for interaction with RESTful web services. REST stands for representational state transfer and was created by computer scientist Roy Fielding.

Create CRUD REST Apis using Node.js Express and MySQL

  • Step 1 – Create Node JS Express + MySQL App
  • Step 2 – Install Required Node Module
  • Step 3 – Create Database and Table For this App
  • Step 4 – Connect App to database
  • Step 5 – Create App.js File and CRUD Rest Apis
  • Step 6 – Start App Server
  • Step 7 – Test CRUD Rest Apis

Step 1 – Create Node JS Express + MySQL App

Execute the following command on command prompt to create new project directory:

 mkdir node-rest-crud-api
cd node-rest-crud-api

Then Execute the following command on cmd or command prompt to create your node js express app with name:

npm init --yes

npm install

Step 2 – Install Required Node Module

Install required node module into your app; so execute the following command on terminal to install into it:

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

Step 3 – Create Database and table For this App

Create database and table to build a restful crud api with node.js express and MySQL; So execute the following query into your mysql admin panel:

CREATE DATABASE demo;

-- 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');

Step 4 – Connect App to database

Connect your restful crud api with node.js express and MySQL app to database; So visit your app root directory and create a new file name database.js and add the following code into it:

// connection configurations
var mysql = require('mysql');
 var dbConn = mysql.createConnection({
     host: 'localhost',
     user: 'root',
     password: '',
     database: 'node_js_api'
 });
 // connect to database
 dbConn.connect(); 

Step 5 – Create App.js File and CRUD Rest Apis

Create app.js file; so visit your app root directory and create app.js file. Then import above install node module into it. And as well as create routes for execute crud operation using rest apis with node.js express and MySQL; As shown below:

var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var dbConn = require('./database');
 
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;

Step 6 – Start App Server

Execute the following command on terminal to start your restful crud api with node.js express and MySQL 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

Step 7 – Test CRUD Rest Apis

Get All users list rest api

API URL :-  http://127.0.0.1:3000/users

Method :- GET

Fetch Single User Detail REST API

API URL :-  http://127.0.0.1/user/1 // 1 is user id

Method :- GET

Create New User REST API

API URL :- http://127.0.0.1:3000/add

Method :- POST

Note:- Send Form data into application/x-www-form-urlencoded formate

Update User REST API

API URL :- http://127.0.0.1/user/{id}

Method :- PUT

Note:- Send Form data into application/x-www-form-urlencoded formate

DELETE User REST API

API URL :- http://127.0.0.1/user/{id}

Method :- DELETE

Note:- Send Form data into application/x-www-form-urlencoded formate

Conclusion

Create a restful crud api with node.js express and mysql tutorial’ You have successfully learned and implement restful crud apis with node express js + MySQL database.

Recommended Node JS Tutorials

4 Comments

  1. ufuk said:

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

    August 28, 2021
    Reply
  2. johns said:

    thank you very much

    August 28, 2021
    Reply
  3. Oholiab 225 said:

    Super genials your tutorial,

    Thanks you so much.

    August 28, 2021
    Reply
  4. Musembya Allan said:

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

    August 28, 2021
    Reply

Leave a Reply

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