Prerequisite:
Before starting this tutorial you have to confirm that MySQL server and client packages are installed and working properly in your system. If you install the MySQL server for the first time then the password of root user is empty by default. But you have to set the password for the root user to make a connection with MySQL server using the node-mysql client. You can check this tutorial to know how to change the root password of the MySQL server.
Run the following commands to work as a root user and connect with MySQL server by using MySQL client.
$ mysql -u root -p
Enter the root password and run the following SQL commands to create a new database, create a table on that database and insert some records in that table.
The following command will create a database named mydb.
The following command to select the database for doing database operations.
The following command will create a table named book in the database mydb.
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(50) NOT NULL,
author VARCHAR(50) NOT NULL,
price int(5));
The following command will insert four records into book table.
(NULL,'Learning PHP and MySQL', 'Robin Nixon', 45),
(NULL,'Learning JQuery', 'Jonathan', 35),
(NULL,'Angular in Action', 'Jeremy', 50),
(NULL,'Mastering Laravel', 'Christopher', 55);
Install mysql client for nodejs:
Run the following command to check nodejs is installed in the system before running the command of installing mysql client of nodejs. It will show the installed version of nodejs.
If it not installed then you have to install it by running the following command.
You will require another package named npm to be installed in the system to install mysql client for nodejs. If it is not installed before run the following command to install npm.
Now, run the following command to update the system.
The following command will install mysql module for nodejs that will work as mysql client.
Simple MySQL connection using NodeJS:
Create a JS file named connection1.js with the following script to make a connection with the previously created database named mydb and read data from book table. mysql module is imported and used for creating a simple connection with the MySQL server. Next, a query will be executed to read all records from book table, if the database is connected properly. If the query executed properly then all records of book table will be printed in the terminal and the database connection will be closed.
connection1.js
let mysql = require('mysql');
// Setup database connection parameter
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '1234',
database: 'mydb'
});
// Connect with the database
connection.connect(function(e) {
if (e) {
// Show error messaage on failure
return console.error('error: ' + e.message);
}
// Show success message if connected
console.log('\nConnected to the MySQL server...\n');
});
// Set the query message
$query = 'SELECT * from book';
// Execute the database query
connection.query($query, function(e, rows) {
if(e){
// Show the error message
console.log("Error ocurred in executing the query.");
return;
}
/* Display the formatted data retrieved from 'book' table
using for loop */
console.log("The records of book table:\n");
console.log("Title\t\t\t\t Author\t\tprice\n");
for(let row of rows) {
console.log(row['title'],"\t\t",row['author'],"\t","$",row['price']);
}
});
// Close the database connection
connection.end(function(){
console.log('\nConnection closed.\n');
});
Output:
Run the following command to execute the script.
The following output will appear after running the script.
Pooled MySQL connection using NodeJS:
Making a simple MySQL connection with NodeJS using mysql module is shown in the previous example. But many users can connect with the database server at a time through the application when the application is created with MySQL database for production purposes. You will require the express module to handle concurrent database users and support multiple database connections.
Run the following command to install the express module.
Create a JS file named connection2.js with the following script. If you connect with MySQL with the following script then 10 concurrent users will be able to make a connection with the database server and retrieve data from the table based on the query. It will make a connection at the port 5000.
connection2.js
var mysql = require('mysql');
// Import express module
var express = require("express");
// Define object of express module
var app = express();
// Make database connection to handle 10 concurrent users
var pool = mysql.createPool({
connectionLimit :10,
host : 'localhost',
user : 'root',
password : '1234',
database : 'mydb',
debug : true
});
/* Make pooled connection with a database and read specific records from a table of that
database */
function handle_database(request,response) {
// Make connection
pool.getConnection(function(e,connection){
if (e) {
//Send error message for unsuccessful connection and terminate
response.json({"code" : 300, "status" : "Database connection errror"});
return;
}
// Display success message in the terminal
console.log('Database connected');
// Read particular records from book table
connection.query("SELECT * from book where title like '%PHP%' or title like
'%Laravel%'",function(e,rows){ connection.release();
if(!e) {
// Return the resultset of the query if it is successfully executed
response.json(rows);
}
});
// Check the connection error occurs or not
connection.on('error', function(e) {
response.json({"code" : 300, "status" : "Database connection errror"});
return;
});
});
}
// Call the function for making connections
app.get("/",function(request,response){-
handle_database(request,response);
});
// Listen the connection request on port 5000
app.listen(5000);
Output:
Run the script from the terminal like the previous example. It will wait for the connection request after running the script.
Now, open any browser and go to the following URL to send a connection request.
The following output will appear as a response after executing the query.
If you open the terminal now then you will see the following output.
Ten connection requests can be sent at a time from 10 browsers in the way mentioned above.
Conclusion:
The most simple ways to work with MySQL and NodeJS are shown by two examples in this tutorial. If you are a new Node developer and want to work with MySQL database then I hope you will be able to do your task after reading this tutorial.
