Working with Relational Data in Bookshelf.js

schema

There are several ORMs available to use in JavaScript-based projects, the most popular among them being Sequelize and Bookshelf.js. Having used both, I prefer Bookshelf, as it borrows many conventions from Backbone.js and is built on the Knex query builder.

While it is easy enough to get started with Bookshelf, the documentation is surprisingly sparse on examples of working with relational data. In this post, I'm going to show one approach to building models with associations and retrieving / updating their data.

Here is the schema we are going to be working with:

schema

Let's start with a simple example - updating the deviceId column for a given participant.

Since many Bookshelf methods return promises, we can easily handle any database errors that occur. This also allows us to write helper functions to handle the actual database work and handle errors elsewhere, relevant to the action that was being performed.

// helpers.js
var updateDeviceId = function(username, deviceId) {  
  // return a new Participant model that we have attempted
  // to fetch from the database and update
  return new models.Participant()
    // the query method allows us to build SQL queries
    .query({where: {name: username}})
    // fetch executes the query
    .fetch()
    // once the query is executed, we attempt to update the
    // deviceId column and return the model, which is another
    // promise
    .then(function(model) {
      model.set('device_id', deviceId);
      model.save();
      return model;
    });
};

Notice that the entire function above returns a Participant model, which is a promise in Bookshelf.js. If this was being invoked as a result of an API call, we can send the appropriate response if an error is encountered.

// routes.js
app.post('/api/devices/register', function(req, res) {  
  // grab the username and deviceId from the request body
  var username = req.body.username;
  var deviceId = req.body.deviceId;
  // call the updateDeviceId helper above
  // since this returns a promise, any failures in the DB
  // query will be handled by the catch block
  helpers.updateDeviceId(username, deviceId)
    .then(function() {
      res.status(201).send('Device registered');
    })
    .catch(function() {
      res.status(404).send('Unable to register device');
    });
});

Now that we're familiar with the general pattern, let's retrieve some relational data.

In the schema above, events and participants have a many-to-many relationship. Here is how we can define it in Bookshelf:

// models.js
var Participant = bookshelf.Model.extend({  
  tableName: 'participants',
  events: function() {
    return this.belongsToMany(Event).through(EventParticipant);
  }
});

var Event = bookshelf.Model.extend({  
  tableName: 'events',
  participants: function() {
    return this.belongsToMany(Participant)
      .through(EventParticipant)
      .withPivot(['status', 'checkin_time']);
  }
});

Notice that we are using a join table, events_participants. This is necessary for a many-to-many relationships and is represented in our schema.

Now that the relationship is built, we can retrieve a list of events for a given participant.

// helpers.js
var getEvents = function(participantId) {  
  // return a certain Participant
  return new models.Participant()
    // with a given participantId
    .query({where: {id: participantId}})
    // get the related event data
    // require: true will throw an error if the query fails
    .fetch({withRelated: ['events'], require: true})
    .then(function(model) {
      return model;
    });
};

Note the withRelated: ['events'] parameter given to the fetch() call - this uses the associations we've defined to get the events for a given participant.

We can now use the results of this query in our API.

app.get('/api/participants/:participantId/events', function(req, res) {  
  // grab the participantId from the request parameters
  var participantId = req.params.participantId;
  // invoke the getEvents helper above
  helpers.getEvents(participantId)
    // if the query succeeded, we can use the toJSON helper for the response
    .then(function(model) {
      res.json(model.toJSON());
    })
    // otherwise, respond with an error
    .catch(function(error) {
      res.status(404).send('Unable to fetch events for this participant');
    });
});

Getting used to the Promise pattern takes some time, but it prevents the application from blocking on database queries, and makes error handling a snap.

Let's look at a more complicated function that updates a participant's event status based on her deviceId. We'll compose it from several smaller helper functions.

// helpers.js

// get Participant given a deviceId
exports.getParticipant = function(deviceId) {  
  return new models.Participant()
    .query({where: {device_id: deviceId}})
    .fetch({require: true})
    .then(function(model) {
      return model;
    });
};

// get the closest Event in time
exports.getCurrentEvent = function() {  
  return new models.Event()
      // note the use of native SQL method here
    .query('orderByRaw', 'ABS(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(start_time)) ASC')
    .fetch({require:true})
    .then(function(model) {
      return model;
    });
};

We'll now use these helpers to check in a participant:

exports.checkinUser = function(deviceId) {

  var participantId;
  var eventId;
  var eventStartTime;
  var status;
  var now = moment();

  // get the participant_id from the deviceID
  return exports.getParticipant(deviceId)

    // get the event_id of the closest event in time
    .then(function(model) {
      participantId = model.get('id');
      return exports.getCurrentEvent();
    })
    .then(function(model) {
      eventId = model.get('id');
      eventStartTime = moment(model.get('start_time'));
      // update the event_participant status and check-in time
      status = (eventStartTime.format('X') - now.format('X') >= 0) ? 'ontime' : 'late';
      // return an instance of EventParticipant, which is a promise
      return new models.EventParticipant({event_id: eventId, participant_id: participantId})
        .fetch();
    })
    .then(function(model) {
      if (model && !model.get('status')) {
        // record exists with a null status, update it
        model.set('status', status);
        model.set('checkin_time', moment().format('YYYY-MM-DD HH:mm:ss'));
        model.save();
      } else if (!model) {
        // record doesn't exist, create it
        models.EventParticipant.forge({
          event_id: eventId,
          participant_id: participantId,
          status: status,
          checkin_time: now.format('YYYY-MM-DD HH:mm:ss')
        }).save();
      } else {
        // status is already set, do nothing
        return;
      }
      return {
        deviceId: deviceId,
        eventId: eventId,
        participantId: participantId,
        status: status
      };
    });
};

There you have it. Following the basic promise pattern allows us to use Bookshelf.js to build multiple chained methods in order to run complex queries.

Valentyn Boginskey
Valentyn Boginskey

Valentyn is a system administrator turning web developer. He is passionate about privacy and virtual currencies. In his spare time, he enjoys mountain biking, skiing, backpacking, and racing go-karts.