Bookshelf.js: A Node.js ORM

One of the most common resources you'll interact with in a language like Node.js (primarily a web-focused language) are databases. And with SQL being the most common of all the different types, you'll need a good library to help you interact with it and its many features.

Bookshelf.js is among the most popular of the Node.js ORM packages. It stems from the Knex.js, which is a flexible query builder that works with PostgreSQL, MySQL and SQLite3. Bookshelf.js builds on top of this by providing functionality for creating data models, forming relations between these models, and other common tasks needed when querying a database.

Bookshelf also supports multiple database back-ends, like MySQL, PostgreSQL, and SQLite. This way you can easily switch databases when needed, or use a smaller DB like SQLite during development and Postgre in production.

Throughout this article I'll show you how to get the most out of this Node ORM, including connecting to a database, creating models, and saving/loading objects.

Install Bookshelf

Bookshelf is a bit different than most Node packages in that it doesn't install all of its dependencies for you automatically. In this case, you must manually install Knex along with Bookshelf:

$ npm install knex --save
$ npm install bookshelf --save

In addition to that, you need to choose which database you'll want to use Bookshelf with. Your choices are:

• pg (PostgreSQL)
• mysql
• mariasql
• sqlite3

These can be installed with:

$ npm install pg --save
$ npm install mysql --save
$ npm install mariasql --save
$ npm install sqlite3 --save

One thing that I tend to do with my projects is install a production-grade DB (like Postgre) using --save, while using --save-dev for a smaller DB like SQLite for use during development.

$ npm install pg --save
$ npm install sqlite3 --save-dev

This way we can easily switch between the databases in production and development without having to worry about flooding my production environment with unnecessary dependencies.

Connecting to a Database

All of the lower-level functions, like connecting to the database, are handled by the underlying Knex library. So, naturally, in order to initialize your bookshelf instance you'll need to create a knex instance first, like this:

var knex = require('knex')({
    client: 'sqlite3',
    connection: {
        filename: './db.sqlite'
    }
});

var bookshelf = require('bookshelf')(knex);

And now you're able to use the bookshelf instance to create your models.

Setting up the Tables

Knex, as its own website states, is a "batteries included" SQL query builder, so you can do just about anything through Knex that you'd want to do with raw SQL statements. One of these important features is table creation and manipulation. Knex can be used directly to set up your schema within the database (think database initialization, schema migration, etc).

So first of all, you'll want to create your table using knex.schema.createTable(), which will create and return a table object that contains a bunch of schema building functions, like table.increments(), table.string(), and table.date(). For each model you create, you'll need to do something like this for each one:

knex.schema.createTable('users', function(table) {
    table.increments();
    table.string('name');
    table.string('email', 128);
    table.string('role').defaultTo('admin');
    table.string('password');
    table.timestamps();
});

Here you can see that we create a table called 'users', which we then initialize with columns 'name', 'email', 'role', and 'password'. We can even take it a step further and specify the maximum length of a string column (128 for column 'email') or a default value ('admin' for column 'role').

Some convenience functions are also provided, like timestamps(). This function will add two timestamp columns to the table, created_at and updated_at. If you use this, consider also setting the hasTimestamps property to true in your model (see 'Creating a Model' below).

There are quite a few more options you can specify for each table/column, so I'd definitely recommend checking out the full Knex documentation for more details.

Creating a Model

One of my gripes about Bookshelf is that you always need an initialized bookshelf instance in order to create a model, so structuring some applications can be a bit messy if you keep all your models in different files. Personally, I prefer to just make bookshelf a global using global.bookshelf = bookshelf, but that isn't necessarily the best way to do it.

Anyway, let's see what it takes to create a simple model:

var User = bookshelf.Model.extend({
    tableName: 'users',
    hasTimestamps: true,

    verifyPassword: function(password) {
        return this.get('password') === password;
    }
}, {
    byEmail: function(email) {
        return this.forge().query({where:{ email: email }}).fetch();
    }
});

Here we have a pretty simple model to demonstrate some of the available features. First of all, the only required property is tableName, which tells the model where to save and load data from in the DB. Obviously it's pretty minimal to get a model set up since all of the schema declaration is already done elsewhere.

