Skip to main content

Node.js MySQL CRUD – Create CRUD Operations Using Node.js Express and MySQL

Create CRUD Operations in Node.js Express

Before Getting Started this Script, You must do the following things.
myapp/
  |__bin/
  |__config/
  |     |__database.js
  |__controllers/
  |     |__crud-controller.js
  |__models/
  |     |__crud-models.js
  |__node_modules/
  |__public/
  |__routes/
  |     |__crud-route.js
  |__views/
  |     |__crud-operation.ejs
  |__package-lock.json
  |__package.json

CRUD Configuration

Now, you need to create  MVC, MySQL database &  table to implement CRUD Operation in Node.js Express. So, configure the following steps

Model, View, Controller Folder

Create Some files in the Model, View, & Controller as the following ways
Create controllers & models folder in myapp Express Folder.
CRUD – Model
Create file crud-model.js in the models  folder & load it in the crud-controller.js by using var crudModel=require('../models/crud-model');
File Path: models/crud-model.
  • var db= require('../config/database');
  • module.exports={
  • // CRUD model script will be defined here
  • }
CRUD – Controller
Create file crud-controller.js in the controllers  folder & load it in the crud-route.js by using var crudController=require('../controllers/crud-controller.js');
File Path: controllers/crud-controller
  • var crudModel=require('../models/crud-model');
  • module.exports={
  • CRUD controller script will be defined here
  • }
CRUD – View
Create file crud-form.ejs & crud-list.ejs in the views  folder
You can read Express MVC Structure tutorials to create Model, View & Controller in Node.js Express.

MySQL Database and Table

