Node.js and MySQL Complete Tutorial

node js and mysql tutorial

Node.js and MySQL are some of the necessary binding needed for any web application. MySQL is one of the most popular open-source databases in the world and efficient as well. Almost every popular programming language like Java and PHP provides drivers to access and perform operations with MySQL.

In this Node js and MySQL tutorial, we are going to learn how to connect Node js server with MySQL database. We will also learn how to pool connections to improve performance, query the tables, and call stored procedures.

To be able to follow up with the code examples in this Node.js and MySQL tutorial, you should have MySQL installed on your computer.

You can download a free MySQL database at https://www.mysql.com/downloads/.

Quick Start: How to Use MySQL in Node

Assuming you have Node and MySQL installed on your computer. Let’s quickly use MySQL in Node in three easy steps:

Step 1: Create a new Node.js project

Create a new directory and initialize a Node project using the NPM.

$ mkdir mysqlexperiment && cd mysqlexperiment
$ npm init --y

Step 2: Install mysql node module

Install the mysql node module using the NPM.

npm install --save mysql

Step 3: Connect with MySQL

Create an app.js file and copy/paste the code shown below. Change the MySQL credentials accordingly with your system.

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'databasename'
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL Server!');
});

Run the code using the following command.

node app.js

Observe the ‘Connected to MySQL Server!’ message in the terminal.

If you have the latest MySQL server installed, you might end up getting an error saying the following.

{
  code: 'ER_NOT_SUPPORTED_AUTH_MODE',
  errno: 1251,
  sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client',
  sqlState: '08004',
  fatal: true
}

To tackle this issue, create a new user in your MySQL server with ‘mysql_native_password’ authentication mechanisum.

Here is how you can do it quickly. First, log in to the MySQL server using root access.

mysql -u root -p

Then run these commands one by one.

CREATE USER 'newuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'newpassword';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

In the code, pass the new credentials to connect to the MySQL server. Let’s proceed further.

Pooling MySQL Connections

The code shown earlier is not meant for production use. It’s merely to get you started with Node and MySQL. In a production scenario, we must use connection pooling to improve the performance of MySQL and not overload the MySQL server with too many connections.

Let’s explain it with a simple example.

Consider the code shown below.

const express = require("express");
const app = express();
const mysql = require('mysql');

const connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'username',
  password : 'password',
  database : 'databasename'
});

connection.connect((err) => {
    if(err) throw err;
    console.log('Connected to MySQL Server!');
});

app.get("/",(req,res) => {
    connection.query('SELECT * from users LIMIT 1', (err, rows) => {
        if(err) throw err;
        console.log('The data from users table are: \n', rows);
        connection.end();
    });
});

app.listen(3000, () => {
    console.log('Server is running at port 3000');
});

We are integrating express module to create a web server. Install the module using the following command.

npm install --save express

We are creating a MySQL connection on every request coming from the user. Soon after getting multiple concurrent requests, the MySQL server will get overloaded and throw an error.

To simulate the concurrent connection scenario, we are going to use a tool called siege.

Use this command to install it in Ubuntu system.

sudo apt-get install siege

Run our Node server.

node app.js

Let’s simulate the concurrent requests.

siege -c10 -t1M http://localhost:3000

Assuming you are running the Node server on Port 3000.

Here is the output.

Node.js and mysql

As you can see from the output above, our server crashed while handling concurrent requests. To tackle this scenario, we use the Pooling mechanism.

Connection Pooling is a mechanism to maintain a cache of database connection so that the connection can be reused after releasing it.

Let’s rewrite our code to support connection pooling.

const express = require("express");
const app = express();
const mysql = require('mysql');

const pool = mysql.createPool({
  host     : 'localhost',
  user     : 'username',
  password : 'password',
  database : 'databasename'
});

app.get("/",(req,res) => {
    pool.getConnection((err, connection) => {
        if(err) throw err;
        console.log('connected as id ' + connection.threadId);
        connection.query('SELECT * from users LIMIT 1', (err, rows) => {
            connection.release(); // return the connection to pool
            if(err) throw err;
            console.log('The data from users table are: \n', rows);
        });
    });
});

app.listen(3000, () => {
    console.log('Server is running at port 3000');
});

Run the app using the following command.

node app.js

Let’s fire up 10 concurrent users for 1 minute using siege by using this command.

siege -c10 -t1M http://localhost:3000

