Virus Corona (Covid-19)

Dapatkan Informasi terbaru

Contoh CRUD Mysql dengan Node JS Express

Pikolo 17 Aug 2021 26

Node js express contoh crud dengan MySQL. Dalam tutorial ini, Anda akan belajar bagaimana membangun aplikasi crud (create, update, read, delete) di node js express js framework dengan database MySQL.

Tutorial crud Node js express MySQL akan membuat aplikasi manajemen pelanggan. Di mana Anda dapat menambahkan pelanggan, mengedit pelanggan dan juga menghapus pelanggan dari database menggunakan node js express dengan database MySQL.

Operasi crud di node js menggunakan tutorial MySQL express akan membantu membuat aplikasi operasi crud di node js express dengan database MySQL.

Langkah 1 – Buat Aplikasi Node JS

Jalankan perintah berikut di terminal untuk membuat aplikasi node js express:

Gunakan perintah di bawah ini dan buat proyek ekspres Anda dengan nama expressfirst

express --view=ejs expressfirst

Setelah berhasil membuat folder expressfirst di sistem Anda. Selanjutnya ikuti perintah di bawah ini dan instal node js di proyek Anda:

cd expressfirst

npm install  

Langkah 2 – Instal flash, validator, session, override MySQL Libraries

Instal flash,validator,session,override MySQL Libraries ke node Anda js express crud + aplikasi MySQL dengan menjalankan perintah berikut di terminal:

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

express-flash

Flash adalah perpanjangan dari connect-flash dengan kemampuan untuk mendefinisikan pesan flash dan merendernya tanpa mengarahkan permintaan.

Dalam node ini js mysql crud tutorial express flash digunakan untuk menampilkan peringatan, kesalahan dan pesan informasi

 

express-session

Express-session digunakan untuk membuat session seperti pada PHP. Dalam tutorial node js mysql crud ini, session dibutuhkan sebagai kebutuhan express dari express-flash.

 

express-validator

Validator ekspres digunakan untuk memvalidasi data formulir yang mudah digunakan. express-validator sangat efektif dan efisien untuk mempercepat pembuatan aplikasi.

 

method-override

NPM digunakan untuk menjalankan metode DELETE dan PUT dari form HTML. Di beberapa browser web hanya mendukung metode GET dan POST.

 

MySQL

Driver untuk menghubungkan node.js dengan MySQL

Langkah 3 – Hubungkan ke Aplikasi Node js Express CRUD

Buat satu nama folder lib dan buat nama file baru db.js di dalam folder ini. Kami akan menghubungkan node js ke mysql menggunakan file ini

 

lib/db.js

var mysql=require('mysql');
 var connection=mysql.createConnection({
   host:'localhost',
   user:'your username',
   password:'your password',
   database:'your database name'
 });
connection.connect(function(error){
   if(!!error){
     console.log(error);
   }else{
     console.log('Connected!:)');
   }
 });  
module.exports = connection; 

Langkah 4 – Buat File Server.js

Kunjungi direktori root aplikasi Anda dan buat nama file baru server.js Dan tambahkan kode berikut ke dalamnya:

 var createError = require('http-errors');
 var express = require('express');
 var path = require('path');
 var cookieParser = require('cookie-parser');
 var logger = require('morgan');
 var expressValidator = require('express-validator');
 var flash = require('express-flash');
 var session = require('express-session');
 var bodyParser = require('body-parser');
 
 var mysql = require('mysql');
 var connection  = require('./lib/db');
 
 var indexRouter = require('./routes/index');
 var usersRouter = require('./routes/users');
 var customersRouter = require('./routes/customers');
 
 var app = express();
 
// view engine setup
 app.set('views', path.join(__dirname, 'views'));
 app.set('view engine', 'ejs');
 
 app.use(logger('dev'));
 app.use(bodyParser.json());
 app.use(bodyParser.urlencoded({ extended: true }));
 app.use(cookieParser());
 app.use(express.static(path.join(__dirname, 'public')));
 
 app.use(session({ 
     secret: '123456cat',
     resave: false,
     saveUninitialized: true,
     cookie: { maxAge: 60000 }
 }))
 
 app.use(flash());
 app.use(expressValidator());
 
 app.use('/', indexRouter);
 app.use('/users', usersRouter);
 app.use('/customers', customersRouter);
 
 // catch 404 and forward to error handler
 app.use(function(req, res, next) {
   next(createError(404));
 });
 
 // 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;

