How to Create Database Manually in MySQL Database Server using NodeJS Application

By Posted on

Introduction

The following is an article where the main focus is in the title of this article. It is about how to create database manually in NodeJS application. This article has a connection with the previous article. Actually, the previous one is showing how to connect to MySQL Database Server with the title of ‘How to Connect to MySQL Database Server from a NodeJS Application’ in this link.

Create Database Manually in MySQL Database Server using NodeJS Application

Using the intention it the previous part for creating a database, the following are the steps for creating it manually in MySQL Database Server :

  1. First of all, as usual just connect to the server or the machine where the NodeJS tool exist. Just log in directly or remotely. If it is not a local server or machine for instance it is a virtual server, just access it remotely. Read the article with the title of ‘How to Remote CentOS Virtual Server running in a VirtualBox with a NAT Network using SSH’ in this link.

  2. If it is not exist, just install it. Just read either the article with the title of ‘How to install npm’ in this link or another article with the title of ‘How to Install npm in Ubuntu Linux operating system’ in this link.

  3. Next, access the command line interface in that server or that machine.

  4. Create a file with the name of ‘app.js’. Just put it in a certain folder. The following is the content of the file : 

    const mysql = require("mysql");
const db = mysql.createConnection({
        host: '10.0.2.2',
        user: 'db_user',
        database: 'db_master'
});                                                                                                                                                                       
db.connect(function(err){
        if(err){
                return console.error("Error : " + err.message);
        }

        let sql = "CREATE DATABASE db_testing";                                                                                                                                   db.query(sql, function(err, result) {
                if(err){
                        return console.error("Error : " + err.message);
                }else{
                        console.log("Database created !");                                                                                                                                }
        });                                                                                                                                                               
        console.log("Connected to MySQL Database Server");                                                                                                                });

  5. Soon after, execute the NodeJS script using a nodemon tool. The following is the execution of the tool : 

    [admin@10 db]$ nodemon app.js
[nodemon] 2.0.7
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,json
[nodemon] starting `node app.js`
Connected to MySQL Database Server
Error : ER_DBACCESS_DENIED_ERROR: Access denied for user 'db_user'@'%' to database 'db_testing'

  6. Apparently the error appear after succesfully connecting to MySQL Database Server. Despite successfully connect to MySQL Database Server, it fail to create a new database with the name of ‘db_testing’. That is because ‘db_user’ do not have a privilege to create a new database. So, edit the above source code accordingly. In other words, change the user and password so that it can connect and also create a new database as follows : 

    const mysql = require("mysql");
const db = mysql.createConnection({
        host: '10.0.2.2', 
        user: 'root', 
        password: 'password'
});                                                                                                                                                                       
db.connect(function(err){
        if(err){
                return console.error("Error : " + err.message);
        }

        let sql = "CREATE DATABASE db_testing";                                                                                                                                   db.query(sql, function(err, result) {
                if(err){
                        return console.error("Error : " + err.message);
                }else{
                        console.log("Database created !");                                                                                                                                }
        });                                                                                                                                                               
        console.log("Connected to MySQL Database Server");                                                                                                                });

  7. After changing it to the root account as in the above line codes. Do not need to specify the database name since it wil be created later. The following output appear : 

    [nodemon] restarting due to changes...
[nodemon] starting `node db-create-table-manual.js`
Error : ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
[nodemon] clean exit - waiting for changes before restart

  8. The solution for the above error exist in the article with the title of ‘How to Solve Error Message ‘Client does not support authentication protocol requested by server; consider upgrading MySQL client’ upon connecting to MySQL Database from a NodeJS script’ in this link. It is because the connection process is going through MySQL Database Server 8.x. The process exist as follows : 

    C:\Users\Personal>mysql -uroot -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'password';
Query OK, 0 rows affected (0.53 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

mysql>

    Soon after the execution of the above process is a success, the following output of the ‘app.js’ file execution using ‘nodemon’ will appear : 

    [nodemon] restarting due to changes...
[nodemon] starting `node db-create-table-manual.js`
[nodemon] restarting due to changes...
[nodemon] starting `node db-create-table-manual.js`
Connected to MySQL Database Server
Database created !

Leave a Reply