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

Popular posts from this blog

How to use Ngx-Charts in Angular ?

Charts helps us to visualize large amount of data in an easy to understand and interactive way. This helps businesses to grow more by taking important decisions from the data. For example, e-commerce can have charts or reports for product sales, with various categories like product type, year, etc. In angular, we have various charting libraries to create charts.  Ngx-charts  is one of them. Check out the list of  best angular chart libraries .  In this article, we will see data visualization with ngx-charts and how to use ngx-charts in angular application ? We will see, How to install ngx-charts in angular ? Create a vertical bar chart Create a pie chart, advanced pie chart and pie chart grid Introduction ngx-charts  is an open-source and declarative charting framework for angular2+. It is maintained by  Swimlane . It is using Angular to render and animate the SVG elements with all of its binding and speed goodness and uses d3 for the excellent math functio...

Understand Angular’s forRoot and forChild

  forRoot   /   forChild   is a pattern for singleton services that most of us know from routing. Routing is actually the main use case for it and as it is not commonly used outside of it, I wouldn’t be surprised if most Angular developers haven’t given it a second thought. However, as the official Angular documentation puts it: “Understanding how  forRoot()  works to make sure a service is a singleton will inform your development at a deeper level.” So let’s go. Providers & Injectors Angular comes with a dependency injection (DI) mechanism. When a component depends on a service, you don’t manually create an instance of the service. You  inject  the service and the dependency injection system takes care of providing an instance. import { Component, OnInit } from '@angular/core'; import { TestService } from 'src/app/services/test.service'; @Component({ selector: 'app-test', templateUrl: './test.component.html', styleUrls: ['./test.compon...

How to solve Puppeteer TimeoutError: Navigation timeout of 30000 ms exceeded

During the automation of multiple tasks on my job and personal projects, i decided to move on  Puppeteer  instead of the old school PhantomJS. One of the most usual problems with pages that contain a lot of content, because of the ads, images etc. is the load time, an exception is thrown (specifically the TimeoutError) after a page takes more than 30000ms (30 seconds) to load totally. To solve this problem, you will have 2 options, either to increase this timeout in the configuration or remove it at all. Personally, i prefer to remove the limit as i know that the pages that i work with will end up loading someday. In this article, i'll explain you briefly 2 ways to bypass this limitation. A. Globally on the tab The option that i prefer, as i browse multiple pages in the same tab, is to remove the timeout limit on the tab that i use to browse. For example, to remove the limit you should add: await page . setDefaultNavigationTimeout ( 0 ) ;  COPY SNIPPET The setDefaultNav...