Langkah 5 – Buat Rute CRUD

Buat nama file route mentah customers.js, jadi kunjungi di dalam folder route dan buat file ini. Kemudian tambahkan kode berikut ke dalamnya:

var express = require('express');
var router = express.Router();
var connection  = require('../lib/db');
/* GET home page. */
router.get('/', function(req, res, next) {
connection.query('SELECT * FROM customers ORDER BY id desc',function(err,rows)     {
if(err){
req.flash('error', err); 
res.render('customers',{page_title:"Customers - Node.js",data:''});   
}else{
res.render('customers',{page_title:"Customers - Node.js",data:rows});
}
});
});
// SHOW ADD USER FORM
router.get('/add', function(req, res, next){    
// render to views/user/add.ejs
res.render('customers/add', {
title: 'Add New Customers',
name: '',
email: ''       
})
})
// ADD NEW USER POST ACTION
router.post('/add', function(req, res, next){    
req.assert('name', 'Name is required').notEmpty()           //Validate name
req.assert('email', 'A valid email is required').isEmail()  //Validate email
var errors = req.validationErrors()
if( !errors ) {   //No errors were found.  Passed Validation!
var user = {
name: req.sanitize('name').escape().trim(),
email: req.sanitize('email').escape().trim()
}
connection.query('INSERT INTO customers SET ?', user, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// render to views/user/add.ejs
res.render('customers/add', {
title: 'Add New Customer',
name: user.name,
email: user.email                    
})
} else {                
req.flash('success', 'Data added successfully!');
res.redirect('/customers');
}
})
}
else {   //Display errors to user
var error_msg = ''
errors.forEach(function(error) {
error_msg += error.msg + '<br>'
})                
req.flash('error', error_msg)        
/**
* Using req.body.name 
* because req.param('name') is deprecated
*/
res.render('customers/add', { 
title: 'Add New Customer',
name: req.body.name,
email: req.body.email
})
}
})
// SHOW EDIT USER FORM
router.get('/edit/(:id)', function(req, res, next){
connection.query('SELECT * FROM customers WHERE id = ' + req.params.id, function(err, rows, fields) {
if(err) throw err
// if user not found
if (rows.length <= 0) {
req.flash('error', 'Customers not found with id = ' + req.params.id)
res.redirect('/customers')
}
else { // if user found
// render to views/user/edit.ejs template file
res.render('customers/edit', {
title: 'Edit Customer', 
//data: rows[0],
id: rows[0].id,
name: rows[0].name,
email: rows[0].email                    
})
}            
})
})
// EDIT USER POST ACTION
router.post('/update/:id', function(req, res, next) {
req.assert('name', 'Name is required').notEmpty()           //Validate nam           //Validate age
req.assert('email', 'A valid email is required').isEmail()  //Validate email
var errors = req.validationErrors()
if( !errors ) {   
var user = {
name: req.sanitize('name').escape().trim(),
email: req.sanitize('email').escape().trim()
}
connection.query('UPDATE customers SET ? WHERE id = ' + req.params.id, user, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// render to views/user/add.ejs
res.render('customers/edit', {
title: 'Edit Customer',
id: req.params.id,
name: req.body.name,
email: req.body.email
})
} else {
req.flash('success', 'Data updated successfully!');
res.redirect('/customers');
}
})
}
else {   //Display errors to user
var error_msg = ''
errors.forEach(function(error) {
error_msg += error.msg + '<br>'
})
req.flash('error', error_msg)
/**
* Using req.body.name 
* because req.param('name') is deprecated
*/
res.render('customers/edit', { 
title: 'Edit Customer',            
id: req.params.id, 
name: req.body.name,
email: req.body.email
})
}
})
// DELETE USER
router.get('/delete/(:id)', function(req, res, next) {
var user = { id: req.params.id }
connection.query('DELETE FROM customers WHERE id = ' + req.params.id, user, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// redirect to users list page
res.redirect('/customers')
} else {
req.flash('success', 'Customer deleted successfully! id = ' + req.params.id)
// redirect to users list page
res.redirect('/customers')
}
})
})
module.exports = router;

