Open in app

Sign up

Sign in

Write

Sign up

Sign in

Home

Following

Library

Stories

Stats

Mastodon

Node JS CRUD With MySQL

Codes With Pankaj
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 file

Node.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 init

Step 2: Install Embedded JavaScript templates (ejs)

In this step, we will install ejs using the below command :

npm install ejs

Step 3: Install express.js in App

Now, install express js using the below command.

$ npm install express --save

Step 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 --save

Step 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>
        &nbsp; 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.js

In your browser open the below URL.

http://localhost:3000/users

Click to Download Code …

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Sign up for free

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

Try for $5/month
Nodejs
Node Js Tutorial
Node Js Development
Curd
P4n

--

--

Codes With Pankaj
Codes With Pankaj
Follow

Written by Codes With Pankaj

99 Followers
4 Following

@codeawithpankaj.com

Follow

Responses (1)

Write a response

What are your thoughts?

Help

Status

About

Careers

Press

Blog

Privacy

Rules

Terms

Text to speech