Node JS CRUD With MySQL
7 min readAug 30, 2023
Step 1: Create Node js Application for Node js CRUD example
Step 2: Install Embedded JavaScript templates (ejs)
Step 3: Install express.js in App
Step 4: Install Dependency
Step 5: Create Database and Table for node js crud example with mysql
Step 6: Make Routes
Step 7: Create View Files for crud xample in node.js
Step 8: Create index.js File
Step 9: Run the index.js fileNode.js is an open-source, cross-platform runtime environment for developing server-side and networking applications. You should have a basic understanding of node js.
https://nodejs.org/en
Express.js is one of the most trending web frameworks for node.js. It is built on top of the node.js http module and adds support for routing, middleware, view system, etc.
https://expressjs.com/
MySQL is an open-source relational database management system that can operate on various platforms. It is easy to manage.
https://dev.mysql.com/downloads/installer/
Step 1: Create Node.js Application for node.js crud example
In this step, we will create a node.js application using the below commands.
mkdir CurdAppNodejs
cd CurdAppNodejs
npm initStep 2: Install Embedded JavaScript templates (ejs)
In this step, we will install ejs using the below command :
npm install ejsStep 3: Install express.js in App
Now, install express js using the below command.
$ npm install express --saveStep 4: Install Dependency
Next, we need to install some dependencies. So, copy the below command and paste it into your terminal
# flash message:
npm install express-flash --save
# session like PHP:
npm install express-session --save
# to send PUT and DELETE requests:
npm install method-override --save
# driver to connect Node.js with MySQL:
npm install mysql --saveStep 5: Create Database and Table for node js crud example with mysql
show databases;
create database p4n;
use p4n;
CREATE TABLE `users` (
`id` int(50) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`position` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
ALTER TABLE `users`
MODIFY `id` int(50) NOT NULL AUTO_INCREMENT;
COMMIT;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';
select * from users;After creating the table and database. we are connecting the database and application. In the project directory, make a folder called lib. In the lib folder, make a file named db.js.
path: lib/db.js
var mysql = require('mysql');
var connection = mysql.createConnection({
host:'localhost',
user:'root',
password:'admin',
database:'p4n'
});
connection.connect(function(error){
if(!!error) {
console.log(error);
} else {
console.log('Database Connected Successfully..!!');
}
});
module.exports = connection;Step 6: Make Routes
In this step, we will create the routes folder. In the routes folder, creates a users.js file and add the below code.
//www.codeswithpankaj.com
var express = require('express');
var router = express.Router();
var dbConn = require('../lib/db');
// display user page
router.get('/', function(req, res, next) {
dbConn.query('SELECT * FROM users ORDER BY id desc',function(err,rows) {
if(err) {
req.flash('error', err);
// render to views/users/index.ejs
res.render('users',{data:''});
} else {
// render to views/users/index.ejs
res.render('users',{data:rows});
}
});
});
// display add user page
router.get('/add', function(req, res, next) {
// render to add.ejs
res.render('users/add', {
name: '',
email: '',
position:''
})
})
// add a new user
router.post('/add', function(req, res, next) {
let name = req.body.name;
let email = req.body.email;
let position = req.body.position;
let errors = false;
if(name.length === 0 || email.length === 0 || position === 0) {
errors = true;
// set flash message
req.flash('error', "Please enter name and email and position");
// render to add.ejs with flash message
res.render('users/add', {
name: name,
email: email,
position:position
})
}
// if no error
if(!errors) {
var form_data = {
name: name,
email: email,
position:position
}
// insert query
dbConn.query('INSERT INTO users SET ?', form_data, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// render to add.ejs
res.render('users/add', {
name: form_data.name,
email: form_data.email,
position:form_data.position
})
} else {
req.flash('success', 'User successfully added');
res.redirect('/users');
}
})
}
})
// display edit user page
router.get('/edit/(:id)', function(req, res, next) {
let id = req.params.id;
dbConn.query('SELECT * FROM users WHERE id = ' + id, function(err, rows, fields) {
if(err) throw err
// if user not found
if (rows.length <= 0) {
req.flash('error', 'User not found with id = ' + id)
res.redirect('/users')
}
// if user found
else {
// render to edit.ejs
res.render('users/edit', {
title: 'Edit User',
id: rows[0].id,
name: rows[0].name,
email: rows[0].email,
position: rows[0].position
})
}
})
})
// update user data
router.post('/update/:id', function(req, res, next) {
let id = req.params.id;
let name = req.body.name;
let email = req.body.email;
let position = req.body.position;
let errors = false;
if(name.length === 0 || email.length === 0 || position.length === 0) {
errors = true;
// set flash message
req.flash('error', "Please enter name and email and position");
// render to add.ejs with flash message
res.render('users/edit', {
id: req.params.id,
name: name,
email: email,
position:position
})
}
// if no error
if( !errors ) {
var form_data = {
name: name,
email: email,
position:position
}
// update query
dbConn.query('UPDATE users SET ? WHERE id = ' + id, form_data, function(err, result) {
//if(err) throw err
if (err) {
// set flash message
req.flash('error', err)
// render to edit.ejs
res.render('users/edit', {
id: req.params.id,
name: form_data.name,
email: form_data.email,
position: form_data.position
})
} else {
req.flash('success', 'User successfully updated');
res.redirect('/users');
}
})
}
})
// delete user
router.get('/delete/(:id)', function(req, res, next) {
let id = req.params.id;
dbConn.query('DELETE FROM users WHERE id = ' + id, function(err, result) {
//if(err) throw err
if (err) {
// set flash message
req.flash('error', err)
// redirect to user page
res.redirect('/users')
} else {
// set flash message
req.flash('success', 'User successfully deleted! ID = ' + id)
// redirect to user page
res.redirect('/users')
}
})
})
module.exports = router;Step 7: Create View Files for crud xample in node.js
Now, We need to create view files for add, edit, and view. We’ve set EJS for templating. Go to the views folder & create a folder called users. Under the user’s folder, make files named nav.html,index.ejs, add.ejs and edit.ejs.
views/users/nav.html
<nav class="navbar navbar-expand-lg bg-dark">
<div class="container-fluid">
<a class="navbar-brand text-white" href="https://www.codeswithpankaj.com/">
<svg xmlns="http://www.w3.org/2000/svg" width="30" height="30" fill="currentColor" class="bi bi-journal-code" viewBox="0 0 16 16">
<path fill-rule="evenodd" d="M8.646 5.646a.5.5 0 0 1 .708 0l2 2a.5.5 0 0 1 0 .708l-2 2a.5.5 0 0 1-.708-.708L10.293 8 8.646 6.354a.5.5 0 0 1 0-.708zm-1.292 0a.5.5 0 0 0-.708 0l-2 2a.5.5 0 0 0 0 .708l2 2a.5.5 0 0 0 .708-.708L5.707 8l1.647-1.646a.5.5 0 0 0 0-.708z"/>
<path d="M3 0h10a2 2 0 0 1 2 2v12a2 2 0 0 1-2 2H3a2 2 0 0 1-2-2v-1h1v1a1 1 0 0 0 1 1h10a1 1 0 0 0 1-1V2a1 1 0 0 0-1-1H3a1 1 0 0 0-1 1v1H1V2a2 2 0 0 1 2-2z"/>
<path d="M1 5v-.5a.5.5 0 0 1 1 0V5h.5a.5.5 0 0 1 0 1h-2a.5.5 0 0 1 0-1H1zm0 3v-.5a.5.5 0 0 1 1 0V8h.5a.5.5 0 0 1 0 1h-2a.5.5 0 0 1 0-1H1zm0 3v-.5a.5.5 0 0 1 1 0v.5h.5a.5.5 0 0 1 0 1h-2a.5.5 0 0 1 0-1H1z"/>
</svg>
CodesWithPankaj
</a>
<button class="navbar-toggler text-white bg-white" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav">
</ul>
</div>
</div>
</nav>views/users/index.ejs
<!--www.codeswithpankaj.com -->
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>p4n.in</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body class="container" style="margin-top: 50px;">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-4bw+/aepP/YC94hEpVNVgiZdgIC5+VKNBQNGCHeKRQN+PtmoHDEXuppvnDJzQIu9" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-HwwvtgBNo3bZJJLYd8oVXjrBZt8cqVSpeBNS5n7C8IVInixGAoxmnlMuBnhbgrkm" crossorigin="anonymous"></script>
<%- include('nav.html') %>
<h1>Codes With Pankaj</h1>
<br>
<% if (messages.success) { %>
<div class="alert alert-success" role="alert"><%- messages.success %></div>
<% } %>
<% if (messages.error) { %>
<div class="alert alert-danger" role="alert"><%- messages.error %></div>
<% } %>
<div class="card">
<div class="card-header">
<ul class="nav nav-pills w-100">
<li class="nav-pill active">
<a class="nav-link">Users</a>
</li>
<li class="nav-pill ml-auto">
<a class="nav-link active" href="/users/add">Add User</a>
</li>
</ul>
</div>
<div class="card-body">
<% if(data.length) { %>
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">Name</th>
<th scope="col">Email</th>
<th scope="col">Position</th>
<th width="200px">Action</th>
</tr>
</thead>
<tbody>
<% 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><%= data[i].position%></td>
<td>
<a class="btn btn-success edit" href="../users/edit/<%=data[i].id%>">Edit</a>
<a class="btn btn-danger delete" onclick="return alert('Are you sure want to delete this record?')" href="../users/delete/<%=data[i].id%>">Delete</a>
</td>
</tr>
<% } %>
</tbody>
</table>
<% } %>
<!-- if result is empty -->
<% if(!data.length) { %>
<p class="text-center">No users found!</p>
<% } %>
</div>
</div>
</body>
</html>views/users/add.ejs
<!--www.codeswithpankaj.com -->
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Add User</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body class="container" style="margin-top: 50px;">
<%- include('nav.html') %>
<h1>Codes With Pankaj</h1>
<br>
<% if (messages.error) { %>
<div class="alert alert-danger" role="alert"><%- messages.error %></div>
<% } %>
<div class="card">
<div class="card-header">
Add Users
</div>
<div class="card-body">
<form action="/users/add" method="post">
<div class="form-group">
<label>Name:</label>
<input type="text" class="form-control" name="name" value="<%= name %>" autocomplete="off">
</div>
<div class="form-group">
<label>Email:</label>
<input type="text" class="form-control" name="email" value="<%= email %>" autocomplete="off">
</div>
<div class="form-group">
<label>Position:</label>
<input type="text" class="form-control" name="position" value="<%= position %>" autocomplete="off">
</div>
<div class="form-group">
<input type="submit" class="btn btn-info" value="Add"/>
</div>
</form>
</div>
</div>
</body>
</html>views/users/edit.ejs
<!--www.codeswithpankaj.com -->
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Edit User</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body class="container" style="margin-top: 50px;">
<%- include('nav.html') %>
<h1>Codes With Pankaj</h1>
<br>
<% if (messages.error) { %>
<div class="alert alert-danger" role="alert"><%- messages.error %></div>
<% } %>
<div class="card">
<div class="card-header">
Edit User
</div>
<div class="card-body">
<form action="/users/update/<%= id %>" method="post">
<div class="form-group">
<label>Name:</label>
<input type="text" class="form-control" name="name" value="<%= name %>" autocomplete="off">
</div>
<div class="form-group">
<label>Email:</label>
<input type="text" class="form-control" name="email" value="<%= email %>" autocomplete="off">
</div>
<div class="form-group">
<label>Position:</label>
<input type="text" class="form-control" name="position" value="<%= position %>" autocomplete="off">
</div>
<div class="form-group">
<input type="submit" class="btn btn-info" value="Update"/>
</div>
</form>
</div>
</div>
</body>
</html>Step 8: Create index.js File
In this step, import all dependencies like express js, flash message, MySQL drivers, etc.
var createError = require('http-errors');
var express = require('express');
var path = require('path');
var flash = require('express-flash');
var session = require('express-session');
var mysql = require('mysql');
var connection = require('./lib/db');
var usersRouter = require('./routes/users');
var app = express();
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(express.static(path.join(__dirname, 'public')));
app.use(session({
cookie: { maxAge: 60000 },
store: new session.MemoryStore,
saveUninitialized: true,
resave: 'true',
secret: 'secret'
}))
app.use(flash());
app.use('/users', usersRouter);
// catch 404 and forward to error handler
app.use(function(req, res, next) {
next(createError(404));
});
app.listen(3000);Step 9: Run the index.js file
Now, run index.js using the below code
node index.jsIn your browser open the below URL.
http://localhost:3000/users
