4. ExpressJS & MySQL

tech

Table of Content

• Check Koneksi
  • Create Database
  • Perbarui database.js
  • Menambahkan Table
  • Tambah Data
  • Membaca Data
  • Update Data
  • Menghapus Data

Check Koneksi

util/database.js

const mysql = require("mysql");
 
const db = mysql.createConnection({
	host: "localhost",
	user: "root",
	password: ""
});
 
db.connect(function(err){
	if(err) throw err;
	console.info("mysql connected!");
})
 
module.exports = db;
 

Jika user root tidak ada, maka akan dilempar error exception. Jangan lupa untuk export module nya. Supaya dapat digunakan di modul lain (modular).

Untuk latihan buat 1 file lagi bernama playground.js

Create Database

const db = require("./database.js");
 
const sql = "CREATE DATABASE test_express_js";
 
db.query(sql, function(err, result){
	if(err) throw err;
	console.info("database created!");
});

Perbarui database.js

const mysql = require("mysql");
 
const db = mysql.createConnection({
	host: "localhost",
	user: "root",
	password: "",
	database: "test_express_js"
});
 
db.connect(function(err){
	if(err) throw err;
	console.info("mysql connected!");
})
 
module.exports = db;

Menambahkan Table

const db = require("./database.js");
 
const sql = `CREATE TABLE biodatas
(
	id int NOT NULL AUTO_INCREMENT,
	nama VARCHAR(50),
	umur INTEGER(2),
	hobi VARCHAR(25),
	PRIMARY KEY (id)
)`;
 
db.query(sql, function(err, result){
	if(err) throw err;
	console.info("table created!");
});

Perlu di Note : Jika SQL lebih dari 1 baris, gunakan backtick!

Tambah Data

Bagaimana jika ingin menambahkan data lebih dari 1?

const db = require("./database.js");
 
const sql = 'INSERT INTO biodatas (nama, umur, hobi) VALUES ?';
 
const data = [
	['Andaru', 22, 'Makan'],
	['Fahri', 22, 'Tidur'],
	['Bagus', 22, 'Makan']
];
db.query(sql, [data], function(err, result){
	if(err) throw err;
	console.info("record created : "+result.affectedRows);
});

Membaca Data

const db = require("./database.js");
 
const sql = "SELECT * FROM biodatas";
 
db.query(sql, function(err, result){
	if(err) throw err;
	console.info(result);
});

Update Data

const db = require("./database.js");
 
const sql = `UPDATE biodatas
	SET nama='Triadi', umur='21', hobi='Ngoding'
	WHERE id=1`;
 
db.query(sql, function(err, result){
	if(err) throw err;
	console.info(result);
});

Menghapus Data

const db = require("./database.js");
 
const sql = 'DELETE FROM biodatas WHERE id=1';
 
db.query(sql, function(err, result){
	if(err) throw err;
	console.info("record deleted!");
});

Referensi : https://www.petanikode.com/nodejs-mysql/