c:\> npx express --view=ejs crud_api

c:\crud_api> npm init -y

c:\crud_api> npm install

c:\crud_api> npm install express --save

c:\crud_api> npm install body-parser --save

c:\crud_api> npm install mysql --save

c:\crud_api> npm install --save-dev nodemon

CREATE TABLE IF NOT EXISTS users (
id int(11) NOT NULL AUTO_INCREMENT,
primary key(id),
name varchar(128) NOT NULL,
email varchar(128) NOT NULL,
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (id, name, email, created_at) VALUES
(1, 'Nusrat', 'nb@g.co', '2019-02-28 13:20:20'),
(2, 'sana', 'sana@g.co', '2019-02-28 13:20:20'),
(3, 'mahira', 'mahira@g.co', '2019-02-28 13:20:20'),
(4, 'sara', 'sara@g.co', '2019-02-28 13:20:20'),
(5, 'arham', 'arham@g.co', '2019-02-28 13:20:20');

const mysql = require('mysql');
const dbConn = mysql.createConnection({
host : 'localhost',
user : 'root',
password : 'root',
database : 'nodedb'
});
dbConn.connect(function(err) {
if (err) throw err;
console.log("Database Connected!");
});
module.exports = dbConn;

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;