Node.js Simple CRUD with Express.js and MySQL
In this guide, I’m going to build a CRUD application in Node.js with Express.js and MySQL. I’ll create a books table and will implement CRUD for the books table.
Table of Contents
- Create Project and Install Dependencies
- Create Database, Table and Connect to Database
- Make CRUD Routes
- Create View Files
- Import Created Files to app.js
- Run and See Output
Step 1 : Create Project and Install Dependencies
If you didn’t install the Express application generator, then install this globally using this command:
# with NPM command
npm install -g express-generator
Now we are going to create a project using the express application generator. Go to the project directory and create a project named “nodejs-crud“.
# create project
express --view=ejs nodejs-crud
# go to the project folder:
cd nodejs-crud
Next, we need to install some dependencies. Let’s install these:
# to send flash message:
npm install express-flash --save
# to make 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 --save
Step 2 : Create Database, Table and Connect to Database
Create a database and then create a books table. Here’s the table structure for books table:
CREATE TABLE `books` (
`id` int(11) NOT NULL,
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`author` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `books` ADD PRIMARY KEY (`id`);
ALTER TABLE `books` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
We’ve created a database and table. Let’s connect our project to this database. In the root project directory, make a folder called ‘lib‘. Under the lib folder, make a file named ‘db.js‘.
Then open the db.js file and paste this code:
var mysql = require('mysql');
var connection = mysql.createConnection({
host:'localhost',
user:'USERNAME_HERE',
password:'PASSWORD_HERE',
database:'DATABASE_NAME_HERE'
});
connection.connect(function(error){
if(!!error) {
console.log(error);
} else {
console.log('Connected..!');
}
});
module.exports = connection;
Don’t forget to enter your database credentials.
Step 3 : Make CRUD Routes
In this step, we are going to register CRUD routes: create, read, update and delete book data from books table. Go to the routes folder and make a file called ‘books.js‘. We will define routes in this file.
var express = require('express');
var router = express.Router();
var dbConn = require('../lib/db');
// display books page
router.get('/', function(req, res, next) {
dbConn.query('SELECT * FROM books ORDER BY id desc',function(err,rows) {
if(err) {
req.flash('error', err);
// render to views/books/index.ejs
res.render('books',{data:''});
} else {
// render to views/books/index.ejs
res.render('books',{data:rows});
}
});
});
// display add book page
router.get('/add', function(req, res, next) {
// render to add.ejs
res.render('books/add', {
name: '',
author: ''
})
})
// add a new book
router.post('/add', function(req, res, next) {
let name = req.body.name;
let author = req.body.author;
let errors = false;
if(name.length === 0 || author.length === 0) {
errors = true;
// set flash message
req.flash('error', "Please enter name and author");
// render to add.ejs with flash message
res.render('books/add', {
name: name,
author: author
})
}
// if no error
if(!errors) {
var form_data = {
name: name,
author: author
}
// insert query
dbConn.query('INSERT INTO books SET ?', form_data, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// render to add.ejs
res.render('books/add', {
name: form_data.name,
author: form_data.author
})
} else {
req.flash('success', 'Book successfully added');
res.redirect('/books');
}
})
}
})
// display edit book page
router.get('/edit/(:id)', function(req, res, next) {
let id = req.params.id;
dbConn.query('SELECT * FROM books WHERE id = ' + id, function(err, rows, fields) {
if(err) throw err
// if user not found
if (rows.length <= 0) {
req.flash('error', 'Book not found with id = ' + id)
res.redirect('/books')
}
// if book found
else {
// render to edit.ejs
res.render('books/edit', {
title: 'Edit Book',
id: rows[0].id,
name: rows[0].name,
author: rows[0].author
})
}
})
})
// update book data
router.post('/update/:id', function(req, res, next) {
let id = req.params.id;
let name = req.body.name;
let author = req.body.author;
let errors = false;
if(name.length === 0 || author.length === 0) {
errors = true;
// set flash message
req.flash('error', "Please enter name and author");
// render to add.ejs with flash message
res.render('books/edit', {
id: req.params.id,
name: name,
author: author
})
}
// if no error
if( !errors ) {
var form_data = {
name: name,
author: author
}
// update query
dbConn.query('UPDATE books 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('books/edit', {
id: req.params.id,
name: form_data.name,
author: form_data.author
})
} else {
req.flash('success', 'Book successfully updated');
res.redirect('/books');
}
})
}
})
// delete book
router.get('/delete/(:id)', function(req, res, next) {
let id = req.params.id;
dbConn.query('DELETE FROM books WHERE id = ' + id, function(err, result) {
//if(err) throw err
if (err) {
// set flash message
req.flash('error', err)
// redirect to books page
res.redirect('/books')
} else {
// set flash message
req.flash('success', 'Book successfully deleted! ID = ' + id)
// redirect to books page
res.redirect('/books')
}
})
})
module.exports = router;
So, our CRUD routes are ready.
Step 4 : Create View Files
To display all books, add & edit book form, we need to create three view files. We've set EJS for templating. Go to the views folder & create a folder called books. Under the books folder, make three files named index.ejs, add.ejs and edit.ejs. Then copy the below code and paste:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Books</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;">
<% 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">Books</a>
</li>
<li class="nav-pill ml-auto">
<a class="nav-link active" href="/books/add">Add Book</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">Author</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].author%></td>
<td>
<a class="btn btn-success edit" href="../books/edit/<%=data[i].id%>">Edit</a>
<a class="btn btn-danger delete" onclick="return alert('Are You sure?')" href="../books/delete/<%=data[i].id%>">Delete</a>
</td>
</tr>
<% } %>
</tbody>
</table>
<% } %>
<!-- if result is empty -->
<% if(!data.length) { %>
<p class="text-center">No book found!</p>
<% } %>
</div>
</div>
</body>
</html>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Add Book</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;">
<% if (messages.error) { %>
<div class="alert alert-danger" role="alert"><%- messages.error %></div>
<% } %>
<div class="card">
<div class="card-header">
Add Book
</div>
<div class="card-body">
<form action="/books/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>Author:</label>
<input type="text" class="form-control" name="author" value="<%= author %>" autocomplete="off">
</div>
<div class="form-group">
<input type="submit" class="btn btn-info" value="Add"/>
</div>
</form>
</div>
</div>
</body>
</html>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Edit Book</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;">
<% if (messages.error) { %>
<div class="alert alert-danger" role="alert"><%- messages.error %></div>
<% } %>
<div class="card">
<div class="card-header">
Edit Book
</div>
<div class="card-body">
<form action="/books/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>Author:</label>
<input type="text" class="form-control" name="author" value="<%= author %>" autocomplete="off">
</div>
<div class="form-group">
<input type="submit" class="btn btn-info" value="Update"/>
</div>
</form>
</div>
</div>
</body>
</html>
Step 5 : Import Created Files to app.js
Our project is about to finish. We have to import all created files to app.js. We need to import these lines:
var flash = require('express-flash');
var session = require('express-session');
var mysql = require('mysql');
var connection = require('./lib/db');
var booksRouter = require('./routes/books');
app.use(session({
cookie: { maxAge: 60000 },
store: new session.MemoryStore,
saveUninitialized: true,
resave: 'true',
secret: 'secret'
}))
app.use(flash());
app.use('/books', booksRouter);
After importing all files, the app.js looks like:
var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var flash = require('express-flash');
var session = require('express-session');
var mysql = require('mysql');
var connection = require('./lib/db');
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var booksRouter = require('./routes/books');
var app = express();
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
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('/', indexRouter);
app.use('/users', usersRouter);
app.use('/books', booksRouter);
// 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;
Step 6 : Run and See Output
We have finished all the steps. It's time to run the project. Let's run the project and see the output:
# run project:
npm start
# project URL:
http://localhost:3000
# books crud URL:
http://localhost:3000/books
The final output of this project:
The tutorial is over. You can download this project from GitHub. Thank you. 😊
Md Obydullah
Software Engineer | Ethical Hacker & Cybersecurity...
Md Obydullah is a software engineer and full stack developer specialist at Laravel, Django, Vue.js, Node.js, Android, Linux Server, and Ethichal Hacking.