Tutorial Create CRUD With Node.Js , Express, MySQL And AdminLTE For Beginner

By Sigit Prasetya Nugroho ∙ November 18, 2016 ∙ Javascript ∙ 24 Comments

Continuing the tutorial on node.js express tutorial, I will share tutorial to create CRUD using MySQL, node.js, express and adminLTE. If you haven’t read my previous article regarding how to transfer adminLTE to jade template engine in express.js, please read it first because this article is a continuation of the article. In this tutorial, I will create a simple web application to create, read, update and delete customer data. It is important to note, this tutorial for beginners, so for those who are already expert could advise me through the comments field.

CRUD is a standard concept for manufacturing information system application. In this blog I have been several times made articles about CRUD using PHP, maybe if interested you can read

– Tutorial PHP Class For Simple CRUD with PDO For Beginners.
– Create Simple CRUD with Datatables, Jquery, and AdminLTE

Table of Contents

1 Let’s start create CRUD with Node.Js , Express, MySQL and AdminLTE Tutorial for beginner
- 1.1 Connecting template adminLTE to express js
- 1.2 Thus article about Create CRUD With Node.Js , Express, MySQL, AdminLTE Tutorial For Beginner. Hopefully useful.

Let’s start create CRUD with Node.Js , Express, MySQL and AdminLTE Tutorial for beginner

As early initialization, create a database with the name nodejs and a customer table.

Insert table customer with the data below

insert into 'customer' ('id', 'name', 'address', 'email', 'phone') values('6','Richard Gere','Washington DC Street Block C 85','richardgere@yahoo.com','038372636232');
insert into 'customer' ('id', 'name', 'address', 'email', 'phone') values('9','Tukul Arwana','Jln Lingkaran subur 58','tukul@yahoo.com','038373273262');
insert into 'customer' ('id', 'name', 'address', 'email', 'phone') values('10','Taylor Swift','Hollywoord','tswift@yahoo.com','039223232323');
insert into 'customer' ('id', 'name', 'address', 'email', 'phone') values('15','Sigit Prasetya','Indonesia central of java','sigit@gmail.com','0271 85858588');

insert into 'customer' ('id', 'name', 'address', 'email', 'phone') values('6','Richard Gere','Washington DC Street Block C 85','richardgere@yahoo.com','038372636232');

insert into 'customer' ('id', 'name', 'address', 'email', 'phone') values('9','Tukul Arwana','Jln Lingkaran subur 58','tukul@yahoo.com','038373273262');

insert into 'customer' ('id', 'name', 'address', 'email', 'phone') values('10','Taylor Swift','Hollywoord','tswift@yahoo.com','039223232323');

insert into 'customer' ('id', 'name', 'address', 'email', 'phone') values('15','Sigit Prasetya','Indonesia central of java','sigit@gmail.com','0271 85858588');

Create a new express project with expressproject name (if you have read the previous tutorial, skip this section)

 express --view=jade expressproject

1	express --view=jade expressproject

Then enter the expressproject folder and do the following command

cd myapp
npm install

1 2	cd myapp npm install

Package.json which I use in this example are as follows

Install all package/plugin that is required

npm install express-flash --save
npm install express-session --save
npm install express-validator --save
npm install method-override --save
npm install mysql --save

npm install express-flash --save

npm install express-session --save

npm install express-validator --save

npm install method-override --save

npm install mysql --save

A little explanation :

express-flash
Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.
In this tutorial express flash is used to display a warning, error and information message
express-session
Express-session is used to made a session as in PHP. In this tutorial, session is needed as the express requirement of express-flash.
express-validator
To make the validation of the input form, already provided plugin which is very comprehensive and easy to use, express-validator highly effective and efficient way to accelerate the creation of applications.
method-override
NPM is used to run a DELETE and PUT method from an HTML form. Because now, in several web browsers only support GET and POST methods.
MySQL
Driver to connect node.js with MySQL

Connecting template adminLTE to express js

Please download the source adminLTE in my previous tutorial here and ensure order in the expressproject folder as shown below

Then please adjust your app.js file as in the following code:

var express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');
var flash = require('express-flash');
var session = require('express-session');
var index = require('./routes/index');
var users = require('./routes/users');
var customers = require('./routes/customers');
var expressValidator = require('express-validator');
var methodOverride = require('method-override');

var connection  = require('express-myconnection'); 
var mysql = require('mysql');

var app = express();
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');

// uncomment after placing your favicon in /public
//app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(session({secret:"secretpass123456"}));
app.use(flash());
app.use(expressValidator());
app.use(methodOverride(function(req, res){
 if (req.body && typeof req.body == 'object' && '_method' in req.body) 
  { 
      var method = req.body._method;
      delete req.body._method;
      return method;
    } 
  }));

app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

/*------------------------------------------
    connection peer, register as middleware
    type koneksi : single,pool and request 
-------------------------------------------*/
app.use(
    connection(mysql,{
        host: 'localhost',
        user: 'root', // your mysql user
        password : '123456', // your mysql password
        port : 3306, //port mysql
        database:'nodejs' // your database name
    },'pool') //or single

);

app.use('/', index);
app.use('/customers', customers);
app.use('/users', users);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  var err = new Error('Not Found');
  err.status = 404;
  next(err);
});

// 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;

var express = require('express');

var path = require('path');

var favicon = require('serve-favicon');

var logger = require('morgan');

var cookieParser = require('cookie-parser');

var bodyParser = require('body-parser');

var flash = require('express-flash');

var session = require('express-session');

var index = require('./routes/index');

var users = require('./routes/users');

var customers = require('./routes/customers');

var expressValidator = require('express-validator');

var methodOverride = require('method-override');

var connection = require('express-myconnection');

var mysql = require('mysql');

var app = express();

// view engine setup

app.set('views', path.join(__dirname, 'views'));

app.set('view engine', 'jade');

// uncomment after placing your favicon in /public

//app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));

app.use(logger('dev'));

app.use(bodyParser.json());

app.use(bodyParser.urlencoded({ extended: false }));

