Execute Transactions from Azure Mobile App API (node.js)
Using the Azure Mobile App API interface (like: https://mymobileapp.azurewebsites.net/api/doTransActionStuff ) with a node.js backend, I found documentation around executing SQL Transactions a little light. So… Here are some options you could use:
1. Call a stored procedure that does everything for you
This would be the way I would do it! Stick all of the logic in a stored procedure and make the Azure SQL Database do the work for you. This also is the best performing solution since there is only one call to the Database and the Database is doing the work for me. This assumes you can write a stored procedure of course! Simply call the stored procedure with arguments from within the API using something like this:
// an example of executing a stored procedure that internally is using transactions
post: (request, response, next) => {
var query = {
sql: 'EXEC completeAllStoredProcedure @completed',
parameters: [
{ name: 'completed', value: request.query.completed }
]
};
request.azureMobile.data.execute(query)
.then(function (results) {
response.json(results);
});
}
Note: The parameters are passed in via the client. The raw HTTP would look something like this –> https://mymobileapp.azurewebsites.net/api/doTransActionStuff?completed=true. For .NET Clients you would use InvokeApiAsync https://msdn.microsoft.com/en-us/library/azure/dn268343(v=azure.10).aspx
iOS clients: InvokeApi https://azure.microsoft.com/en-us/documentation/articles/app-service-mobile-ios-how-to-use-client-library/
2. Call execute and build your statements into a query
Yet another way that would be fairly efficient is to build up the SQL statement as a string and embedded any passed in parameters (like above) if necessary and call execute
"get": function (req, res, next) {
var query = {
sql: "SET XACT_ABORT ON \
BEGIN TRANSACTION \
<”normal” statement that works> \
<”invalid” statement that goes against a constraint I created> \
COMMIT TRANSACTION \
SET XACT_ABORT OFF",
parameters: []
};
req.azureMobile.data.execute(query);
}
3. User the built in Driver Transaction functionality
I would use this if I am already familiar with driver (like tedious) and want all my code to be in the Mobile App. Not the most efficient and results in several calls to the Database:
module.exports = {
"get": function (req, res, next) {
var globalErrMessage = "";
function handleErr(source, err) {
var theError = source + ": " + err;
console.error(theError);
globalErrMessage = globalErrMessage + theError;
globalSuccess = false;
}
var sql = require('mssql');
var config = {
driver: 'tedious', // use tedious driver
server: 'jsandersmobileappdbserver.database.windows.net',
user: 'username@jsandersmobileappdbserver',
password: 'password',
port: '1433',
options: {
encrypt: true,
database: 'jsandersmobileDb',
debug: {
packet: true,
data: true,
payload: true,
token: false,
log: true
}
}
};
var rolledBack = false;
var globalSuccess = true;
console.info("starting");
sql.connect(config, function (err) {
if (err) {
handleErr("sql.connect", err);
}
else {
// ... error checks - todo
console.info("OK:connected");
var transaction = new sql.Transaction(/* [connection] */);
transaction.begin(function (err) {
// ... error checks
if (err) {
handleErr("transaction.begin", err);
}
//Rollback event
transaction.on('rollback', function (aborted) {
// emited with aborted === true
console.info("Event:transaction.on('rollback')");
rolledBack = true;
});
var request = new sql.Request(transaction);
request.query('insert into mytable (bitcolumn) values (2)', function (err, recordset) {
// insert should fail because of invalid value
if (err) {
handleErr("ERR:request.query", err);
// if not rolled back then rollback this transaction
if (!rolledBack) {
transaction.rollback(function (err) {
if (err) {
handleErr("ERR:transaction.rollback", err);
}
else {
console.info("OK:transaction.rollback");
}
});
}
} else {
transaction.commit(function (err) {
// ... error checks
if (err) {
console.log("ERR:transaction.commit");
console.log(err);
}
else {
console.log("OK:transaction.commit");
}
});
}
});
});
}
});
if (rolledBack) {
res.json("Rolled Back");
}
else if (globalSuccess) {
res.json("success");
}
else {
res.json("error");
}
}
};
I hope this is useful in getting you jumpstarted!
Drop me a note if you find this useful!