Node.js Simple CRUD with Express.js and MySQL

  • Oct 08, 2019
  • Article · 12 min
  • 2428 words

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

  1. Create Project and Install Dependencies
  2. Create Database, Table and Connect to Database
  3. Make CRUD Routes
  4. Create View Files
  5. Import Created Files to app.js
  6. 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:

lib/db.js
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.

routes/books.js
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:

books/index.ejs
<!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>
books/add.ejs
<!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>
books/edit.ejs
<!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:

app.js
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:

Node.js Simple CRUD with Express.js and MySQL

The tutorial is over. You can download this project from GitHub. Thank you. 😊

Node.js

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.

Random Stories

Laravel How to Display Social Media Share Icons

article

Laravel 9 How to Send Mail using Mailgun

article

Laravel Usage of Laravel Eloquent when() Conditions

article

Use Word Captcha Code and Validation in Laravel

article

Laravel Livewire CRUD with Example

article