Learning Nodejs # 9: How to Use the MySQL Database on Nodejs

In the previous tutorial, we have made various Nodejs programs such as:

Next we will try to learn how to make the Nodejs program with the database.

This is important to learn, because we will not only store data in variables and files.

We will also store data in the database, so that later we can create a dynamic web.

Nodejs is usually paired with the Mongodb database . But in this tutorial, we will use MySQL.

How to?

Let's learn.

Make Project Nodejs

Create a new directory called nodejs-mysql:

mkdir nodejs-mysql

Later in this directory it will contain the following files:

nodejs-mysql/
├── create_db.js
├── create_table.js
├── db_config.js
├── delete.js
├── insert.js
├── insert_multi.js
├── read.js
└── update.js

Install the MySQL Module

We need a module mysqlto connect Nodejs with MySQL.

This module is not carried by default by Nodejs. Because of that, we have to install it.

Type the following command to install the module mysql:

npm install mysql

Wait until the process is complete.

Connecting Nodejs with MySQL

Create a new file named db_config.js, then fill in the following code:

var mysql = require('mysql');

var db = mysql.createConnection({
    host: "localhost",
    user: "dian",
    password: "kopi"
});

db.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");
});

Note the parameters we give to the method createConnection()...

... there we have to fill in the address host, usermysql, and passwordmysql.

Usually, if you use XAMPP, the user is rootand without a password.

So use this for XAMPP:

var db = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: ""
});

After that, try executing the file db_config.js.

When the message is Connected!displayed, it means that our nodejs program connection with the mysql server is successful .

However, if it fails ...

... the error will appear like this:

Error:

Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'dian'@'localhost' (using password: YES)

This is because I use the wrong password.

Creating a Database with Nodejs

We have successfully connected Nodejs with Mysql. The next step is to query.

Query or SQL (Structured Query Language) is the language used to access MySQL.

First we will try to query to create a database.

What do you know about the query?

Not yet?

Wow, try learning MySQL first ...

The following query is for creating a database:

CREATE DATABASE nama_database;

Now, we will execute this query in the Nodejs program.

Please create a new file named create_db.js, then fill in the following code:

var db = require("./db_config");

db.connect(function(err) {
    if (err) throw err;
    
    let sql = "CREATE DATABASE petani_kopi";
    db.query(sql, function (err, result) {
        if (err) throw err;
        console.log("Database created");
    });
});

In the above code, we import the file db_config.js as a module.

That means we have to export inside db_config.js so we can import it.

Add and change db_config.jsto like this:

var mysql = require('mysql');

var db = mysql.createConnection({
    host: "localhost",
    user: "dian",
    password: "kopi"
});

module.exports = db;

After that, try executing the program create_db.js.

The database was successfully created.

If we check from PHPmyadmin, it will look like this:

The name of the database petani_kopi, because in the SQL code in the program we gave that name.

var db = require("./db_config");

db.connect(function(err) {
    if (err) throw err;
    
    let sql = "CREATE DATABASE petani_kopi";
    db.query(sql, function (err, result) {
        if (err) throw err;
        console.log("Database created");
    });
});

Every time we want to do a SQL query, we have to open the connection first with the function db.connect().

Then in it, we can call the function db.query().

The function db.query()has two parameters that must be given.

The first parameter sqlis a MYSQL query in the form of a string.

... and the second is a function that will be executed when the query is performed.

db.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Database created");
});

This function also has two parameters: errand result.

The parameter errwill be the object that saves errif it fails to query.

Whereas it resultwill be an object that stores query data.

Create MySQL Tables with Nodejs

We already know how to execute a SQL query on Nodejs. Next, we will learn how to create a table.

The method is the same as creating a database. The difference in the query used.

However, before that ...

Please change the contents db_config.jsto something like this:

var mysql = require('mysql');

var db = mysql.createConnection({
    host: "localhost",
    user: "dian",
    password: "kopi",
    database: "petani_kopi"
});

module.exports = db;

In the code, we only add the database name that was created.

After that, create a new file named create_table.js with the contents as follows:

var db = require("./db_config");

db.connect(function(err) {
    if (err) throw err;
    
    let sql = `CREATE TABLE customers 
    (
        id int NOT NULL AUTO_INCREMENT,
        name VARCHAR(255), 
        address VARCHAR(255),
        PRIMARY KEY (id)
    )`;
    db.query(sql, function (err, result) {
        if (err) throw err;
        console.log("Table created");
    });
});

Pay attention to the code above ...

We use this quote to write more than one line query.

These quotes are usually above the Tab button on the keyboard.

This is a technique for writing more than one line in Javascript.

let sql = `CREATE TABLE customers 
    (
        id int NOT NULL AUTO_INCREMENT,
        name VARCHAR(255), 
        address VARCHAR(255),
        PRIMARY KEY (id)
    )`;

