CODEWITHSUNDEEP
mysqlnode.js
Jeremy Jones
Jeremy Jones

Reputation: 5631

How do I disconnect from my database gracefully in a callback / promise - based environment?

I need to know how to disconnect from my MySQL database after lots of individual callbacks have finished. I have a node.js cron script running on AWS EC2 which accesses s3 buckets and MySQL databases on AWS RDS. The cron script looks something like this:

const mysql = require("mysql2"),
  AWS = require("aws-sdk"),
  s3 = new AWS.S3(),
  connection = mysql.connect({...});

connection.connect();

connection.query(`SELECT ... LIMIT 100`, (error, results) => {
   if (error) throw new Error(error);
   for (let idx in results) {
      const row = results[idx],
            Key = `my/key/${row.id}`;

      s3.getObject({Bucket, Key}, (error, object) => {
          // do more things, with more callbacks
      });
   }
});

setTimeout(() => connection.end(), 10000);  // disconnect database in 10 seconds

The script doesn't exit until I disconnect from the database using connection.end(). I can't disconnect as normal e.g. after the for loop, because the various callbacks are still running. I need to know when they're all finished. Currently I just disconnect after 10 seconds because everything should have completed by then. If I don't do that then I end up with lots of never-ending processes running.

Do I need to set flags & counts of each thing, and then use setInterval or something until they're all finished and it's safe to disconnect? OK to do but is that the right approach when using callbacks, promises & thens?

Upvotes: 0

Views: 192

Answers (2)

Jeremy Jones
Jeremy Jones

Reputation: 5631

I just wanted to post as well that Promise.all() is definitely a great way to go, however it's not the only approach.

In this day & age, where the cost of connecting to & disconnecting from your database can be very cheap, I find it simpler to just connect on every query and disconnect after:

const dbOneQuery = (sql, bindVars, callback) => {
  const dbConnection = getConnection(); // mysql2.createConnection etc
  dbConnection.query(sql, bindVars, (error, result) => {
    dbConnection.end();
    if (callback) callback(error, result);
  });
};

and that way there aren't any connections held open to be closed.

If in future I move to persistent connections again, I can just change what getConnection() does and use something that overrides .end() etc..

For me this approach has been simpler overall compared to managing a single shared connection to the database, with no real downsides.

Upvotes: 0

Manuel Spigolon
Manuel Spigolon

Reputation: 12870

You can do it with counters or flags as you said, or with Promise.all:

const mysql = require("mysql2"),
  AWS = require("aws-sdk"),
  s3 = new AWS.S3(),
  connection = mysql.connect({...});

function doQuery(){
  connection.connect();
  return new Promise((resolve, reject)=>{
    connection.query(`SELECT ... LIMIT 100`, (error, results) => {
      if (error) { return reject(new Error(error)); }
      resolve(results)
   });
  })
}

doQuery()
  .then(results => {
    const jobs = results.map(row => {
      const Key = `my/key/${row.id}`;
      return new Promise((resolve, reject) => {
        s3.getObject({Bucket, Key}, (error, object) => {
          // do more things, with more callbacks
          resolve('ok')
        });
      })

    })

    return Promise.all(jobs)
  })
  .finally(()=>{
    connection.end()
  })

Upvotes: 1

Related Questions