Node-mysql2: Example of using transaction with promise connection?

[hezjing]

Hi

Can you show me an example of how to start a transaction in the promise based connection?

[hezjing]

I'm trying to start a transaction with the connection like the following,

const mysql = require('mysql2/promise');
const pool = mysql.createPool({
...
});

pool.getConnection()
  .then(connection => {
    connection.beginTransaction();
    ...
  });

but got TypeError: connection.beginTransaction is not a function. What is the correct way of using promise wrapper of mysql2?

[sidorares]

Hi @hezjing ! beginTransaction() is a very simple helper that just makes START TRANSACTION query. Currently transaction helpers don't exist in promise wrapper for connection. You could do same query directly instead:

const mysql = require('mysql2/promise');
const pool = mysql.createPool({
...
});

pool.getConnection()
  .then(connection => {
    return connection.query('START TRANSACTION');
  })
  .then( () => {
       // do queries inside transaction
  })
  .then( () => {
       return connection.query('COMMIT');
  }) 

Alternatively you can get original connection object and use it with callback-style api:

const mysql = require('mysql2/promise');
const pool = mysql.createPool({
...
});

pool.getConnection()
  .then(promiseConnection => {
      var conn = promiseConnection.connection;
      conn.beginTransaction( (err) => {
        conn.query('select 1+1', () => {
           conn.commit( () => conn.release() )
        })
     });
  })
});

[hezjing]

Thanks and appreciate your advice!

It would be helpful to include this in the documentation too!

[hezjing]

By the way, I think the first approach which return connection.query('START TRANSACTION') is rather complicated because we have find a way to pass down the same connection to the next then(). Unless we keep the connection in a variable and referenced it in the then().

[sidorares]

you could save ref to connection in outer closure and then use return connection.query(...) so that next then gets result as a parameter:

pool.getConnection().then(conn => {
   conn.query('select 1+1')
     .then( (rows1) => {
        //use data from previous query, make another one
        return conn.query('select 2+2'); 
     })
     .then( (rows2) => {
        //use data from previous query, make another one
        return conn.query('select 2+1'); 
     })
});

[pradip9545]

how to rollback the transaction when there is a error to query execution?

dbCon.getConnection()
    .then(connection => {
        return connection.query('START TRANSACTION')
            .then((row) => {
                dbCon.query('INSERT INTO `tbl_activity_log`(dt, tm,userid,username,activity) VALUES(?,?,?,?,?)',
                    ['2019-02-21', '10:22:01', 'S', 'Pradip', 'RAhul3']);
                console.log('asa');
            })
            .then((row1) => {
                dbCon.query('INSERT INTO `tbl_activity_log`(dt, tm,userid,username,activity) VALUES(?,?,?,?,?)',
                    ['2019-02-21', '10:22:01', 'S', 'Pradip','this is test and the valid out put is this and then']);
                //return connection.query('COMMIT');
            }).then(() => {
                return connection.query('COMMIT');
            }).catch((error) => { 
                return connection.query('ROLLBACK');
            })
    });

i have used the above menioned code but not successful in rollbacking the first entry

[sidorares]

can you use async/await?

const connection = await dbCon.getConnection();
try {
  await connection.query('START TRANSACTION');
  await query('INSERT INTO `tbl_activity_log` (dt, tm,userid,username,activity) VALUES(?,?,?,?,?)',
                    ['2019-02-21', '10:22:01', 'S', 'Pradip', 'RAhul3']);
  await dbCon.query('INSERT INTO `tbl_activity_log` (dt, tm,userid,username,activity) VALUES(?,?,?,?,?)', ['2019-02-21', '10:22:01', 'S', 'Pradip','this is test and the valid out put is this and then']);
  await connection.release();
} catch(e) {
  await connection.query('ROLLBACK');
  await connection.release();
}

[belqit]

can you use async/await?

const connection = await dbCon.getConnection();
try {
  await connection.query('START TRANSACTION');
  await query('INSERT INTO `tbl_activity_log` (dt, tm,userid,username,activity) VALUES(?,?,?,?,?)',
                    ['2019-02-21', '10:22:01', 'S', 'Pradip', 'RAhul3']);
  await dbCon.query('INSERT INTO `tbl_activity_log` (dt, tm,userid,username,activity) VALUES(?,?,?,?,?)', ['2019-02-21', '10:22:01', 'S', 'Pradip','this is test and the valid out put is this and then']);
  await connection.release();
} catch(e) {
  await connection.query('ROLLBACK');
  await connection.release();
}

you forgot await connection.commit();