app.use(session({secret:"secretpass123456"}));

app.use(flash());

app.use(expressValidator());

app.use(methodOverride(function(req, res){

if (req.body && typeof req.body == 'object' && '_method' in req.body)

{

var method = req.body._method;

delete req.body._method;

return method;

}

}));

app.use(cookieParser());

app.use(express.static(path.join(__dirname, 'public')));

/*------------------------------------------

connection peer, register as middleware

type koneksi : single,pool and request

-------------------------------------------*/

app.use(

connection(mysql,{

host: 'localhost',

user: 'root', // your mysql user

password : '123456', // your mysql password

port : 3306, //port mysql

database:'nodejs' // your database name

},'pool') //or single

);

app.use('/', index);

app.use('/customers', customers);

app.use('/users', users);

// catch 404 and forward to error handler

app.use(function(req, res, next) {

var err = new Error('Not Found');

err.status = 404;

next(err);

});

// 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;

A brief description :

Express-session needs a secret key

 app.use(session({secret:"secretpass123456"}));

1	app.use(session({secret:"secretpass123456"}));

The script below is used to be able to use the DELETE and PUT method on method-override package

app.use(methodOverride(function(req, res){
 if (req.body && typeof req.body == 'object' && '_method' in req.body) 
  { 
      var method = req.body._method;
      delete req.body._method;
      return method;
    } 
  }));

app.use(methodOverride(function(req, res){

if (req.body && typeof req.body == 'object' && '_method' in req.body)

{

var method = req.body._method;

delete req.body._method;

return method;

}

}));

To connect MySQL with Node.js, we need to declare the initial connection with the following code

app.use(
    connection(mysql,{
        host: 'localhost',
        user: 'root', // your mysql user
        password : '123456', // your mysql password
        port : 3306, //port mysql
        database:'nodejs' // your database name
    },'pool') //or single

);

app.use(

connection(mysql,{

host: 'localhost',

user: 'root', // your mysql user

password : '123456', // your mysql password

port : 3306, //port mysql

database:'nodejs' // your database name

},'pool') //or single

);

In this simple tutorial, we will create a CRUD to the customer so that we need to create a new route with the name customers.js

app.use('/', index);
app.use('/customers', customers);
app.use('/users', users);

app.use('/', index);

app.use('/customers', customers);

app.use('/users', users);

The next step creates a new route file in the folder with the name customers.js routes

Read the customer MySQL data

Let customers.js file empty, because first, we will create a new page to display a list of customers.

Create a folder called customer in the folder views, we will create a new view to display the customer list page. Create a file named list.jade and copy the following code

extends ../layout/base 
block content 
	section.content-header 
		h1 
			| Blank Page
			small it all starts here
		ol.breadcrumb 
			li 
				a(href='#') 
					i.fa.fa-dashboard
					| Level 
			li.active Here 
	section.content
		.box
			.box-header.with-border
				h3.box-title
					| Customer
				.box-tools.pull-right
					button(type='button', data-widget='collapse', data-toggle='tooltip',title='collapse').btn.btn-box-tool
						i.fa.fa-minus
			.box-body
				- if(messages.msg_info)
					.alert.alert-success.alert-dismissable
						button.close(type='button', data-dismiss='alert', aria-hidden='true')
							| ×
						h4
							i.icon.fa.fa-check
							| Success!
						| !{messages.msg_info}
				- if(messages.msg_error)
					.alert.alert-danger.alert-dismissable
						button.close(type='button', data-dismiss='alert', aria-hidden='true')
							| ×
						h4
							i.icon.fa.fa-ban
							| Alert!
						| !{messages.msg_error}
				a(href='customers/add').pull-left.btn.btn-primary
					i.fa.fa-plus 
					|  Add Customer
				.data-table
					table.table.table-bordered
						tr
							th
								| No
							th
								| Name
							th
								| Address
							th
								| Phone
							th
								| Email
							th
								| Action
							if messages.msg_error
								| !{messages.msg_error}
							else
								for row, index in data 
									tr
										td #{index+1}
										td #{row.name}
										td #{row.address}
										td #{row.email}
										td #{row.phone}
										td
											div(style='display:inline-block')
												a(href='/customers/edit/#{row.id}').btn.btn-sm.btn-primary
														i.fa.fa-edit
											| 
											div(style='display:inline-block')
												form(method='post', action='/customers/delete/#{row.id}')
													input(type="hidden",name="_method", value="DELETE")
													button.btn.btn-sm.btn-danger(type='submit')
														i.fa.fa-remove

extends ../layout/base

block content

section.content-header

| Blank Page

small it all starts here

ol.breadcrumb

a(href='#')

i.fa.fa-dashboard

| Level

li.active Here

section.content

.box

.box-header.with-border

h3.box-title

| Customer

.box-tools.pull-right

button(type='button', data-widget='collapse', data-toggle='tooltip',title='collapse').btn.btn-box-tool

i.fa.fa-minus

.box-body

- if(messages.msg_info)

.alert.alert-success.alert-dismissable

button.close(type='button', data-dismiss='alert', aria-hidden='true')

| ×

i.icon.fa.fa-check

| Success!

| !{messages.msg_info}

- if(messages.msg_error)

.alert.alert-danger.alert-dismissable

button.close(type='button', data-dismiss='alert', aria-hidden='true')

| ×

i.icon.fa.fa-ban

| Alert!

| !{messages.msg_error}

a(href='customers/add').pull-left.btn.btn-primary

i.fa.fa-plus

| Add Customer

.data-table

table.table.table-bordered

| No

| Name

| Address

| Phone

| Email

| Action

if messages.msg_error

| !{messages.msg_error}

else

for row, index in data

td #{index+1}

td #{row.name}

td #{row.address}

td #{row.email}

td #{row.phone}

div(style='display:inline-block')

a(href='/customers/edit/#{row.id}').btn.btn-sm.btn-primary

i.fa.fa-edit

div(style='display:inline-block')

form(method='post', action='/customers/delete/#{row.id}')

