We began our Node database interactions through the Bookshelf SQL ORM. It was good to start out with and helped us quickly get moving. But, inevitably, as our relations got more complex and our use cases more specific, we had the choice of pushing further into the quagmire of Bookshelf, or to cut it loose. As apparently ORMs are the CS equivalent of the Vietnam War, we made the choice to cut it loose.
This post will briefly go through our reasoning on creating a new pattern as well as a code example of how the pattern works. If you only want to look at the code, check it out on github here.
Fortunately, the KnexJS library is a happy medium between the often heavy handed Bookshelf and scrapping out raw SQL strings until you loose your mind. Bookshelf is built on top of Knex which is a query builder that closely resembles raw SQL statements. If you know SQL and you know Javascript, you're well on your way to understanding everything there is in Knex.
The pattern we establish was coined as a 'Bag of Functions' with one bag roughly correlating to each type of entry we had on our DB. The functions in these proverbial bags would be generic and composable and would handle all of the DB interaction. These functions would in turn be imported into our controllers which would combine them to make digital magic happen.
This pattern worked well for us for two primary reasons: easy learning curve, and separation of concerns. As mentioned before, Knex is straight forward if you understand SQL, which many of us do, whereas Bookshelf is only straightforward if you understand Bookshelf, which few of us do. The bag of functions helped us separate out concerns as well. With Bookshelf, we inadvisably exposed the Bookshelf models throughout our controllers so the DB logic quickly metastasized itself into many places it didn't belong. By containing all the DB functionality in the bag of functions, the controllers can be kept agnostic of what's happening behind the scenes.
As I always find code samples much more elucidating than my wordy ramblings, I'll outline an example of how to model some basic CRUD functions for a many to many relationship using the Bag of Functions pattern.
Knex Setup
I'll gloss over the setup for knex. If you'd like a walkthrough on how to connect Knex and set up migrations, checkout this post. Likewise, take a look at the example code repo.
For the purposes of our example, we'll use a fairly trivial example of a many to many relationship, in this case between posts and categories. We'll use Knex migrations to set up a table for each as well as a join table.
Migrations
exports.up = function(knex, Promise) {
return Promise.all([
knex.schema.createTable('posts', function(table){
table.increments('id').primary();
table.string('title');
}),
knex.schema.createTable('categories_posts', function(table){
table.increments('id').primary();
table.integer('post_id').references('posts.id');
table.integer('category_id').references('categories.id');
}),
knex.schema.createTable('categories', function(table){
table.increments('id').primary();
table.string('title');
})
]);
};
exports.down = function(knex, Promise) {
return Promise.all([
knex.schema.dropTable('posts'),
knex.schema.dropTable('categories_posts'),
knex.schema.dropTable('category_id')
]);
};
After setting up the migration and a knexfile, create a db.js file in the root directory which will export our db connection pool. I also like to expose a TABLES object which holds onto the string name values of the tables we're interacting with.
const knex = require('knex');
const environment = 'development'
const config = require('./knexfile');
exports.db = knex(config[environment]);
exports.TABLES = {
CATEGORIES: 'categories',
POSTS: 'posts',
CATEGORIES_POSTS: 'categories_posts'
};
File Structure
Our project code will be split primarily into controllers and models directories. We will have a model file for each type of entry we are dealing with, in this case posts and categories. We'll only have a single controller file, more as an illustration as to how to tie the model function bags together.
Here's the layout of the project.
├── controllers
│ └── post_controller.js
├── db.js
├── knexfile.js
├── migrations
│ └── 20160911120323_post_categories.js
├── models
│ ├── categories.js
│ └── posts.js
├── package.json
└── test
├── models_spec.js
└── post_controller_spec.js
Bags of Functions
The crux of the code will be in the models files. These are the proverbial 'bags'. Whenever we need some functionality interacting with the database, we'll add in a function here. Preferably, composing earlier functions to make our lives even easier.
Creation Functions
These functions simply take in a value and save the appropriate entry to the database.
For posts:
// models/posts.js
const { db, TABLES } = require('../db');
// create post
exports.createPost = title => {
return db.insert({ title }).into(TABLES.POSTS).returning('*');
};
For categories:
// models/categories.js
const { db, TABLES } = require('../db');
// create category
exports.createCategory = title => {
return db.insert({ title }).into(TABLES.CATEGORIES).returning('*');
};
All well and good, but we need a way to connect the two together to get all that SQL relationy goodness. Where there could be an argument for creating a separate model file for the join table, I opted for placing all the relational logic into posts.js as it could be seen as the more important of the two. The cross table logic could run you into some circular dependency issues, so make sure you decide where that logic should live and stick with it.
Our attach function will take an id for both the category and the post that it will be attached to and create the appropriate entry on the join table.
...// models/posts.js
// attach category to post
exports.attachPostCategory = (postId, categoryId) => {
return db.insert({ post_id: postId, category_id: categoryId }).into(TABLES.CATEGORIES_POSTS).returning('*');
};
And what kind of developers would we be if we didn't have any tests? We'll create test/models_spec.js to make sure that we're doing what we hope. Make sure you have mocha and chai installed as dev dependencies!
// test/models_spec.js
const { assert } = require('chai');
const { db, TABLES } = require('../db');
const posts = require('../models/posts');
const categories = require('../models/categories');
const categoryTitle = 'new category';
const postTitle = 'new post';
describe('db models', () => {
afterEach(() => {
return db(TABLES.CATEGORIES_POSTS).del()
.then(() => db(TABLES.CATEGORIES).del())
.then(() => db(TABLES.POSTS).del());
});
it('creates a new post', () => {
return posts.createPost(postTitle)
.then(([result]) => {
assert.equal(result.title, postTitle);
return db.select('*').from(TABLES.POSTS);
}).then(([result]) => {
assert.equal(result.title, postTitle);
});
});
it('creates a new category', () => {
return categories.createCategory(categoryTitle)
.then(([result]) => {
assert.equal(result.title, categoryTitle);
return db.select('*').from(TABLES.CATEGORIES);
}).then(([result]) => {
assert.equal(result.title, categoryTitle);
});
});
describe('posts and categories', () => {
let postId;
let categoryId;
beforeEach(() => {
return categories.createCategory(categoryTitle)
.then(([category]) => {
categoryId = category.id;
return posts.createPost(postTitle);
}).then(([post]) => postId = post.id);
});
it('attaches a post and a category', () => {
return posts.attachPostCategory(postId, categoryId)
.then(() => {
return db.select('*').from(TABLES.CATEGORIES_POSTS).where({
post_id: postId,
category_id: categoryId
});
}).then(result => assert.equal(result.length, 1));
});
});
});
Look Up Queries
Once we write out creation functions, we'll have to set up ways to find what we've saved. In our implementation, we're going to want to look up lists of categories on our database.
exports.getCategoriesByTitleList = titleList => {
return db.select('*').from(TABLES.CATEGORIES).whereIn('title', titleList);
}
We'll save the joining for the posts. Here, we're going to want to find a post by its id, populated with all of its associated categories. The knex query will look like this:
// models/posts.js
exports.getPostCategoriesById = id => {
return db.select(`${TABLES.POSTS}.*`, `${TABLES.CATEGORIES}.title as category_title`)
.from(TABLES.POSTS)
.leftJoin(TABLES.CATEGORIES_POSTS, `${TABLES.CATEGORIES_POSTS}.post_id`, `${TABLES.POSTS}.id`)
.leftJoin(TABLES.CATEGORIES, `${TABLES.CATEGORIES_POSTS}.category_id`, `${TABLES.CATEGORIES}.id`)
.where(`${TABLES.POSTS}.id`, id);
This is all well and good, but it doesn't give us a nice object format. It will return us an array with one item for each category which so we'll add in some data formatting here as well to make it useful. We want a single object representing the post with a categories property with an array of all associated categories.
// models/posts.js
// query post with categories
exports.getPostCategoriesById = id => {
return db.select(`${TABLES.POSTS}.*`, `${TABLES.CATEGORIES}.title as category_title`)
.from(TABLES.POSTS)
.leftJoin(TABLES.CATEGORIES_POSTS, `${TABLES.CATEGORIES_POSTS}.post_id`, `${TABLES.POSTS}.id`)
.leftJoin(TABLES.CATEGORIES, `${TABLES.CATEGORIES_POSTS}.category_id`, `${TABLES.CATEGORIES}.id`)
.where(`${TABLES.POSTS}.id`, id)
.then(results => {
return results.reduce((memo, categoryEntry) => {
if (!memo.title){
memo.title = categoryEntry.title;
}
if (!memo.categories) {
memo.categories = [];
}
memo.categories.push(categoryEntry.category_title);
return memo;
}, {});
});
};
And of course, just to make sure it works.
// test/models_spec.js
...
it('gets a post with all associated categories', () => {
return posts.attachPostCategory(postId, categoryId)
.then(() => posts.getPostCategoriesById(postId))
.then((result) => {
assert.equal(result.title, postTitle);
assert.deepEqual(result.categories, [categoryTitle]);
});
});
...
Controllers
So we have all these sweet model bags of functions, but a lot of good they do if we don't have something to use them. Controllers would handle most of the business logic of our server, but would never directly connect with the database. They would instead compose our model methods. For our example here, we'll mimic the use case where a user create a post along with a string list of all categories the post should be associated with. Our controller will handle this in a few steps.
- Find all categories in the list that already exist
- Create any categories in the list that don't already exist on our DB.
- Create a post
- Associate the post with all of the category entries on the db
- Return a formatted representation of our post populated with categories.
Fortunately, we have functions that deal with all of these steps. We just need to require them in, and compose them together.
// controllers/post_controller.js
const posts = require('../models/posts');
const categories = require('../models/categories');
exports.createPostWithCategories = (postTitle, categoryTitles) => {
let persistedCategories;
let persistedPost;
return categories.getCategoriesByTitleList(categoryTitles)
.then(existingCategories => {
persistedCategories = existingCategories;
const unpersistedCategories = categoryTitles.filter(category => persistedCategories.map(p => p.title).indexOf(category) === -1);
return Promise.all(unpersistedCategories.map(category => {
return categories.createCategory(category);
}));
}).then(([newCategories]) => {
persistedCategories = persistedCategories.concat(newCategories);
return posts.createPost(postTitle);
}).then(([newPost]) => {
persistedPost = newPost;
return Promise.all(persistedCategories.map(category => {
return posts.attachPostCategory(persistedPost.id, category.id);
}));
}).then(results => {
return posts.getPostCategoriesById(persistedPost.id);
});
};
And a test:
// test/post_controller_spec.js
const { assert } = require('chai');
const { db, TABLES } = require('../db');
const posts = require('../models/posts');
const categories = require('../models/categories');
const controller = require('../controllers/post_controller');
const categoryTitle = 'new category';
const postTitle = 'new post';
describe('db controller', () => {
let postId;
let categoryId;
beforeEach(() => {
return categories.createCategory(categoryTitle)
.then(([category]) => {
categoryId = category.id;
return posts.createPost(postTitle);
}).then(([post]) => postId = post.id);
});
afterEach(() => {
return db(TABLES.CATEGORIES_POSTS).del()
.then(() => db(TABLES.CATEGORIES).del())
.then(() => db(TABLES.POSTS).del());
});
it('creates a post associated with a list of categories', () => {
const newPostTitle = 'second post';
const newCategoryTitle = 'second category';
const categories = [categoryTitle, newCategoryTitle];
return controller.createPostWithCategories(newPostTitle, categories)
.then((result) => {
assert.equal(result.title, newPostTitle);
assert.deepEqual(result.categories, categories);
return db.select('*').from(TABLES.CATEGORIES).where('title', categoryTitle);
}).then(results => {
assert.equal(results.length, 1);
});
});
});
We were very satisfied with our decision to pull out Bookshelf and replace it with a bag of Knex functions. While we had to write out some functionality that Bookshelf provided out of the box, we had significantly more flexibility in how we access and formatted data. Our controllers were much cleaner as they were able to outsource all of the DB heavy lifting to external functions. In the end, the pattern proved intuitive, flexible and easily accessible by our developers and helped us create a maintainable and powerful backend.