Complex Query

var express = require('express');
var router = express.Router();

var createConnection = require('../utils/database');

router.post('/notes', function(req, res) {

    var note_post = {
        title: req.body.title,
        body: req.body.body,
        author_id: req.body.author_id
    };

    // get a connection from the pool
    createConnection(function(err, connection) {
        if (err) {
            console.log(err);
            res.send({ success: false, message: 'database error', error: err });
            return;
        }

        // set up error listener for the connection
        connection.on('error', function(err) {
            console.log(err);
            res.send({ success: false, message: 'database error', error: err });
            return;
        });

        // start a transaction
        connection.beginTransaction(function(err) {
            if (err) {
                console.log(err);
                return connection.rollback(function() {
                    res.send({ success: false, message: 'start transaction error' });
                    connection.release();
                });
            }

            // execute a query
            connection.query("INSERT INTO notes SET ?", note_post, function(err, result) {
                if (err) {
                    console.log(err);
                    return connection.rollback(function() {
                        res.send({ success: false, message: 'query error' });
                        connection.release();
                    });
                }

                var insertId = result.insertId;

                // execute another query
                connection.query("UPDATE authors SET num_notes = num_notes + 1 WHERE id = ?", req.body.author_id, function(err, result) {
                    if (err) {
                         console.log(err);
                        return connection.rollback(function() {
                            res.send({ success: false, message: 'query error' });
                            connection.release();
                        });
                    }

                    // commit the transaction
                    connection.commit(function(err) {
                        if (err) {
                            console.log(err);
                            return connection.rollback(function() {
                                res.send({ success: false, message: 'commit error' });
                                connection.release();
                            });
                        }

                        res.setHeader('Location', '/test/notes/' + insertId);
                        res.status(201).send({ success: true });
                    });
                });
            });
        });
    });
});

module.exports = router;