input(type="hidden",name="_method", value="DELETE")

button.btn.btn-sm.btn-danger(type='submit')

i.fa.fa-remove

Note : Remember, writing jade template engine is A clean, whitespace-sensitive template. So it needs to be carefully and careful

Then open routes customers.js that we make before and copy the following script:

var express = require('express');
var router = express.Router();

/* GET Customer page. */

router.get('/', function(req, res, next) {
	req.getConnection(function(err,connection){
		var query = connection.query('SELECT * FROM customer',function(err,rows)
		{
			if(err)
				var errornya  = ("Error Selecting : %s ",err );   
			req.flash('msg_error', errornya);   
			res.render('customer/list',{title:"Customers",data:rows});
		});
     });
});
module.exports = router;

var express = require('express');

var router = express.Router();

/* GET Customer page. */

router.get('/', function(req, res, next) {

req.getConnection(function(err,connection){

var query = connection.query('SELECT * FROM customer',function(err,rows)

{

if(err)

var errornya = ("Error Selecting : %s ",err );

req.flash('msg_error', errornya);

res.render('customer/list',{title:"Customers",data:rows});

});

module.exports = router;

Run the following command in a terminal or command prompt

 DEBUG=expressproject:* npm start

1	DEBUG=expressproject:* npm start

If using windows add the character ” & “

 DEBUG=expressproject:* & npm start

1	DEBUG=expressproject:* & npm start

Then open your browser and enter the following URL address to open a customer list
http://localhost:3000/customers

A simple explanation :

router.get('/', function(req, res, next) {
	req.getConnection(function(err,connection){
		var query = connection.query('SELECT * FROM customer',function(err,rows)
		{
			if(err)
				var errornya  = ("Error Selecting : %s ",err );   
			req.flash('msg_error', errornya);   
			res.render('customer/list',{title:"Customers",data:rows});
		});

         //console.log(query.sql);
     });
});

router.get('/', function(req, res, next) {

req.getConnection(function(err,connection){

var query = connection.query('SELECT * FROM customer',function(err,rows)

{

if(err)

var errornya = ("Error Selecting : %s ",err );

req.flash('msg_error', errornya);

res.render('customer/list',{title:"Customers",data:rows});

});

//console.log(query.sql);

});

connection.query used to run a MySQL query. Error when doing MySQL query will be accommodated into the “err” variable. Then if there is an error, to display the error message, using the express-flash req.flash (‘msg_error’, error). Mysql query data is collected into the “rows” variable as a JSON form.

To display the customer data on file list.jade, looping required as follows

for row, index in data 
									tr
										td #{index+1}
										td #{row.name}
										td #{row.address}
										td #{row.email}
										td #{row.phone}
										td
											div(style='display:inline-block')
												a(href='/customers/edit/#{row.id}').btn.btn-sm.btn-primary
														i.fa.fa-edit
											| 
											div(style='display:inline-block')
												form(method='post', action='/customers/delete/#{row.id}')
													input(type="hidden",name="_method", value="DELETE")
													button.btn.btn-sm.btn-danger(type='submit')
														i.fa.fa-remove

for row, index in data

td #{index+1}

td #{row.name}

td #{row.address}

td #{row.email}

td #{row.phone}

div(style='display:inline-block')

a(href='/customers/edit/#{row.id}').btn.btn-sm.btn-primary

i.fa.fa-edit

div(style='display:inline-block')

form(method='post', action='/customers/delete/#{row.id}')

input(type="hidden",name="_method", value="DELETE")

button.btn.btn-sm.btn-danger(type='submit')

i.fa.fa-remove

In the delete button, there is an input with hidden type that will be used to override the DELETE method

The session read customer data from mysql has been completed, the next step will create a new customer

CREATE a new customer

create a new view page as add-customer.js in the folder views/customer/add-customer.js and copy the following code

extends ../layout/base 
block content 
	section.content-header 
		h1 
			| Blank Page
			small it all starts here
		ol.breadcrumb 
			li 
				a(href='#') 
					i.fa.fa-dashboard
					| Level 
			li.active Here 
	section.content
		.box
			.box-header.with-border
				h3.box-title
					| Customer
				.box-tools.pull-right
					button(type='button', data-widget='collapse', data-toggle='tooltip',title='collapse').btn.btn-box-tool
						i.fa.fa-minus
					
			.box-body
				form(role='form',method='post' action='/customers/add')
					.row
						
						.col-xs-6
							.box-body
								- if (messages.msg_error) 
									.alert.alert-danger.alert-dismissable 
										button.close(type='button', data-dismiss='alert', aria-hidden='true') 
											| ×
										h4
											i.icon.fa.fa-ban
											| Alert! 
										| !{messages.msg_error}
								.form-group
									label
										| Name
									input(type='text',value='#{name}',name='name',placeholder='Enter Name', autofocus).form-control
								.form-group
									label
										| Address
									input(type='text',name='address',placeholder='Enter address').form-control
								.form-group
									label
										| Email
									input(type='text',value='#{email}',name='email',placeholder='Enter Address').form-control
								.form-group
									label
										| Phone
									input(type='text',name='phone',placeholder='Enter Phone').form-control
							.box-footer
								button(type='submit').btn.btn-primary
									| Submit
								a(href='/customers').btn.btn-primary.pull-right
									| Back

extends ../layout/base

block content

section.content-header

| Blank Page

small it all starts here

ol.breadcrumb

a(href='#')

i.fa.fa-dashboard

| Level

li.active Here

section.content

.box

.box-header.with-border

h3.box-title

| Customer

.box-tools.pull-right

button(type='button', data-widget='collapse', data-toggle='tooltip',title='collapse').btn.btn-box-tool

i.fa.fa-minus

.box-body

form(role='form',method='post' action='/customers/add')

.row

.col-xs-6

.box-body

- if (messages.msg_error)

.alert.alert-danger.alert-dismissable

button.close(type='button', data-dismiss='alert', aria-hidden='true')

| ×

i.icon.fa.fa-ban

| Alert!

| !{messages.msg_error}

.form-group

label

| Name

input(type='text',value='#{name}',name='name',placeholder='Enter Name', autofocus).form-control

.form-group

label

| Address

input(type='text',name='address',placeholder='Enter address').form-control

.form-group

label

| Email

input(type='text',value='#{email}',name='email',placeholder='Enter Address').form-control

.form-group

label

| Phone

input(type='text',name='phone',placeholder='Enter Phone').form-control

.box-footer

button(type='submit').btn.btn-primary

| Submit

a(href='/customers').btn.btn-primary.pull-right

| Back

Then add the script on the following customer.js routes

router.post('/add', function(req, res, next) {
	req.assert('name', 'Please fill the name').notEmpty();
	var errors = req.validationErrors();
	if (!errors) {

		v_name = req.sanitize( 'name' ).escape().trim(); 
		v_email = req.sanitize( 'email' ).escape().trim();
		v_address = req.sanitize( 'address' ).escape().trim();
		v_phone = req.sanitize( 'phone' ).escape();

		var customer = {
			name: v_name,
			address: v_address,
			email: v_email,
			phone : v_phone
		}

		var insert_sql = 'INSERT INTO customer SET ?';
		req.getConnection(function(err,connection){
			var query = connection.query(insert_sql, customer, function(err, result){
				if(err)
				{
					var errors_detail  = ("Error Insert : %s ",err );   
					req.flash('msg_error', errors_detail); 
					res.render('customer/add-customer', 
					{ 
						name: req.param('name'), 
						address: req.param('address'),
						email: req.param('email'),
						phone: req.param('phone'),
					});
				}else{
					req.flash('msg_info', 'Create customer success'); 
					res.redirect('/customers');
				}		
			});
		});
	}else{
		console.log(errors);
		errors_detail = "

Sory there are error
<ul>";
		for (i in errors) 
		{ 
			error = errors[i]; 
			errors_detail += '
<li>'+error.msg+'</li>

'; 
		} 
		errors_detail += "</ul>

"; 
		req.flash('msg_error', errors_detail); 
		res.render('customer/add-customer', 
		{ 
			name: req.param('name'), 
			address: req.param('address')
		});
	}

});

router.get('/add', function(req, res, next) {
	res.render(	'customer/add-customer', 
	{ 
		title: 'Add New Customer',
		name: '',
		email: '',
		phone:'',
		address:''
	});
});

router.post('/add', function(req, res, next) {

req.assert('name', 'Please fill the name').notEmpty();

var errors = req.validationErrors();

if (!errors) {

v_name = req.sanitize( 'name' ).escape().trim();

v_email = req.sanitize( 'email' ).escape().trim();

v_address = req.sanitize( 'address' ).escape().trim();

v_phone = req.sanitize( 'phone' ).escape();

var customer = {

address: v_address,

email: v_email,

phone : v_phone

}

var insert_sql = 'INSERT INTO customer SET ?';

req.getConnection(function(err,connection){

var query = connection.query(insert_sql, customer, function(err, result){

if(err)

{

var errors_detail = ("Error Insert : %s ",err );

req.flash('msg_error', errors_detail);

res.render('customer/add-customer',

{

address: req.param('address'),

email: req.param('email'),

phone: req.param('phone'),

});

}else{

req.flash('msg_info', 'Create customer success');

res.redirect('/customers');

}

});

}else{

console.log(errors);

errors_detail = "

Sory there are error

<ul>";

for (i in errors)

{

error = errors[i];

errors_detail += '

<li>'+error.msg+'</li>

}

errors_detail += "</ul>

req.flash('msg_error', errors_detail);

res.render('customer/add-customer',

{

address: req.param('address')

});

}

});

router.get('/add', function(req, res, next) {

res.render( 'customer/add-customer',

{

title: 'Add New Customer',

email: '',

phone:'',

address:''

});

Explanation :

When the URL localhost: 3000/customers/add loaded, the server will call the GET method. After pressing the save button, the server will execute the POST method.

req.assert('name', 'Name can').notEmpty();
var errors = req.validationErrors();

1 2	req.assert('name', 'Name can').notEmpty(); var errors = req.validationErrors();

The above code is used to validate the input name. The entire function of validation, you can learn in https://github.com/chriso/validator.js

Save and run the node.js server, if successful it will be as shown below

Until this stage, the process of creating a new customer has been completed. The next stage is the process to update customer data. Let’s continue the following node.js express tutorial

Update the customer data

Make an edit page with name edit.jade on the folder views/customer/edit.jade. Then copy the following code

extends ../layout/base 
block content 
	section.content-header 
		h1 
			| Edit Customer
			small menu for editing customer data
		ol.breadcrumb 
			li 
				a(href='#') 
					i.fa.fa-dashboard
					| Level 
			li.active Here 
	section.content
		.box
			.box-header.with-border
				h3.box-title
					| Customer
				.box-tools.pull-right
					button(type='button', data-widget='collapse', data-toggle='tooltip',title='collapse').btn.btn-box-tool
						i.fa.fa-minus
					
			.box-body
				form(role='form',method='post' action='/customers/edit/#{(id==undefined) ? data.id : id}')
					.row
						
						.col-xs-6
							.box-body
								- if (messages.msg_error) 
									.alert.alert-danger.alert-dismissable 
										button.close(type='button', data-dismiss='alert', aria-hidden='true') 
											| ×
										h4
											i.icon.fa.fa-ban
											| Alert! 
										| !{messages.msg_error}
								- if (messages.msg_info) 
									.alert.alert-success.alert-dismissable 
										button.close(type='button', data-dismiss='alert', aria-hidden='true') 
											| ×
										h4
											i.icon.fa.fa-ban
											| Success! 
										| !{messages.msg_info}
								.form-group
									label
										| Name
									input(type='text',value="#{data.name}",name='name',placeholder='Enter Name', autofocus).form-control
								.form-group
									label
										| Address
									input(type='text',value="#{ (address == undefined) ? data.address : address }",name='address',placeholder='Enter address').form-control
								.form-group
									label
										| Email
									input(type='text',value="#{ (email == undefined) ? data.email : email }",name='email',placeholder='Enter Address').form-control
								.form-group
									label
										| Phone
									input(type='text',value="#{ (phone == undefined) ? data.phone : phone }",name='phone',placeholder='Enter Phone').form-control
							.box-footer
								input(type="hidden",name="_method", value="PUT")
								button(type='submit').btn.btn-primary
									i.fa.fa-pencil 
									|  Edit
								a(href='/customers').btn.btn-primary.pull-right
									| Back

extends ../layout/base

block content

section.content-header

| Edit Customer

small menu for editing customer data

ol.breadcrumb

a(href='#')

i.fa.fa-dashboard

| Level

li.active Here

section.content

.box

.box-header.with-border

h3.box-title

| Customer

.box-tools.pull-right

button(type='button', data-widget='collapse', data-toggle='tooltip',title='collapse').btn.btn-box-tool

i.fa.fa-minus

.box-body

form(role='form',method='post' action='/customers/edit/#{(id==undefined) ? data.id : id}')

.row

.col-xs-6

.box-body

- if (messages.msg_error)

.alert.alert-danger.alert-dismissable

button.close(type='button', data-dismiss='alert', aria-hidden='true')

| ×

i.icon.fa.fa-ban

| Alert!

| !{messages.msg_error}

- if (messages.msg_info)

.alert.alert-success.alert-dismissable

button.close(type='button', data-dismiss='alert', aria-hidden='true')

| ×

i.icon.fa.fa-ban

| Success!

| !{messages.msg_info}

.form-group

label

| Name

input(type='text',value="#{data.name}",name='name',placeholder='Enter Name', autofocus).form-control

.form-group

label

| Address

input(type='text',value="#{ (address == undefined) ? data.address : address }",name='address',placeholder='Enter address').form-control

.form-group

label

| Email

input(type='text',value="#{ (email == undefined) ? data.email : email }",name='email',placeholder='Enter Address').form-control

.form-group

label

| Phone

input(type='text',value="#{ (phone == undefined) ? data.phone : phone }",name='phone',placeholder='Enter Phone').form-control

.box-footer

input(type="hidden",name="_method", value="PUT")

button(type='submit').btn.btn-primary

i.fa.fa-pencil

| Edit

a(href='/customers').btn.btn-primary.pull-right

| Back

Then add a new route for editing data on the file routes/customers.js. In this session, PUT method will be used to update the customer data. Please copy the following code.

router.get('/edit/(:id)', function(req,res,next){
	req.getConnection(function(err,connection){
		var query = connection.query('SELECT * FROM customer where id='+req.params.id,function(err,rows)
		{
			if(err)
			{
				var errornya  = ("Error Selecting : %s ",err );  
				req.flash('msg_error', errors_detail); 
				res.redirect('/customers'); 
			}else
			{
				if(rows.length <=0)
				{
					req.flash('msg_error', "Customer can't be find!"); 
					res.redirect('/customers');
				}
				else
				{	
					console.log(rows);
					res.render('customer/edit',{title:"Edit ",data:rows[0]});

				}
			}

		});
	});
});
router.put('/edit/(:id)', function(req,res,next){
	req.assert('name', 'Please fill the name').notEmpty();
	var errors = req.validationErrors();
	if (!errors) {
		v_name = req.sanitize( 'name' ).escape().trim(); 
		v_email = req.sanitize( 'email' ).escape().trim();
		v_address = req.sanitize( 'address' ).escape().trim();
		v_phone = req.sanitize( 'phone' ).escape();

		var customer = {
			name: v_name,
			address: v_address,
			email: v_email,
			phone : v_phone
		}

		var update_sql = 'update customer SET ? where id = '+req.params.id;
		req.getConnection(function(err,connection){
			var query = connection.query(update_sql, customer, function(err, result){
				if(err)
				{
					var errors_detail  = ("Error Update : %s ",err );   
					req.flash('msg_error', errors_detail); 
					res.render('customer/edit', 
					{ 
						name: req.param('name'), 
						address: req.param('address'),
						email: req.param('email'),
						phone: req.param('phone'),
					});
				}else{
					req.flash('msg_info', 'Update customer success'); 
					res.redirect('/customers/edit/'+req.params.id);
				}		
			});
		});
	}else{

		console.log(errors);
		errors_detail = "

Sory there are error
<ul>";
		for (i in errors) 
		{ 
			error = errors[i]; 
			errors_detail += '
<li>'+error.msg+'</li>

'; 
		} 
		errors_detail += "</ul>

"; 
		req.flash('msg_error', errors_detail); 
		res.render('customer/add-customer', 
		{ 
			name: req.param('name'), 
			address: req.param('address')
		});
	}
});

router.get('/edit/(:id)', function(req,res,next){

req.getConnection(function(err,connection){

var query = connection.query('SELECT * FROM customer where id='+req.params.id,function(err,rows)

{

if(err)

{

var errornya = ("Error Selecting : %s ",err );

req.flash('msg_error', errors_detail);

res.redirect('/customers');

}else

{

if(rows.length <=0)

{

req.flash('msg_error', "Customer can't be find!");

res.redirect('/customers');

}

else

{

console.log(rows);

res.render('customer/edit',{title:"Edit ",data:rows[0]});

}

});

router.put('/edit/(:id)', function(req,res,next){

req.assert('name', 'Please fill the name').notEmpty();

var errors = req.validationErrors();

if (!errors) {

v_name = req.sanitize( 'name' ).escape().trim();

v_email = req.sanitize( 'email' ).escape().trim();

v_address = req.sanitize( 'address' ).escape().trim();

v_phone = req.sanitize( 'phone' ).escape();

var customer = {

address: v_address,

email: v_email,

phone : v_phone

}

var update_sql = 'update customer SET ? where id = '+req.params.id;

req.getConnection(function(err,connection){

var query = connection.query(update_sql, customer, function(err, result){

if(err)

{

var errors_detail = ("Error Update : %s ",err );

req.flash('msg_error', errors_detail);

res.render('customer/edit',

{

address: req.param('address'),

email: req.param('email'),

phone: req.param('phone'),

});

}else{

req.flash('msg_info', 'Update customer success');

res.redirect('/customers/edit/'+req.params.id);

}

});

}else{

console.log(errors);

errors_detail = "

Sory there are error

<ul>";

for (i in errors)

{

error = errors[i];

errors_detail += '

<li>'+error.msg+'</li>

}

errors_detail += "</ul>

req.flash('msg_error', errors_detail);

res.render('customer/add-customer',

{

address: req.param('address')

});

}

});

Run the node.js server and load page localhost:3000/customers, and press the edit button. If successful will be as shown below

If you ask why should use the PUT method to perform the update function?? I found an interesting answer to check out, please visit

http://stackoverflow.com/questions/630453/put-vs-post-in-rest

up here UPDATE customer data stage has been completed.

DELETE the customer data

At this session, the DELETE method will be used. Add routes.delete on customers.js file and copy the following code

router.delete('/delete/(:id)', function(req, res, next) {
	req.getConnection(function(err,connection){
		var customer = {
			id: req.params.id,
		}
		
		var delete_sql = 'delete from customer where ?';
		req.getConnection(function(err,connection){
			var query = connection.query(delete_sql, customer, function(err, result){
				if(err)
				{
					var errors_detail  = ("Error Delete : %s ",err);
					req.flash('msg_error', errors_detail); 
					res.redirect('/customers');
				}
				else{
					req.flash('msg_info', 'Delete Customer Success'); 
					res.redirect('/customers');
				}
			});
		});
	});
});

router.delete('/delete/(:id)', function(req, res, next) {

req.getConnection(function(err,connection){

var customer = {

id: req.params.id,

}

var delete_sql = 'delete from customer where ?';

req.getConnection(function(err,connection){

var query = connection.query(delete_sql, customer, function(err, result){

if(err)

{

var errors_detail = ("Error Delete : %s ",err);

req.flash('msg_error', errors_detail);

res.redirect('/customers');

}

else{

req.flash('msg_info', 'Delete Customer Success');

res.redirect('/customers');

}

});

Save and run Node.js server. Up here the tutorial creates a crud using node.js has been completed. Customers.js overall code like the following code

var express = require('express');
var router = express.Router();

/* GET Customer page. */

router.get('/', function(req, res, next) {
	req.getConnection(function(err,connection){
		var query = connection.query('SELECT * FROM customer',function(err,rows)
		{
			if(err)
				var errornya  = ("Error Selecting : %s ",err );   
			req.flash('msg_error', errornya);   
			res.render('customer/list',{title:"Customers",data:rows});
		});
         //console.log(query.sql);
     });
});

router.delete('/delete/(:id)', function(req, res, next) {
	req.getConnection(function(err,connection){
		var customer = {
			id: req.params.id,
		}
		
		var delete_sql = 'delete from customer where ?';
		req.getConnection(function(err,connection){
			var query = connection.query(delete_sql, customer, function(err, result){
				if(err)
				{
					var errors_detail  = ("Error Delete : %s ",err);
					req.flash('msg_error', errors_detail); 
					res.redirect('/customers');
				}
				else{
					req.flash('msg_info', 'Delete Customer Success'); 
					res.redirect('/customers');
				}
			});
		});
	});
});
router.get('/edit/(:id)', function(req,res,next){
	req.getConnection(function(err,connection){
		var query = connection.query('SELECT * FROM customer where id='+req.params.id,function(err,rows)
		{
			if(err)
			{
				var errornya  = ("Error Selecting : %s ",err );  
				req.flash('msg_error', errors_detail); 
				res.redirect('/customers'); 
			}else
			{
				if(rows.length <=0)
				{
					req.flash('msg_error', "Customer can't be find!"); 
					res.redirect('/customers');
				}
				else
				{	
					console.log(rows);
					res.render('customer/edit',{title:"Edit ",data:rows[0]});

				}
			}

		});
	});
});
router.put('/edit/(:id)', function(req,res,next){
	req.assert('name', 'Please fill the name').notEmpty();
	var errors = req.validationErrors();
	if (!errors) {
		v_name = req.sanitize( 'name' ).escape().trim(); 
		v_email = req.sanitize( 'email' ).escape().trim();
		v_address = req.sanitize( 'address' ).escape().trim();
		v_phone = req.sanitize( 'phone' ).escape();

		var customer = {
			name: v_name,
			address: v_address,
			email: v_email,
			phone : v_phone
		}

		var update_sql = 'update customer SET ? where id = '+req.params.id;
		req.getConnection(function(err,connection){
			var query = connection.query(update_sql, customer, function(err, result){
				if(err)
				{
					var errors_detail  = ("Error Update : %s ",err );   
					req.flash('msg_error', errors_detail); 
					res.render('customer/edit', 
					{ 
						name: req.param('name'), 
						address: req.param('address'),
						email: req.param('email'),
						phone: req.param('phone'),
					});
				}else{
					req.flash('msg_info', 'Update customer success'); 
					res.redirect('/customers/edit/'+req.params.id);
				}		
			});
		});
	}else{

		console.log(errors);
		errors_detail = "

Sory there are error
<ul>";
		for (i in errors) 
		{ 
			error = errors[i]; 
			errors_detail += '
<li>'+error.msg+'</li>

'; 
		} 
		errors_detail += "</ul>

"; 
		req.flash('msg_error', errors_detail); 
		res.render('customer/add-customer', 
		{ 
			name: req.param('name'), 
			address: req.param('address')
		});
	}
});

router.post('/add', function(req, res, next) {
	req.assert('name', 'Please fill the name').notEmpty();
	var errors = req.validationErrors();
	if (!errors) {

		v_name = req.sanitize( 'name' ).escape().trim(); 
		v_email = req.sanitize( 'email' ).escape().trim();
		v_address = req.sanitize( 'address' ).escape().trim();
		v_phone = req.sanitize( 'phone' ).escape();

		var customer = {
			name: v_name,
			address: v_address,
			email: v_email,
			phone : v_phone
		}

		var insert_sql = 'INSERT INTO customer SET ?';
		req.getConnection(function(err,connection){
			var query = connection.query(insert_sql, customer, function(err, result){
				if(err)
				{
					var errors_detail  = ("Error Insert : %s ",err );   
					req.flash('msg_error', errors_detail); 
					res.render('customer/add-customer', 
					{ 
						name: req.param('name'), 
						address: req.param('address'),
						email: req.param('email'),
						phone: req.param('phone'),
					});
				}else{
					req.flash('msg_info', 'Create customer success'); 
					res.redirect('/customers');
				}		
			});
		});
	}else{

		console.log(errors);
		errors_detail = "

Sory there are error
<ul>";
		for (i in errors) 
		{ 
			error = errors[i]; 
			errors_detail += '
<li>'+error.msg+'</li>

'; 
		} 
		errors_detail += "</ul>

"; 
		req.flash('msg_error', errors_detail); 
		res.render('customer/add-customer', 
		{ 
			name: req.param('name'), 
			address: req.param('address')
		});
	}

});

router.get('/add', function(req, res, next) {
	res.render(	'customer/add-customer', 
	{ 
		title: 'Add New Customer',
		name: '',
		email: '',
		phone:'',
		address:''
	});
});

module.exports = router;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

var express = require('express');

var router = express.Router();

/* GET Customer page. */

router.get('/', function(req, res, next) {

req.getConnection(function(err,connection){

var query = connection.query('SELECT * FROM customer',function(err,rows)

{

if(err)

var errornya = ("Error Selecting : %s ",err );

req.flash('msg_error', errornya);

res.render('customer/list',{title:"Customers",data:rows});

});

//console.log(query.sql);

});

router.delete('/delete/(:id)', function(req, res, next) {

req.getConnection(function(err,connection){

var customer = {

id: req.params.id,

}

var delete_sql = 'delete from customer where ?';

req.getConnection(function(err,connection){

var query = connection.query(delete_sql, customer, function(err, result){

if(err)

{

var errors_detail = ("Error Delete : %s ",err);

req.flash('msg_error', errors_detail);

res.redirect('/customers');

}

else{

req.flash('msg_info', 'Delete Customer Success');

res.redirect('/customers');

}

});

router.get('/edit/(:id)', function(req,res,next){

req.getConnection(function(err,connection){

var query = connection.query('SELECT * FROM customer where id='+req.params.id,function(err,rows)

{

if(err)

{

var errornya = ("Error Selecting : %s ",err );

req.flash('msg_error', errors_detail);

res.redirect('/customers');

}else

{

if(rows.length <=0)

{

req.flash('msg_error', "Customer can't be find!");

res.redirect('/customers');

}

else

{

console.log(rows);

res.render('customer/edit',{title:"Edit ",data:rows[0]});

}

});

router.put('/edit/(:id)', function(req,res,next){

req.assert('name', 'Please fill the name').notEmpty();

var errors = req.validationErrors();

if (!errors) {

v_name = req.sanitize( 'name' ).escape().trim();

v_email = req.sanitize( 'email' ).escape().trim();

v_address = req.sanitize( 'address' ).escape().trim();

v_phone = req.sanitize( 'phone' ).escape();

var customer = {

address: v_address,

email: v_email,

phone : v_phone

}

var update_sql = 'update customer SET ? where id = '+req.params.id;

req.getConnection(function(err,connection){

var query = connection.query(update_sql, customer, function(err, result){

if(err)

{

var errors_detail = ("Error Update : %s ",err );

req.flash('msg_error', errors_detail);

res.render('customer/edit',

{

address: req.param('address'),

email: req.param('email'),

phone: req.param('phone'),

});

}else{

req.flash('msg_info', 'Update customer success');

res.redirect('/customers/edit/'+req.params.id);

}

});

}else{

console.log(errors);

errors_detail = "

Sory there are error

<ul>";

for (i in errors)

{

error = errors[i];

errors_detail += '

<li>'+error.msg+'</li>

}

errors_detail += "</ul>

req.flash('msg_error', errors_detail);

res.render('customer/add-customer',

{

address: req.param('address')

});

}

});

router.post('/add', function(req, res, next) {

req.assert('name', 'Please fill the name').notEmpty();

var errors = req.validationErrors();

if (!errors) {

v_name = req.sanitize( 'name' ).escape().trim();

v_email = req.sanitize( 'email' ).escape().trim();

v_address = req.sanitize( 'address' ).escape().trim();

v_phone = req.sanitize( 'phone' ).escape();

var customer = {

address: v_address,

email: v_email,

phone : v_phone

}

var insert_sql = 'INSERT INTO customer SET ?';

req.getConnection(function(err,connection){

var query = connection.query(insert_sql, customer, function(err, result){

if(err)

{

var errors_detail = ("Error Insert : %s ",err );

req.flash('msg_error', errors_detail);

res.render('customer/add-customer',

{

address: req.param('address'),

email: req.param('email'),

phone: req.param('phone'),

});

}else{

req.flash('msg_info', 'Create customer success');

res.redirect('/customers');

}

});

}else{

console.log(errors);

errors_detail = "

Sory there are error

<ul>";

for (i in errors)

{

error = errors[i];

errors_detail += '

<li>'+error.msg+'</li>

}

errors_detail += "</ul>

req.flash('msg_error', errors_detail);

res.render('customer/add-customer',

{

address: req.param('address')

});

}

});

router.get('/add', function(req, res, next) {

res.render( 'customer/add-customer',

{

title: 'Add New Customer',

email: '',

phone:'',

address:''

});

module.exports = router;

Project complete CRUD express mysql above can be downloaded at the following link, please share this article via the download button below to open the download button

Thus article about Create CRUD With Node.Js , Express, MySQL, AdminLTE Tutorial For Beginner. Hopefully useful.

If there is any questions or suggestions, please use the comment fields below.

About Sigit Prasetya Nugroho

This site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.

Comments

Helmi says
February 6, 2017 at 1:55 am
Hi there.
Cann you please help me? Im having problems with calling the function on router.get.
Problems I’m having is “req.getConnection is not a function” when I try to click the page.
Thanks
Reply
- Sigit Prasetya Nugroho says
  February 6, 2017 at 4:40 am
  The code sequences should be correct
  [php]
  var express = require(‘express’);
  var path = require(‘path’);
  var favicon = require(‘serve-favicon’);
  var logger = require(‘morgan’);
  var cookieParser = require(‘cookie-parser’);
  var bodyParser = require(‘body-parser’);
  var flash = require(‘express-flash’);
  var session = require(‘express-session’);
  var index = require(‘./routes/index’);
  var users = require(‘./routes/users’);
  var customers = require(‘./routes/customers’);
  var expressValidator = require(‘express-validator’);
  var methodOverride = require(‘method-override’);
  var connection = require(‘express-myconnection’);
  var mysql = require(‘mysql’);
  …………………..
  ………………….
  app.use(
  connection(mysql,{
  host: ‘localhost’,
  user: ‘root’, // your mysql user
  password : ‘123456’, // your mysql password
  port : 3306, //port mysql
  database:’nodejs’ // your database name
  },’pool’) //or single
  );
  app.use(‘/’, index);
  app.use(‘/customers’, customers);
  app.use(‘/users’, users);
  [/php]
  Reply
Hector says
April 21, 2017 at 10:03 pm
would you mind doing this tutorial using MongoDB instead?
Reply
- Sigit Prasetya Nugroho says
  April 22, 2017 at 5:41 am
  I have not used mongoDB yet, please wait for the next tutorial 🙂
  Reply
Angelo Escasio says
May 8, 2017 at 1:06 pm
Delete route respond 404
Reply
- Sigit Prasetya Nugroho says
  May 8, 2017 at 1:37 pm
  please check again your code 🙂
  Reply
kasur says
May 20, 2017 at 1:30 pm
where code insert id i am error when insert new customer
Reply
- Sigit Prasetya Nugroho says
  May 20, 2017 at 2:49 pm
  Sory i’m forget to explain it… The customer table in id field i set autoincrement and set as primary key 🙂
  Reply
Akshay says
August 9, 2017 at 9:46 am
I get error as it
home/akshay/expressproject/app.js:32
if (req.body && typeof req.body == ‘object’ && ‘_method’ in req.body)
^
Reply
- Sigit Prasetya Nugroho says
  August 10, 2017 at 1:23 am
  hello,
  Please change these code with
  if (req.body && typeof req.body == 'object' && '_method' in req.body)
  1
  if (req.body && typeof req.body == 'object' && '_method' in req.body)
  My WordPress change && with &
  hope fix the problem
  Reply
amuba says
November 5, 2017 at 11:06 am
Hi there,
Could you pleace help me, Im having problem like this:
error: cannot find module ‘express-myconnection’
Reply
- Sigit Prasetya Nugroho says
  November 6, 2017 at 2:45 am
  install
  npm install --save @types/express-myconnection
  1
  npm install --save @types/express-myconnection
  Reply
Matt says
February 27, 2018 at 2:51 pm
I have this problem..
$ node app.js
express-session deprecated undefined resave option; provide resave option app.js
:29:9
express-session deprecated undefined saveUninitialized option; provide saveUnini
tialized option app.js:29:9
Some help?
Reply
- Sigit Prasetya Nugroho says
  February 28, 2018 at 4:16 am
  check this site for this issue
  https://github.com/expressjs/session/issues/438
  Reply
vlai says
March 7, 2018 at 6:58 am
how to add more page, i always get error “Router.use() requires middleware function but got a Object”
after i add “app.use(‘/dashboards’, dashboards);”
on app.js
=====
app.use(‘/’, index);
app.use(‘/customers’, customers);
app.use(‘/users’, users);
app.use(‘/dashboards’, dashboards);
====
Reply
- vlai says
  March 7, 2018 at 7:00 am
  i also add “var dashboards = require(‘./routes/dashboards’);”
  on app.js
  Reply
- Sigit Prasetya Nugroho says
  March 9, 2018 at 1:33 am
  sorry I have not used node and express for a long time, but you can check your express version . i found some solution from stackoverflow:
  – https://stackoverflow.com/questions/27465850/typeerror-router-use-requires-middleware-function-but-got-a-object
  – https://stackoverflow.com/questions/13254549/in-express-what-does-app-router-do-exactly
  hope solve your problems. 🙂
  Reply
AMAR says
April 26, 2018 at 7:47 pm
I configured as per your guidelines . But it doesnot look like fully responsive. In mobile view left menu panel is not hiding
Reply
- Sigit Prasetya Nugroho says
  April 27, 2018 at 1:21 am
  Download my example project and try it again.. 🙂
  Reply
irfan says
August 16, 2018 at 2:16 am
mantap mas
Reply
WynnXin says
December 1, 2018 at 4:28 pm
where can I download your project?
Reply
- Sigit Prasetya Nugroho says
  December 3, 2018 at 6:37 am
  share button and get your project 🙂
  Reply
Richard says
April 19, 2019 at 2:54 pm
Is your code open source? Can I use it in my project and publish in github?
Thanks
Reply
- Sigit Prasetya Nugroho says
  April 20, 2019 at 9:36 am
  yes you can 🙂
  Reply

Let’s start create CRUD with Node.Js , Express, MySQL and AdminLTE Tutorial for beginner

Connecting template adminLTE to express js

Read the customer MySQL data

CREATE a new customer

Update the customer data

DELETE the customer data

Thus article about Create CRUD With Node.Js , Express, MySQL, AdminLTE Tutorial For Beginner. Hopefully useful.

About Sigit Prasetya Nugroho

Reader Interactions

Comments

Leave a Reply Cancel reply