NodeJS Express REST API CRUD MySQL Example

5 Comments on NodeJS Express REST API CRUD MySQL Example

In this tutorial, you will learn how to implement a RESTful CRUD (Create, Retrieve, Update, Delete) API with Node.js, Express, and MySQL database.

How to Build CRUD Rest API With NodeJS Express + MySQL

Steps to create crud rest APIs:

Step 1 – Create Node JS Express App

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

Step 2 – Create Database and table For this App

Next you 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 the database using the SQL query:

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');
Recommended:-Node JS Express Crud with Mysql Example

Step 3 – Connect App to database

In this step, you 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();

Step 4 – Create Rest Apis and Add in server.js

Next, you need to create server.js file inside of node-rest-crud-api directory and add the following code into it:

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;

Explanation of restful API methods:

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
Recommended:-Node JS Facebook Login Example

Create users list api

This method 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

This method get 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

This method 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

This method 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

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

Step 5 – Start Development Server

Execute the following command on terminal to run development 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

Conclusion

Building 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

Recommended:-How To Install Node Js In Windows 10 with Images
Recommended:-Node js Read File Example
Recommended:-Node js Express Upload File/Image Example
Recommended:-Node js Send Email Through Gmail with Attachment Example
Recommended:-Node Js Google ReCaptcha v3 Example
Recommended:-Node.js Express Login Example with MySQL
Recommended:-Create Registration and Login Form in Node.js MySQL
Recommended:-Node JS Resize Image Before Upload using Multer Sharp Example
Recommended:-Node JS Google Authentication with Passport Example
Recommended:-Node JS Express Socket IO Chat Application Example
Recommended:-Node JS LinkedIn Login using Passport Example
Recommended:-Node.js Web Scraping Example
Recommended:-Node js Express Get Client IP Address Tutorial
Recommended:-VUE JS CRUD + Node.js + Express + MySQL Example
Recommended:-React JS + Node JS File Upload Tutorial with Example
Recommended:-Angular 12/11 CRUD Application with NodeJS and Express REST API
Recommended:-Angular 12 CRUD + Node.js + Express + MySQL Tutorial

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

Filed Under: Node js, REST API Tagged With: , , , , , , ,

AuthorDevendra Dode

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including PHP, Python, JavaScript, jQuery, Laravel, Livewire, CodeIgniter, Node.js, Express.js, Vue.js, Angular.js, React.js, MySQL, MongoDB, REST APIs, Windows, XAMPP, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL, and Bootstrap. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

5 replies to NodeJS Express REST API CRUD MySQL Example

  1. Super genials your tutorial,

    Thanks you so much.

    Reply

  2. thank you very much

    Reply

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

    Reply

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

    Reply

  5. thanks , nice

    Reply

Leave a Reply

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