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
We will cover the following topics in this tutorial;
For you to successfully complete this tutorial, you will need to know the following.
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,
MySQL
module and adds it as a dependency to package.json fileWe 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
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
modulerequire('console.table');
imports the console table
modulevar connection = mysql.createConnection({...});
creates a connection objectvar sql_stmt = "";
creates a global variable that we will use to store the various SQL statementsfunction getArgument(argument){...}
defines a custom function that we will use to grab input from the consoleconnection.connect(function(error){...});
establishes the database connectionfunction listRecords(){...}
retrieves all rows from the database and displays them in a beautiful tablefunction addRecord(){...}
inserts a new record into the databasefunction updateRecord(){...}
updates an existing recordfunction deleteRecord(){...}
deletes an existing recordvar action = getArgument('--action');
grabs the value of an argument from the command lineswitch(action){...}
used to decide the action i.e. add, update or delete recordlistRecords();
calls the listRecords functionconnection.end(function(error) {...});
closes the database connectionThe 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
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 statementsql_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 statementconnection.query(sql_stmt…);
executes the SQL statementGo 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 databaseYou will get the following results.
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
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.
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 version 1: Date Published 2016-08-02