Using Bookshelf With Node.js

Yes, you can and you probably fucking should use a relational database in your Node/Express apps. Most data you come across is relational so I’ll never understand this dogma being preached that if you use Node as your back-end then you have to pair it with MongoDB as your persistence layer (a database). You know, that MEAN stack crap everyone cheers on. Bullshit! It’s utter bullshit and anyone who’s used Node.js whilst actually thinking about their data models can see it. You use the database that best fits your dataset. Sort of like that whole “right tool for the job” idea (which is excellent advice, by the way)? Now let’s get you set up with the Bookshelf ORM and do some relational database modeling. We’ll also talk about integrating this into an Express application.

What’s an ORM?

ORM stands for Object Relational Mapping which is a technique that maps object properties to database tables. The idea is that you create classes (in any language) that are representations of real word concepts or physical objects. They have properties and they have methods. A simple example would be of a User model. A user in most web applications has a username and a password as well as a bunch of other stuff like email address and such. A user can also have methods like owning a number of other model objects like blog posts, for example. The class has a direct correlation with a table in the database where each of the table’s fields is represented by one of the class’ properties. Each foreign key in the table becomes a method on the class which performs a join query to pull in related data. Every mainstream language has at least two different relational ORMs to choose from. Node.js only has the MongoDB driver for the longest time. They had to create their own wrapper for it to build an ORM. Then Mongoose (technically an ODM – object document modeler – since its for a non-relational databases) can around and filled that voice. Since then everyone and their mother was using Mongoose with MongoDB and creating schemas for their non-relational databases. How odd. A schema for a database that claims to be non-relational? Sure, there are good reasons to enforce a schema on Mongo but the ability to create arbitrary fields should be enabled by default.

Installation

This section will walk you through installing the production and development dependencies needed to use Bookshelf in an Express app.

Note that as of this writing I’m using the 0.9.x release of Bookshelf, the 0.10.x release of Knex, and the latest SQLite3 driver (3.1.2 right now).

Database dependencies

Bookshelf is an ORM that lets you model relational data. It’s like Node’s version of ActiveRecord and way easier to set up and use than Sequelize, another Node.js relational ORM (Hooray for choice! Hooray for competition!). Unfortunately the creators of Bookshelf decided that their very thorough, straightforward, and easy-to-read documentation might make their library popular and easier to use so they fucked it all up with whatever is on their site now which includes far fewer so people like me have to dig through GitHub issues, outdated wiki articles, and StackOverflow questions to get enough information to write articles like this so that beginners can get up to speed and fill in the gaps that the Bookshelf docs shouldn’t be missing.

Bookshelf is an amazing and simple ORM! Please don’t let the docs scare you off! Relational data is awesome in Node and Bookshelf is one of the best ORMs for relational data in Node. Even if you don’t use Bookshelf, don’t default to using MongoDB because “that’s what Node dev do”.

First off, know that Bookshelf is based on Backbone and relies on Knex. If you like Backbone then you’ll love Bookshelf. If you don’t know what Backbone is, it’s still super easy to use Bookshelf as it requires no prior knowledge of Backbone.js. Knex is a database connection library. It handles things connecting to the database, connection pooling, and supports drivers for all the most popular relational database engines (MySQL, MariaDB, Postgres, SQLite, etc.). Without Knex you can’t have database migrations and connections. Without Bookshelf you can’t use the M in MVC. So no models for you. But together they make a great pairing. Bookshelf wraps around the Knex API and creates JavaScript objects (as in class instances) which can be saved as rows and relations in your database through Knex. Knex can then, in turn, generate an SQL query your database can understand.

The first step toward having a usable Express app backed by a relational database is to install Knex globally. So run:

1
2
3
4
5
npm install -g knex              # Install Knex globally
npm install knex --save          # Install Knex as a dependency
npm install sqlite3 --save-dev   # Install SQLite3 in dev mode
nam install mysql —save          # Install MySQL driver in prod
npm install bookshelf --save     # Install Bookshelf in prod

This should get you all of the dependencies you need to get started with connecting to a database. You’ll need Bookshelf and Knex as general dependencies, SQLite3 as a dev dependency, MySQL (or another database package) for Knex to use as a database adapter in production, and you’ll also need Knex installed globally so you can use its CLI tools to generate and run migrations. As far as the production database drivers go, you’re free to use Postgres, MariaDB or any other Knex-compatible database driver. See the docs for more info.

Express setup and libraries