As for the rest of the properties/functions, here is a quick rundown of what User includes:

• tableName: A string that tells the model where to save and load data from in the DB (required)
• hasTimestamps: A boolean value telling the model whether we need created_at and updated_at timestamps
• verifyPassword: An instance function
• byEmail: A class (static) function

So, for example, we'll use byEmail as a shorter way to query a user by their email address:

User.byEmail('[email protected]').then(function(u) {
    console.log('Got user:', u.get('name'));
});

Notice how you access model data in Bookshelf. Instead of using a direct property (like u.name), we have to use the .get() method.

ES6 Support

As of the time of this writing, Bookshelf doesn't seem to have full ES6 support (see this issue). However, you can still write much of your model code using the new ES6 classes. Using the model from above, we can re-create it using the new class syntax like this:

class User extends bookshelf.Model {
    get tableName() {
        return 'users';
    }

    get hasTimestamps() {
        return true;
    }

    verifyPassword(password) {
        return this.get('password') === password;
    }

    static byEmail(email) {
        return this.forge().query({where:{ email: email }}).fetch();
    }
}

And now this model can be used exactly as the one before. This method won't give you any functional advantages, but it's more familiar for some people, so take advantage of it if you want to.

Collections

In Bookshelf you also need to create a separate object for collections of a given model. So if you want to perform an operation on multiple Users at the same time, for example, you need to create a Collection.

Continuing with our example from above, here is how we'd create the Users Collection object:

var Users = bookshelf.Collection.extend({
    model: User
});

Pretty simple, right? Now we can easily query for all users with (although this was already possible with a model using .fetchAll()):

Users.forge().fetch().then(function(users) {
    console.log('Got a bunch of users!');
});

Even better, we can now use some nice model methods on the collection as a whole, instead of having to iterate over each model individually. One of these methods that seems to get a lot of use, in web-apps especially, is .toJSON():

exports.get = function(req, res) {
    Users.forge().fetch().then(function(users) {
        res.json(users.toJSON());
    });
};

This returns a plain JavaScript object of the entire collection.

Extending your Models

As a developer, one of the most important principles I've followed is the DRY (Don't Repeat Yourself) principle. This is just one of the many reasons why model/schema extension is so important to your software design.

Using Bookshelf's .extend() method, you can inherit all of the properties, instance methods, and class methods of a base model. This way you can create and take advantage of base methods that aren't already provided, like .find(), .findOne(), etc.

One great example of model extension is in the bookshelf-modelbase project, which provides many of the missing methods that you'd expect to come standard in most ORMs.

If you were to create your own simple base model, it might look like this:

var model = bookshelf.Model.extend({
    hasTimestamps: ['created_at', 'updated_at'],
}, {
    findAll: function(filter, options) {
        return this.forge().where(filter).fetchAll(options);
    },

    findOne: function(query, options) {
        return this.forge(query).fetch(options);
    },

    create: function(data, options) {
        return this.forge(data).save(null, options);
    },
});

Now all of your models can take advantage of these useful methods.

Saving and Updating Models

There are a couple different ways to save models in Bookshelf, depending on your preferences and format of your data.

The first, and most obvious way, is to just call .save() on a model instance.

var user = new User();
user.set('name', 'Joe');
user.set('email', '[email protected]');
user.set('age', 28);

user.save().then(function(u) {
    console.log('User saved:', u.get('name'));
});

This works for a model you create yourself (like the one above), or with model instances that are returned to you from a query call.

The other option is to use the .forge() method and initialize it with data. 'Forge' is really just a shorthand way for creating a new model (like new User()). But this way you don't need an extra line to create the model before starting the query/save string.

Using .forge(), the above code would look like this:

var data = {
    name: 'Joe',
    email: '[email protected]',
    age: 28
}

User.forge(data).save().then(function(u) {
    console.log('User saved:', u.get('name'));
});

This won't really save you any lines of code, but it can be convenient if data is actually incoming JSON or something like that.

Loading Models

