confusion about how connection.end() works in node.js mySQL module

Question

const mysql = require('mysql'); 
let connection = mysql.createConnection(...);
        connection.connect((err)=>{
           ...
           connection.query((err)=>{
           ...
           connection.end();});
        });

After I close the connection by using

connection.end()

, if I want to query the database again using the same credentials, do I need to make a new connection by calling

mysql.createConnection(...)

Or can I reuse the same connection by simply calling

connection.connect(...)

A little background: I'm deploying an angular/node.js app to a shared hosting website, and the web host has a maximum limit of 25 concurrent connections to mySQL database, therefore I need to make sure I close a connection properly after a user does his query. I am not sure if I could reuse the connection created by mysql.createConnection(...) after I close that connection, or do I need to create a brand new connection.

You'll want a pool. Configure it to not use more than 25 connections. — Evert, Jun 26 '18 at 08:38

score 2 · Accepted Answer · answered Jun 26 '18 at 05:11

2

You can use one global connection for getting data from db .

If You working on single file than you can write as

app.js one file only

 var mysql      = require('mysql');
 var connection = mysql.createConnection(...);

    connection.query('SELECT 1', function (error, results, fields) {
     if (error) throw error;
   // connected!
  });

if you want to use same connection in multiple file than you can write as
app.js

 app.use(  
    connection(mysql, {
         host: xxxxx, 
         user: 'root',
         password : xxxx, 
         port : 3306, 
         database:dbname

     },'pool'),  
   );

  var oem = require('./routes/type');
  app.get('/api/oemtype',oem.type);

For the second file type.js

   exports.type = function(req, res){

     req.getConnection(function(err,connection){

      var query = connection.query('SELECT * FROM type',function(err,rows)
      {
          if(err)
          res.json({
            status:0
        });

              res.send(rows);
         res.render('customers',{page_title:"Customers - Node.js",data:rows});


       });

  });

   };

No need to use of connection.end().

answered Jun 26 '18 at 05:11

Prashant Gupta

788
8
26

1

The web host I am deploying my app to has a maximum limit of 25 concurrent connections to mySQL database. Will not calling connection.end( ) leave the connection hanging, therefore causing problems to the maximum limit? – Eddie Lin Jun 26 '18 at 05:30
1

If you end the connection every time then mysql create every time new connection It will decrease the performance of the app and mysql. It's better to use pool connection. – Prashant Gupta Jun 26 '18 at 05:36
1

Does the pool create a single pool for all users? For instance, if I set the connectionLimit to 25 (as in var pool=mysql.createPool({connectionLimit:10, host:...}); will this create a connection pool of 25 for each user or will this create a connection pool of 25 for all users? – Eddie Lin Jun 26 '18 at 05:47
1

Sorry for the delay, pool will be create for all 25 user not for each user.. instead of end mehod you can use connection.release() after query work is done. check this https://stackoverflow.com/questions/18496540/node-js-mysql-connection-pooling – Prashant Gupta Jun 26 '18 at 11:55
Thanks! This is new knowledge to me and I will do more research myself :) – Eddie Lin Jun 26 '18 at 16:12

confusion about how connection.end() works in node.js mySQL module

1 Answers1