Now we need to create a web application and we’ll build it on top of Express, the Node framework that gives you the basic routing, middleware, and request/response facilities you need in any web application while leaving the implementation of each route’s body up to you. There are a lot of great ways to set up an Express app and here I’ll show you my favorite way.

The Installation

Run these commands:

1
npm install express express-handlebars —save

Easy. There’s a whole lot more you can add on top of this but we’ll start with the basics and just get Express and Handlebars templating installed as production dependencies.

Usage

With the minimum amount packages installed you can now start using them in your Express app. Here we assume that in addition to all of the database drivers and ORMs installed you also have Express and it’s dependencies installed. If not, you should get acquainted with running an Express app.

In addition, let’s assume that your file structure looks like this:

“The

Here’s a simplified version of that file structure based on the image above:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
project_root/
    |
    |_ server/
    |    |
    |    |_ controllers/
    |    |_ models/
    |    |_ public/
    |    |      |_ css/
    |    |      |_ js/
    |    |      |_ img/
    |    |      |_ less/
    |    |
    |    |_ views/
    |    |      |_ layouts/
    |    |      |_ partials/
    |    |      |_ home.hbs
    |    |
    |    |_ app.js
    |
    |_ .gitignore
    |
    |_ .editorconfig
    |
    |_ gulpfile.js
    |
    |_ package.json
    |
    |_ README.md

Here you see that we have a root folder filled with the usual stuff like a gulpfile.js, knexfile.js, etc. The server/ folder is where all of the app’s guts live. It’s where we store our controllers, models, and views while the root of the project is where we keep our project configuration files, task runner files, and other project setup files.

Database configuration

Before you get into writing any code you need to run a command to generate that knexfile.js you see. That command is

1
knex init

This will create a Knexfile in your project root. The development environment should be good to go for your situation. The only time you’ll need to revisit this is when you get ready to deploy your application.

In the image above you can see the contents of my models/ folder which contains a file called database.js (see it’s code on GitHub here). The contents of that file are responsible for connecting each model to the database. For those of you who are wondering about connection pooling, it is my understanding that because of the way in which Node requires modules, each use of a model will not cause you to open up a new pool of DB connections but instead will simply use any connection pool that already exists. Let’s go through the database connection file (database.js) line by line. Pay close attention to the comments in the file as these will explain exactly what’s going on at each point along the way:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
// server/models/database.js
// =========================
// Connects models to the database

'use strict';

// Require Knex then pass it a configuration file that connects 
// it to the proper database based on the current environment.
//
// We then pass that instance of Knex to Bookshelf which allows
// us to create models and maintain a connection to the database                                                                                  
var knex      = require('knex')(require(__dirname + '/../../knexfile')[process.env.NODE_ENV]),
    bookshelf = require('bookshelf')(knex);

// Now we enable the "registry" plugin. This will help us later
// on in our models when we need to avoid circular dependency
// errors. If you don't know what that error is or want to learn
// more check out my previous post about structuring Bookshelf 
// models which covers circular dependency errors and how to 
// avoid them using this plugin which comes with Bookshelf @ 
// http://billpatrianakos.me/blog/2015/11/30/how-to-structure-bookshelf-dot-js-models/
bookshelf.plugin('registry');

// Here we export the database connection. The first time it 
// gets required by a model it will create a database connection 
// pool and keep it open and available for all other models
module.exports = bookshelf;

Now you have a database connection file that, because of how Node imports or requires files, will give you a persistent connection to the database when you use your first model. But models are backed by database tables so you’ll need to create those. Again, we turn to Knex to generate those tables.

Running the command knex migrate:make create_users_table will create a new migration file in a new folder that’s created for your called migrations. Within that folder you’ll notice a file has been generated. Within that file you’ll define the database table you want to create. Check out the schema building section of the Knex docs for more. A basic migration looks like this:

1
2
3
4
5
6
7
8
9
10
11
// Create users table
exports.up = function(knex, Promise) {
  return Promise.all([
    knex.schema.createTable('users', function(table) {
      table.increments();
      table.string('email').unique();
      table.string('password');
      table.timestamps();
    })
  ]);
};

This is how you should model your own migrations. Obviously you’ll have different requirements but this is how to use the starter code given to you in each migration generated.

Once you’ve written your migration you need to actually apply it to your database. To do this you run knex migrate:latest.

With the database migrations applied you need an actual model to actually use that database connection. Here’s how we create it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// User Model
// ==========
// Create a user model class

'use strict';

var Bookshelf = require('./database');

require('./favorite');
require('./subscriber');

