Apparently there are those among us who are really into writing SQL statements. Good for you guys. As for the rest of us, there's Bookshelf. A sweet ORM for NodeJS, Bookshelf can be a nice layer of abstraction to make our lives a lot easier when setting up a SQL database. But as with anything in code, it takes a little investment in time and research before you can reap those benefits.
I'm going to go through how to set up Bookshelf, connect it with an ExpressJS app, and how to set up a model.
Structuring The Application
I won't go into how to set up an Express app and getting your server running, but the application architecture I'm going to use will look like this.
├── index.js
├── knexfile.js
├── package.json
└── server
├── collections
│ └── users.js
├── config
│ └── bookshelf.config.js
├── controllers
│ └── userController.js
├── models
│ └── user.js
├── routes
│ └── userRouter.js
└── server.js
In addition to Express and its dependencies, make sure and install bookshelf and knex and mysql using npm.
npm install --save knex bookshelf mysql
Connecting Bookshelf
The next step is to hook our app up to Bookshelf. Bookshelf is based on the Knex query builder. We will need to first configure Knex as it is what will be managing the connection to our database. Knex also allows us to run migrations to set up our tables and our schemas. If you're interested in a walkthrough of how to run migrations in Knex, or if the next section makes no sense at all, check out my post here.
Otherwise, make sure you have a file containing the connection information for Knex to reference. Mine is set up as knexfile.js in the root directory and looks like this:
// knexfile.js
var db = (process.env.NODE_ENV === 'test') ? 'bookshelf_test' : 'bookshelf'
module.exports = {
client: 'mysql',
connection: {
host: '127.0.0.1',
user: 'root',
password: '',
database: db,
charset: 'utf8'
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
};
I've set it up to use MySQL, but you can use which ever SQL flavor you prefer. Likewise, make sure your database is set up with a users table with the necessary columns. Here is my schema written out with Knex.
knex.schema.createTable('users', function(table){
table.increments('id').primary();
table.string('email').notNullable();
table.string('password').notNullable();
table.string('name');
})
Once that is all put together, we'll create a file which will configure and initialize Bookshelf for us. My file lives in the server/config/bookshelf.config.js file.
// bookshelf.config.js
var knex = require('knex')(require('../../knexfile'));
var Bookshelf = require('bookshelf')(knex);
Bookshelf.plugin('registry');
module.exports = Bookshelf;
The first line grabs Knex and feeds it our configuration file so it can set up a connection to the database. Next, we initialize Bookshelf by passing in our knex instance. I have opted to use the registry plugin for Bookshelf to make it easier to prevent model dependency issues. Then, we export the whole thing.
Creating a User Model
Models are the heart and soul of Bookshelf. They give a convenient Object Oriented interface to allow you to manage the contents of your database. They also make it much easier to manage the various relationship types in MySQL; many to many etc. I'm going to save all the foreign key fun for another post. Here, I'll create a simple model without any external relationships.
In our models directory, we'll create a file called user.js to hold on to our user model.
// user.js
var Bookshelf = require('../config/bookshelf.config');
var User = Bookshelf.Model.extend({
tableName: 'users'
});
module.exports = db.model('User', User);
First, we import the Bookshelf instance we set up earlier through our bookshelf.config.js file.
As bookshelf's syntax is based on backbonejs, we extend the bookshelf model and, at minimum, tell bookshelf which database table to save the information on.
Finally, we export our model. The specific syntax here is based on the registry plugin (which we set up in the bookshelf.config.js file). As our models get more complex, through adding in many to many relationships and so on, we will need to include references to external models when we create each model. Registry makes it cleaner to include outside models and gets rid of circular dependency issues. Not an issue now, but a huge help as our data gets more complex.
This is the basic form for our model. To use it, just require it inside your express app, and you have access to all the model goodness that comes with Bookshelf.
Testing Our Model
Testing. It's like eating kale or exercising. Something you know you should be doing, but would rather do later. Likewise, a little investment now, will make you feel way better in the future.
Let's write out some tests to make sure our models work. I like using mocha as a test framework and chai as my assertion library. Make sure and install them as dev dependencies.
npm install --save-dev mocha chai
Then in package.json add the following script so we can easily run npm test.
"scripts": {
"test": "./node_modules/.bin/mocha"
},
Next, let's add a test directory in our root directory and add in a file called userModel.spec.js. Mocha will look for a test directory by default and try and run *.js files inside of it.
// userModel.spec.js
process.env.NODE_ENV = 'test';
var expect = require('chai').expect;
var knex = require('knex')(require('../knexfile'));
var User = require('../server/models/user');
describe('User Route', function() {
// Testing goodness goes here
});
To ensure that I'm working with a clean database, and to clean it out for subsequent tests, I'm going to use knex migrations. While not necessary, it makes it a lot easier to setup and teardown the testing database while working on it. Again check out my post here for more on migrations.
beforeEach will clear out the database using knex.migrate.rollback, then reapply our table schemas using knex.migrate.latest. Finally, we'll make sure and clear out the database by using rollback again in our after function.
// userModel.spec.js
...
describe('User Route', function() {
beforeEach(function(done) {
return knex.migrate.rollback()
.then(function() {
return knex.migrate.latest()
})
.then(function() {
done();
});
});
after(function (done) {
return knex.migrate.rollback()
.then(function(){
done();
});
});
});
Our model is pretty simple at this point, so I'll just write two tests. One as a sanity check to make sure that the model is working correctly and we have an empty database and the second to make sure we can save a model and then access it on the database.
it('should not have any models', function (done) {
User.forge().fetch().then(function(results){
expect(results).to.equal(null);
done()
});
});
it('should save a model to the database', function (done) {
var user = new User({
email: 'test@test.com',
password: 'defconbravo',
name: 'Yossarian'
}).save()
.then(function(){
return User.where({email: 'test@test.com'}).fetch();
})
.then(function(user){
expect(user.get('name')).to.equal('Yossarian');
done();
});
});
In the terminal run npm test and, hopefully, everything should be happily running along.
There is a basic example of how to set up a bookshelf model and test it out. In a later post, I hope to go through how to model more complex relationships using Bookshelf.