Create a RESTful API With MySQL Database Node.js - Today we will learn how to create node js APIs with MySQL, using node js framework express. How to create a relaxing API in node js with MySQL.
This lesson If you want to build a RESTful API with MySQL, Node Js framework Express js? Here are some API crud tutorials in node js, by reading this tutorial you can create a REST API application server.
We will use the most popular framework node js called Express js to build RESTful APIs using the MySQL website.
Create Project
First of all, We need to new express js application Create a new express js application. Then install MySQL driver with NPM. Go to the terminal and use the below commands :
npm install mysql --save
npm install body-parser --save
Create Database And Table
Next, we need to create a database and table to perform crud operation of node js restful API with MySQL.
-- 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, 'ajay', 'ajay@g.co', '2020-02-02 13:20:20'),
(2, 'abhay', 'abhay@g.co', '2020-02-02 13:20:20'),
(3, 'nirbhay', 'nirbhay@g.co', '2020-02-02 13:20:20'),
(4, 'vijay', 'vijay@g.co', '2020-02-02 13:20:20'),
(5, 'santosh', 'santosh@g.co', '2020-02-02 13:20:20');
Create Database Connection
This Step, We need to required a database connection for fetching or update data into the database see below :
var mysql = require('mysql');
// connection configurations
var dbConn = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'node_js_api'
});
// connect to database
dbConn.connect();
We will implement following APIs with methods name
| 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 in node js
Now we will first implement the route of users list API with we will Fetch all users into MySQL 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: 'All Users List.' });
});
});
This function simply returns all user's 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 in node js
Now we will implement a single-user API. This API will return a single user record in the MySQL database.
// 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: 'Singht User List.' });
});
});
Call this API to use this URL http://127.0.0.1:3000/user/2.
Add User API in node js express js
Now We will implement add a new user route so that when a user sends a post method request with required data, the app will add a new user 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 requests and inserts records in your database. To call this API use this URL http://127.0.0.1:3000/user
Update User API in node js
Now We will implement an updated user route so that when a user sends a post request with required data, the app will update the 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:3000/user
Delete User API in node js
We are going to create a new route so that when a user sends a delete request in nodejs, the app 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.' });
});
});
Here is the complete index.js file.
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: 'Welcome to Rathorji' })
});
// 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;
I hope it can help you...