[higorvaz]

await connection.release();

It is:
await connection.release();

[joobisb]

@higorvaz @belqit @sidorares i'm trying to run something like this
const connection = await dbCon.getConnection(); try { await connection.query('START TRANSACTION'); for (const item of items) { await connection.query(`INSERT INTO ITEM....`) } await connection.release(); } catch(e) { await connection.query('ROLLBACK'); await connection.release(); }
but it throws this error
message: 'Lock wait timeout exceeded; try restarting transaction', code: 'ER_LOCK_WAIT_TIMEOUT', errno: 1205, sqlState: 'HY000', sqlMessage: 'Lock wait timeout exceeded; try restarting transaction'

[merodiro]

You can insert multiple items using one query instead of using multiple insert queries.
I don't know if this will fix your problem, though.

await connection.query('INSERT INTO items (k1, k2, k3) VALUES ?', [
        items.map((item) => [item.k1, item.k2, item.k3]),
])

[joobisb]

You can insert multiple items using one query instead of using multiple insert queries.
I don't know if this will fix your problem, though.

await connection.query('INSERT INTO items (k1, k2, k3) VALUES ?', [
        items.map((item) => [item.k1, item.k2, item.k3]),
])

@merodiro yes i've already implemeted it like this..it actually works

[TrejoCode]

can you use async/await?

const connection = await dbCon.getConnection();
try {
  await connection.query('START TRANSACTION');
  await query('INSERT INTO `tbl_activity_log` (dt, tm,userid,username,activity) VALUES(?,?,?,?,?)',
                    ['2019-02-21', '10:22:01', 'S', 'Pradip', 'RAhul3']);
  await dbCon.query('INSERT INTO `tbl_activity_log` (dt, tm,userid,username,activity) VALUES(?,?,?,?,?)', ['2019-02-21', '10:22:01', 'S', 'Pradip','this is test and the valid out put is this and then']);
  await connection.release();
} catch(e) {
  await connection.query('ROLLBACK');
  await connection.release();
}

Isn't it necessary to commit?

[jastisriradheshyam]

Assumption: The following code in wrapped by async function

For Node.js version 8+

let conn = null;
try {
    conn = await pool.getConnection();
    await conn.beginTransaction();
    const [response, meta] = await conn.query("SELECT * FROM tbl_sample");
    console.log(response, meta);
    await conn.query("INSERT INTO tbl_sample SET data = ?",
      ["some_data"]);
    await conn.query("INSERT INTO tbl_another_sample SET data = ?",
      ["some_data"]);
    await conn.commit();
    await conn.release();
  } catch (error) {
    if (conn) {
        await conn.rollback();
        await conn.release();
    }
    throw error;
  }

For Node.js version 10+

let conn = null;
try {
    conn = await pool.getConnection();
    await conn.beginTransaction();
    const [response, meta] = await conn.query("SELECT * FROM tbl_sample");
    console.log(response, meta);
    await conn.query("INSERT INTO tbl_sample SET data = ?",
      ["some_data"]);
    await conn.query("INSERT INTO tbl_another_sample SET data = ?",
      ["some_data"]);
    await conn.commit();
  } catch (error) {
    if (conn) await conn.rollback();
    throw error;
  } finally {
    if (conn) await conn.release();
  }

[tech-srinath]

I'm sorry to say, but none of these answers seems to be working.
Here's the code I'm using

#384

smFn = async (req, res)=>{
let conn = null;
var paramz = [req.body.urcode, req.body.urname, req.body.pasw, req.body.blocked, req.body.blockeddate, req.body.branchid]
try {
conn = await con.getConnection();
console.log(conn)
await conn.beginTransaction();
const [data1, fld1] = await con.query("insert into bkusers (urcode, urname, pasw, blocked, blockeddate, branchid) values (?,?,?,?,?,?) ", paramz)
const [data2, fld2] = await con.query("insert into bkusers (urcode, urname, pasw, blocked, blockeddate, branchid) values (?,?,?,?,?,?) ", paramz)
const [data3, fld3] = await con.query("insert into bkusers (urcoder, urname, pasw, blocked, blockeddate, branchid) values (?,?,?,?,?,?) ", paramz)
const [data4, fld4] = await con.query("insert into bkusers (urcode, urname, pasw, blocked, blockeddate, branchid) values (?,?,?,?,?,?) ", paramz)
res.send(fld1, fld2, fld3, fld4)
await conn.commit()
// console.log(conn)
} catch (error) {
if (conn) await conn.rollback();
throw error;
} finally {
if (conn) await conn.release();
}
}