https://tutorials.kode-blog.com/nodejs-database-mysql


Most apps are usually powered by a database. The database could a relational database management system or NoSQL. Node.JS supports many different types of databases. All you need is the relevant driver.

In this tutorial, we will look at how to work with a relational database using Node.JS. By the time that you are done with this tutorial, you would have created the following Node.JS console application

TOPICS TO BE COVERED

We will cover the following topics in this tutorial;

  • Tutorial pre-requisites
  • Node.JS MySQL Module
  • Node.JS MySQL Tutorial Database
  • Node.JS MySQL Create, Read, Update, and Delete (CRUD) Example

TUTORIAL PRE-REQUISITES

For you to successfully complete this tutorial, you will need to know the following.

  • You have alreadyinstalled the latest version of Node.JS. If you haven’t yet done so, then read the tutorial on how to install and configure Node.JS that guides you on how to download and install Node.JS
  • You have installed MySQL serverand its up and running
  • Structured Query Language (SQL)basics
  • Command line / terminal – Node.JS makes heavy use of the command line
  • Active internet connection– we need to download packages from the internet
  • A text editor / IDE– you need it to write code
  • JavaScript basic skills– you don’t need to be a JavaScript ninja to successfully complete this tutorial. Just the basics

NODE.JS MYSQL MODULE

The mysql module driver allows Node.JS applications to interact with MySQL database. You can install the module using NPM. You can get more information about this module from the npmjs website https://www.npmjs.com/package/mysql

Let’s create a new project and install the module

I will create a new project mysqldb in drive C:\node\mysqldb. Feel free to use any directory or project name

Open the command prompt and browse to the project root directory.

npm init

Set the required parameters and say yes to create the package.json file

Let’s now install mysql module

Run the following command in the command prompt window

npm install mysql --save

HERE,

  • The above command installs MySQL module and adds it as a dependency to package.json file

NODE.JS MYSQL TUTORIAL DATABASE

We will create a simple database that records the information of music artists. This section assumes you already have MySQL database running and you know the basics of SQL.

Run the following command in MySQL to create the database, artists table and add some sample data.

CREATE SCHEMA node_mysql;

USE node_mysql;

CREATE TABLE `node_mysql`.`artists` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `artist_name` VARCHAR(245) NULL,
  `genre` VARCHAR(745) NULL,
  `label` VARCHAR(245) NULL,
  `country` VARCHAR(145) NULL,
  PRIMARY KEY (`id`));


INSERT INTO artists (artist_name,genre,label,country)
VALUES ('Disturbed','Heavy Metal','Warner Bros.','United States')
,('Behemoth','Blackened Death Metal','Nuclear Blast','Poland')
,('Diamond Platnumz','Bongo Flava','Wasafi Records Blast','Tanzania')
,('Slap Dee','Zed Hip Hop','XYZ.','Zambia');

SELECT * FROM artists;

You will get the following results

NodeJS MySQL

NODE.JS MYSQL CREATE, READ, UPDATE, AND DELETE (CRUD) EXAMPLE

Now that we have installed mysql module and created a database, let’s create a simple console app that will interact with our database using the mysql driver.

Create a file app.js in the root directory

We will need to install console.table

Run the following command to install the console.table module

npm install console.table

Before we code our app, let’s look at the methods that the app will have.

var mysql = require('mysql');
require('console.table');

var connection = mysql.createConnection({...});

var sql_stmt = "";

function getArgument(argument){...}

connection.connect(function(error){...});

function listRecords(){...}

function addRecord(){...}

function updateRecord(){...}

function deleteRecord(){...}

var action = getArgument('--action');

switch(action){...}

listRecords();

connection.end(function(error) {...});

HERE,

  • var mysql = require('mysql'); imports the mysql module
  • require('console.table'); imports the console table module
  • var connection = mysql.createConnection({...}); creates a connection object
  • var sql_stmt = ""; creates a global variable that we will use to store the various SQL statements
  • function getArgument(argument){...} defines a custom function that we will use to grab input from the console
  • connection.connect(function(error){...}); establishes the database connection
  • function listRecords(){...} retrieves all rows from the database and displays them in a beautiful table
  • function addRecord(){...} inserts a new record into the database
  • function updateRecord(){...} updates an existing record
  • function deleteRecord(){...} deletes an existing record
  • var action = getArgument('--action'); grabs the value of an argument from the command line
  • switch(action){...} used to decide the action i.e. add, update or delete record
  • listRecords(); calls the listRecords function
  • connection.end(function(error) {...}); closes the database connection

The complete code for app.js is as follows

var mysql = require('mysql');
require('console.table');

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'melody',
    database : 'node_mysql'
});

var sql_stmt = "";

function getArgument(argument){
    var index = process.argv.indexOf(argument);

    return (index === -1) ? null : process.argv[index + 1];
}

connection.connect(function(error){
    if(error){
        console.log();
        console.log('The following error occured while trying to connect to MySQL ' + error.message);
        return;
    }
    console.log();
    console.log('Connection to MySQL established successfully');       

});