Langkah 6 – Buat View

Pertama kita akan membuat satu folder nama customer di dalam folder views.

Selanjutnya kita perlu membuat tiga tampilan nama file add.ejs, edit.ejs dan index.ejs. Kami akan membuat tiga file tampilan di dalam folder views/customers.

 

Buat file pertama index.ejs

File Index.ejs, kami akan menampilkan daftar customers.

<!DOCTYPE html>
<html>
<head>
<title>Customers</title>
<link rel='stylesheet' href='/stylesheets/style.css' />
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<div>
<a href="/" class="btn btn-primary ml-3">Home</a>  
<a href="/customers/add" class="btn btn-secondary ml-3">New Customer</a> 
<a href="/customers" class="btn btn-info ml-3">Customer List</a>
</div>    
<!--   <% if (messages.error) { %>
<p style="color:red"><%- messages.error %></p>
<% } %> -->
<% if (messages.success) { %>
<p class="alert alert-success mt-4"><%- messages.success %></p>
<% } %>  
<br>
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">Name</th>
<th scope="col">Email</th>
<th width="200px">Action</th>
</tr>
</thead>
<tbody>
<% if(data.length){
for(var i = 0; i< data.length; i++) {%>  
<tr>
<th scope="row"><%= (i+1) %></th>
<td><%= data[i].name%></td>
<td><%= data[i].email%></td>
<td>
<a class="btn btn-success edit" href="../customers/edit/<%=data[i].id%>">Edit</a>                       
<a class="btn btn-danger delete" onclick="return alert('Are You sure?')" href="../customers/delete/<%=data[i].id%>">Delete</a>                       
</td>
</tr>
<% }
}else{ %>
<tr>
<td colspan="3">No user</td>
</tr>
<% } %>    
</tbody>
</table>
</body>
</html>

Buat nama file kedua add.ejs

File Add.ejs, kita akan membuat form untuk mengirim data ke database.

<!DOCTYPE html>
<html>
<head>
<title>Customers</title>
<link rel='stylesheet' href='/stylesheets/style.css' />
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<% if (messages.error) { %>
<p style="color:red"><%- messages.error %></p>
<% } %>
<% if (messages.success) { %>
<p style="color:green"><%- messages.success %></p>
<% } %>
<form action="/customers/add" method="post" name="form1">
<div class="form-group">
<label for="exampleInputPassword1">Name</label>
<input type="text" class="form-control" name="name" id="name" value="" placeholder="Name">
</div>
<div class="form-group">
<label for="exampleInputEmail1">Email address</label>
<input type="email" name="email" class="form-control" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="">
</div>
<input type="submit" class="btn btn-primary" value="Add">
</form>
</body>
</html>

Buat nama file ketiga edit.ejs

Selanjutnya buat file edit.ejs terakhir, kita akan mengedit data di form ini.

<!DOCTYPE html>
<html>
<head>
<title>Customers</title>
<link rel='stylesheet' href='/stylesheets/style.css' />
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<form action="/customers/update/<%= id %>" method="post" name="form1">
<div class="form-group">
<label for="exampleInputPassword1">Name</label>
<input type="text" class="form-control" name="name" id="name" value="<%= name %>" placeholder="Name">
</div>
<div class="form-group">
<label for="exampleInputEmail1">Email address</label>
<input type="email" class="form-control" name="email" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="<%= email %>">
</div>
<button type="submit" class="btn btn-info">Update</button>
</form>
</body>
</html>

Langkah 7 – Start Node Express js Crud + MySQL app

run the below command

npm start

after run this command open your browser and hit 

http://127.0.0.1:3000/customers

 

Node js + express mentah dengan tutorial MySQL. Anda telah belajar bagaimana membangun aplikasi CRUD (Create, Read, Update, Delete) dengan database mysql di node express js.

Node js express crud tutorial mysql akan terlihat seperti:

Tags

example crud mysql node js express node js express js mysql

Related Stories


Latest Stories