Mastering Database Transactions in Node.js with Bookshelf.js ORM

Transaction refers to a set of operations that form a single logical work unit, such as transferring money from one account to another, which involves two separate updates.

In English, transaction also means trade, which is why computer scientists adopted the term for this database feature.

Transactions have the following properties:

Atomicity : all operations in the transaction are either fully applied or not applied at all.

Consistency : when a transaction runs in isolation (no concurrent transactions), the database remains consistent.

Isolation : even if multiple transactions run concurrently, each transaction sees either the other transaction completed before it starts or after it finishes, never an intermediate state.

Durability : once a transaction commits, its changes are permanent and survive system failures.

These are collectively known as the ACID characteristics.

In Node.js we can conveniently implement transaction operations using an ORM; this example uses bookshelf.js. bookshelf.js is a Node.js ORM built on knex.js, supporting PostgreSQL, MySQL, and SQLite3. It is a lean Object‑Relational Mapper that allows direct use of the underlying knex interface for custom queries.

Bookshelf follows the same Model and Collection concepts as backbone.js, so developers familiar with Backbone can easily adopt it.

Bookshelf uses bluebird to manage asynchronous operations.

Below is a demonstration of transaction management with Bookshelf.

model/db_config.json

{
  "client": "mysql",
  "connection": {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "your_database_user",
    "password": "your_database_password",
    "database": "myapp_test",
    "charset": "utf8"
  }
}

model/base.js

'use strict'

/**
 * init bookshelf connection
 */
let Bookshelf = null,
    dbConfig = require('./db_config');
module.exports = function () {
    "use strict";
    if (Bookshelf) {
        return Bookshelf;
    }
    var knex = require('knex')(dbConfig);
    Bookshelf = require('bookshelf')(knex);
    Bookshelf.plugin('registry');
    return Bookshelf;
};

model/users.js

'use strict'

const bookshelf = require('./base')();

// Typically the DB and tables are prepared by DBAs; we just initialize the ORM model.
var Users = bookshelf.Model.extend({
    tableName: 'users'
});

module.exports = Users;

model/room.js

'use strict'

const bookshelf = require('./base')();

// Typically the DB and tables are prepared by DBAs; we just initialize the ORM model.
var Room = bookshelf.Model.extend({
    tableName: 'room'
});

module.exports = Room;

controllers/transaction.js

'use strict'

const Bookshelf = require('../model/base')();
const Room = require('../model/room');
const Users = require('../model/users');

Bookshelf.transaction(function (t) {
    return Users.forge(data) // data to insert into users table
        .save(null, { transacting: t })
        .then(function (users) {
            return Room.forge({ userId: users.id }) // also insert into room table
                .save(null, { transacting: t })
                .then(function () {
                    return users; // return the users model
                });
        });
}).then((users) => {
    // doSomething
}).catch((error) => {
    // handle error
});

For more information about bookshelf.js, visit the official site: http://bookshelfjs.org/