var User = Bookshelf.Model.extend({
  tableName: 'users',
  hasTimestamps: true,
  favorites: function() {
    return this.hasMany('Favorite');
  },
  profile: function() {
    return this.hasOne('Subscriber');
  }
});

module.exports = Bookshelf.model('User', User);

The above model (held in server/models/user.js) describes a user model with relations to a Favorites and Profile mode. A hasMany and hasOne relation respectively. Remember that registry plugin we used earlier in our database connection script? This is where it gets used. You’ll notice that we’re referencing other, related models, by their names as strings and not as variables. This avoids circular dependency errors. Without it your only option is to define all of your models in a single file. Our goal here is to keep our application modular so that Bookshelf registry plugin is priceless to us.

In this example of a User model we’re exporting a Bookshelf.model(‘User’, User) (the 'User' string relates to the User variable we created which is our User model. There are only a few hard and fast rules when it comes to Bookshelf models. Obviously the syntax matters but beyond that you have a lot of freedom to define methods and custom properties for your model. Let’s break the user model down.

1
var Bookshelf = require('./database');

Bookshelf is our variable that holds the connection to the database. Our model needs this. Once a single model calls on it all others will use that same connection so you won’t end up making redundant database connections each time you use a model.

1
2
3
4
5
6
7
8
9
10
var User = Bookshelf.Model.extend({
  tableName: 'users',
  hasTimestamps: true,
  favorites: function() {
    return this.hasMany('Favorite');
  },
  profile: function() {
    return this.hasOne('Subscriber');
  }
});

The only “required” properties in this model are the tableName and hasTimestamps. The first because Bookshelf needs to know what table to base your model on. It’ll read your database table and your model’s properties will match the fields in your database table. The second property is only required if your model is set up with time stamp fields. If your table uses timestamps then setting this to true will automatically set this fields each time you create or update a record with no code required to write by you.

Then we get into the favorites and profile properties. Those properties are there only to define the relation to another model. This is why, if you look at the full model presented above, you’ll see we require two other model files but don’t assign them to variables. This is to avoid circular dependencies but also so we can define relationships between models through these methods that in this case we’re calling favorites and profile. These aren’t required unless you need to define relationships between two tables. Note however, that your migrations need to define a relationship between the tables your models represent based on foreign keys.

Running a server

Uh oh. We’ve hit a wall. This is up to you. There are as many ways to do this as there are developers. My preferred way is to use a Grunt or Gulp task that’ll run a local Node server with live reloading capabilities. For a bare-bones setup you can just run node path/to/main-server.js.

Setting up the main server file

This part is mundane and easy. Here’s a basic skeleton:

1
2
3
4
5
6
7
8
9
10
11
12
// Require dependencies
var express = require(express),
        app = express();

// Here we require all of our controllers as middleware…
// and set up the middleware too.
app.use(express.static(__dirname + /public)); // Serve static assets
app.use(/, require(./controllers/home)); // There are easier ways to require your controllers automatically but this is a basic skeleton

var server = app.listen(process.env.PORT || 3000, function() {
  console.log(Some task runners will not move to the next task until you output something so we say Server is running at localhost:”’ + server.address().port);
};

That’ll get you a server running. Now you need just one controller to start using your models. Here’s an example of a model that has a route that looks up all users with the email entered into an input field:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// Require dependencies
var express           = require('express'),
    SignupController  = express.Router(),
    User              = require(__dirname + '/../models/user');

SignupController.route(/)
  .get(function(req, res, next) {
    // Find a user
    User.where({email: req.params.email}).fetch()
      .then(function(user) {
        res.render(users, {
          users: user // An array of User objects
        });
      })
      .catch(function(error) {
        res.send(ERROR!); // Hand your error better than this please
      });
  });

In this controller if someone makes a GET request to http://localhost:3000/ a page will be rendered showing all users with the email address in params. I won’t show how you render those results – that’s on you to figure out.

Conclusion

There it is. That’s how to set up Bookshelf in an Express app. Hopefully this article along with Bookshelf’s docs help you put enough pieces together to get things working for your own app. This post is dedicated to my most recent batch of students who have been asking for a Bookshelf tutorial considering how inaccessible the new Bookshelf docs are to new developers. Good luck out there and remember – the MEAN stack is a mistake. Relational data is far more common than non-relational data. Don’t just slap a connection to MongoDB to your Node app because that’s what’s you do, because that’s how the MEAN stack says to do it. Build your app based on specs. Don’t write your specs according to your pre-existing app.

Database administration, Web development

« Deploying to Heroku Getting a Job as a Developer »

Comments