Here is the output.

Code is stable !

Our server is effectively handling multiple requests with ease. I have used this approach in multiple production software solutions with heavy payload and it works like charm.

Let’s learn how to execute various MySQL queries using Node.

Executing Queries

Let’s learn how to execute queries using Node.js.

Inserting Rows into Table

Here is the code to add new rows to the table.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'todolist',
    debug    :  false
});

// add rows in the table

function addRow(data) {
    let insertQuery = 'INSERT INTO ?? (??,??) VALUES (?,?)';
    let query = mysql.format(insertQuery,["todo","user","notes",data.user,data.value]);
    pool.query(query,(err, response) => {
        if(err) {
            console.error(err);
            return;
        }
        // rows added
        console.log(response.insertId);
    });
}

// timeout just to avoid firing query before connection happens

setTimeout(() => {
    // call the function
    addRow({
        "user": "Shahid",
        "value": "Just adding a note"
    });
},5000);

The mysql.format function will perform the query escape.

Querying data in Table

Here is the code to query rows in the table.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'todolist',
    debug    :  false
});

// query rows in the table

function queryRow(userName) {
    let selectQuery = 'SELECT * FROM ?? WHERE ?? = ?';    
    let query = mysql.format(selectQuery,["todo","user", userName]);
    // query = SELECT * FROM `todo` where `user` = 'shahid'
    pool.query(query,(err, data) => {
        if(err) {
            console.error(err);
            return;
        }
        // rows fetch
        console.log(data);
    });
}

// timeout just to avoid firing query before connection happens

setTimeout(() => {
    // call the function
    // select rows
    queryRow('shahid');
},5000);

If you would like to add multiple rows in a single query, you can pass an array in the values. Like this.

    let insertQuery = 'INSERT INTO ?? (??,??) VALUES (?,?)';
    let values = [["shahid","hello"],["Rohit","Hi"]]; // each array is one row
    let query = mysql.format(insertQuery,["todo","user","notes",values]);

Updating data in Table

Here is the code to update the data in the table.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'todolist',
    debug    :  false
});

// update rows

function updateRow(data) {
    let updateQuery = "UPDATE ?? SET ?? = ? WHERE ?? = ?";
    let query = mysql.format(updateQuery,["todo","notes",data.value,"user",data.user]);
    // query = UPDATE `todo` SET `notes`='Hello' WHERE `name`='shahid'
    pool.query(query,(err, response) => {
        if(err) {
            console.error(err);
            return;
        }
        // rows updated
        console.log(response.affectedRows);
    });
}

// timeout just to avoid firing query before connection happens

setTimeout(() => {
    // call the function
    // update row
    updateRow({
        "user": "Shahid",
        "value": "Just updating a note"
    });
},5000);

Deleting Rows in the table

Here is the code to delete a row from the table.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'todolist',
    debug    :  false
});

function deleteRow(userName) {
    let deleteQuery = "DELETE from ?? where ?? = ?";
    let query = mysql.format(deleteQuery, ["todo", "user", userName]);
    // query = DELETE from `todo` where `user`='shahid';
    pool.query(query,(err, response) => {
        if(err) {
            console.error(err);
            return;
        }
        // rows deleted
        console.log(response.affectedRows);
    });
}

// timeout just to avoid firing query before connection happens

setTimeout(() => {
    // call the function
    // delete row
    deleteRow('shahid');
},5000);

Calling MySQL Stored Procedure Using Node

You can also call a stored procedure using Node.js. If you don’t have stored procedures created in MySQL, you can refer to the code below to do the same.

DELIMITER $$
 
CREATE PROCEDURE `getAllTodo`()
BEGIN
    SELECT * FROM todo;
END$$
 
DELIMITER ;

Here is the code to call the stored procedure from the code.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'todolist',
    debug    :  false
});

function callSP(spName) {
    let spQuery = 'CALL ??';
    let query = mysql.format(spQuery,[spName]);
    // CALL `getAllTodo`
    pool.query(query,(err, result) => {
        if(err) {
            console.error(err);
            return;
        }
        // rows from SP
        console.log(result);
    });
}

// timeout just to avoid firing query before connection happens

setTimeout(() => {
    // call the function
    // call sp
    callSP('getAllTodo')
},5000);

Conclusion :

MySQL is one of the widely used database engines in the world and with Node it really works very well. Node MySQL pooling and event-based debugging are really powerful and easy to code.

