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
Let’s follow the following steps to create crud rest APIs using node js express and 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, 'neveropen', '[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
| Method | Url | Action |
| GET | /users | fetch all users |
| GET | user/1 | fetch user with id ==1 |
| POST | user | add new user |
| PUT | user | update user by id == 1 |
| DELETE | user | delete 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 .
