connection.end() not closing connection

[hugoanderson5]

Hello, I am having trouble trying to get my connections to close. Every query creates a new connection in MySQL (for every query, the number of 'Connection' seen from "show status where variable_name= 'Connections'" increases by one). My code looks like this (see below). I've verified with a log statement that it is getting to the finally{} block. Any ideas what I might be doing wrong?

db.js

const mysql = require('mysql2/promise');
const config = require('../config');

async function query(sql, params) {

  const connection = await mysql.createConnection(config.db);

  let results = {}

  try {

    [results,] = await connection.execute(sql, params);

  } catch (error) {

    console.log('Database error: ' + error)

  } finally {
    
    await connection.end();
  }
  return results;
}

module.exports = {
  query
}

[sidorares]

can you test this with command line script that performs just one query? If the process exits after the query this means underlying tcp connection is closed.

[hugoanderson5]

Thanks sidorares, I'm using this script from the command line (above I'm running in Express) and it seems to be closing just fine.

// get the client
const mysql = require('mysql2');
// create the connection
const con = mysql.createConnection(
  {host:'localhost', user: 'test', password: '*******', database: 'test'}
);
con.promise().query("SELECT * from apps")
  .then( ([rows,fields]) => {
    console.log(rows);
  })
  .catch(console.log)
  .then( () => con.end());

[sidorares]

I was suggesting to use script that imports your query wrapper module. Assuming example in the original comment is in db.js file, try this

const { query } = require('./db.js');

query("SELECT * from apps")
  .then(rows => {
    console.log('non exception result:', rows);
  })
  .catch(console.log);

[KunZhou-at]

I had a a tangent issue where calling end on connections isn't emitting the end events, turns out it's because close sends COM_QUIT to the mysql server and emit the end event asynchronously, unlike close which closes the tcp connection and emits end immediately, and my piece of code that was checking the end event got executed before the end event was emitted due to JS's async nature.

[sidorares]

correct @KunZhou-at , maybe we should document this better. .end() is a 'graceful close' and only sends "hey please finish with this connection" command to the server, and after that expects underlying stream to be closed. Once tcp connection (or other transport if not tcp) is closed by server it emits "end" event on the connection