Bookshelf is a javascript ORM for Node.js, built on the Knex SQL query builder. Featuring both promise based and traditional callback interfaces, it extends the Model & Collection patterns of Backbone.js, providing transaction support, eager/nested-eager relation loading, polymorphic associations, and support for one-to-one, one-to-many, and many-to-many relations.

It is designed to work well with PostgreSQL, MySQL, and SQLite3.

The project is hosted on GitHub, and the annotated source code is available, as well as a full test suite.

Latest Release: 0.6.11 - Change Log

Current Develop — Travis Badge

Bookshelf is available for use under the MIT software license.

You can report bugs and discuss features on the GitHub issues page, add pages to the wiki or send tweets to @tgriesser.

Bookshelf's dependencies are listed in the package.json file, but include Backbone.js, Lo-dash, bluebird for promise flow control, Knex.js. as the query builder foundation, and inflection for crafting intelligent defaults for related key and table names.

Special thanks to Taylor Otwell and his work on the Eloquent ORM, which has heavily influenced the library's design.

Introduction

Bookshelf aims to provide a simple library for common tasks when querying databases in javascript, and forming relations between these objects, taking a lot of ideas from the the Data Mapper Pattern. With a concise, literate codebase, Bookshelf is simple to read, understand, and extend. It doesn't force you to use any specific validation scheme, provides flexible and efficient relation/nested-relation loading, and first class transaction support. It's a lean Object Relational Mapper, allowing you to drop down to the raw knex interface whenever you need a custom query that doesn't quite fit with the stock conventions.

Bookshelf extends the excellent foundation provided by Backbone.js Models and Collections, using similar patterns, naming conventions, and philosophies to build a lightweight, easy to navigate ORM. If you know how to use Backbone, you probably already know how to use Bookshelf.

Installation

You'll need to install either mysql, pg, or sqlite3 from npm. It is advisable to install your own copy of Knex as well if you wish to build any custom queries outside of the ORM. 

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

# Then add one of the following:
$ npm install mysql
$ npm install pg
$ npm install sqlite3

Upgrading

If you are upgrading Bookshelf from 0.3.x to 0.5.x, you'll notice that some things have broken. Luckily, with a few quick fixes to your code, you should be up and runnning in no-time.

Bookshelf.initialize

Bookshelf.initialize is the initializing function that must be called prior to using Bookshelf, accepting either an initialized Knex client, or a hash of parameters to be passed along to Knex.initialize. The knex documentation provides a number of examples for different databases. 


var MySql = Bookshelf.initialize({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test',
    charset  : 'UTF8_GENERAL_CI'
  }
});

var User = MySql.Model.extend({
  tableName: 'users'
});

An initialize should only ever happen once in your application, as it creates a connection pool for the current database, you should use the instance returned from the initialize call throughout your library. You'll need to store this instance created by the initialize somewhere in the application you can reference it. A common pattern to follow is to set the client as a property on the Bookshelf library when your application starts, so you can easily reference it later: 


// When the app starts
var Bookshelf  = require('bookshelf');
Bookshelf.PG = Bookshelf.initialize({
  client: 'pg',
  connection: {
    // your connection config
  }
});

// elsewhere, to use the client:
var Bookshelf = require('bookshelf').PG;

var Post = Bookshelf.Model.extend({
  // ...
});

Bookshelf.Model

Models are simple objects representing individual database rows, specifying the tableName and any relations to other models. They can be extended with any domain-specific methods, which can handle components such as validations, computed properties, and access control.

extendBookshelf.Model.extend(properties, [classProperties])
To create a Model class of your own, you extend Bookshelf.Model and provide instance properties, as well as optional classProperties to be attached directly to the constructor function.

extend correctly sets up the prototype chain, so subclasses created with extend can be further extended and subclassed as far as you like. 


var Customer = Bookshelf.Model.extend({

  initialize: function() { ... },

  account: function() { ... },

  login: function() { ... }

});

Brief aside on super: JavaScript does not provide a simple way to call super — the function of the same name defined higher on the prototype chain. If you override a core function like set, or save, and you want to invoke the parent object's implementation, you'll have to explicitly call it, along these lines: 


var Customer = Bookshelf.Model.extend({
  set: function() {
    ...
    Bookshelf.Model.prototype.set.apply(this, arguments);
    ...
  }
});

forgeBookshelf.Model.forge([attributes], [options])
A simple helper function to instantiate a new Model without needing new. 


var Customer = Bookshelf.Model.extend({
  tableName: 'customers'
});

Customer.forge({item: 'value'}).save().then(function() {
  // ...
});

collectionModel.collection([models], [options])
A simple static helper to instantiate a new Collection, setting the current model as the collection's target. 


Customer.collection().fetch().then(function(collection) {
  // ...
})

constructor / initializenew Model([attributes], [options])
When creating an instance of a model, you can pass in the initial values of the attributes, which will be set on the model. If you define an initialize function, it will be invoked when the model is created. 


new Book({
  title: "One Thousand and One Nights",
  author: "Scheherazade"
});

In rare cases, if you're looking to get fancy, you may want to override constructor, which allows you to replace the actual constructor function for your model. 


var Books = Bookshelf.Model.extend({

  tableName: 'documents',

  constructor: function() {
    Bookshelf.Model.apply(this, arguments);
    this.on('saving', function(model, attrs, options) {
      options.query('where', 'type', '=', 'book');
    });
  }

});

ThetableName and hasTimestamps properties will be directly attached if passed in the options during model creation.

If {parse: true} is passed as an option, the attributes will first be converted by parse before being set on the model.

tableNamemodel.tableName
A required property for any database usage, The tableName property refers to the database table name the model will query against. 


var Television = Bookshelf.Model.extend({
  tableName: 'televisions'
});

idAttributemodel.idAttribute
This tells the model which attribute to expect as the unique identifier for each database row (typically an auto-incrementing primary key named id). Note that if you are using parse and format (to have your model's attributes in camelCase, but your database's columns in snake_case, for example) this refers to the name returned by parse (myId), not the database column (my_id).

idmodel.id
A special property of models, the id is the unique identifier associated, named by the idAttribute. If you set the id in the attributes hash, it will be copied onto the model as a direct property. Models can be retrieved by id from collections, and the id is used in fetching models and building model relations.

