Running Multiple Queries in Nodejs MySQL

Question

I'm using the mysql library for node.js applications. I'm new to MySQL in general, but I've been able to successfully run a single query and return the data to my webclient. The problem is it only seems to run once (before throwing an error). Here's the code running on my server:

//connect to MySQL server
connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }
  console.log('connected as id ' + connection.threadId);
});

//do MySQL query
connection.query('SELECT * FROM `mydatabase` WHERE `tag` = \"' + data.AppID + '\" AND `time` BETWEEN \"' + t_from.toISOString() + '\" AND \"' + t_to.toISOString() + '\" ORDER BY ID DESC', function (error, results, fields) {
  //return the results to webclient here.
});

//close the connection
connection.end();

So, basically every time I click a button, it sends a request to the server (using socket.io) and runs the query. It first creates the connect, runs the query (and returns the data to the webclient), and closes the connection. This works the first time I click the button, but any subsequent clicks returns nothing. My understanding was that I need to open and close the connection every time I run a query (and not leave it open)... but can someone tell me what I'm doing wrong?

you don't have to open and close it every time you make a query. You very well could instead have a connection pool and just request a connection from it for each request. Or have a connection that always stays open and reconnects on error. or that stays open for a period of time and then reconnects. — user400654
– user400654, Commented Mar 18, 2015 at 20:07
Don't close it. Leave it open for future requests. Move out connecting to DB to the initialization, somewhere near server.listen. Also, use connection pool, so you can run multiple queries in parallel and requests don't have to wait fight for it or wait on it to become available. — Aleksei Zabrodskii
– Aleksei Zabrodskii, Commented Mar 18, 2015 at 20:08

andyopayne · Accepted Answer · 2015-03-18 20:27:39Z

1

Per Kevin and Elmigranto's suggestions... I used a connection pool instead. Here's how I set it up:

//setup MySQL connection parameters
var mysql      = require('mysql');

var pool  = mysql.createPool({
  connectionLimit : 10,
  host     : 'somehost.com',
  user     : 'user',
  password : 'password'
});

Then, inside my function that I use to make the query, I use this:

//do MySQL query
pool.getConnection(function(err, connection) {
  // Use the connection
  connection.query('SELECT * FROM `mydatabase` WHERE `tag` = \"' + data.AppID + '\" AND `time` BETWEEN \"' + t_from.toISOString() + '\" AND \"' + t_to.toISOString() + '\" ORDER BY ID DESC', function (error, results, fields) {
    //return the results to the webclient here
    connection.release();
  });
});

Hope that helps anyone looking for the same answer.

answered Mar 18, 2015 at 20:27

andyopayne

1,3783 gold badges25 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

João Pimentel Ferreira Over a year ago

now you can use simply pool.query which is equivalent to getConnection->query->release

Collectives™ on Stack Overflow

Running Multiple Queries in Nodejs MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related