Here I'll talk about how to load models from the database with Bookshelf.

While .forge() didn't really help us out that much in saving documents, it certainly helps in loading them. It would be a bit awkward to create an empty model instance just to load data from the database, so we use .forge() instead.

The simplest example of loading is to just fetch a single model using .fetch():

User.forge({email: '[email protected]'}).fetch().then(function(user) {
    console.log('Got user:', user.get('name'));
});

All we do here is grab a single model that matches the given query. As you can imagine, the query can be as complex as you'd like (like constraining on name and age columns as well).

Just like in plain old SQL, you can greatly customize the query and the data that is returned. For example, this query will only give us the data we need to authenticate a user:

var email = '...';
var plainTextPassword = '...';

User.forge({email: email}).fetch({columns: ['email', 'password_hash', 'salt']})
.then(function(user) {
    if (user.verifyPassword(plainTextPassword)) {
        console.log('User logged in!');
    } else {
        console.log('Authentication failed...');
    }
});

Taking this even further, we can use the withRelations option to automatically load related models, which we'll see in the next section.

Model Relations

In many applications, your models will need to refer to other models, which is achieved in SQL using foreign keys. A simple version of this is supported in Bookshelf via relations.

Within your model, you can tell Bookshelf exactly how other models are related to one another. This is achieved using the belongsTo(), hasMany(), and hasOne() (among others) methods.

So let's say you have two models, User and Address. The User can have multiple Addresses (one for shipping, one for billing, etc), but an Address can belong to only one User. Given this, we might set our models up like this:

var User = bookshelf.Model.extend({
    tableName: 'users',
    
    addresses: function() {
        return this.hasMany('Address', 'user_id');
    },
});

var Address = bookshelf.Model.extend({
    tableName: 'addresses',
    
    user: function() {
        return this.belongsTo('User', 'user_id');
    },
});

Note that I'm using the registry plugin here, which allows me to refer to the Address model with a string.

The hasMany() and belongsTo() methods tells Bookshelf how each model is related to one another. The User "has many" Addresses, while the Address "belongs to" a single user. The second argument is the column name that indicates the location of the key of the model. In this case, both models reference the user_id column in the Address table.

Now we can take advantage of this relation by using the withRelated option on .fetch() methods. So if I wanted to load a user and all of their addresses with one call, I could just do:

User.forge({email: '[email protected]'}).fetch({withRelated: ['addresses']})
.then(function(user) {
    console.log('Got user:', user.get('name'));
    console.log('Got addresses:', user.related('addresses'));
});

If we were to fetch the User model without the withRelated option then user.related('addresses') would just return an empty Collection object.

Be sure to take advantage of these relation methods, they're far easier to use than creating your own SQL JOINs :)

The Good

Bookshelf is one of those libraries that seems to try and not become overly bloated and just sticks to the core features. This is great because the features that are there work very well.

Bookshelf also has a nice, powerful API that lets you easily build your application on top of it. So you don't have to wrestle around with high-level methods that made poor assumptions on how they'd be used.

The Bad

While I do think it's nice that Bookshelf/Knex provides you with some lower-level functions, I still think there is room for improvement. For example, all of the table/schema setup is left up to you, and there isn't an easy way to specify your schema (like in a plain JS object) within the model. The table/schema setup has to be specified in API calls, which isn't all that easy to read and debug.

Another gripe of mine is how they left out many of the helper methods should come standard with the base model, like .create(), .findOne(), .upsert(), and data validation. This is exactly why I mentioned the bookshelf-modelbase project earlier as it fills in many of these gaps.

Conclusion

Overall I've become quite a fan of using Bookshelf/Knex for SQL work, although I do think that some of the problems I just mentioned might be a turn-off for many developers that are used to using ORMs that do just about everything for them out of the box. On the other hand, for other developers that like to have a lot of control, this is the perfect library to use.

While I tried to cover as much of the core API as possible in this article, there are still quite a few features I didn't get to touch on, so be sure to check out the project documentation for more info.

Have you used Bookshelf.js or Knex.js? What do you think? Let us know in the comments!