In the query, we will create a table with names , customerscolumns , and .idnameaddress

Okay, now we try to execute:

... and try checking again at Phpmyadmin.

Insert Data into MySQL with Nodejs

We have successfully created a table, next we will try to fill the data there.

The way is the same ...

The only difference is the query.

Let's try it.

Make a new file named insert.js, then fill in the following code:

var db = require("./db_config");

db.connect(function(err) {
    if (err) throw err;
    
    let sql = `INSERT INTO customers (name, address) 
               VALUES ('Starbucks', 'Lombok Epicentrum Mall')`;

    db.query(sql, function (err, result) {
        if (err) throw err;
        console.log("1 record inserted");
    });
});

After that, try execution.

... and now try checking Phpmyadmin again.

Yay, we created the first data.

Then what if there is a lot of data that we want to add?

This can be done by providing parameters [values] for query execution.

db.query(sql, [values], function (err, result){ ... });

For more details, let's try.

Make a new file named insert_multi.js, then fill in the following code:

var db = require("./db_config");

db.connect(function(err) {
    if (err) throw err;
    
    let sql = "INSERT INTO customers (name, address) VALUES ?";
    var values = [
        ['JS Coffee', 'Highway 71'],
        ['3AM Coffee', 'Lowstreet 4'],
        ['Apple Cafe', 'Apple st 652'],
        ['Laravel Coffee', 'Mountain 21'],
        ['Nodejs Cafe', 'Valley 345'],
        ['PHP Hotel', 'Ocean blvd 2'],
        ['One Cafe', 'Green Grass 1'],
        ['Richard bar', 'Sky st 331'],
        ['Susan Cafe', 'One way 98'],
        ['Vicky Club', 'Yellow Garden 2'],
        ['Ben Resto', 'Park Lane 38'],
        ['William Company', 'Central st 954'],
        ['Chuck Food', 'Main Road 989'],
        ['Viola Coffee', 'Sideway 1633']
    ];
    db.query(sql, [values], function (err, result) {
        if (err) throw err;
        console.log("Number of records inserted: " + result.affectedRows);
    });
});

Look at the code above ...

We use question marks in the MySQL query. This question mark will serve as a placeholder for data.

Later, the question mark will take the value of the variable values we will provide as a parameter in executing the query.

Then in the callback function for queries, we use objects resultto find out how many rows or data have been added.

console.log("Number of records inserted: " + result.affectedRows);

Now try executing the program.

... and try to look at the data in Phpmyadmin again.

Read MySQL Data at Nodejs

Next we will try to read data from MySQL and display it in the program.

The way is the same ...

The difference is in the query only.

Please create a new file named read.js, then fill in the following code:

var db = require("./db_config");

db.connect(function(err) {
    if (err) throw err;
    
    let sql = "SELECT * FROM customers";
    db.query(sql, function (err, result) {
        if (err) throw err;
        console.log(result);
    });
});

After that, try executing:

From the results of this output, we can find out that the variable result is an array containing objects from each row or data.

To make it look better, try changing the code to something like this:

var db = require("./db_config");

db.connect(function(err) {
    if (err) throw err;
    
    let sql = "SELECT * FROM customers";
    db.query(sql, function (err, result) {
        if (err) throw err;
        // gunakan perulangan untuk menampilkan data
        console.log(`ID \t NAME \t\t ADDRESS`);
        console.log(`----------------------------------------------------------`);
        result.forEach(customer => {
            console.log(`${customer.id} \t ${customer.name} \t ${customer.address}`);
        });
    });
});

After that, try executing again.

Update MySQL Data with Nodejs

Next we will try to change the data with a query UPDATE. The method is the same, we just need to change the query.

Please create a new file with the name update.js, then fill in the following code:

var db = require("./db_config");

db.connect(function(err) {
    if (err) throw err;
    
    // kita akan mengubah alamat Starbucks
    let sql = `UPDATE customers
               SET address='LEM Lantai 1'
               WHERE id=1`;

    db.query(sql, function (err, result) {
        if (err) throw err;
        console.log("Number of records inserted: " + result.affectedRows);
    });
});

In the sql code, we will change the address for data whose ID is the same as 1. In this case the id data 1is Starbucks .

Let's try execution.

See, the address changes from Lombok Epicentrum Mallbeing LEM Lantai 1.

Delete MySQL Data with Nodejs

Delete data is the same as updating data, ie using WHERE queries to select data to be deleted.

Let's try ...

Make a new file named delete.js, then fill in the following code:

var db = require("./db_config");

db.connect(function(err) {
    if (err) throw err;
    
    // kita akan menghapus Starbucks
    let sql = `DELETE FROM customers WHERE id=1`;

    db.query(sql, function (err, result) {
        if (err) throw err;
        console.log("Number of records inserted: " + result.affectedRows);
    });
});

Then try execution.

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

Category