Database with transaction

user-route.js

'use strict';

const express = require('express');

const executor = require('app/executor');
const userService = require('app/service/user-service');

//
// Router: /users
//
const router = express.Router({
  caseSensitive: true,
  mergeParams: true,
  strict: true
});

//
// Endpoints...
//


router.get('/', function (req, res) {
  executor.execute(req, res, function (sender) {
    sender(userService.getUserList(), 'users');
  });
});

router.post('/', function (req, res) {
  executor.execute(req, res, function (sender) {
    const userModel = req.body; // {name, email}
    sender(userService.createUser(userModel), 'id');
  });
});

router.put('/:id', function (req, res) {
  executor.execute(req, res, function (sender) {
    var userModel = req.body;
    userModel.id = req.params.id;
    sender(userService.changeUser(userModel), 'user');
  });
});

router.delete('/:id', function (req, res) {
  executor.execute(req, res, function (sender) {
    var id = req.params.id;
    sender(userService.deleteUser(id), 'id');
  });
});

//
// Exports the router
//
module.exports = router;

user-service.js

'use strict';

const db = require('app/db');

module.exports.getUserList = function () {
  return db.getConnection()
    .then(function (conn) {
      return conn.query('SELECT * FROM `users`')
        .then(function (rows) {
          conn.release();
          return rows;
        });
    });
};

module.exports.createUser = function (userModel) {
  return db.getConnection()
    .then(function (conn) {
      return conn.beginTransaction()
        .then(function () {
          return conn.query('INSERT INTO `users` (name, email) VALUES({name}, {email})', userModel);
        })
        .then(function (result) {
          return conn.commit(result.insertId);
        })
        .fail(function (reason) {
          return conn.rollback(reason);
        })
        .finally(function () {
          conn.release();
        });
    });
};

module.exports.changeUser = function (userModel) {
  return db.getConnection()
    .then(function (conn) {
      return conn.beginTransaction()
        .then(function () {
          return conn.query('UPDATE `users` SET `name` = {name}, email = {email} WHERE user_id = {id}', userModel);
        })
        .then(function (result) {
          return conn.commit(userModel);
        })
        .fail(function (reason) {
          return conn.rollback(reason);
        })
        .finally(function () {
          conn.release();
        });
    });
};

module.exports.getUser = function (id) {
  return db.getConnection()
    .then(function (conn) {
      const values = {
        id: id
      };
      return conn.query('SELECT * FROM `users` WHERE user_id = {id}', values)
        .finally(function () {
          conn.release();
        });
    });
};

module.exports.deleteUser = function (id) {
  return db.getConnection()
    .then(function (conn) {
      return conn.beginTransaction()
        .then(function () {
          return conn.query('DELETE FROM `users` WHERE user_id = {id}', { id: id });
        })
        .then(function (result) {
          return conn.commit(id);
        })
        .fail(function (reason) {
          return conn.rollback(reason);
        })
        .finally(function () {
          conn.release();
        });
    });
};