Hello developers, Today we will discuss about node js crud (create, update, read, delete) operation. We will use express node js framework and mysql for database. Express is the most popular framework for node js and other side mysql is also popular database. So let’s get started with the node js crud operation with express and mysql database tutorial
We will use ejs templating engine. It is very simple and easy to understand for everyone.
Let’s start create CRUD with Node.Js , Express, MySQL
Create Express js Project
Use the below command and create your express project with name expressfirst
express --view=ejs expressfirst
After successfully created expressfirst folder in your system. Next follow the below commands and install node js in your project :
cd expressfirst
npm install
Next we need to install some required pacakges, go to terminal and use the below commands :
npm install express-flash --save
npm install express-session --save
npm install express-validator --save
npm install method-override --save
npm install mysql --save
express-flash
Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.
In this node js mysql crud tutorial express flash is used to display a warning, error and information message
express-session
Express-session is used to made a session as like in PHP. In this node js mysql crud tutorial, session is needed as the express requirement of express-flash.
express-validator
Express validator is used to validate form data it is easy to use. express-validator highly effective and efficient way to accelerate the creation of applications.
method-override
NPM is used to run a DELETE and PUT method from an HTML form. In several web browsers only support GET and POST methods.
MySQL
Driver to connect node.js with MySQL
Database Connection with Mysql
Next we need to create one folder name lib and create a new file name db.js inside this folder. We will connect node js to mysql using this file
lib/db.js
var mysql=require('mysql');
var connection=mysql.createConnection({
host:'localhost',
user:'your username',
password:'your password',
database:'your database name'
});
connection.connect(function(error){
if(!!error){
console.log(error);
}else{
console.log('Connected!:)');
}
});
module.exports = connection;
Changes in app.js
We need to some changes in app.js file. go to app.js file and put some code here :
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');
});
module.exports = app;
Create Route
Next We need to create one route file name customers.js inside routes folder. After created this file, We will implement crud logic in this file. Go to routes/customers.js and use the below code :
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;
Create views
First we will create one foleder name customers inside the views folder.
Next we need to create three views file name add.ejs, edit.ejs and index.ejs. We will create three view files inside the views/customers folder.
Create first file index.ejs
Index.ejs file, we will display the list of customers.
<!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>
Create second file name add.ejs
Add.ejs file, we will create form for sending to data in database.
<!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>
Create third file name edit.ejs
Next create last file edit.ejs, we will to edit data in this form.
<!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>
Test Node js Crud app
run the below command
npm start
after run this command open your browser and hit
http://127.0.0.1:3000/customers
Conclusion
In this node express js crud tutorial – We have created node js crud (create, read, update, delete) application with mysql database. We have also successfully install node js framework express with use ejs templating engine.
