[Question/Problem] Can't make transactions work using promise pool

[jwpegram]

Not sure exactly what I am doing wrong, it looks like this should work but here goes.

/lib/services/database.js

/**
 * Module dependencies.
 */
const path = require('path');
const mysql = require('mysql2');
const { ConnectionString } = require('connection-string');
const parse = (cn) => new ConnectionString(cn);

const config = require(path.resolve('./config/config'));
const log = require(path.resolve('./lib/services/logger'));

/**
 * Create mysql database pool for designated instance
 *
 * Connection pools help reduce the time spent connecting to the MySQL server by reusing a previous connection,
 * leaving them open instead of closing when you are done with them. This improves the latency of queries as
 * you avoid all of the overhead that comes with establishing a new connection.
 * The pool does not create all connections upfront but creates them on demand until the connection limit is reached.
 * You can use the pool in the same way as connections (using pool.query() and pool.execute()):
 * https://github.com/sidorares/node-mysql2#using-connection-pools
 *
 * @returns {pool|boolean}
 */
function connection() {
  try {

    const mysqlConnectionString = process.env.MYSQL_URI;
    if (!mysqlConnectionString) throw new Error('MySQL connection string not found.');
    const obj = parse(mysqlConnectionString);

    const pool = mysql.createPool({
      user: obj.user,
      password: obj.password,
      host: obj.hosts[0].name,
      port: obj.hosts[0].port,
      database: obj.path[0],
      charset: config.mysql.options.charset, // Default database encode
      dateStrings: config.mysql.options.dateStrings,
      waitForConnections: config.mysql.pool_options.waitForConnections, // if true, connection queue will waiting can be used connect; if false immediately throw Error.
      connectionLimit: config.mysql.pool_options.connectionLimit, // Maximum number of connections that can be created at a time
      queueLimit: config.mysql.pool_options.queueLimit, // The max request quantity. line up at the head of getConnection function. 0: unlimited
      keepAliveInitialDelay: 10000,
      enableKeepAlive: true,
    });

    return pool.promise();
  } catch (err) {
    log.error(err);
    return false;
  }
}

const pool = connection();

module.exports = {
  connection: async () => pool.getConnection(),
  execute: (...params) => pool.execute(...params),
  query: (...params) => pool.query(...params),
};

test-promise-query.js (works)

const path = require('path');
const conn = require(path.resolve('./lib/services/database'));

async function test() {
  const [rows] = await conn.query('SELECT * FROM mytable');
  console.log(rows);
}

test();

test-promise-transaction.js (fails)

const path = require('path');
const conn = require(path.resolve('./lib/services/database'));
async function test() {

  await conn.beginTransaction();
  const [rows] = await conn.query('SELECT * FROM mytable');
  console.log(rows);
  
  await conn.commit();
  conn.release();
}

test();

The promise transaction test fails with:
error: uncaughtException: conn.beginTransaction is not a function

I'm sure I'm not doing something correctly.. anyone got any ideas?

[sidorares]

if I read your code correctly @jwpegram conn returned from the database helper is a pool wrapper, so you probably want to get connection from the pool first to start transaction (haven't tried the code but hopefully you can see the idea):

const pool = require('./lib/services/database');
async function test() {

  const conn = await pool.connection()
  try {
    await conn.beginTransaction();
    const [rows] = await conn.query('SELECT * FROM mytable');
    console.log(rows);
    await conn.commit();
  } catch(error) {
    await conn.rollback();
  }
  finally {
    conn.release();
  }
}

test().catch(console.error);

[jwpegram]

That fixed it @sidorares. Thanks for the nudge, I figured it was something simple.