Further reading:

130 thoughts on “Node.js and MySQL Complete Tutorial”

  1. Hey, thanks for this. I’m checkin’ it out. FYI, there’s and extra comma in the first package.json file example.

    Project directory:
    —node_module
    I think “node_module” is missing an “s”.

    I assume that index.js contains the first connection to the mysql code, but it doesn’t mention it anywhere – it says to “node file_name.js”

    I think I got that first part to work now. Thanks.

    1. Hey Very Nice article, I m new to Node and would like to know . If i have multiple function to get data ex: shopping list, recommendation, user details, cart details . How can i use this connection in the same page .
      Do i have to use the function handle_database(req,res) in each action is it . Please revert.

  2. This is the best tutorial I have run into yet. Most are so outdated, Very helpful and very well written.

  3. Hello,

    It seems to me that you are registering multiple times the same callback on each connection(when the connection is reused). A better approach could be to conditionally register if connection.listeners(‘error’) if false and EventEmitter.listenerCount(connection, error) matches the count of callbacks registered globally on a specific connection(in this scenario 1).

    1. Ya that would be cool. But actually you don’t need to do that effort since pooling caches your connection it will be reused once you released it.

  4. Where do you keep that script, which connects database? Does that live on client side or server side?

      1. I was using an angular project which is started by calling ‘npm start’. It starts on port 8000 and the angular project is working. When I integrated this mySQL into it, I hoped a call to route defined in my app.js which in turn would invoke controller in controller.js, which would call
        $http.get(‘localhost:3000/load’).success(function(data){
        console.log(“kdfhg”+data);
        $scope.actors=data;
        });
        I don’t understand that in this flow, how and when server.js would be called. Do I need to start service defined in server.js as well by using ‘node server.js’. I tried that anyway. With same port, it is not working. With different port, it gives cross-origin problem.

        1. Both proyect star call the same port Localhost:8080 do you need change the port just for one can be Localhost:8081 or other that you prefer

  5. It seems you have defined two error handeling routines for when something goes wrong with the connection.

    if (err) {
    connection.release();
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    }

    and a second time here:

    connection.on(‘error’, function(err) {
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    });

    Why is this and what is de difference between the two. When for example the database is down. Wich of the two wil be called. Or both?

    1. Hi Chris,
      The line
      if (err) {
      connection.release();
      res.json({“code” : 100, “status” : “Error in connection database”});
      return;
      }
      is to find out that whether there is any issue connecting with database. Once DB is connected, there are chances to get errors like PROTOCOL_CONNECTION_TIMEOUT and those kind of special database errors comes from .on(“error”).

      Hope its clear.

      Thanks,
      Shahid.

  6. Thanks, written very clearly.
    Can i have 1 question? 🙂
    Im not very sure about this code:

    connection.on(‘error’, function(err) {
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    });

    This will be executed when connection is established already, but somewhy it got and error (disconnect). Am i right?

    1. Ya i should change error message actually, these kind of errors comes up after connecting to Database, like maximum queue length reached or connection time out and many more.

  7. Hey Mate NIce tutorial, Can you reccommend any books or other tutorials to learn more about nodejs and mysql? Thanks man!

  8. Hey i’m discovering node atm and i’ve read your tuto but i can’t connect to my database, probably a configuration problem but I am a bit lost, is there an option I should activate on my server configuration to access my db ?

    Thank you

    1. Actually i found it, is was an host problem, but here is my other question: how can you use a variable in your query? Like :
      var pseudo = ‘Jon’;
      connection.query(‘Select name from users where name=’ + pseudo, function(err,rows,fields){

      1. You can send it to View ( user end ) using response variable. Call API using jQuery HTTP method and whatever you return from node in response variable will be present in response variable of jQuery HTTP call.

  9. In the following code is connection.release(); necessary my app is working after removing it.

    if (err) {
    connection.release();
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    }

    1. That’s a good question. It depends upon the application actually, let’s say you have Share market app where in one second you need to do 100000+ sql query, you connectionLimit should increase else pooling will be very slow.

      1. You can also be guided by the number of CPU cores on the host, if the database is on the same device as the node.js server, the type of disks (SSD vs spindle), and any other significant applications running on the host. You want to aim for maximum utilisation of all CPUs, you need to take account of the time spent doing work that is not very CPU-intensive (e.g. waiting for the network or disk access), but you don’t want to go too much over that “sweet spot” or you will waste resources just swapping tasks between CPUs.

        In my case, my host has 72 CPU cores, the database is on the same host as the node.js server, and the storage is SSD, NVMe TLC (so very fast, efficient disk access). I have found that a connection limit of 100 works well, and gets good resource utilisation. (You can monitor that with htop.) If your disk access is a bit slower, you can probably afford to go a bit higher, relative to the number of CPUs.

  10. Hello and thanks for taking the time to write this… I am getting the following warning while running your code after ten requests. Do you know how to best solve this?

    Best – Cameron

    (node) warning: possible EventEmitter memory leak detected. 11 listeners added. Use emitter.setMaxListeners() to increase limit.
    Trace
    at PoolConnection.addListener (events.js:160:15)
    at /home/cbergoon/Documents/Projects/test/app.js:32:20
    at Ping.onPing [as _callback] (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/Pool.js:94:5)
    at Ping.Sequence.end (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Ping.Sequence.OkPacket (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:105:8)
    at Protocol._parsePacket (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket. (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/Connection.js:82:28)
    at Socket.emit (events.js:95:17)

    1. Hello and thanks for taking the time to write this… I am getting the following warning while running your code after ten requests. Do you know how to best solve this?

      Also note this is using the ‘production’ example…

      Best – Cameron

      (node) warning: possible EventEmitter memory leak detected. 11 listeners added. Use emitter.setMaxListeners() to increase limit.
      Trace
      at PoolConnection.addListener (events.js:160:15)
      at /home/cbergoon/Documents/Projects/test/app.js:32:20
      at Ping.onPing [as _callback] (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/Pool.js:94:5)
      at Ping.Sequence.end (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
      at Ping.Sequence.OkPacket (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:105:8)
      at Protocol._parsePacket (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Protocol.js:271:23)
      at Parser.write (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Parser.js:77:12)
      at Protocol.write (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Protocol.js:39:16)
      at Socket. (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/Connection.js:82:28)
      at Socket.emit (events.js:95:17)

      1. Hi this is warning, you can avoid or increase the socket listener amount. You can do so by using emitter.setMaxListeners() in node-mysql package code but i don’t think so that would be good idea.

      2. increase the listeners for you ‘connection’ by doing something like connection.setMaxListeners(100);
        or
        connection.setMaxListeners(0); //no limit

  11. The tutorial is very well written. And i was searching for this kind of solution for MySql and Node. Thank you!

  12. Hi

    I am testing my node.js code as well using siege, and just wondering why does mine do not have concurrency etc in my log, mine only has response time. I only have rps and response time min,max and avg. I used npm to install siege as well.

  13. If we have nested connection.query(),will we write connection.on(‘error’,func(){}) for each of connection.query().For example–

    connection.query(qry,function(err,rows){
    connection.query(qry,function(err,rows){

    });
    connection.on(‘error’,function(err){});
    });
    connection.on(‘error’,function(err){});

  14. Above code is working fine but what about put, post,delete query?
    using
    app.post(‘/client’, function (req, res) {
    value1 = {id: 3, hostname: ‘localhost-3002’, user: ‘abc’};
    console.log(“Insert command”);
    config.connection.query(‘INSERT INTO client SET ?’, value1, function (err,result) {
    if (err) {
    console.log(“ERROR IN QUERY”);
    } else {
    console.log(“Insertion Successful.” + result);
    console.log(‘Inserted ‘ + result.affectedRows + ‘ rows’);
    res.end(result);
    }
    });
    });
    It is not working. please help in inserting, deleting and updating

  15. How did you get siege running in windows? Looks like in your screenshot you’re showing results in windows, status using siege right? So do you install it only on Ubuntu and then somehow your PC can run siege from node? I don’t get it.

  16. Also I’m using OS X, if I wanna use siege, then I install it on the Ubuntu server then still when I run node from the command-line I can use siege, how does that all wire up to running my app local on my machine?

  17. This is a great tutorial.

    I have few doubts:
    1) How could we decide the connection limit?
    2) What type of draw backs happen if we increase the connection limits?
    3) Currently I’m using nodejs for a real time social media app how could make my choice with this case?

    1. Hi,

      1 : You can use connectionLimit syntax when you create MySQL connection.

      2 : There is no such major drawback as it will help you to do pooling of connection.

      3 : You have made a good choice, Node.js + Socket is one of the best choice for real time apps like social media. Also you can look over Meteor.js which is built for real time SPA apps.

  18. Great tutorial Shahid, I did learn from this. Quick question, when would you close or terminate all connections in a pool after you’re done with it? In other words, when to call the pool.end() function? Also, pls note that I’m initiating the pool connection (pool.getConnection) in a separate route.js file, but have it created in the app.js.

    1. That’s a good question.

      Ideally whenever you do connection.query(), better to release that connection immediately so that other process can use the same connection ID. About destroying pool connection, that should happen when your program close or if you wish to close it to create another pool. I don’t find any good reason to do so in my code.

  19. I installed and ran siege on MacOSX 10.7 – No problem

    siege is weird – it runs very well as I keep increasing the number of concurrent users, until a number of concurrent users is reached and then it hangs

  20. After installing node mysql module
    What is default user and password
    how to change it
    how to create new database
    Before actually starting coding node.js code.
    How to access databases tables which tool (Like phpmyadmin for php) to be used.

    1. You have to set the user name and password which should be same as your MySQL one.

      To change the it just pass proper information to createConnection() function.

  21. Well documented Article shahid, i have a problem though..that is what brought me to this article…i am trying to run a webservice that keeps running listening for a post request…and when it see’s one i will insert data into mysql…but my problem is that the connection ends and it also kills the nodejs script.right not i am bypassing this by using forever but i don’t think thats a good solution.do u know why mysql conneciton end would be killing nodejs app???

    1. Hey,

      I am sure you have web server which runs on port and listen to request.

      And connection ends to where mySQL ? Best practice is to create pool connection when your node program starts and on each request you can either check if connection exists or create new mySQL connection.

      Hope it helps.

  22. Hey nice article. It’s very clear. But I have a question about integrating AngularJS in the front. Let’s suppose I got nodeJs in the back and communicating with MySQL. How can I deal with AngularJS in the front and how will the communication be. Also if I wanted to deploy that application, where it will be ? I mean for php for example I can find anywhere I want a free webhost. Is it the same for node ?

  23. David Selden-Treiman

    Hi Shahid. Great article! I really found it helpful, so I added it to my list of Node.js Tutorials for my sites’ visitors. Thanks for the wonderful resource!

  24. Great write up! Can you please provide some suggestions for organizing this code in a MVC architecture?

  25. Thanks for this tutorial. I came across it while trying to learn some basics about NodeJS/MySQL. I love that you included the bit about Siege, which is fascinating stuff.

    When I upped the siege test to 1000 users, I ended up getting availability of only 66%. Not sure what MySQL setting I need to tweak on my local machine, but when I ran siege with 100 users it got 100%.

  26. Nice tutorial, but I have 1 question, does the api support parameterised queries? As the above has sql injectable written all over it.

  27. Frank Deza Cervantes

    First all, thank for this great and fabulous tutorial !!!
    I am newbie working with server side. my question is when I have with many queries to my database from different routes.

    server.js
    –routes
    –index.js
    –queries1.js
    –queries2.js
    –queries3.js
    –public
    –views
    –node_modules

    server.js
    ==========================
    var express = require(“express”);
    var mysql = require(‘mysql’);
    var app = express();

    var pool = mysql.createPool({
    connectionLimit : 100, //important
    host : ‘localhost’,
    user : ‘root’,
    password : ”,
    database : ‘address_book’,
    debug : false
    });

    app.use(‘/’, routes);
    app.use(‘/users’, users);
    app.use(‘/queries1’, queries1);
    app.use(‘/queries2’, queries2);
    app.use(‘/queries2’, queries3);

    queries1.js
    ============================
    var express = require(‘express’);
    var router = express.Router();

    function handle_database(req,res) {
    pool.getConnection(function(err,connection){
    if (err) {
    connection.release();
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    }
    console.log(‘connected as id ‘ + connection.threadId);
    connection.query(“select * from user”,function(err,rows){
    connection.release();
    if(!err) {
    res.json(rows);
    }
    });
    connection.on(‘error’, function(err) {
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    });
    });
    }

    router.get(“/”,function(req,res){-
    handle_database(req,res);
    });

    My question is when we have with many queries from many routes.
    my question is this method is the best way to work with many queries from many routes. If this the best way, how do I connect my variable “pool” from server.js to query.js.

    if there is other way, please could you give me some light about it??

    Thank you in advance for your help!!!
    Best regards,
    Frank

    1. Having different file for each query is nightmare and that too with pool.

      Best way which i feel is having different models folder where you keep every single code of database handling.

      So like this.

      folder : models
      db.js – contains function to create pool and return connection.
      queries.js – contains queries – do not require db.js here.

      in server.js require db.js and create new pool and store that pool into GLOBAL variable and access it in every query.

      Hope i helped.

        1. Shahid (UnixRoot) Shaikh

          Until and unless it makes sense, i don’t think so it would be bad practice.

    1. For mariaDB too, this package works like charm. In fact i used in production for MariaDB.

  28. Your tutorial was awesome bro. I fixed an error after reading your tutorial.
    If was not releasing the connection in connection pooling.
    Thanks.

  29. I am getting following error

    C:\Users\Cres\NodeJS\DatabaseTest>node test
    { [Error: ER_BAD_DB_ERROR: Unknown database ‘nodejs’]
    code: ‘ER_BAD_DB_ERROR’,
    errno: 1049,
    sqlState: ‘42000’,
    fatal: true }
    C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mysql\lib\protocol\Parser.js:77
    throw err; // Rethrow non-MySQL errors
    ^

    TypeError: Cannot read property ‘release’ of undefined
    at C:\Users\Cres\NodeJS\DatabaseTest\test.js:19:21
    at Handshake.onConnect (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mysql
    \lib\Pool.js:54:9)
    at Handshake.Sequence.end (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\my
    sql\lib\protocol\sequences\Sequence.js:96:24)
    at Handshake.ErrorPacket (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mys
    ql\lib\protocol\sequences\Handshake.js:103:8)
    at Protocol._parsePacket (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mys
    ql\lib\protocol\Protocol.js:280:23)
    at Parser.write (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mysql\lib\pr
    otocol\Parser.js:73:12)
    at Protocol.write (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mysql\lib\
    protocol\Protocol.js:39:16)
    at Socket. (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mysql\
    lib\Connection.js:96:28)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)

    Please help

      1. Yes my mysql is working
        Actually i am php guy with phpmyadmin
        I Just Started with node.
        Every thing is fine with node ,but with database node is not working properly

          1. Hey shahid
            Home Query window phpMyAdmin documentation Documentation
            appium
            auburnpublicschools (11)
            books (1)
            circleflip (28)
            codeigniter-test (1)
            crescerance (20)
            demoinvent (26)
            demotest (12)
            educollaborators (30)
            embr (12)
            information_schema (37)
            knextest (1)
            m1
            mad-teacher-new (17)
            madlearn-teachers (11)
            multi_directory (35)
            multisites (41)
            mydb (1)
            mysql (24)
            nodejs (1)
            performance_schema (17)
            piwikdemo (13)
            siouxcity (85)
            test
            test (5)
            _db (1)
            test09
            travel (28)
            truefinancial (11)

            Above are my databases

  30. Genting Highland

    Greetings! I’ve been following your blog for
    some time now and finally got the courage to go ahead
    and give you a shout out from Lubbock Texas! Just wanted to tell you keep up the fantastic job!

  31. Sir, what will happen if I will not release the connection after performing any query. I am connecting with database only once and after create or select queries I am not releasing connection. It will effect to my application or not. Thanks in advance.

    1. Shahid (UnixRoot) Shaikh

      Yes, it can crash your application.

      You will receive “Connection limit reached” error.

      Make sure you return the used connection properly.

      1. Hi Shahid, thanks for the reply I changes my code as
        mysql_connection.getConnection(function(err, connection) {
        if(err){
        console.log(” getConnection error: “+err);
        callback(“”,’database’);
        }
        connection.query(‘SELECT * FROM portfolio.users WHERE ‘ +
        ’email =”‘+ request.email+'”‘, function(err,res) {
        if(err){
        console.log(“database error” + err);
        callback(“”,’database’);
        }
        else if(res == ‘null’ || res == ”){
        console.log(“n data”);
        connection.release();
        callback(“”,’null’);
        }else{
        console.log(“in3”);
        console.log(‘%s: %s \n’, res[0].email, res[0].password);
        connection.release();
        callback(“”,res[0]);
        }
        });
        });

        I need to know if I am using two callbacks so I have to release cnnections in both the places.

  32. Hello Shahid i had an error with this part<

    if(err){
    connection.release();
    log.error('Connecting to database')
    context.fail(null);
    }

    TypeError: Cannot read property 'release' of undefined

    when i remove con.release (); my code works properly

      1. i see , what about if i wrote wrong the query? it never ends , stay in the part Connected as id xxxx
        how should i catch those errors?? thank you for your answer

        1. So at the end this is my code working properly :function handle_database(req,res){
          pool.getConnection(function(err,con) {
          // body…
          if(err){
          log.error(‘Connecting to database’)
          context.fail(err);
          }
          log.info(‘Connected as id ‘+ con.threadId);

          con.query(‘select * from persons’, function(err,rows){
          con.release();

          if(err){
          log.info(‘Resolving the query’)
          context.fail(err);
          }
          log.info(‘Connection succefull’)
          context.succeed(null);
          });

          con.on(‘error’,function(err){
          log.error(‘Connecting to database’)
          context.fail(err);
          });

          });
          };

          idk if your code is wrong or mine

          1. Shahid (UnixRoot) Shaikh

            Your code is correct 🙂 And so is mine. This is the beauty of JavaScript. I did !err condition which was not accurate I believe. Yours is correct and should work like a charm.

        2. Shahid (UnixRoot) Shaikh

          SQL query level error will come under the error callback variable in connection.query() function.

          SQL DB level error will come under error callback variable in connect() function.

  33. Daniel Berlanga

    I’m sorry if this question has already been done, since there’s a lot of comments and I didn’t read them all…
    You are creating a pool of connections, but nodejs is a non-concurrent server, therefore even if you have more than one connection (of the pool), only one will be used at the same time (unless you also have a pool of nodejs servers, managed by some load-balancer like nginx)

    So my point is, there’s no sense of creating pooled mysql connections, since each server will only use once at a time, right?
    Actually I came to this conclussion after running the following test with ab:
    ab -c 1000 -t 30 localhost:7777/test

    # In a mysql 100 pool connection
    > Requests per second: 662.82 [#/sec] (mean)
    > Time per request: 1508.716 [ms] (mean)

    # In a mysql 1 pool connection
    > Requests per second: 1346.24 [#/sec] (mean)
    > Time per request: 742.811 [ms] (mean)

    # In a mysql no-pool (direct) connection
    > Requests per second: 1732.07 [#/sec] (mean)
    > Time per request: 577.344 [ms] (mean)

    Knowing this, isn’t it better to have just one direct connection for each nodejs server?
    The main problem with your code, is that you are doing connection.end();
    You can (and must) leave it open for future requests, that’s the reason your siege is failing…

    1. Shahid (UnixRoot) Shaikh

      Hey Daniel,

      This is the awesome question so far and let me answer it with my understanding.

      First, about the non-concurrent server, I believe you meant single thread ? That’s true but let’s get to the point of threading a little bit later.

      Regarding MySQL pool, what is happening is Node does accept a lot of connection at once and generate thread inside for the various different ops, in this case, MySQL queries. So you have to say 1000 connections and all of them want to query 1000 SELECT queries.

      If I don’t use Pool and just one connection, then I have to do this.

      Repeat till 100
      Execute query.
      Get the next request.
      Done

      This will block your event loop, we want to execute 1000 queries in parallel. But, MySQL does execute them in some queuing manner which is not our concern at this moment. With pool, I can create 100 connections at a time, and a minute MySQL completes one SQL query, I am releasing it to Pool so that same can be reused (Performing connection does take time and HTTP request).

      Hope I answered this part. Regarding threading, libev (event loop of Node) performs the internal thread management by giving programmer one single thread. So for each I/O, Network call there are different threads (or maybe it reuse some, not sure).

    1. Shahid (UnixRoot) Shaikh

      Is MySQL up and running ? If so, is the connection string is correct ?

  34. i am facing problem with node and mysql
    whenever i fetch data mysql which is having more than 30k rows and display the result as json .
    node is not performing well.
    i have to get the mysql result in array , so there is will be for loop which is blocking event

    1. Shahid (UnixRoot) Shaikh

      This is where “streams” comes in handy.

      30K JSON doc won’t get handled in memory.

  35. Fantastic tutorial , but Its happended and strage thing:

    connection.query doesnt work when i set a sleep() function as it: (Any idea?)

    connection.query(‘UPDATE……’)
    sleepFor(2000);

    function sleepFor( sleepDuration ){
    var now = new Date().getTime();
    while(new Date().getTime() < now + sleepDuration){ /* do nothing */ }
    }

  36. Fantastic tutorial , but Its happended an strage thing:

    connection.query doesnt work when i set a sleep() function as it: (Any idea?)

    connection.query(‘UPDATE……’)
    sleepFor(2000);

    function sleepFor( sleepDuration ){
    var now = new Date().getTime();
    while(new Date().getTime() < now + sleepDuration){ /* do nothing */ }
    }

  37. Hi ,
    how can we use it in different files like for example employee.js and inventory.js
    like iam unable to find example wherein you define database name and connect in app.js and use some thing so that we can access db obj in routes file employee.js and inventory.js

    1. Shahid (UnixRoot) Shaikh

      Hi matt,
      I do it like this.
      – Create a separate file containing code for DB handling such as Connection etc.
      – Create different model files that deals with database operation and call our DB file to handle DB stuff.
      – Include these model files in controller where our routes is and call those functions on API call.

      Hope it help.s

  38. I was trying this sample and found the first set of code where you initially do a siege and it produces the error, may be better to just use a browser instead of siege as always the 2nd call to that node server code would error.

    I took it a step further and put the createConnection and connect within the app.get function and did some stress testing using an external RDS mysql. I found that straight createConnection vs createPool connection method results in almost no transaction rate difference (390trans/sec on a siege -c200 -t30s -d1

    That leads me to really question the benefit of that added pool code complexity. I get the idea of reusing a pooled cached connection but not sure where the real benefit is?

    I tried but was unable to even break the straight app.get {createConnection / connect / query / end} type connection without pooled connections.

    1. actually i was wrong. I missed the connection failed errors. under a siege -c200 -t60s -d3 non-pooled connect resulted in 16 fails out of about 8000 hits. So I did prove pooled connection is needed to queue under stress. Thanks for a great learning tool

  39. Installed mysql however npm appears to think that it is extraneous. I have not been able to find any reason why this is so. My only guess is that mysql is somehow already installed with npm by default?

    If not, then I should just ignore the error?

  40. Hello
    I try it in mac (with Mamp)
    but nothing happen and wait in same line
    what is problem?

  41. I have the following error when using the address of my server, with LOCALHOST or 127.0.0.1 it works perfectly:
    Error: ER_ACCESS_DENIED_ERROR: Access denied for user ‘idsprueba’@’static-201-245-170-116.static.etb.net.co’ (using password: YES)
    at Handshake.Sequence._packetToError (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolsequencesSequence.js:52:14)
    at Handshake.ErrorPacket (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolsequencesHandshake.js:103:18)
    at Protocol._parsePacket (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolProtocol.js:279:23)
    at Parser.write (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolParser.js:76:12)
    at Protocol.write (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolProtocol.js:39:16)
    at Socket. (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibConnection.js:103:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    ——————–
    at Protocol._enqueue (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolProtocol.js:145:48)
    at Protocol.handshake (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolProtocol.js:52:23)
    at Connection.connect (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibConnection.js:130:18)
    at logIn

  42. Note that the next release of Sequelize will be practically rewritten by MariaDB. Between the now obsolete MySQL and MariaDB, we see who is taking node far more seriously.

  43. router.get(‘/’, function (req, res, next) {

    do_queries = function (callback) {
    db.all(“SELECT * FROM user where id=?”, 1, function (err, rows) {
    if (err) {
    callback(err);
    return;
    }
    db.each(“SELECT * FROM product where user_id=?”, row.id, function (err, res2) {
    if (err) {
    callback(err);
    return;
    }
    callback(null, res2); // think ‘return’
    });
    });
    }

    request_handler = function (req) {
    do_queries(function (err, result) {
    if (err)
    report_error(err);
    else
    console.log(result);
    res.render(“index”,{“data”:result}); //it show only one record my index template //
    });
    //console.log(data);
    }
    request_handler();

    });

    please solve my problem

  44. Any fix for error “Can’t add new command when connection is in closed state”? mysql2 npm

  45. Hi there, I have a very complex report, so the first run it draw table with some data, then, for each row we need to query some other data on a Node application with MySql. The question is: Do I have one connect open on the database, with one thread or something else?

Comments are closed.