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.
Daftar Isi
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 expressfirstSetelah 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 --saveexpress-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:
andriajah