If you work with PHP, you can create MySQL database & table into PHPMyAdmin
Database
First of all, Create a MySQL database according to you. But I will explain CRUD with the created database name nodeapp .
Table
Create a table crud into the MySQL database nodeapp as the following query
  • CREATE TABLE `users` (
  • `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  • `fullName` varchar(255) DEFAULT NULL,
  • `emailAddress` varchar(255) DEFAULT NULL,
  • `city` varchar(255) DEFAULT NULL,
  • `country` varchar(255) DEFAULT NULL,
  • `created_at` datetime NOT NULL
  • ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Database Connection

  • Create config folder in the myapp express folder
  • Create database.js file in the config folder
  • Use the following script in the database.js file to connect Node.js express to MySQL Database.
  • load database.js in the crud-model.js by using  var db=require('../config/database.js');
File Path: config/database.js
  • var mysql = require('mysql');
  • var conn = mysql.createConnection({
  • host: 'localhost', // Replace with your host name
  • user: 'root', // Replace with your database username
  • password: '', // Replace with your database password
  • database: 'nodeapp' // // Replace with your database Name
  • });
  • conn.connect(function(err) {
  • if (err) throw err;
  • console.log('Database is connected successfully !');
  • });
  • module.exports = conn;
You can read the  Node.js MySQL Connection tutorial to know more database connection with Node.js Express.

CRUD Operations

In CRUD Operation, you will learn to create, Read, & delete data in Node.js Express using MySQL. So, It is explained with the following steps

Node.js Mysql – Create

In the case of Create, You can insert user data through the HTML form into the MySQL database table.
Create Script is written in the following  Model, View,& Controller  Pattern. So, User Input data can be createdeasily.

Write Create Script in Model 

Use the following Create script in the file crud-model.js
  • createCrud:function(userDetails,callback){
  • var sql = 'INSERT INTO crud SET ?';
  • db.query(sql, userDetails,function (err, data) {
  • if (err) throw err;
  • return callback(data);
  • });
  • },

Insert Input From in View

Use the following Create script in the file crud-form.js
  • <form action="/crud/create" method="POST">
  • <label>Full Name</label>
  • <input type="text" placeholder="Enter Full Name" name="fullName" required>
  • <label>Email Address</label>
  • <input type="email" placeholder="Enter Email Address" name="emailAddress" required>
  • <label>City</label>
  • <input type="city" placeholder="Enter Full City" name="city" required>
  • <label>Country</label>
  • <input type="text" placeholder="Enter Full Country" name="country">
  • <button type="submit">Submit</button>
  • </form>

Write Create Script in Controller

Use the following Create script in the file crud-controoler.js
  • This script will display the crud-form.ejs on the browser.
  • crudForm:function(req, res) {
  • res.render('crud-form');
  • },
  • This script will call the crud-model.js and insert user input into the crud  table.
  • createCrud:function(req,res){
  • const userDetails= req.body;
  • crudModel.createCrud(userDetails,function(data){
  • res.redirect('/crud/form');
  • console.log(data.affectedRows + " record(s) updated");
  • });
  • },

Node.js MySQL – Read

In the case of Read, You can fetch user data in the HTML table from the MySQL database table.
Read Script is written in the following  Model, View,& Controller  Pattern. So, User Input data can be Fetchedeasily.

Write Read Script in Model 

Use the following Create script in the file crud-model.js
  • fetchCrud:function(callback){
  • var sql='SELECT * FROM crud';
  • db.query(sql, function (err, data, fields) {
  • if (err) throw err;
  • return callback(data);
  • });
  • },

Fetch table data  in View

Use the following Read script in the file crud-list.ejs
  • <table border="1" >
  • <tr>
  • <th>S.N</th>
  • <th>Full Name</th>
  • <th>Email Address</th>
  • <th>City</th>
  • <th>Country</th>
  • <th>Edit</th>
  • <th>Delete</th>
  • </tr>
  • <%
  • if(fetchData.length!=0){
  • var i=1;
  • fetchData.forEach(function(data){
  • %>
  • <tr>
  • <td><%=i; %></td>
  • <td><%=data.fullName %></td>
  • <td><%=data.emailAddress %></td>
  • <td><%=data.city %></td>
  • <td><%=data.country %></td>
  • <td><a href="/crud/edit/<%=data.id%>">Edit</a></td>
  • <td><a href="/crud/delete/<%=data.id%>">Delete</a></td>
  • </tr>
  • <% i++; }) %>
  • <% } else{ %>
  • <tr>
  • <td colspan="7">No Data Found</td>
  • </tr>
  • <% } %>
  • </table>

Write Read Script in Controller

Use the following Read script in the file crud-controller.js
  • fetchCrud:function(req,res){
  • crudModel.fetchCrud(function(data){
  • res.render('crud-list', { title: 'User List',fetchData:data});
  • });
  • },

Node.js MySQL – Update

In the case of Update, You can update  data of MySQL database table through the HTML form
Update Script is written in the following  Model, View,& Controller  Pattern. So, User Input data can be Updatedeasily.

Write Update Script in Model 

Use the following Update script in the file crud-controller.js
  • This script will fetch the Input data in the crud-form from the  crud table &   edit it on the basis of id
  • editCrud:function(editId, callback){
  • var sql=`SELECT * FROM crud WHERE id=${editId}`;
  • db.query(sql, function (err, data) {
  • if (err) throw err;
  • return callback(data[0]);
  • });
  • },
  • This script will update the Input data of   crud table
  • UpdateCrud:function(updateData,updateId,callback){
  • var sql = `UPDATE crud SET ? WHERE id= ?`;
  • db.query(sql, [updateData, updateId], function (err, data) {
  • if (err) throw err;
  • return callback(data);
  • });
  • },

Update Table Data  From View


Use the following Update script in the file crud-form.js
  • <form action="<%=(typeof editData!='undefined')?'/crud/edit/'+editData.id:'/crud/create'%>" method="POST">
  • <label>Full Name</label>
  • <input type="text" placeholder="Enter Full Name" name="fullName" required value="<%=(typeof editData!='undefined')? editData.fullName:''%>">
  • <label>Email Address</label>
  • <input type="email" placeholder="Enter Email Address" name="emailAddress" required value="<%=(typeof editData!='undefined')? editData.emailAddress:''%>">
  • <label>City</label>
  • <input type="city" placeholder="Enter Full City" name="city" required value="<%=(typeof editData!='undefined')? editData.city:''%>">
  • <label>Country</label>
  • <input type="text" placeholder="Enter Full Country" name="country" required value="<%=(typeof editData!='undefined')? editData.country:''%>">
  • <button type="submit">Submit</button>
  • </form>

Write Update Script in Controller

Use the following Update script in the file crud-controoler.js
  • This script will edit the table record
  • editCrud:function(req,res){
  • const editId=req.params.id;
  • crudModel.editCrud(editId,function(data){
  • res.render('crud-form', { editData:data});
  • console.log(data.affectedRows + " record(s) updated");
  • });
  • },
  • This script will update the table record
  • UpdateCrud:function(req,res){
  • const updateData=req.body;
  • const updateId=req.params.id;
  • crudModel.UpdateCrud(updateData,updateId,function(data){
  • res.redirect('/crud/fetch');
  • console.log(data.affectedRows + " record(s) updated");
  • });
  • },

Node.js MySQL – Delete

In the case of Delete, You can delete  data of MySQL database table from the HTML table
Delete Script is written in the following  Model, View,& Controller  Pattern. So, User Input data can be Deletedeasily.

Write Delete Script in Model 

Use the following Delete script in the file crud-model.js
  • deleteCrud:function(deleteId,callback){
  • var sql = 'DELETE FROM crud WHERE id = ?';
  • db.query(sql, [deleteId], function (err, data) {
  • if (err) throw err;
  • return callback(data);
  • });
  • }

Delete table data From View

You can delete data from the HTML table of  crud-list.ejs

Write Delete Script in Controller

Use the following Delete script in the file crud-controoler.js
  • deleteCrud:function(req,res){
  • const deleteId=req.params.id;
  • crudModel.deleteCrud(deleteId,function(data){
  • res.redirect('/crud/fetch');
  • console.log(data.affectedRows + " record(s) updated");
  • });
  • }

CRUD Routing

Now, you will need to create crud route to execute the CRUD Operation. So, config the following steps.
You can read official Express Routing Documentation to know more about it.

Create CRUD Route

Create file crud-route.js in the routes  folder.
File Path: routes/crud-route
  • var express = require('express');
  • var crudController=require('../controllers/crud-controller');
  • var router = express.Router();
  • // curd form route
  • router.get('/form', crudController.crudForm );
  • // create data route
  • router.post('/create', crudController.createCrud);
  • // display data route
  • router.get('/fetch', crudController.fetchCrud);
  • // edit data route
  • router.get('/edit/:id', crudController.editCrud);
  • // update data route
  • router.post('/edit/:id', crudController.UpdateCrud);
  • // delete data route
  • router.get('/delete/:id', crudController.deleteCrud);
  • module.exports = router;

Load CRUD Route

  • var crudRouter = require('./routes/crud-route');
  • app.use('/crud', crudRouter);

Complete CRUD Script in Node.js Express & MySQL

Now, you can use the following script directly in your Node.js application.
File Path: models/crud-model.js
Copy & paste the following script in the crud-model.js
  • var db=require('../config/database');
  • module.exports={
  • createCrud:function(userDetails,callback){
  • var sql = 'INSERT INTO crud SET ?';
  • db.query(sql, userDetails,function (err, data) {
  • if (err) throw err;
  • return callback(data);
  • });
  • },
  • fetchCrud:function(callback){
  • var sql='SELECT * FROM crud';
  • db.query(sql, function (err, data, fields) {
  • if (err) throw err;
  • return callback(data);
  • });
  • },
  • editCrud:function(editId, callback){
  • var sql=`SELECT * FROM crud WHERE id=${editId}`;
  • db.query(sql, function (err, data) {
  • if (err) throw err;
  • return callback(data[0]);
  • });
  • },
  • UpdateCrud:function(updateData,updateId,callback){
  • var sql = `UPDATE crud SET ? WHERE id= ?`;
  • db.query(sql, [updateData, updateId], function (err, data) {
  • if (err) throw err;
  • return callback(data);
  • });
  • },
  • deleteCrud:function(deleteId,callback){
  • var sql = 'DELETE FROM crud WHERE id = ?';
  • db.query(sql, [deleteId], function (err, data) {
  • if (err) throw err;
  • return callback(data);
  • });
  • }
  • }
File Path: controllers/crud-controller.js
Copy & paste the following script in the crud-controller.js
  • var crudModel=require('../models/crud-model');
  • module.exports={
  • crudForm:function(req, res) {
  • res.render('crud-form');
  • },
  • createCrud:function(req,res){
  • const userDetails= req.body;
  • crudModel.createCrud(userDetails,function(data){
  • res.redirect('/crud/form');
  • console.log(data.affectedRows + " record(s) updated");
  • });
  • },
  • fetchCrud:function(req,res){
  • crudModel.fetchCrud(function(data){
  • res.render('crud-list', { title: 'User List',fetchData:data});
  • });
  • },
  • editCrud:function(req,res){
  • const editId=req.params.id;
  • crudModel.editCrud(editId,function(data){
  • res.render('crud-form', { editData:data});
  • console.log(data.affectedRows + " record(s) updated");
  • });
  • },
  • UpdateCrud:function(req,res){
  • const updateData=req.body;
  • const updateId=req.params.id;
  • crudModel.UpdateCrud(updateData,updateId,function(data){
  • res.redirect('/crud/fetch');
  • console.log(data.affectedRows + " record(s) updated");
  • });
  • },
  • deleteCrud:function(req,res){
  • const deleteId=req.params.id;
  • crudModel.deleteCrud(deleteId,function(data){
  • res.redirect('/crud/fetch');
  • console.log(data.affectedRows + " record(s) updated");
  • });
  • }
  • }
File Path: views/crud-form.ejs

Comments

Post a Comment

Popular posts from this blog

4 Ways to Communicate Across Browser Tabs in Realtime

1. Local Storage Events You might have already used LocalStorage, which is accessible across Tabs within the same application origin. But do you know that it also supports events? You can use this feature to communicate across Browser Tabs, where other Tabs will receive the event once the storage is updated. For example, let’s say in one Tab, we execute the following JavaScript code. window.localStorage.setItem("loggedIn", "true"); The other Tabs which listen to the event will receive it, as shown below. window.addEventListener('storage', (event) => { if (event.storageArea != localStorage) return; if (event.key === 'loggedIn') { // Do something with event.newValue } }); 2. Broadcast Channel API The Broadcast Channel API allows communication between Tabs, Windows, Frames, Iframes, and  Web Workers . One Tab can create and post to a channel as follows. const channel = new BroadcastChannel('app-data'); channel.postMessage(data); And oth...
Post a Comment
Read more

Certbot SSL configuration in ubuntu

  Introduction Let’s Encrypt is a Certificate Authority (CA) that provides an easy way to obtain and install free  TLS/SSL certificates , thereby enabling encrypted HTTPS on web servers. It simplifies the process by providing a software client, Certbot, that attempts to automate most (if not all) of the required steps. Currently, the entire process of obtaining and installing a certificate is fully automated on both Apache and Nginx. In this tutorial, you will use Certbot to obtain a free SSL certificate for Apache on Ubuntu 18.04 and set up your certificate to renew automatically. This tutorial will use a separate Apache virtual host file instead of the default configuration file.  We recommend  creating new Apache virtual host files for each domain because it helps to avoid common mistakes and maintains the default files as a fallback configuration. Prerequisites To follow this tutorial, you will need: One Ubuntu 18.04 server set up by following this  initial ...
Post a Comment
Read more

Working with Node.js streams

  Introduction Streams are one of the major features that most Node.js applications rely on, especially when handling HTTP requests, reading/writing files, and making socket communications. Streams are very predictable since we can always expect data, error, and end events when using streams. This article will teach Node developers how to use streams to efficiently handle large amounts of data. This is a typical real-world challenge faced by Node developers when they have to deal with a large data source, and it may not be feasible to process this data all at once. This article will cover the following topics: Types of streams When to adopt Node.js streams Batching Composing streams in Node.js Transforming data with transform streams Piping streams Error handling Node.js streams Types of streams The following are four main types of streams in Node.js: Readable streams: The readable stream is responsible for reading data from a source file Writable streams: The writable stream is re...
Post a Comment
Read more