CODEWITHSUNDEEP
javascriptmysqlnode.jsmariadb
TedTrippin
TedTrippin

Reputation: 3652

JS - how to end a db connection with chained promise

I declare my DB functions in one file and want them to return a promise so I can re-use them elsewhere. Question is how to end the connection? I know I could probably call conn.end() in every place I call my DB function but could I not do it in the DB function itself?

Caller...

function scanBoard(qrCode) {
    getBoard(qrCode)
    .then(b => {
        board = b;
    });
}

DB function (note, code wont run as it contains all the things I've tried)

function getBoard(qrcode) {

    return db.createConnection(dbConfig)
    .then(conn => {
        let rows = conn.query("SELECT * FROM boards WHERE id = ?", [qrcode])
        .then(() => { conn.end() }); // Can't call it here cus it ends conn before rows is set

        conn.end(); // Can't call it here cus rows is still pending

        if (rows.length >= 1)
            return rows[0];

        return null;
    })
    .finally({
        conn.end(); // Can't call it here cus it gets called before the 'then' block in the caller
    });


}

Upvotes: 0

Views: 665

Answers (4)

Tim VN
Tim VN

Reputation: 1193

conn.query is async, therefor then will complete and finally will be triggered before the query has yielded any results, even if it only takes a couple ms. It does not matter that you're resolving the promise, the enclosing promise will fulfill, or rather the chained then will fulfill.

async function getBoard(qrcode) {
  const conn = await db.createConnection(dbConfig);

  try {
    return await conn.query("SELECT * FROM boards WHERE id = ?", [qrcode]);
  } finally {
    conn.end();
  }
}

Just as a tip - If this application is running constantly, you can just connect to the db once and reuse the connection, then the need to open and close the connection for every query is eliminated.

Upvotes: 2

Sohan
Sohan

Reputation: 6809

  async function scanBoard(qrCode)
 {

      let board = await getBoard(qrCode);
  }
  catch (e)
  {
      throw e;
  }

 }

async function getBoard(qrcode)
      {

                let dbConn = await  db.createConnection(dbConfig);
                let rows = await dbConn.query("SELECT * FROM boards WHERE id = ?", [qrcode]);
                if (rows.length >= 1)

                // If you need to re-use connection, create a connection instance and re-use.Do not close here

                return rows[0];            
        }

Upvotes: -1

Ashish Modi
Ashish Modi

Reputation: 7770

I wouldn't suggest closing the connection after each db method call. it is very less efficient, instead you could use connection pool. see here for more details

https://www.npmjs.com/package/mysql#pooling-connections

And when all the work is done you can close the connections in pool. See this

https://www.npmjs.com/package/mysql#closing-all-the-connections-in-a-pool

Hope this helps.

Upvotes: 2

Devolux
Devolux

Reputation: 164

I think that here the best solution is following the best practices... To solve your problem i think is better to sorround the conn.end(); in the finally part of the promise chain with an if so it gets triggered only if the query went well or if there is no requests pending. I'll do it like so:

.finally({
      if(conn){
          conn.end();
      }
 });

Obviously you have to remove the conn.end(); code line from any other part of your source code. I would even think about checking for other errors with more catch().

hope it helped it me up with any question you want.

Upvotes: -1

Related Questions