setmodel.set(attributes, [options])
Set a hash of attributes (one or many) on the model. If any of the attributes change the model's state, a "change" event will be triggered. You may also pass individual keys and values. 


customer.set({first_name: "Joe", last_name: "Customer"});

customer.set("telephone", "555-555-1212");

getmodel.get(attribute)
Get the current value of an attribute from the model. For example: note.get("title")

fetchmodel.fetch([options]).then(function(model) {...
Fetches a model from the database, using any attributes currently set on the model to form a select query. Returns a promise, which will resolve with the fetched model, or undefined if the model isn't fetched. If you wish to trigger an error if the fetched model is not found, pass {require: true} as one of the options to the fetch call. A "fetching" event will be fired just before the record is fetched; a good place to hook into for validation. A "fetched" event will be fired when a record is successfully retrieved. If you need to constrain the query performed by fetch, you can call the query method before calling fetch. 


// select * from `books` where `ISBN-13` = '9780440180296'
new Book({'ISBN-13': '9780440180296'})
  .fetch()
  .then(function(model) {
    // outputs 'Slaughterhouse Five'
    console.log(model.get('title'));
  });

If you'd like to only fetch specific columns, you may specify a columns property, in the options for the fetch call, or use the query method, tapping into the knex column method to specify which columns will be fetched.

The withRelated parameter may be specified to fetch the resource, along with any specified relations named on the model. A single property, or an array of properties can be specified as a value for the withRelated property. The results of these relation queries will be loaded into a relations property on the model, may be retrieved with the related method, and will be serialized as properties on a toJSON call unless {shallow: true} is passed. 


var Book = Bookshelf.Model.extend({
  tableName: 'books',
  editions: function() {
    return this.hasMany(Edition);
  },
  genre: function() {
    return this.belongsTo(Genre);
  }
})

new Book({'ISBN-13': '9780440180296'}).fetch({
  withRelated: ['genre', 'editions']
}).then(function(book) {
  console.log(book.related('genre').toJSON());
  console.log(book.related('editions').toJSON());
  console.log(book.toJSON());
});

loadmodel.load(relations, [options]).then(function(model) {...
The load method takes an array of relations to eager load attributes onto a Model, in a similar way that the withRelated property works on fetch. Dot separated attributes may be used to specify deep eager loading. 


new Posts().fetch().then(function(collection) {
  collection.at(0)
  .load(['author', 'content', 'comments.tags'])
  .then(function(model) {
    JSON.stringify(model);
  });
});

{
  title: 'post title',
  author: {...},
  content: {...},
  comments: [
    {tags: [...]}, {tags: [...]}
  ]
}

Relation Types:

Relationships are used to create associations between models.

There are four types of relationships that may be defined between Models and Collections: hasOne, hasMany, belongsTo, belongsToMany. The relations are specified by creating named methods on the model, which return the appropriate relation type. 


var Summary = Bookshelf.Model.extend({tableName: 'summaries'});

var Author = Bookshelf.Model.extend({tableName: 'authors'});

var Owner  = Bookshelf.Model.extend({tableName: 'owners'});

var Book = Bookshelf.Model.extend({
  summary: function() {
    return this.hasOne(Summary);
  },
  owner: function() {
    return this.belongsTo(Author);
  },
  pages: function() {
    return this.hasMany(Pages);
  },
  author: function() {
    return this.belongsToMany(Author);
  }
});

new Book({id: 1}).related('summary').fetch().then(function(summary) {
  console.log(summary.toJSON());
});

// or:

new Book({id: 1}).summary().fetch().then(function(summary) {
  console.log(summary.toJSON());
});

Relations may also be loaded eagerly, by specifying a withRelated option during the fetch call. 


new Book({id: 2}).fetch({
  withRelated: ['summary', 'owner', 'pages', 'author']
}).then(function(book) {
  console.log(book.toJSON());
  var owner = book.related('owner');
  if (owner) {
    console.log(owner.toJSON());
  }
});

You may also want to eagerly load related models on a model or collection after it has already been fetched. For this, you may use the load method to specify which relations should be eagerly loaded on the model or collection. 


var accounts = new Accounts();
accounts.fetch()
  .then(function(collection) {
    return collection.at(0).load('account_info');
  })
  .then(function(model) {
    res.json({
      accounts: accounts.toJSON({shallow: true}),
      current_account: model
    });
  });

Nested eager loads may be performed, by separating the nested relations with '.'. 


new Story({id: 2}).fetch({
  withRelated: ['comments.tags', 'comments.author', 'author']
}).then(function(model) {
  JSON.stringify(model);
});

// performs 5 queries in total, outputting:
{
  id: 2,
  title: '',
  author: {...},
  comments: [
    {tags: [{...}, {...}], author: {...}},
    {tags: [...], author: {...}},
    {tags: [...], author: {...}}
  ]
}

An object may be passed as a relation, where the first argument is the key and the second a function which constraints the relation, called with the context of the current related model and accepting the Knex query builder object as the first argument. 


new Story({id: 2}).fetch({
  withRelated: ['comments.tags', 'comments.author', {
    'author': function(qb) {
      qb.where('status', 'active')
    }
  }]
}).then(...

hasOnemodel.hasOne(Target, [foreignKey])
A one-to-one relation is a very basic relation, where the model has exactly one of another Target model, referenced by a foreignKey in the Target model. By default, the foreignkey is assumed to be the singular form of the current model's tableName, followed by _id. 


var Record = Bookshelf.Model.extend({
  tableName: 'health_records'
});

var Patient = Bookshelf.Model.extend({
  tableName: 'patients',
  record: function() {
    return this.hasOne(Record);
  }
});

// select * from `health_records` where `patient_id` = 1;
new Patient({id: 1}).related('record').fetch().then(function(model) {
  ...
});

// alternatively, if you don't need the relation loaded on the patient's relations hash:
new Patient({id: 1}).record().fetch().then(function(model) {
  ...
});

If the foreign key is different than the one assumed by the query builder, you may specify it in the second argument of the query.

hasManymodel.hasMany(Target, [foreignKey])
Typically the most common relationship type, a hasMany is when the model has a "one-to-many" relationship with the Target model or collection. The model is referenced by a foreignKey in the Target model, which defaults to the singular form of the current model's tableName, followed by _id.

belongsTomodel.belongsTo(Target, [foreignKey])
The belongsTo relationship is used when a model is a member of another Target model. It can be used in a one-to-one associations as the inverse of a hasOne. It can also used in one-to-many associations as the inverse of a hasMany (and is the one side of that association). In both cases, the belongsTo relationship is used for a model that is a member of another Target model, referenced by the foreignKey in the current model. By default, the foreignKey is assumed to be the singular form of the Target model's tableName, followed by _id. 


var Book = Bookshelf.Model.extend({
  tableName: 'books',
  author: function() {
    return this.belongsTo(Author);
  }
});

// select * from `books` where id = 1
// select * from `authors` where id = book.author_id
new Book({id: 1}).fetch({withRelated: ['author']}).then(function(book) {
  console.log(JSON.stringify(book.related('author')));
});

belongsToManymodel.belongsToMany(Target, [table], [foreignKey], [otherKey])
The belongsToMany method defines a many-to-many relation, where the current model is joined to one or more of a Target model through another table. The default name for the joining table is the two table names, joined by an underscore, ordered alphabetically. For example, a users table and an accounts table would have a joining table of accounts_users. 


var Account = Bookshelf.Model.extend({
  tableName: 'accounts'
});

var User = Bookshelf.Model.extend({

  tableName: 'users',

  allAccounts: function () {
    return this.belongsToMany(Account);
  },

  adminAccounts: function() {
    return this.belongsToMany(Account).query('where', 'access', '=', 'admin');
  },

  viewAccounts: function() {
    return this.belongsToMany(Account).query('where', 'access', '=', 'readonly');
  }

});

The default key names in the joining table are the singular versions of the model table names, followed by _id. So in the above case, the columns in the joining table would be user_id, account_id, and access, which is used as an example of how dynamic relations can be formed using different contexts. To customize the keys used in, or the tableName used for the join table, you may specify them like so: 


this.belongsToMany(Account, 'users_accounts', 'userid', 'accountid');

If you wish to create a belongsToMany association where the joining table has a primary key, and more information about the model, you may create a belongsToMany through relation: 


var Doctor = Bookshelf.Model.extend({

  patients: function() {
    return this.belongsToMany(Patient).through(Appointment);
  }

});

var Appointment = Bookshelf.Model.extend({

  patient: function() {
    return this.belongsTo(Patient);
  },

  doctor: function() {
    return this.belongsTo(Doctor);
  }

});

var Patient = Bookshelf.Model.extend({

  doctors: function() {
    return this.belongsToMany(Doctor).through(Appointment);
  }

});

through.through(JoinModel, [throughFk], [otherKey])
The through method helps to create dynamic relations between models & collections, where a hasOne, hasMany, belongsTo, or belongsToMany relation may run through a JoinModel.

A good example of where this would be useful is if a book hasMany paragraphs through chapters. Consider the following examples: 


var Book = Bookshelf.Model.extend({

  tableName: 'books',

  // Find all paragraphs associated with this book, by
  // passing through the "Chapter" model.
  paragraphs: function() {
    return this.hasMany(Paragraph).through(Chapter);
  },

  chapters: function() {
    return this.hasMany(Chapter);
  }

});

var Chapter = Bookshelf.Model.extend({

  tableName: 'chapters',

  paragraphs: function() {
    return this.hasMany(Paragraph);
  }

});

var Paragraph = Bookshelf.Model.extend({

  tableName: 'paragraphs',

  chapter: function() {
    return this.belongsTo(Chapter);
  },

  // A reverse relation, where we can get the book from the chapter.
  book: function() {
    return this.belongsTo(Book).through(Chapter);
  }

});

The "through" table creates a pivot model, which it assigns to model.pivot after it is created. On toJSON, the pivot model is flattened to values prefixed with _pivot_.

attachrelation.attach(ids, [options])
Attaches one or more ids from a foreign table to the current table, in a belongsToMany relationship. Creates & saves a new model and attaches the model with the related model. 


new Site({id: 1}).related('admins').attach([1, 2]);

The attach function may also take one or more models to attach to the current model: 


var admin1 = new Admin({username: 'user1', password: 'test'});
var admin2 = new Admin({username: 'user2', password: 'test'});

Promise.all([admin1.save(), admin2.save()])
  .then(function() {
    return Promise.all([
      new Site({id: 1}).admins().attach([admin1, admin2]),
      new Site({id: 2}).admins().attach(admin2)
    ]);
  })

detachrelation.detach(ids, [options])
Detach one or more related objects from their pivot tables. If a model or id is passed, it attempts to remove the pivot table based on that foreign key. If no parameters are specified, we assume we will detach all related associations.

withPivotrelation.withPivot(columns)
The withPivot method is used exclusively on belongsToMany relations, and allows for additional fields to be pulled from the joining table. 


var Tag = Bookshelf.Model.extend({
  comments: function() {
    return this.belongsToMany(Comment).withPivot(['created_at', 'order']);
  }
});

updatePivotrelation.updatePivot(attrs, [options]) The updatePivot method is used exclusively on belongsToMany relations, and allows for updating pivot rows on the joining table. If you wish to confine the update clause, you may pass a query property on the options, which acts similar to the model.query method. If you are expecting an update and wish for an error (rejected promise) when no rows are updated, you can pass {require: true} in the options.

Polymorphic Associations:

With polymorphic associations, a model can belong to more than one other model, on a single association. For example, you might have a photo model that belongs to either a Site model or a Post model. Here’s how this could be declared: 


var Site = Bookshelf.Model.extend({
  tableName: 'sites',
  photo: function() {
    return this.morphOne(Photo, 'imageable');
  }
});

var Post = Bookshelf.Model.extend({
  tableName: 'posts',
  photos: function() {
    return this.morphMany(Photo, 'imageable');
  }
});

var Photo = Bookshelf.Model.extend({
  tableName: 'photos',
  imageable: function() {
    return this.morphTo('imageable', Site, Author);
  }
});

morphOnemodel.morphOne(Target, name, [morphValue])
The morphOne is used to signify a polymorphic relation with another Target model, where the name of the model is used to determine which database table keys are used. The naming convention requires the name prefix an _id and _type field in the database. So for the case above the table names would be imageable_type and imageable_id. The morphValue may be optionally set to store/retrieve a different value in the _type column than the tableName. 


var Site = Bookshelf.Model.extend({
  tableName: 'sites',
  photo: function() {
    return this.morphOne(Photo, 'imageable');
  }
});

morphManymodel.morphMany(Target, name, [morphValue])
The morphMany property is essentially the same as a morphOne, but creating a collection rather than a model (similar to a hasOne vs. hasMany relation). The morphValue may be optionally set to store/retrieve a different value in the _type column than the tableName. 


var Post = Bookshelf.Model.extend({
  tableName: 'posts',
  photos: function() {
    return this.morphMany(Photo, 'imageable');
  }
});

morphTomodel.morphTo(name, targets*)
The morphTo relation is used to specify the inverse of the "morphOne" or "morphMany" relations, where the targets (constructors) must be passed to signify which models are the potential opposite end of the polymorphic relation. 


var Photo = Bookshelf.Model.extend({
  tableName: 'photos',
  imageable: function() {
    return this.morphTo('imageable', Site, Author);
  }
});

relationsmodel.relations
The relations property is the internal hash containing each of the relations (models or collections) loaded on the model with load or with the withRelated property during fetch. This is also populated by default when using Model#related to retrieve a relation, if one does not already exist. You can access the relation using the related method on the model. 


new Photo({id: 1}).fetch({
  withRelated: ['account']
}).then(function(photo) {
  if (photo) {
    var account = photo.related('account');
    if (account.id) {
      return account.related('trips').fetch();
    }
  }
});

relatedDatamodel.relatedData
The relatedData is an internal "Relation" object of information about the current model's relation, including the type, foreignKey, fkValue, and other data depending on the relation type. Mainly for use internally, but possibly helpful for custom behavior.

savemodel.save([params], [options]).then(function(model) {...
The Model's save method is used to perform an insert or update query with the model, dependent on whether the model isNew. If the model isNew, any defaults will be added to the object being saved. If you wish to use update rather than insert or vice versa, the {method: ...} option may be specified to explicitly state which Bookshelf.Sync method to use in the save call. If the method is update, a {defaults: true} option may be specified to also merge with the default values. 


new Post({name: 'New Article'}).save().then(function(model) {
  // ...
});

If you only wish to update with the params passed to the save, you may pass a {patch: true} flag to the database: 


// update authors set "bio" = 'Short user bio' where "id" = 1
new Author({id: 1, first_name: 'User'})
  .save({bio: 'Short user bio'}, {patch: true})
  .then(function(model) {
    // ...
  });

Several events fired on the model when saving: a "creating", or "updating" event if the model is being inserted or updated, and a "saving" event in either case. To prevent saving the model (with validation, etc.), throwing an error inside one of these event listeners will stop saving the model and reject the promise. A "created", or "updated" event is fired after the model is saved, as well as a "saved" event either way. If you wish to modify the query when the "saving" event is fired, the knex query object should be available on options.query.

destroymodel.destroy
The Model's destroy method performs a delete on the model, using the model's idAttribute to constrain the query. A "destroying" event is triggered on the model before being destroyed. To prevent destroying the model (with validation, etc.), throwing an error inside one of these event listeners will stop destroying the model and reject the promise. A "destroyed" event is fired after the model's removal is completed.

attributesmodel.attributes
The attributes property is the internal hash containing the database row.

Please use set to update the attributes instead of modifying them directly. If you'd like to retrieve and munge a copy of the model's attributes, use toJSON instead.

defaultsmodel.defaults or model.defaults()
The defaults hash (or function) can be used to specify the default attributes for your model. Unlike with Backbone's model defaults, Bookshelf defaults are applied before an "insert" query, rather than during object creation. 


var Meal = Bookshelf.Model.extend({
  tableName: 'meals',
  defaults: {
    appetizer:  "caesar salad",
    entree:     "ravioli",
    dessert:    "cheesecake"
  }
});

new Meal().save().then(function(meal) {
  alert("Dessert will be " + meal.get('dessert'));
});

Remember that in JavaScript, objects are passed by reference, so if you include an object as a default value, it will be shared among all instances. Instead, define defaults as a function.

formatmodel.format(attributes, [options])
The format method is used to modify the current state of the model before it is persisted to the database. The attributes passed are a shallow clone of the model, and are only used for inserting/updating - the current values of the model are left intact. 


// Example of a "format" to convert camelCase to snake_case when saving, using `underscore.string`
model.format = function(attrs) {
  return _.reduce(attrs, function(memo, val, key) {
    memo[_.str.underscored(key)] = val;
    return memo;
  }, {});
};

parsemodel.parse(response, [options])
The parse method is called whenever a model's data is returned in a fetch call. The function is passed the raw database response object, and should return the attributes hash to be set on the model. The default implementation is a no-op, simply passing through the JSON response. Override this if you need to format the database responses - for example calling JSON.parse on a text field containing JSON, or explicitly typecasting a boolean in a sqlite3 database response. 


// Example of a "parse" to convert snake_case to camelCase, using `underscore.string`
model.parse = function(attrs) {
  return _.reduce(attrs, function(memo, val, key) {
    memo[_.str.camelize(key)] = val;
    return memo;
  }, {});
};

toJSONmodel.toJSON([options])
Return a copy of the model's attributes for JSON stringification. If the model has any relations defined, this will also call toJSON on each of the related objects, and include them on the object unless {shallow: true} is passed as an option. For more information on the use of toJSON, check out the JavaScript API for JSON.stringify. 


var artist = new Bookshelf.Model({
  firstName: "Wassily",
  lastName: "Kandinsky"
});

artist.set({birthday: "December 16, 1866"});

console.log(JSON.stringify(artist));
// {firstName: "Wassily", lastName: "Kandinsky", birthday: "December 16, 1866"}

Additional Backbone Methods (4)
Bookshelf also proxies to Backbone.js for more core Model methods that aren't documented here. The Backbone documentation applies equally for Bookshelf, and can be used to read more on these methods.

Underscore Methods (6)
Bookshelf proxies to the Underscore implementation to provide 6 object functions on Bookshelf.Model. They aren't all documented here, but you can take a look at the Underscore documentation for the full details… 


user.pick('first_name', 'last_name', 'email');

chapters.keys().join(', ');

querymodel.query([method], [*parameters])
The query method is used to tap into the underlying Knex query builder instance for the current model. If called with no arguments, it will return the query builder directly. Otherwise, it will call the specified method on the query builder, applying any additional arguments from the model.query call. If the method argument is a function, it will be called with the Knex query builder as the context and the first argument, returning the current model. 


model
  .query('where', 'other_id', '=', '5')
  .fetch()
  .then(function(model) {
    ...
  });

model.query(function(qb) {
  qb.where('other_person', 'LIKE', '%Demo').orWhere('other_id', '>', 10);
}).fetch()
  .then(function(model) {...

var qb = model.query();
    qb.where({id: 1}).select().then(function(resp) {...

resetQuerymodel.resetQuery()
Used to reset the internal state of the current query builder instance. This method is called internally each time a database action is completed by Bookshelf.Sync.

hasTimestampsmodel.hasTimestamps
If this value is set, the timestamp method will be called on a model.save() to set the created_at and updated_at values on save. If this value is an array, the first value will be used as the model's key for the "created at" value and the second for the "updated at" value. 


var PostModel = Bookshelf.Model.extend({
  hasTimestamps: ['createdAt', 'updatedAt']
});

timestampmodel.timestamp(options)
Sets the timestamp attributes on the model, if hasTimestamps is set to true. The default implementation is to check if the model isNew and set the created_at and updated_at attributes to the current date if it is new, and just the updated_at attribute if it isn't. You may override this method if you wish to use different column names or types for the timestamps.

clonemodel.clone()
Returns a new instance of the model with identical attributes, including any relations from the cloned model.

isNewmodel.isNew()
Checks for the existence of an id to determine whether the model is considered "new". 


var modelA = new Bookshelf.Model();
modelA.isNew(); // true

var modelB = new Bookshelf.Model({id: 1});
modelB.isNew(); // false

changedmodel.changed
The changed property is the internal hash containing all the attributes that have changed since the model's last fetch, save, or destroy.

hasChangedmodel.hasChanged([attribute])
Returns true if any attribute has changed since the last fetch, save, or destroy. If an attribute is passed, returns true only if that specific attribute has changed.

previousmodel.previous(attribute)
Returns the this previous value of a changed attribute, or undefined if one had not been specified previously.

previousAttributesmodel.previousAttributes()
Return a copy of the model's previous attributes from the model's last fetch, save, or destroy. Useful for getting a diff between versions of a model, or getting back to a valid state after an error occurs.

syncmodel.sync(method, model, [options])
Creates and returns a new Bookshelf.Sync instance. Can be overridden for custom behavior.

Associations

Bookshelf handles one-to-one, one-to-many, and many-to-many associations by defining relationships on models.

One-to-one

One-to-one associations can be created with belongsTo and hasOne. 


var Book, Summary;

Book = Bookshelf.Model.extend({
  tableName: 'books',
  summary: function() {
    return this.hasOne(Summary);
  }
});

Summary = Bookshelf.Model.extend({
  tableName: 'summaries',
  book: function() {
    return this.belongsTo(Book);
  }
});

A Knex migration for the above relationship could be created with: 


exports.up = function(knex, Promise) {
  var createBooks = function() {
    return knex.schema.createTable('books', function(table) {
      table.increments('id').primary();
      table.string('name');
    });
  };
  var createSummaries = function() {
    return knex.schema.createTable('summaries', function(table) {
      table.increments('id').primary();
      table.string('details');
      table.integer('book_id').unique().notNullable()
        .references('id').inTable('books');
    });
  };
  return createBooks().then(createSummaries);
};

exports.down = function(knex, Promise) {
  var dropBooks = function() {
    return knex.schema.dropTable('books');
  };
  var dropSummaries = function() {
    return knex.schema.dropTable('summaries');
  };
  return dropSummaries().then(dropBooks);
};

One-to-many

One-to-many associations can be created with belongsTo and hasMany. 


var Book, Page;

Book = Bookshelf.Model.extend({
  tableName: 'books',
  pages: function() {
    return this.hasMany(Page);
  }
});

Page = Bookshelf.Model.extend({
  tableName: 'pages',
  book: function() {
    return this.belongsTo(Book);
  }
});

A Knex migration for the above relationship could be created with: 


exports.up = function(knex, Promise) {
  var createBooks = function() {
    return knex.schema.createTable('books', function(table) {
      table.increments('id').primary();
      table.string('name');
    });
  };
  var createPages = function() {
    return knex.schema.createTable('pages', function(table) {
      table.increments('id').primary();
      table.string('content');
      table.integer('book_id').notNullable()
        .references('id').inTable('books');
    });
  };
  return createBooks().then(createPages);
};

exports.down = function(knex, Promise) {
  var dropBooks = function() {
    return knex.schema.dropTable('books');
  };
  var dropPages = function() {
    return knex.schema.dropTable('pages');
  };
  return dropPages().then(dropBooks);
};

Many-to-many

Many-to-many associations can be created with belongsToMany. 


var Book, Author;

Book = Bookshelf.Model.extend({
  tableName: 'books',
  authors: function() {
    return this.belongsToMany(Author);
  }
});

Author = Bookshelf.Model.extend({
  tableName: 'authors',
  books: function() {
    return this.belongsToMany(Book);
  }
});

A Knex migration for the above relationship could be created with: 


exports.up = function(knex, Promise) {
  var createBooks = function() {
    return knex.schema.createTable('books', function(table) {
      table.increments('id').primary();
      table.string('name');
    });
  };
  var createAuthors = function() {
    return knex.schema.createTable('authors', function(table) {
      table.increments('id').primary();
      table.string('name');
    });
  };
  var createJoinTable = function() {
    return knex.schema.createTable('authors_books', function(table) {
      table.integer('author_id').notNullable()
        .references('id').inTable('authors');
      table.integer('book_id').notNullable()
        .references('id').inTable('books');
    });
  };
  return createBooks().then(createAuthors).then(createJoinTable);
};

exports.down = function(knex, Promise) {
  var dropBooks = function() {
    return knex.schema.dropTable('books');
  };
  var dropAuthors = function() {
    return knex.schema.dropTable('authors');
  };
  var dropJoinTable = function() {
    return knex.schema.dropTable('authors_books');
  };
  return dropJoinTable().then(dropAuthors).then(dropBooks);
};

Bookshelf.Collection

Collections are ordered sets of models returned from the database, which may be used with a full suite of Underscore methods.

extendBookshelf.Collection.extend(properties, [classProperties])
To create a Collection class of your own, extend Bookshelf.Collection, providing instance properties, as well as optional classProperties to be attached directly to the collection's constructor function.

forgeBookshelf.Collection.forge([models], [options])
A simple helper function to instantiate a new Collection without needing new. 


var Promise = require('bluebird');
var Accounts = Bookshelf.Collection.extend({
  model: Account
});

var accounts = Accounts.forge([
  {name: 'Person1'},
  {name: 'Person2'}
])

Promise.all(accounts.invoke('save')).then(function() {
  // collection models should now be saved...
});

modelcollection.model
Override this property to specify the model class that the collection contains. If defined, you can pass raw attributes objects (and arrays) to add, create, and reset, and the attributes will be converted into a model of the proper type. Unlike in Backbone.js, the model attribute may not be a polymorphic model. 


var Trilogy = Bookshelf.Collection.extend({
  model: Book
});

constructor / initializenew Collection([models], [options])
When creating a Collection, you may choose to pass in the initial array of models. The collection's comparator may be included as an option. Passing false as the comparator option will prevent sorting. If you define an initialize function, it will be invoked when the collection is created. 


var tabs = new TabSet([tab1, tab2, tab3]);

modelscollection.models
Raw access to the JavaScript array of models inside of the collection. Usually you'll want to use get, at, or the Underscore methods to access model objects, but occasionally a direct reference to the array is desired.

parsecollection.parse(response, [options])
The parse method is called whenever a collection's data is returned in a fetch call. The function is passed the raw database response array, and should return an array to be set on the collection. The default implementation is a no-op, simply passing through the JSON response.

toJSONcollection.toJSON([options])
Return an array containing the toJSON for each model in the collection. For more information about toJSON, check out JavaScript's JSON API. You may pass {shallow: true} in the options to omit any relations loaded on the collection's models.

fetchcollection.fetch([options])
Fetch the default set of models for this collection from the database, resetting the collection when they arrive. If you wish to trigger an error if the fetched collection is empty, pass {require: true} as one of the options to the fetch call. A "fetched" event will be fired when records are successfully retrieved. If you need to constrain the query performed by fetch, you can call the query method before calling fetch.

If you'd like to only fetch specific columns, you may specify a columns property, in the options for the fetch call, or use the query method, tapping into the knex column method to specify which columns will be fetched.

The withRelated parameter may be specified to fetch the models of the collection, eager loading any specified relations named on the model. A single property, or an array of properties can be specified as a value for the withRelated property. The results of these relation queries will be loaded into a relations property on the respective models, may be retrieved with the related method, and will be serialized as properties on a toJSON call unless {shallow: true} is passed.

fetchOnecollection.fetchOne([options])
Fetch and return a single model from the collection, maintaining any relation data from the collection, and any query parameters that have already been passed to the collection. Especially helpful on relations, where you would only like to return a single model from the associated collection. 


// select * from authors where site_id = 1 and id = 2 limit 1;
new Site({id:1})
  .authors()
  .query({where: {id: 2}})
  .fetchOne()
  .then(function(model) {
    ... //
  });

mapThencollection.mapThen(iterator, [context])
Shortcut for calling Promise.map(collection.models, iterator) with an optionally bound context, returning a promise which is resolved with the result of the map. 


new Collection([{id: 1}, {id: 2}, {id: 3}]).mapThen(function(model) {

  return model.save().then(function() {
    return model.get('id') + '-saved';
  });

}).then(function(resp) {

  // resp: ['1-saved', '2-saved', '3-saved']

});

reduceThencollection.reduceThen(iterator, [memo], [context])
Shortcut for calling Promise.reduce(collection.models, iterator, memo) with an optionally bound context, returning a promise which is resolved with the result of the reduce. 


new Collection([{id: 1}, {id: 2}, {id: 3}]).reduceThen(function(memo, model) {

  return model.save().then(function() {
    memo++;
    return memo;
  });

}, 0).then(function(saved) {

  // resp: 3

});

invokeThencollection.invokeThen(method, args*)
Shortcut for calling Promise.all around a collection.invoke, this will delegate to the collection's invoke method, resolving the promise with an array of responses all async (and sync) behavior has settled. Useful for bulk saving or deleting models: 


collection.invokeThen('save', null, options).then(function() {
  // ... all models in the collection have been saved
});

collection.invokeThen('destroy', options).then(function() {
  // ... all models in the collection have been destroyed
});

loadcollection.load(relations, options).then(function(collection) {...
The load method can be used to eager load attributes onto a Collection, in a similar way that the withRelated property works on fetch. Nested eager loads can be specified by separating the nested relations with '.'. 


new Stories().fetch({
  withRelated: 'author'
}).tap(function(stories) {
  if (req.withComments) return stories.load(['comments.tags', 'comments.author']);
}).then(function() {
  console.log(JSON.stringify(stories));
});

addcollection.add(models, [options])
Add a model (or an array of models) to the collection, You may also pass raw attributes objects, and have them be vivified as instances of the model. Pass {at: index} to splice the model into the collection at the specified index. If you're adding models to the collection that are already in the collection, they'll be ignored, unless you pass {merge: true}, in which case their attributes will be merged into the corresponding models, firing any appropriate "change" events. 

var ships = new Backbone.Collection;

ships.on("add", function(ship) {
  alert("Ahoy " + ship.get("name") + "!");
});

ships.add([
  {name: "Flying Dutchman"},
  {name: "Black Pearl"}
]);

Note that adding the same model (a model with the same id) to a collection more than once
is a no-op.

removecollection.remove(models, [options])
Remove a model (or an array of models) from the collection, but does not remove the model from the database, use the model's destroy method for this.

resetcollection.reset([models], [options])
Adding and removing models one at a time is all well and good, but sometimes you have so many models to change that you'd rather just update the collection in bulk. Use reset to replace a collection with a new list of models (or attribute hashes). Calling collection.reset() without passing any models as arguments will empty the entire collection.

setcollection.set(models, [options])
The set method performs a "smart" update of the collection with the passed list of models. If a model in the list isn't yet in the collection it will be added; if the model is already in the collection its attributes will be merged; and if the collection contains any models that aren't present in the list, they'll be removed. If you'd like to customize the behavior, you can disable it with options: {add: false}, {remove: false}, or {merge: false}. 


var vanHalen = new Backbone.Collection([eddie, alex, stone, roth]);

vanHalen.set([eddie, alex, stone, hagar]);

// Fires a "remove" event for roth, and an "add" event for "hagar".
// Updates any of stone, alex, and eddie's attributes that may have
// changed over the years.

getcollection.get(id)
Get a model from a collection, specified by an id, a cid, or by passing in a model. 


var book = library.get(110);

atcollection.at(index)
Get a model from a collection, specified by index. Useful if your collection is sorted, and if your collection isn't sorted, at will still retrieve models in insertion order.

querycollection.query([method], [*parameters])
The query method is used to tap into the underlying Knex query builder instance for the current collection. If called with no arguments, it will return the query builder directly. Otherwise, it will call the specified method on the query builder, applying any additional arguments from the collection.query call. If the method argument is a function, it will be called with the Knex query builder as the context and the first argument, returning the current model. 


var qb = collection.query();
    qb.where({id: 1}).select().then(function(resp) {...

collection.query(function(qb) {
  qb.where('id', '>', 5).andWhere('first_name', '=', 'Test');
}).fetch()
  .then(function(collection) {...

collection
  .query('where', 'other_id', '=', '5')
  .fetch()
  .then(function(collection) {
    ...
  });

resetQuerycollection.resetQuery()
Used to reset the internal state of the current query builder instance. This method is called internally each time a database action is completed by Bookshelf.Sync.

synccollection.sync([options])
Creates and returns a new Bookshelf.Sync instance. Can be overridden for custom behavior.

createcollection.create(relations, options).then(function(model) {...
Convenience to create a new instance of a model within a collection. Equivalent to instantiating a model with a hash of attributes, saving the model to the database, and adding the model to the collection after being successfully created. Returns a promise, resolving with the new model.

Additional Backbone Methods (12)
Bookshelf also proxies to Backbone.js for more core Collection methods that aren't documented here. The Backbone documentation applies equally for Bookshelf, and can be used to read more on these methods.

Underscore Methods (28)
Just as with Backbone, Bookshelf proxies to the Underscore implementation to provide 28 iteration functions on Bookshelf.Collection. They aren't all documented here, but you can take a look at the Underscore documentation for the full details…

Bookshelf.Events

Backbone Methods (5)
Bookshelf mixes in the Events module from Backbone, and therefore each Model and Collection has access to each of the following methods, which can be referenced in the Backbone documentation.

.triggerThen(name, args*)
A promise version of "trigger", returning a promise which resolves with all return values from triggered event handlers. If any of the event handlers throw an Error or return a rejected promise, the promise will be rejected. Used internally on the "creating", "updating", "saving", and "destroying" events, and can be helpful when needing async event handlers (for validations, etc).

Catalog of Events
Here's the complete list of recognized Bookshelf events, with arguments. Other events from Backbone might be triggered, but aren't officially supported unless they are noted in this list. You're also free to trigger your own events on Models and Collections and The Bookshelf object itself mixes in Events, and can be used to emit any global events that your application needs.

Model:

Collection:

For fetching events, columns is an array of columns that will be passed to knex. If you want to modify the columns, make sure to preserve the reference: 


  // won't work
  function (model, columns) {
    columns = ['my', 'columns'];
  }

  // will work
  function (model, columns) {
    columns.length = 0;
    columns.push('my', 'columns');
  }

Utility

Bookshelf.knex
A reference to the Knex.js instance being used by Bookshelf.

Bookshelf.transaction
An alias to Knex.transaction, the transaction object must be passed along in the options of any relevant Bookshelf.Sync calls, to ensure all queries are on the same connection. The entire transaction block is a promise that will resolve when the transaction is committed, or fail if the transaction is rolled back. 


var Promise = require('bluebird');

Bookshelf.transaction(function(t) {

  new Library({name: 'Old Books'})
    .save(null, {transacting: t})
    .then(function(model) {

      return Promise.all(_.map([
        {title: 'Canterbury Tales'},
        {title: 'Moby Dick'},
        {title: 'Hamlet'}
      ], function(info) {

        // Some validation could take place here.
        return new Book(info).save('shelf_id', model.id, {transacting: t});
      }));
    })
    .then(function(rows) {
      t.commit([model.get('name'), rows.length]);
    }, t.rollback);

  }).then(function(response) {

    // ['Old Books', 3]
    console.log(response);

  }, function() {

    console.log('Error saving the books.');

  });

Plugins

F.A.Q.

My relations don't seem to be loading, what's up?
Make sure you check that the type is correct for the initial parameters passed to the initial model being fetched. For example new Model({id: '1'}).load([relations...]) will not return the same as Model({id: 1}).load([relations...]) - notice that the id is a string in one case and a number in the other. This can be a common mistake if retrieving the id from a url parameter.

This is only an issue if you're eager loading data with load without first fetching the original model. Model({id: '1'}).fetch({withRelated: [relations...]}) should work just fine.

Can I use standard node.js style callbacks.
Yes - you can call .exec(function(err, resp) { on any "sync" method and use the standard (err, result) style callback interface if you prefer.

How do I debug?
If you pass {debug: true} as one of the options in your initialize settings, you can see all of the query calls being made. Sometimes you need to dive a bit further into the various calls and see what all is going on behind the scenes. I'd recommend node-inspector, which allows you to debug code with debugger statements like you would in the browser.

Bookshelf uses it's own copy of the "bluebird" promise library, you can read up here for more on debugging these promises... but in short, adding: 


process.stderr.on('data', function(data) {
  console.log(data);
});

At the start of your application code will catch any errors not otherwise caught in the normal promise chain handlers, which is very helpful in debugging.

How do I run the test suite?
The test suite looks for an environment variable called BOOKSHELF_TEST for the path to the database configuration. If you run the following command: $ export BOOKSHELF_TEST='/path/to/your/bookshelf_config.js', replacing with the path to your config file, and the config file is valid, the test suite should run with npm test.

If you're going to add a test, you should set $ export BOOKSHELF_DEV=1 which will automatically save the the outputs data from the tests into the shared/output.js file.

Also note that you will have to create the appropriate database(s) for the test suite to run. For example, with mysql, you'll need to run the command create database bookshelf_test; in addition to exporting the correct test settings prior to running the test suite.

Can I use Bookshelf outside of Node.js
While it primarily targets Node.js, all dependencies are browser compatible, and it could be adapted to work with other javascript environments supporting a sqlite3 database, by providing a custom Knex adapter.

Change Log

0.6.11 — June 4, 2014 — Diff
Temporarily add knex to peerDependencies until 0.7 is released to support knex 0.6 and there exists a better internal method of doing a semver check.

0.6.10 — April 3, 2014 — Diff

0.6.9 — April 3, 2014 — Diff
Only prefix model fields with the "tableName" after format has been called, (#308).

0.6.8 — March 6, 2014 — Diff

0.6.7 — March 2, 2014 — Diff
Bugfix for edge case for eager loaded relations and relatedData settings.

0.6.6 — March 1, 2014 — Diff
Bugfix for registry plugin, resolving correct models for "through" relations. (#260)

0.6.5 — February 28, 2014 — Diff

0.6.4 — February 11, 2014 — Diff
Adds static method Model.collection as a shortcut for creating a collection with the current model.

0.6.3 — February 9, 2014 — Diff

Plugins:

0.6.2 — December 18, 2013 — Diff

0.6.1 — November 26, 2013 — Diff
Fixes bug with promise code and saving event firing, where promises are not properly resolved with ".all" during saving events.

0.6.0 — November 25, 2013 — Diff

0.5.8 — November 24, 2013 — Diff

0.5.7 — October 11, 2013 — Diff
The "fetching" event is now fired on eager loaded relation fetches.

0.5.6 — October 10, 2013 — Diff
The options.query now contains the appropriate knex instance during the "fetching" event handler.

0.5.5 — October 1, 2013 — Diff
An eager loaded morphTo relation may now have child relations nested beneath it that are properly eager loaded, depending on the parent.

0.5.4 — October 1, 2013 — Diff

0.5.3 — September 26, 2013 — Diff
The columns explicitly specified in a fetch are no-longer passed along when eager loading relations, fixes (#70).

0.5.2 — September 22, 2013 — Diff
Fixed incorrect eager loading in belongsTo relations (#65).

0.5.1 — September 21, 2013 — Diff
Fixed incorrect eager loading in hasOne relations (#63).

0.5.0 — September 20, 2013 — Diff
Major Breaking changes:

Other Changes:

0.3.1 — August 29, 2013 — Diff — Docs
Fixed regression in belongsToMany custom column name order.

0.3.0 — August 28, 2013
Support for a through clause on various model relations. Creating a model from a related collection maintains the appropriate relation data (#35). Support for a {patch: true} flag on save, to only update specific saved attributes. Added a fetchOne method, for pulling out a single model from a collection, mostly useful for related collection instances. Updated to Knex "0.2.x" syntax for insert / returning, Ability to specify a morphValue on morphOne or morphMany relations. Internal refactor of relations for more consistent behavior.

0.2.8 — August 26, 2013
Some minor fixes to make the Sync methods more consistent in their behavior when called directly, (#53).

0.2.7 — August 21, 2013
Timestamp for created_at is not set during an "update" query, and the update where clause does not include the idAttribute if it isn't present (#51).

0.2.6 — August 21, 2013
Fixes bug with query function feature added in 0.2.5, mentioned in (#51).

0.2.5 — August 19, 2013
The query method may now accept a function, for even more dynamic query building (#45). Fix for relations not allowing "0" as a valid foreign key value (#49).

0.2.4 — July 30, 2013
More consistent query resetting, fixing query issues on post-query event handlers. The toJSON is only called on a related model if the method exists, allowing for objects or arrays to be manually specified on the relations hash and serialized properly on the parent's toJSON.

0.2.3 — July 7, 2013
Fixing bug where triggerThen wasn't actually being used for several of the events as noted in 0.2.1 release.

0.2.2 — July 2, 2013
The Model's related method is now a no-op if the model doesn't have the related method. Any withPivot columns on many-to-many relations are now prefixed with _pivot rather than pivot unless named otherwise, for consistency. The _reset is not called until after all triggered events so that hasChanged can be used on the current model state in the "created", "updated", "saved", and "destroyed" events. Eager queries may be specified as an object with a function, to constrain the eager queries: 


user.fetch({withRelated: ['accounts', {
  'accounts.settings': function(qb) { qb.where('status', 'enabled'); }
}, 'other_data']}).then(...

0.2.1 — June 26, 2013
Using triggerThen instead of trigger for "created", "updated", "saved", "destroyed", and "fetched" events - if any async operations are needed after the model is created but before resolving the original promise.

0.2.0 — June 24, 2013
Resolve Model's fetch promise with null rather than undefined. An object of query. constraints (e.g. {where: {...}, orWhere: {...}}may be passed to the query method (#30). Fix for empty eager relation responses not providing an empty model or collection instance on the model.relations object.

0.1.9 — June 19, 2013
Resolve Model's fetch promise with undefined if no model was returned. An array of "created at" and "updated at" values may be used for hasTimestamps. Format is called on the Model#fetch method. Added an exec plugin to provide a node callback style interface for any of the promise methods.

0.1.8 — June 18, 2013
Added support for polymorphic associations, with morphOne, morphMany, and morphTo model methods.

0.1.7 — June 15, 2013
Bugfix where detach may be used with no parameters to detach all related items (#19).

0.1.6 — June 15, 2013
Fixing bug allowing custom idAttribute values to be used in eager loaded many-to-many relations (#18).

0.1.5 — June 11, 2013
Ensuring each of the _previousAttribute and changed values are properly reset on related models after sync actions.

0.1.4 — June 10, 2013
Fixing issue with idAttribute not being assigned after database inserts. Removing various aliases Events methods for clarity.

0.1.3 — June 10, 2013
Added hasChanged, previous, and previousAttributes methods, for getting the previous value of the model since the last sync. Using Object.create(null) for various internal model objects dealing with user values. Calling related on a model will now create an empty related object if one is not present on the relations object. Removed the {patch: true} option on save, instead only applying defaults if the object isNew, or if {defaults: true} is passed. Fix for model.clone's relation responses.

0.1.2 — May 17, 2013
Added triggerThen and emitThen for promise based events, used internally in the "creating", "updating", "saving", and "destroying" events. Docs updates, fixing {patch: true} on update to have intended functionality. A model's toJSON is now correctly called on any related properties.

0.1.1 — May 16, 2013
Fixed bug with eager loaded belongsTo relations (#14).

0.1.0 — May 13, 2013
Initial Bookshelf release.