bluebird node-mysql2 transaction

Question

been trying to promisify node-mysql2 transaction, but can't get it to work, any pointers?

i've been through the docs at http://bluebirdjs.com/docs/api/disposer.html

i also have working version of non transaction related code, but getting transactions to work, along with proper resource disposal and error handling has been difficult. here are the call back based implementation taken from node-mysql api docs

connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query('INSERT INTO posts SET title=?', title, function(err, result) {
    if (err) {
      return connection.rollback(function() {
        throw err;
      });
    }

    var log = 'Post ' + result.insertId + ' added';

    connection.query('INSERT INTO log SET data=?', log, function(err, result) {
      if (err) {
        return connection.rollback(function() {
          throw err;
        });
      }  
      connection.commit(function(err) {
        if (err) {
          return connection.rollback(function() {
            throw err;
          });
        }
        console.log('success!');
      });
    });
  });
});

here are my implementations for query management

import { mysqldb } from '../config';
import { createPool } from 'mysql2';
import Pool from 'mysql2/lib/pool';
import Connection from 'mysql2/lib/connection';
import { using, promisifyAll, try as promiseTry } from 'bluebird';

promisifyAll([Pool, Connection]);
const pool = createPool(mysqldb);

export const getConnection = () =>
  pool.getConnectionAsync().disposer(connection =>
      connection.release());

export const withTransaction = (fn) =>
  using(getConnection(), tx =>
    promiseTry(fn(tx), tx.beginTransaction()).then(
      res => tx.commitAsync().catch(e => e).thenReturn(res),
      err => tx.rollbackAsync().catch(e => e).thenThrow(err)
    )
);

// withTransaction(tx =>
//   tx.executeAsync('sql1')
//     .then(tx.executeAsync('sql2'))
//     .then(tx.executeAsync('sql3'))
//     .then(tx.executeAsync('sql4'))
// );

export const query = (sql, params) =>
  using(getConnection(), connection =>
    connection.executeAsync(sql, params)
  );

however transactions gives me

Unhandled rejection TypeError: expecting a function but got [object Object]
    at apiRejection (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:10:27)
    at Promise.attempt.Promise.try (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/method.js:26:16)
    at /Users/willh/workspace/ChenPin/graphql/lib/services/mysql.js:35:30
    at tryCatcher (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/util.js:16:23)
    at /Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/using.js:184:26
    at tryCatcher (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:503:31)
    at Promise._settlePromise (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:560:18)
    at Promise._settlePromise0 (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:605:10)
    at Promise._settlePromises (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:684:18)
    at Promise._fulfill (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:629:18)
    at PromiseArray._resolve (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise_array.js:125:19)
    at PromiseArray._promiseFulfilled (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise_array.js:143:14)
    at Promise._settlePromise (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:565:26)
    at Promise._settlePromise0 (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:605:10)
    at Promise._settlePromises (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:684:18)
    at Promise._fulfill (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:629:18)
    at Promise._resolveCallback (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:424:57)
    at Promise._settlePromiseFromHandler (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:515:17)
    at Promise._settlePromise (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:560:18)
    at Promise._settlePromise0 (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:605:10)
    at Promise._settlePromises (/Users/willh/workspace/ChenPin/graphql/node_modules/bluebird/js/release/promise.js:684:18)

what is the right way?

update:

got it to work by going through the source code, turns out it was just a simple helper and it was really not worth using their helpers, below is the final working version. thanks for the help anyway :D

export const withTransaction = (fn) =>
  using(getConnection(), tx =>
    tx.queryAsync('START TRANSACTION').then(fn(tx)).then(
      res => tx.queryAsync('COMMIT').thenReturn(res),
      err => tx.queryAsync('ROLLBACK').catch(e => e).thenThrow(err)
    )
  );

// withTransaction(tx =>
//   tx.executeAsync('select :x + :y as z', { x: 1, y: 2 }).then(res1 =>
//   tx.executeAsync('select :x + :y as z', { x: res1[0].z, y: 1 })).then(res2 =>
//   tx.executeAsync('select :x + :y as z', { x: res2[0].z, y: 1 })).then(res3 =>
//   tx.executeAsync('select :x + :y as z', { x: res3[0].z, y: 1 })).then(res4 => res4)
// );

by the way, the original promisify method works, there was no difference but thanks for helping me out!

Vlad Ankudinov · Answer 1 · 2016-03-03T07:48:23.023

You've got the error because promisifyAll works with functions and not the objects. In most cases functions live inside prototype of the Class. Try this

var Promise = require('bluebird');
var mysql = require('mysql2');
Promise.promisifyAll(mysql.Connection.prototype);
Promise.promisifyAll(require('mysql2/lib/pool').prototype)

In my practice example above works just fine.

If you feel like you want some more experiments in this area you could also manually traverse thru all elements and pick functions only, like in the following example I personally use for Postgres

var Promise = require('bluebird');
var pg = require('pg');
Object.keys(pg).forEach(function (key) {
  var Cls = null;
  try {
    Cls = pg[key];
    if (typeof Cls === 'function') {
      Promise.promisifyAll(Cls.prototype);
      Promise.promisifyAll(Cls);
    }
  } catch (e) {
    console.log(e);
  }
});
Promise.promisifyAll(pg);

bluebird node-mysql2 transaction

1 Answers1