Nodetrine provides a unified api for transactions, this is available on the Connection class.

The available methods are Connection#beginTransaction(), Connection#commit() and Connection#rollBack().

Here is an example on how to use it

let connection = DriverManager.getConnection([/*..*/]);

return connection.connect()
    .then(function () {
        return connection.beginTransaction();
    })
    .then(function () {
        let table = "post";
        let data = new Map();
        data.set('customer_id', '8a6sd876as87d6ad');
        data.set('title', 'bitgandtter');
        data.set('body', 'inserted by nodetrine');

        return connection.insert(table, data);
    })
    .then(function () {
        return connection.commit();
    })
    .catch(function(error){
        connection.rollback();
    });

7.1 Transaction Nesting

A Connection also adds support for nesting transactions, or rather propagating transaction control up the call stack. For that purpose, the Connection class keeps an internal counter that represents the nesting level and is increased/decreased as beginTransaction(), commit() and rollBack() are invoked. beginTransaction() increases the nesting level whilst commit() and rollBack() decrease the nesting level. The nesting level starts at Whenever the nesting level transitions from 0 to 1, beginTransaction() is invoked on the underlying driver connection and whenever the nesting level transitions from 1 to 0, commit() or rollBack() is invoked on the underlying driver, depending on whether the transition was caused by Connection#commit() or Connection#rollBack().

To visualize what this means in practice, consider the following example:

let connection = DriverManager.getConnection([/*..*/]);

return connection.connect()
   .then(function () {
       return connection.beginTransaction(); // 0 => 1, transaction started
   })
   .then(function () {
       // nested transaction block.
       return connection.beginTransaction(); // 1 => 2
   })
   .then(function (id) {
       return connection.commit() // 2 => 1
       .catch(function(){
            return connection.roolback(); // 2 => 1, transaction marked for rollback only
       });
   })
   .then(function () {
       return connection.commit() // 1 => 0
              .catch(function(){
                   return connection.roolback(); // 1 => 0, transaction rollback
              });
   });

However, a rollback in a nested transaction block without savepoints will always mark the current transaction so that the only possible outcome of the transaction is to be rolled back. That means in the above example, the rollback in the inner transaction block marks the whole transaction for rollback only. This also means that you can not successfully commit some changes in an outer transaction if an inner transaction block fails and issues a rollback, even if this would be the desired behavior (i.e. because the nested operation is “optional” for the purpose of the outer transaction block). To achieve that, you need to restructure your application logic so as to avoid nesting transaction blocks.

NOTE: The transaction nesting described here is a debated feature that has its critics. Form your own opinion. We recommend avoiding nesting transaction blocks when possible, and most of the time, it is possible. Transaction control should mostly be left to a service layer and not be handled in data access objects or similar.

7.2 Auto-commit mode

A Connection supports setting the auto-commit mode to control whether queries should be automatically wrapped into a transaction or directly be committed to the database. By default a connection runs in auto-commit mode which means that it is non-transactional unless you start a transaction explicitly via beginTransaction(). To have a connection automatically open up a new transaction on connect() and after commit() or rollBack(), you can disable auto-commit mode with setAutoCommit(false).

let connection = DriverManager.getConnection([/*..*/]);

connection.setAutoCommit(false); // disables auto-commit
return connection.connect() // connects and immediately starts a new transaction
   .then(function () {
       return connection.commit(); // commits transaction and immediately starts a new one
   })
   .catch(function(){
       return connection.roolback(); // rolls back transaction and immediately starts a new one
   });

NOTE: Changing auto-commit mode during an active transaction, implicitly commits active transactions for that particular connection.

let connection = DriverManager.getConnection([/*..*/]);

return connection.connect()
   .then(function () {
       // we are in auto-commit mode
       return connection.beginTransaction();
   })
   .then(function () {
       // disable auto-commit, commits currently active transaction
       return connection.setAutoCommit(false);
   })
   .then(function () {
       // no-op as auto-commit is already disabled
       return connection.setAutoCommit(false);
   })
   .then(function () {
       // enable auto-commit again, commits currently active transaction
       return connection.setAutoCommit(true);
   });