Node js Express + MySQL + CRUD Rest APIs Example

Node js Express + MySQL + CRUD Rest APIs Example

Build RESTful CRUD APIs in Node js express + MySQL example; In this tutorial, you will learn how to build a restful crud APIs with node.js express + MySQL database.

CRUD stands for Create, Read, Update, and Delete

  • Create — To insert any record to the database.
  • Read — To retrieve records from the database.
  • Update — To update a record in the database.
  • Delete — To delete a record in the database

This node.js express crud restful APIs with MySQL example will show you very easy and simple way for building a restful crud APIs with node.js express + MySQL database.

How To Build CRUD Rest API With NodeJS Express + MySQL

  • Step 1 – Create Node JS Express App
  • Step 2 – Create Database and table For this App
  • Step 3 – Connect App to database
  • Step 4 – Create Rest Apis and Add in server.js
  • Step 5 – Start Development Server

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 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 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 node.js express crud mysql rest api example as shown below:

Implement following apis with methods name
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

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

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

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of 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 from a starting stage. As well as demo example.

4 replies to Node js Express + MySQL + CRUD Rest APIs Example

  1. Super genials your tutorial,

    Thanks you so much.

  2. thank you very much

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

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

Leave a Reply

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