Getting Started with Bookshelf.js

Joe Pettit

Joe Pettit

— 3 min read

I recently started a project where I was tasked to use Bookshelf.js to interact with the database. The library its self is pretty handy, it is built on top of Knex.js, a great query builder that works with Postgres, MySQL, and SQLite, but my major gripe was an overall lack of examples in the documentation. That being said, I wanted to document what I learned for folks using Bookshelf.js in the future (and for myself when I inevitably forget how it was done).

Installing Bookshelf.js & Knex.js

Installation requires Bookshelf.js, Knex.js as well as the corresponding package for the database server you are using (MySQL, Postgres, SQLite):

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

Next, install the package for your database server:

// Pick one
npm install pg --save // Postgres
npm install mysql --save // MySQL
npm install mariasql --save // MariaDB
npm install sqlite3 --save // SQLite

Connecting with Database

Once Establishing a connection with the database is fairly simple, pass in a JSON object of the necessary options:

var knex = require('knex')({
	client: '{pg|mysql|sqlite3}',
    connection: {
    	host: 'host' // IP or domain name
        user: 'user' // DB username
        password: 'password' // DB password
        database: 'database' // DB name
        charset: 'utf8' // Or your preferred charset
    }
});

If you are using SQLite, add the filename directive to the connection directives, this should be the path to your database on the filesystem.

Next, bring Bookshelf into the loop:

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

With that, you're ready to get started!

Models

The first thing you'll want to do is extend Model so you can interact with tables in your database. This is done like so:

var model = bookshelf.Model.extend({
	tableName: "nameOfTable"
});

You can now use the variable model to interact with this table.

A quick side note, you can append functions to the model delcaration if need be as shown in this example from bookshelfjs.org:


var checkit  = require('checkit');
var Promise  = require('bluebird');
var bcrypt   = Promise.promisifyAll(require('bcrypt'));

var Customer = bookshelf.Model.extend({

  initialize: function() {
    this.on('saving', this.validateSave);
  },

  validateSave: function() {
    return checkit(rules).run(this.attributes);
  },

  account: function() {
    return this.belongsTo(Account);
  },

}, {

  login: Promise.method(function(email, password) {
    if (!email || !password) throw new Error('Email and password are both required');
    return new this({email: email.toLowerCase().trim()}).fetch({require: true}).tap(function(customer) {
      return bcrypt.compareAsync(customer.get('password'), password);
    });
  })

});

Customer.login(email, password)
  .then(function(customer) {
    res.json(customer.omit('password'));
  }).catch(Customer.NotFoundError, function() {
    res.json(400, {error: email + ' not found'});
  }).catch(function(err) {
    console.error(err);
  });

Creating a New Record

Next, lets create a record - I'll continue using the variable model as declared in the previous section:

new model({
	'column1': 'columnValue',
    'column2': 'columnValue'
    // and so on...
}).save().then(function(newRow) {
	console.log(newRow.id); // Returns ID of new row
	// Code executed after row successfully created
}).catch(function(err) {
	// Handle errors
});

You can also use forge instead of the new notation:

model.forge({
	'column1': 'columnValue'
    // and so on...
}).save().then(function(newRow) {
	console.log(newRow.id); // Returns ID of new row
}).catch(function(err) {
	// Handle errors
});

Bookshelf has built in promisification so be sure to take advantage of that feature in your code!

Update Record(s)

Updating records looks pretty similar to creating a new record except you'll also need to pass save() a JSON object that contains the updates you want to make:

new model({
	// The query will match these parameters
    'id': 1
    // Will return row with ID 1
}).save({
	// These updates will be made
    'name': 'Joe'
    // Record's name will be updated to 'Joe'
}).function(updatedModel) { ... }).catch(function(err) { ... });

Fetch Record(s)

Fetching records also looks pretty similar to the previous operations except you will need to use fetch():

new model({
	// Query params 
}).fetch().function(fetchedModel) {
	// Do stuff with fetchedModel 
}).catch(function(err) { ... });

Delete Record

Last but not least, you can delete a record using just about everything used in the fetch example while adding the destroy() function:

new model({
	// Query params
}).fetch().then(function(fetchedModel) {
	fetchedModel.destroy(); 
}).catch(function(err) { ... });

You can avoid requiring the then() handling if your query includes the id of the row:

new model({
	'id': 1
}).fetch().destroy();

Conclusion

This is as far as I have gotten with Bookshelf.js - I do intend to continue writing about this library as I learn more myself. I hope this helps somebody else getting started with Bookshelf.js! If you are interested in reading the official documentation you can find that here.

Read more