NodeJs Tutorials
- NodeJs
- Install NodeJs
- Why use NodeJs
- NodeJs Process Model
- create First Application
- Run NodeJs Application
- Node.js Console
- Node.Js Modules
- URL Modules
- node.js Callback
- Node.js Events
- Upload Files
- Upload single & Multiple Files
- NodeJs File System
- NodeJs Email
- Debugging NodeJs
- .ENV
- NodeJs Mysql
- Helpers
- encription and decription in nodeJs
- Query string
- Date & Time
- Express Js
- Template Engine with Express
- MVC Pattern in Node.Js
- list of NPM Module
- Middleware
- Body Parser
- Render
- Nodemon module
- Morgan module
- Flash Message in ExpressJs
- Session
- Session store in database
- Cookies
- Helmet
- Multer
- Router: How To Use Routing In Node.Js
- App.Js
- express.json() and express.urlencoded()
- REST APIs in NodeJs
- Gloabal Objects
- Submit Form Data
- How to get Post Data in Node.js
- How to Get URL Parameters in Node.js
- How to create Node Project
- How to Insert Form Data Into the MySql Table Using Node.js
- How to fetch Data from MySQL database table using Node.js
- CRUD Example
- Await and Async
- Promises
- Login Example
- Password Encription
- How to validate Form (Form Validation) in Node.Js?
- Registration & Login form usingn Node.Js & MySql?
- Forgot & Reset Password
- File Upload in Node.Js with ExpressJs
- Resize Image Before Upload using Multer Sharp
- Upload multiple file using node.js with multer module
- Upload file using node.js with multer module
- Client IP Address
- REST API Downloading File in NodeJs
- Export and Download CSV From MySQL Database
- CRUD REST API
- CRUD REST API Example 2
- Enable HTTPS using Node
- How to send EMAIL using NodeJs?
- Dynamic dependent dropdown using NodeJs?
- Autocomplete Search
- Get and Send Data From Ajax Request
- Get and Post Data using Ajax
- Passport Authentication
- Node Js Data type
- Node Js Error
- Node Js Array Function
- Node Js String Function
- Puppeter Module
CRUD in NodeJs with mySql?
In this tutorial, we will learn how to build crud application (create, read, update, delete) in node js using express js framework with MySQL database.
You will follow some steps to develiop CRUD Application in node.Js
File Name :
Step 1 - Install Node.JS
Step 2 – Create Node.Js App (Project folder)
Step 3 – Install flash,validator,session ,override MySQL Libraries
Step 4 – Connect to Node js Express CRUD App
Step 5 – Create Server.js File
Step 6 – Create CRUD Routes
Step 7 – Create views
Step 8 – Run Node.Js Application
Step 1 :- Create Node JS Express App
File Name :
c:\> npx express --view=ejs crud_app
this command create a successfully Express Project structure crud_app directory (folder).
Step 2 :- npm install
File Name :
c:\> cd crud_app
c:\crud_app> npm install
npm install command successfully installed node_modeles folder(node library) in your project directory (crud_app).
Step 3 – Install session, flash, validator, override and MySQL Libraries
File Name :
c:\crud_app> npm install express-flash --save
c:\crud_app> npm install express-session --save
c:\crud_app> npm install express-validator --save
c:\crud_app> npm install method-override --save
c:\crud_app> npm install mysql --save
While using version 6, you will encounter the following error:
TypeError: expressValidator is not a function
To avoid this issue you can use the previous version which is 5.3.1
TypeError: expressValidator is not a function issue by reinstall the express validator NPM package and run the server again.
npm uninstall express-validator
npm install express-validator@5.3.0
package.json
All dependencies added in package.json file.
File Name : package.json
{
"name": "crud-app",
"version": "0.0.0",
"private": true,
"scripts": {
"start": "node ./bin/www"
},
"dependencies": {
"cookie-parser": "~1.4.4",
"debug": "~2.6.9",
"ejs": "~2.6.1",
"express": "~4.16.1",
"express-flash": "^0.0.2",
"express-session": "^1.17.3",
"express-validator": "^6.14.2",
"http-errors": "~1.6.3",
"method-override": "^3.0.0",
"morgan": "~1.9.1",
"mysql": "^2.18.1"
}
}
method-override :-
File Name :
NPM is used to run a DELETE and PUT method from an HTML form. In several web browsers only support GET and POST methods.
Step 4 – Create Database connection :-
Create directory name lib and create a new file name db.js inside this folder. after that you will connect node js to mysql database using db.js file
File Name : lib/db.js
var mysql=require('mysql');
var connection=mysql.createConnection({
host:'localhost',
user:'root',
password:'root',
database:'nodedb'
});
connection.connect(function(error){
if(!!error){
console.log(error);
}else{
console.log('Connected!:)');
}
});
module.exports = connection;
Step 5 – Create server.js File
create a new file name server.js in your root project directory (crud_app)
File Name : server.js
var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var expressValidator = require('express-validator');
var flash = require('express-flash');
var session = require('express-session');
var bodyParser = require('body-parser');
var mysql = require('mysql');
var connection = require('./lib/db');
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var customersRouter = require('./routes/customers');
var app = express();
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
app.use(session({
secret: '123456cat',
resave: false,
saveUninitialized: true,
cookie: { maxAge: 60000 }
}))
app.use(flash());
app.use(expressValidator());
app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/customers', customersRouter);
// catch 404 and forward to error handler
app.use(function(req, res, next) {
next(createError(404));
});
// error handler
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};
// render the error page
res.status(err.status || 500);
res.render('error');
});
// port must be set to 3000 because incoming http requests are routed from port 80 to port 8080
app.listen(3000, function () {
console.log('Node app is running on port 3000');
});
module.exports = app;
Step 6 – Create CRUD Routes customers.js :-
Create crud route file name customers.js. in routes folder.
File Name : routes/customers.js
var express = require('express');
var router = express.Router();
var connection = require('../lib/db');
/* GET home page. */
router.get('/', function(req, res, next) {
connection.query('SELECT * FROM customers ORDER BY id desc',function(err,rows) {
if(err){
req.flash('error', err);
res.render('customers',{page_title:"Customers - Node.js",data:''});
}else{
res.render('customers',{page_title:"Customers - Node.js",data:rows});
}
});
});
// SHOW ADD USER FORM
router.get('/add', function(req, res, next){
// render to views/user/add.ejs
res.render('customers/add', {
title: 'Add New Customers',
name: '',
email: ''
})
})
// ADD NEW USER POST ACTION
router.post('/add', function(req, res, next){
req.assert('name', 'Name is required').notEmpty() //Validate name
req.assert('email', 'A valid email is required').isEmail() //Validate email
var errors = req.validationErrors()
if( !errors ) { //No errors were found. Passed Validation!
var user = {
name: req.sanitize('name').escape().trim(),
email: req.sanitize('email').escape().trim()
}
connection.query('INSERT INTO customers SET ?', user, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// render to views/user/add.ejs
res.render('customers/add', {
title: 'Add New Customer',
name: user.name,
email: user.email
})
} else {
req.flash('success', 'Data added successfully!');
res.redirect('/customers');
}
})
}
else { //Display errors to user
var error_msg = ''
errors.forEach(function(error) {
error_msg += error.msg + '<br>'
})
req.flash('error', error_msg)
/**
* Using req.body.name
* because req.param('name') is deprecated
*/
res.render('customers/add', {
title: 'Add New Customer',
name: req.body.name,
email: req.body.email
})
}
})
// SHOW EDIT USER FORM
router.get('/edit/(:id)', function(req, res, next){
connection.query('SELECT * FROM customers WHERE id = ' + req.params.id, function(err, rows, fields) {
if(err) throw err
// if user not found
if (rows.length <= 0) {
req.flash('error', 'Customers not found with id = ' + req.params.id)
res.redirect('/customers')
}
else { // if user found
// render to views/user/edit.ejs template file
res.render('customers/edit', {
title: 'Edit Customer',
//data: rows[0],
id: rows[0].id,
name: rows[0].name,
email: rows[0].email
})
}
})
})
// EDIT USER POST ACTION
router.post('/update/:id', function(req, res, next) {
req.assert('name', 'Name is required').notEmpty() //Validate nam //Validate age
req.assert('email', 'A valid email is required').isEmail() //Validate email
var errors = req.validationErrors()
if( !errors ) {
var user = {
name: req.sanitize('name').escape().trim(),
email: req.sanitize('email').escape().trim()
}
connection.query('UPDATE customers SET ? WHERE id = ' + req.params.id, user, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// render to views/user/add.ejs
res.render('customers/edit', {
title: 'Edit Customer',
id: req.params.id,
name: req.body.name,
email: req.body.email
})
} else {
req.flash('success', 'Data updated successfully!');
res.redirect('/customers');
}
})
}
else { //Display errors to user
var error_msg = ''
errors.forEach(function(error) {
error_msg += error.msg + '<br>'
})
req.flash('error', error_msg)
/**
* Using req.body.name
* because req.param('name') is deprecated
*/
res.render('customers/edit', {
title: 'Edit Customer',
id: req.params.id,
name: req.body.name,
email: req.body.email
})
}
})
// DELETE USER
router.get('/delete/(:id)', function(req, res, next) {
var user = { id: req.params.id }
connection.query('DELETE FROM customers WHERE id = ' + req.params.id, user, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// redirect to users list page
res.redirect('/customers')
} else {
req.flash('success', 'Customer deleted successfully! id = ' + req.params.id)
// redirect to users list page
res.redirect('/customers')
}
})
})
module.exports = router;
Step 7 – Create views
First we will create one foleder name customers inside the views folder.
File Name : customers/index.ejs
<!DOCTYPE html>
<html>
<head>
<title>Customers</title>
<link rel='stylesheet' href='/stylesheets/style.css' />
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<div>
<a href="/" class="btn btn-primary ml-3">Home</a>
<a href="/customers/add" class="btn btn-secondary ml-3">New Customer</a>
<a href="/customers" class="btn btn-info ml-3">Customer List</a>
</div>
<!-- <% if (messages.error) { %>
<p style="color:red"><%- messages.error %></p>
<% } %> -->
<% if (messages.success) { %>
<p class="alert alert-success mt-4"><%- messages.success %></p>
<% } %>
<br>
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">Name</th>
<th scope="col">Email</th>
<th width="200px">Action</th>
</tr>
</thead>
<tbody>
<% if(data.length){
for(var i = 0; i< data.length; i++) {%>
<tr>
<th scope="row"><%= (i+1) %></th>
<td><%= data[i].name%></td>
<td><%= data[i].email%></td>
<td>
<a class="btn btn-success edit" href="../customers/edit/<%=data[i].id%>">Edit</a>
<a class="btn btn-danger delete" onclick="return alert('Are You sure?')" href="../customers/delete/<%=data[i].id%>">Delete</a>
</td>
</tr>
<% }
}else{ %>
<tr>
<td colspan="3">No user</td>
</tr>
<% } %>
</tbody>
</table>
</body>
</html>
add.ejs
File Name : customers/add.ejs
<!DOCTYPE html>
<html>
<head>
<title>Customers</title>
<link rel='stylesheet' href='/stylesheets/style.css' />
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<% if (messages.error) { %>
<p style="color:red"><%- messages.error %></p>
<% } %>
<% if (messages.success) { %>
<p style="color:green"><%- messages.success %></p>
<% } %>
<form action="/customers/add" method="post" name="form1">
<div class="form-group">
<label for="exampleInputPassword1">Name</label>
<input type="text" class="form-control" name="name" id="name" value="" placeholder="Name">
</div>
<div class="form-group">
<label for="exampleInputEmail1">Email address</label>
<input type="email" name="email" class="form-control" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="">
</div>
<input type="submit" class="btn btn-primary" value="Add">
</form>
</body>
</html>
edit.ejs
File Name : customers/edit.ejs
<!DOCTYPE html>
<html>
<head>
<title>Customers</title>
<link rel='stylesheet' href='/stylesheets/style.css' />
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<form action="/customers/update/<%= id %>" method="post" name="form1">
<div class="form-group">
<label for="exampleInputPassword1">Name</label>
<input type="text" class="form-control" name="name" id="name" value="<%= name %>" placeholder="Name">
</div>
<div class="form-group">
<label for="exampleInputEmail1">Email address</label>
<input type="email" class="form-control" name="email" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="<%= email %>">
</div>
<button type="submit" class="btn btn-info">Update</button>
</form>
</body>
</html>
Step 8 – Start Node application
File Name :
npm start
after run this command open your browser and hit
http://127.0.0.1:3000/customers
################# OR ################
c:\crud_app> node server.js
after run this command open your browser and hit
http://127.0.0.1:3000/customers
Database Table
File Name :
CREATE TABLE customers(id int(11) NOT NULL AUTO_INCREMENT,
primary key(id),
name varchar(50) NOT NULL,
email varchar(100) NOT NULL
);
INSERT INTO `customers` (`id`, `name`, `email`) VALUES (1, 'sana', 'sana@gmail.com');
INSERT INTO `customers` (`id`, `name`, `email`) VALUES (2, 'sara', 'sara@gmail.com');
INSERT INTO `customers` (`id`, `name`, `email`) VALUES (3, 'arham', 'arham@gmail.com');
INSERT INTO `customers` (`id`, `name`, `email`) VALUES (4, 'mahira', 'mahira@gmail.com');
INSERT INTO `customers` (`id`, `name`, `email`) VALUES (5, 'mahira', 'mahira@gmail.com');
INSERT INTO `customers` (`id`, `name`, `email`) VALUES (6, 'habib', 'habib@gmail.com');