Create CRUD Operations in Node.js Express
Before Getting Started this Script, You must do the following things.
- You have to Install Basic Express Application like the following project folder structure.
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 themyapp
express folder - Create
database.js
file in theconfig
folder - Use the following script in the
database.js
file to connect Node.js express to MySQL Database. - load
database.js
in thecrud-model.js
by usingvar 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 created
easily.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 thecrud
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 Fetched
easily.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 formUpdate
Script is written in the following Model, View,& Controller Pattern. So, User Input data can be Updated
easily.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 thecrud
table & edit it on the basis ofid
- 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 tableDelete
Script is written in the following Model, View,& Controller Pattern. So, User Input data can be Deleted
easily.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