promise - Exiting Loop with ACID Transactions with Knex SQL Query Builder & Bluebird in Node.js -
we're using knex sql query builder perform acid transaction in node , we're experiencing strange behavior while using loop knex. code below takes array of tables , conditionally performs insert or update. first table 'transactionheader' , processed first. then, rows in 'transactiondetail' table processed within overall transaction. new keys (rowids) accumulated in 'rowids' array.
problem: main problem seems impossible exit loop in processtransactiondetail() if there's error returned knex. neither throw nor return exit loop or function. means if there error processing transactiondetail continue process remaining rows before exiting.
let rowids: any[] = []; knex.transaction(function(trx) { // process transactionheader if (transactionheader.rowid) { // update transactionheader trx('transaction') .transacting(trx) .update(transactionheader) .where('rowid', transactionheader.rowid) .then(function(transactionrowid) { rowids.push({ table: 'transaction', rowid: transactionheader.rowid }); // update transactiondetail rows. processtransactiondetail(transactionrowid, trx); }) .catch(trx.rollback); } else { // insert transactionheader trx('transaction') .transacting(trx) .insert(transactionheader, 'rowid') .then(function(transactionrowid) { rowids.push({ table: 'transaction', rowid: transactionrowid }); // insert transactiondetail rows. processtransactiondetail(transactionrowid, trx); }) .catch(trx.rollback); } }).then(function(inserts) { console.log('success!', rowids) callback(null, { success: true }, { data: rowids }) return; }).catch(function(error) { console.error('error', error); callback(null, { success: false, message: error.message }, { data: rowids }) return; }); /* * process transactiondetail rows. */ function processtransactiondetail(transactionheaderrowid: number, trx) { var promise: any; let table: table; let rowid: number; (let = 1; < tablestoprocess.length; i++) { table = tablestoprocess[i]; rowid = table.data[0].rowid; // update if (rowid) { (let row = 0; row < table.data.length; row++) { promise = knex(table.name) .transacting(trx) .update(table.data[row]) .where('rowid', rowid) .then(function(rowid) { rowids.push({ table: table.name, rowid: rowid }); .catch(function(error) { // -------------------------------- // **problem**: there no way break loop // -------------------------------- throw 'error'; return; // -------------------------------- }) } // insert } else { (let row = 0; row < table.data.length; row++) { promise = knex(table.name) .transacting(trx) .insert(table.data[row]) .then(function(rowid) { rowids.push({ table: table.name, rowid: rowid }); }) .catch(function(error) { // -------------------------------- // **problem**: there no way break loop // -------------------------------- throw 'error'; return; // -------------------------------- }); } } } promise.then(function(x) { promise.then(trx.commit); }); } updated: proper structure? not sure error handlers @ bottom needed.
knex.transaction(function(trx) { // update row function updaterow(table, rowid, row) { return knex(table.name) .transacting(trx) .update(row) .where('rowid', rowid) .then(function(rowid) { rowids.push({ table: table.name, rowid: rowid }); }); } // insert row function insertrow(table, rowid, row) { return knex(table.name) .transacting(trx) .insert(row) .then(function(rowid) { rowids.push({ table: table.name, rowid: rowid }); }); } // process tables promise.mapseries(tablestoprocess, function(table) { let rowid = table.data[0].rowid; // choose right function apply rows var fn = rowid ? updaterow : insertrow; // fn needs table , rowid fn = fn.bind(this, table, rowid); // process rows return promise.mapseries(table.data, fn) .then(function(result) { // result array knex promises result return result; }).catch(function(err) { console.log('an error happened'); //trx.rollback(); // question: needed? throw err; // there way }); }).then(function(result) { console.log('success', result); trx.commit(); // callback(null, { success: true }, { data: rowids }) // return; }).catch(function(error) { console.log('error', error); trx.rollback(); callback(null, { success: false, message: error.message }, { data: rowids }) }); }).then(function(inserts) { console.log('success!', rowids) callback(null, { success: true }, { data: rowids }) }).catch(function(error) { console.log('error', error); callback(null, { success: false, message: error.message }, { data: rowids }) });
you're dealing promises have use way loop supporting them. there bluebird's mapseries() example:
var promise = require('bluebird'); function updaterow(table, rowid, row) { return knex(table.name) .transacting(trx) .update(table.data[row]) .where('rowid', rowid) .then(function(rowid) { rowids.push({ table: table.name, rowid: rowid }); }); } function insertrow(table, rowid, row) { return knex(table.name) .transacting(trx) .insert(table.data[row]) .then(function(rowid) { rowids.push({ table: table.name, rowid: rowid }); }); } // if there error, iteration stop promise.mapseries(tablestoprocess, function(table) { rowid = table.data[0].rowid; // choose right function apply rows var fn = rowid ? updaterow : insertrow; // fn need table , rowid fn = fn.bind(this, table, rowid); // call fn each row // if there error, iteration stop return promise.mapseries(table.data, fn) .then(function(result) { // result array knex promises result return result; }).catch(function(err) { console.log('an error happened'); trx.rollback(); throw err; }); }).then(function(result) { console.log('all good'); // can safely commit here trx.commit(); }).catch(function(err) { console.log('an error happened'); trx.rollback(); }); update
about questions:
knex.transaction(function (trx) { // update row function updaterow(table, rowid, row) { return knex(table.name) .transacting(trx) .update(row) .where('rowid', rowid) .then(function (rowid) { rowids.push({ table: table.name, rowid: rowid }); }); } // insert row function insertrow(table, rowid, row) { return knex(table.name) .transacting(trx) .insert(row) .then(function (rowid) { rowids.push({ table: table.name, rowid: rowid }); }); } // need return here 'then(function (inserts)' , catch work // process tables return promise.mapseries(tablestoprocess, function (table) { let rowid = table.data[0].rowid; // choose right function apply rows var fn = rowid ? updaterow : insertrow; // fn needs table , rowid fn = fn.bind(this, table, rowid); // process rows // if don't special in , catch, can remove them return promise.mapseries(table.data, fn) .then(function (result) { // result array knex promises result return result; }).catch(function (err) { // catch not necessary, // can remove don't need here console.log('an error happened'); //trx.rollback(); // question: needed? << // no, mistake, rollback done on other catch throw err; // there way }); }).then(function (result) { console.log('success', result); trx.commit(); return result; // callback(null, { success: true }, { data: rowids }) // return; }).catch(function (error) { console.log('error', error); trx.rollback(); throw error; // rethrow error when chain, if don't, it's promise resolved (ok) // below // callback(null, { success: false, message: error.message }, { data: rowids }); }); }).then(function (inserts) { console.log('success!', rowids) callback(null, { success: true }, { data: rowids }) }).catch(function (error) { console.log('error', error); callback(null, { success: false, message: error.message }, { data: rowids }) }); the 2 , 2 catch @ bottom merged. also, why there callback? it's better not mix promises , callbacks unless can't avoid it.
Comments
Post a Comment