function listRecords(){
    sql_stmt = "SELECT * FROM artists;";

    connection.query(sql_stmt,function (err, rows){
        console.log();
        console.log("Artists Listing");
        console.log();

        console.table(rows);

        console.log("Total rows returned: " + rows.length);
    });
}

function addRecord(){
    var name = getArgument('--name');
    var genre = getArgument('--genre');
    var label = getArgument('--label');
    var country = getArgument('--country');

    sql_stmt = "INSERT INTO artists(name,genre,label,country) VALUES (?,?,?,?)";


    var values = [name, genre, label,country];

    sql_stmt = mysql.format(sql_stmt, values);

    connection.query(sql_stmt, function (error, result) {
        if (error) {
            console.log('The following error occured while trying to insert a new record ' + error.message);
        }
        console.log();
        console.log('Created new artist with id ' + result.insertId);
    })
}

function updateRecord(){
    var id = getArgument('--id');
    var name = getArgument('--name');
    var genre = getArgument('--genre');
    var label = getArgument('--label');
    var country = getArgument('--country');

    sql_stmt = "UPDATE artists SET name = ?,genre = ?,label = ?,country = ? WHERE id = ?";

    var values = [name, genre, label,country,id];

    sql_stmt = mysql.format(sql_stmt, values);

    connection.query(sql_stmt, function (error, result) {
        if (error) {
            console.log('The following error occured while trying to insert a new record ' + error.message);
        }
        console.log();
        console.log('Updated artist with id ' + id);
    })
}

function deleteRecord(){
    var id = getArgument('--id');

    sql_stmt = "DELETE FROM artists WHERE id = ?";

    var artist_id = [id];

    sql_stmt = mysql.format(sql_stmt, artist_id);

    connection.query(sql_stmt, function (error, result) {
        if (error) {
            console.log('The following error occured while trying to insert a new record ' + error.message);
        }
        console.log();
        console.log('Deleted artist with id ' + id);
    })
}

var action = getArgument('--action');

switch(action){
    case "add":
        addRecord();
        break;

    case "update":
        updateRecord();
        break;

    case "delete":
        deleteRecord();
        break;
}

listRecords();

connection.end(function(error) {
    if (error){
        console.log('The following error occured while trying to connect to MySQL ' + error.message);
    }else{
        console.log();
        console.log('Connection to MySQL established closed');
    }
});

Taking the app for a spin

Let’s now take our app for a spin

Run the following command in the console. Make sure you are in the root directory

Displaying all records

node app

You will get the results

NodeJS MySQL Select Query

Add new record

Before we add a new record, let’s first examine the code behind the scenes

var name = getArgument('--name');
var genre = getArgument('--genre');
var label = getArgument('--label');
var country = getArgument('--country');

sql_stmt = "INSERT INTO artists(name,genre,label,country) VALUES (?,?,?,?)";

var values = [name, genre, label,country];

sql_stmt = mysql.format(sql_stmt, values);

connection.query(sql_stmt, function (error, result) {
    if (error) {
        console.log('The following error occured while trying to insert a new record ' + error.message);
    }
    console.log();
    console.log('Created new artist with id ' + result.insertId);
});

HERE,

  • sql_stmt = "INSERT INTO artists(name,genre,label,country) VALUES (?,?,?,?)"; creates a prepared statement for security reasons. This will help us avoid SQL Injection and also handle special characters i.e. single quotes properly.
  • var values = [name, genre, label,country,id]; creates an array variable with values that will be used as parameters to the prepared statement
  • sql_stmt = mysql.format(sql_stmt, values); calls the format method of the mysql object. The format method will escape all the values passed in and complete the SQL statement
  • connection.query(sql_stmt…); executes the SQL statement

Go back to the console window and run the following command

node app --action add --name "Rick Ross" --genre "Hip Hop" --label "Self" --country "United States"

HERE,

  • node app runs our application
  • --action add tells the application what action to perform in addition to displaying the records
  • --name "Rick Ross" --genre "Hip Hop" --label "Self" --country "United States" specifies the values that should be inserted into the database

You will get the following results.

NodeJS MySQL Insert Record

The following command will update, delete existing records

node app --action update --id 5 --name "Raymond" --genre "Bongo Flava" --label "Wasafi" --country "Tanzania"

node app --action delete --id 5

SUMMARY

In this tutorial, we have learnt how to connect to MySQL database using the mysql driver for Node.JS. We also looked at how we can create, read, update and delete data using prepared.

WHAT’S NEXT?

The next tutorial will show you how to create a simple Node.JS MongoDB database application. The simple application will be able to create, read, update and delete data.

If you found this tutorial useful, support us by using the social media buttons to like and share the tutorial. If you didn’t find it useful, please use the comments section below to let us know how we can do better next time.

Subscribe to our newsletter, like our Facebook fan page or follow us on Twitter to get free updates when we publish new tutorials

TUTORIAL HISTORY

Tutorial version 1: Date Published 2016-08-02