How to promisify a mysql pool connection in Node js?

Question

I'm trying to understand how to use the node js util to promisify pool connections. I would keep my code clean using async/await logic ( without the callback hell, that I really don't like especially with transactions ).

here is my config file:

const mysql = require('mysql');
const util = require('util');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME
});

// Ping database to check for common exception errors.
pool.getConnection((err, connection) => {
  if (err) {
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      console.error('Database connection was closed.')
    }
    if (err.code === 'ER_CON_COUNT_ERROR') {
      console.error('Database has too many connections.')
    }
    if (err.code === 'ECONNREFUSED') {
      console.error('Database connection was refused.')
    }
  }

  if (connection) connection.release();

  return;
})

// Promisify for Node.js async/await.
pool.query = util.promisify(pool.query)

module.exports = pool;

I'm executing single easy queries like this (and they work fine):

let sql = "SELECT * FROM products WHERE code = ? ;"
let param = [code];
let results = await pool.query(sql, param);

I'm developing transactions in this way ( I think it is a completely wrong approach ):

try {
    await pool.query('START TRANSACTION');

    sql = "INSERT INTO test (name) VALUES ( ? ) ;"
    param = ['pippo'];
    results = []
    await pool.query(sql, param);

    await pool.query('COMMIT');
} catch (error) {
    await pool.query('ROLLBACK');
    return next(error)
}

With transactions I shouldn't use pool.query ( that, I think, get each time a new connection and automatically release it when the query is finished ).

It seems to me that pool.query create a big problem with transactions: If just run one transaction at a time is ok, but if run 2 (or more) transactions at the same time maybe the COMMIT of the second transaction can COMMIT all the queries of the first transaction just because is executed before the first COMMIT.

I think I should instead get a new connection use the connection for the entire flow of the transaction and release it at the end. So each transaction flow needs an own connection.

But I don't know how to promisify a pool.getConnection as I promisify the pool.query.

I was trying something like:

pool.getConnection = util.promisify(pool.getConnection).bind(pool)
const conn = await pool.getConnection();

let sql = "SELECT * FROM test ;"
let param = [];
let results = await conn.query(sql); // I don't get here the expected rows 

but it doesn't work. I don't become the rows in result but if i console.log(results) I have this:

Query {
  _events: [Object: null prototype] {
    error: [Function],
    packet: [Function],
    timeout: [Function],
    end: [Function]
  },
  _eventsCount: 4,
  _maxListeners: undefined,
  _callback: undefined,
  _callSite: Error
      at Protocol._enqueue (C:\Users\rocco\wa\ferramenta\server\node_modules\mysql\lib\protocol\Protocol.js:144:48)
      at PoolConnection.query (C:\Users\rocco\wa\ferramenta\server\node_modules\mysql\lib\Connection.js:198:25)
      at C:\Users\rocco\wa\ferramenta\server\routes\imports.js:304:30
      at processTicksAndRejections (internal/process/task_queues.js:97:5),
  _ended: false,
  _timeout: undefined,
  _timer: Timer { _object: [Circular], _timeout: null },
  sql: 'SELECT * FROM test ; ',
  values: [],
  typeCast: true,
  nestTables: false,
  _resultSet: null,
  _results: [],
  _fields: [],
  _index: 0,
  _loadError: null,
  _connection: PoolConnection {
    _events: [Object: null prototype] {
      end: [Function: _removeFromPool],
      error: [Function]
    },
    _eventsCount: 2,
    _maxListeners: undefined,
    config: ConnectionConfig {
      host: 'localhost',
      port: '3306',
      localAddress: undefined,
      socketPath: undefined,
      user: 'root',
      password: '---myPassword---',
      database: '---nameOfMyDb---',
      connectTimeout: 10000,
      insecureAuth: false,
      supportBigNumbers: false,
      bigNumberStrings: false,
      dateStrings: false,
      debug: undefined,
      trace: true,
      stringifyObjects: false,
      timezone: 'local',
      flags: '',
      queryFormat: undefined,
      pool: [Pool],
      ssl: false,
      localInfile: true,
      multipleStatements: false,
      typeCast: true,
      maxPacketSize: 0,
      charsetNumber: 33,
      clientFlags: 455631,
      protocol41: true
    },
    _socket: Socket {
      connecting: false,
      _hadError: false,
      _parent: null,
      _host: 'localhost',
      _readableState: [ReadableState],
      readable: true,
      _events: [Object: null prototype],
      _eventsCount: 4,
      _maxListeners: undefined,
      _writableState: [WritableState],
      writable: true,
      allowHalfOpen: false,
      _sockname: null,
      _pendingData: null,
      _pendingEncoding: '',
      server: null,
      _server: null,
      timeout: 0,
      [Symbol(asyncId)]: 11,
      [Symbol(kHandle)]: [TCP],
      [Symbol(kSetNoDelay)]: false,
      [Symbol(lastWriteQueueSize)]: 0,
      [Symbol(timeout)]: Timeout {
        _idleTimeout: -1,
        _idlePrev: null,
        _idleNext: null,
        _idleStart: 1284,
        _onTimeout: null,
        _timerArgs: undefined,
        _repeat: null,
        _destroyed: true,
        [Symbol(refed)]: false,
        [Symbol(kHasPrimitive)]: false,
        [Symbol(asyncId)]: 14,
        [Symbol(triggerId)]: 1
      },
      [Symbol(kBuffer)]: null,
      [Symbol(kBufferCb)]: null,
      [Symbol(kBufferGen)]: null,
      [Symbol(kCapture)]: false,
      [Symbol(kBytesRead)]: 0,
      [Symbol(kBytesWritten)]: 0
    },
    _protocol: Protocol {
      _events: [Object: null prototype],
      _eventsCount: 7,
      _maxListeners: undefined,
      readable: true,
      writable: true,
      _config: [ConnectionConfig],
      _connection: [Circular],
      _callback: null,
      _fatalError: null,
      _quitSequence: null,
      _handshake: true,
      _handshaked: true,
      _ended: false,
      _destroyed: false,
      _queue: [Array],
      _handshakeInitializationPacket: [HandshakeInitializationPacket],
      _parser: [Parser],
      [Symbol(kCapture)]: false
    },
    _connectCalled: true,
    state: 'authenticated',
    threadId: 117,
    _pool: Pool {
      _events: [Object: null prototype] {},
      _eventsCount: 0,
      _maxListeners: undefined,
      config: [PoolConfig],
      _acquiringConnections: [],
      _allConnections: [Array],
      _freeConnections: [],
      _connectionQueue: [],
      _closed: false,
      query: [Function],
      getConnection: [Function: bound ],
      [Symbol(kCapture)]: false
    },
    [Symbol(kCapture)]: false
  },
  [Symbol(kCapture)]: false
}

Some ideas ? THX

Answer 1

For 2024 users, mysql2 package provide promisified function:

import { Pool, PoolConnection } from 'mysql2/promise';

Answer 2

Here is an easy example about how I solved it:

pool.getConnection = util.promisify(pool.getConnection)
let conn = await pool.getConnection();
conn.query = util.promisify(conn.query)

sql = "INSERT INTO test (name) VALUES ( ? ) ; ";
param = ['fakename'];
results = [];
results = await conn.query(sql, param)

conn.release(); // is important remember to release the connection