Building a Node Js Application and deploying through Docker: Let’s talk ORM

At the end of Part 1, we find ourselves with a barebones node express server that doesn’t do much. Now, let’s get our hands dirty by adding some functionalities to our server and routes.

Hello, knex! Hello, bookshelf!

knex.js is a query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, and Oracle. Bookshelf is a JavaScript ORM for Node.js, built on top of the Knex SQL query builder. Both follow the traditional node async flow through callbacks and promises.

An obvious question that most people have when exploring knex and sequelize (the guy she tells you not to worry about, and which has been around longer), is why one over the other? There are more worthy people out there who can explain this in detail. My reasons for choosing knex over sequelize was the outdated documentation and bad response times on many open issues. A case in question was one issue I faced while following a tutorial and found it as an open issue on git for over 1 year with no response. In contrast, the knex community is fairly active and issues have a faster response. Again, Open Source. Don’t like something, try to change it.

I would also like to touch upon why not mongoDB and mongoose. The answer is that NoSQL, although very appealing to accommodate frequent schema changes, and hence promoting faster speed of development, isn’t very scalable. The intention of this series will be to build up this application to scale in the long run. And for that, MySQL offers a more robust backbone. (It’s debatable I know, hence “more” and not “most”).

Let’s get started. The first step would be to install knex and bookshelf (Duh!?)

npm install knex -g
npm install bookshelf --save

We’re choosing to install knex globally to take advantage of its CLI tool which will make our lives much easier in the future. In addition to that, you need to choose which database you’ll want to use Bookshelf with. For the purpose of this project, we’ll be using MySQL.

npm install mysql --save

We’ll be building a simple API about a database of books (You see that’s funny since our ORM is BOOKshelf. 😂 Yes, I crack jokes and laugh at them myself 😓). We’ll be having the following relations amongst our models:

• A Book belongs to a Author.
• An Author has many Books.
• A Book belongs to many Genres.
• A Genre belongs to many Books.

Migrations and Setup

Let’s get cracking.

knex init

This will generate knexfile.js in your application root. Your database configurations go here. We’ll be ignoring the production and staging configurations for the time being. The CLI will be defaulting to the development configs unless explicitly specified to use something else. Change your knexfile.js to look something like this:

module.exports = {
development: {
    client: 'mysql',
    connection: {
      database: 'mybooks',  // make sure you create this db
      user: 'root',         // can be something else
      password: '',         // your corresponding password
      host: 'localhost',
      port: '3306'          // Default port
    }
  }
};

Now, we’ll write the corresponding migrations for our Models.

knex migrate:make create_authors
knex migrate:make create_books
knex migrate:make create_genres
knex migrate:make create_books_genres

Your terminal at this point:

Edit the content of your migrations to create the columns that we need:

create_authors.js

//create_authors.js

exports.up = function(knex, Promise) {
  return knex.schema.createTable('authors', (table) => {
    table.increments('id').unsigned().primary();
    table.string('first_name').notNull();
    table.string('last_name');
  })
};
exports.down = function(knex, Promise) {
  return knex.schema.dropTable('authors');
};

create_books.js

//create_books.js

exports.up = function(knex, Promise) {
  return knex.schema.createTable('books', (table) => {
    table.increments('id').unsigned().primary();
    table.integer('author_id').unsigned().references('authors.id');
    table.string('title').notNull();
    table.integer('year');
  })
};
exports.down = function(knex, Promise) {
  return knex.schema.dropTable('books');
};

create_genres.js

//create_genres.js
exports.up = function(knex, Promise) {
  return knex.schema.createTable('genres', (table) => {
    table.increments('id').unsigned().primary();
    table.string('name').notNull();
  })
};
exports.down = function(knex, Promise) {
  return knex.schema.dropTable('genres');
};

create_books_genres.js

//create_books_genres.js

exports.up = function(knex, Promise) {
  return knex.schema.createTable('books_genres', function(table) {
    table.increments('id').unsigned().primary();
    table.integer('book_id').unsigned().references('books.id');
    table.integer('genre_id').unsigned().references('genres.id');
  });
};
exports.down = function(knex, Promise) {
  return knex.schema.dropTable('books_genres');
};

Your migrations are ready. To run them, do

knex migrate:latest

This should create the tables as expected. You can always log into mysql and check if you don’t trust me. I promise to not take offence 😜.

NOTE: The timestamps here are important. Knex runs migrations sequentially based upon these. So, it’s essential that you write your migrations in the correct order. The chances for foreign key errors are higher if you’re not careful. For example, if you had your create_book migration before create_authors the FK_Constraint for the author_id would fail since the table hasn’t been created yet.

Models and ORM

You awesome beast, you’ve created your tables and are ready to play with data.

Create a file bookshelf.js and have it look something like this:

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'mybooks',
    charset  : 'utf8'
  }
});
var bookshelf = require('bookshelf')(knex);
bookshelf.plugin('registry');
export default bookshelf;

A few things to talk about here.

Many people prefer to do var knex = require ('knex')(require ('./knexfile')[process.env.NODE_ENV]) over what we’ve done. Though it might seem like code repetition but there’s a reason behind our approach. We want to keep our CLI and migrations separate from our application operations. When we build an application to scale, we don’t want our application to have all permissions as opposed to your migration tool. Case in point, DELETE operations are generally avoided on production data. It’s a good idea to assign a different set of user credentials to your application with limited permissions to avoid such a case.

Next is the registry plugin. We use it to avoid circular referencing in case of our models. This plugin allows you to specify relations between models using a string instead of passing variables. You can read more about how it works on the wiki but for our purposes what you need to know is that it takes care of those circular dependencies.

Because of how Node import calls work, each time you require this file you’ll always get back the same instance of Bookshelf so you don’t need to worry about new connection pools being opened each time you reference it. The file itself is pretty simple. Just a reference to Knex, Bookshelf, then a line that exports the Bookshelf instance.

The following is an example of circular referencing, taken directly from the Bookshelf Wiki.

// file: a.js
var b = require('./b'); // => {}
module.exports = 'foo';
// file: b.js
var a = require('./a'); // => {}
module.exports = function() {
  return 'bar';
};
//file: c.js
var b = require('./b');
b(); // => TypeError, a is not a function

Lastly, ES5. Yeah it’s definitely ugly. To change to ES6 syntax, we’ll have to make a few changes. Firstly, edit your pm2_configs/config.json as following:

{
  "apps" : [{
    "name"        : "myAwesomeApp",
    "script"      : "./bin/www",
    "exec_mode"   : "fork",           // <-- Will explain later
    "exec_interpreter": "babel-node", // <-- This is new
    "instances" : 1,
    "merge_logs" :true,
    "env": {
      "PORT": 3000,
      "MONITORING": false
    },
  }]
}

The reason behind this is that node by default doesn’t understand the ES6 syntax. babel-node functions as a transpiler and the above config change is equivalent to running our app as babel-node ./bin/www.

Now, create your .babelrc file with the content:

{
  "presets": [
    "es2015"
  ]
}

Install the transpiler and the presets:

npm install -g babel-cli
npm install --save babel-preset-es2015

You should be good to go and be able to throw about es2016 syntax everywhere 😁

It’s about time we actually made our models. Start by creating a models folder. Within that we’ll have our models: Author.js, Book.js, and Genre.js.

Author.js

// Author.js
'use strict';
import Bookshelf from '../bookshelf';
import Book from './Book';
class Author extends Bookshelf.Model{
get tableName() {
    return 'authors';
  };
books() {
    return this.hasMany('Book');
  };
};
export default Bookshelf.model('Author', Author);

Book.js

// Book.js
'use strict';
import Bookshelf from '../bookshelf';
import Author from './Author';
import Genre from './Genre';
class Book extends Bookshelf.Model{
get tableName() {
    return 'books';
  };
author() {
    return this.belongsTo('Author');
  };
genres() {
    return this.belongsToMany('Genre')
  }
};
export default Bookshelf.model('Book', Book);

Genre.js

// Genre.js
'use strict';
import Bookshelf from '../bookshelf';
import Book from './Book';
class Genre extends Bookshelf.Model{
get tableName() {
    return 'genres';
  };
books() {
    return this.belongsToMany('Book')
  }
};
export default Bookshelf.model('Genre', Genre);

It’s just a matter of importing these models into our routes/controllers to use their abstraction over our DB.

Getting down to the CRUD

Import and add new routes to your app.js file:

var authors = require('./routes/authors');
var books = require('./routes/books');
app.use('/authors', authors);
app.use('/books', books);

Create the files authors.js and books.js in your routes folder with both looking something like this (If you’re in the habit of running your server while you develop like I am, having such placeholder files helps to stop the server logs from flipping over):

import express from 'express';
const router = express.Router();
router.get('/', function(req, res, next) {
  res.send('respond with a resource');
});
module.exports = router;

The basic CRUD operations are explained below using the authors.js route.

authors.js

import express from 'express';
import Author from '../models/Author'; // Import the Author Model
const router = express.Router();
//Get all Authors with the books that they wrote
router.get('/', (req, res, next) => {
  Author
  .fetchAll({withRelated: ['books']})
  .then((author)=>{
    res.json(author);
  })
});
//Get Author with specified ID
router.get('/:id',(req, res, next) => {
  Author
  .where({id : req.params.id})
  .fetch({withRelated: ['books']})
  .then((author)=>{
    res.json(author)
  })
})
//Create a New Author
router.post('/', (req, res, next) => {
  if(req.body.first_name){
    Author.forge({
      first_name : req.body.first_name,
      last_name : req.body.last_name || null
    })
    .save()
    .then((saved) => {
      res.json({saved})
    })
  }
  else{
    res.status(400).send('Missing Parameters')
  }
})
//Delete an Author with the Given ID
router.delete('/:id', (req, res, next) => {
  Author.forge({id : req.params.id})
  .fetch({require: true})
  .then((author) => {
    author.destroy()
    .then(()=>{
      res.json("Successfully deleted Author")
    })
  })
})
//Update the Author with the specified ID
router.patch('/:id', (req, res, next) => {
  Author
  .where({id : req.params.id})
  .fetch({withRelated: ["books"]})
  .then((author)=>{
    author.save({
      first_name : req.body.first_name || author.first_name,
      last_name : req.body.last_name || author.last_name
    }, {
      method: 'update',
      patch: true
    })
    .then((update)=>{
      res.json(update);
    })
  })
})
module.exports = router;

It’s in the books router, where we try to make the many-to-many relations with the genres, that the things get interesting. A naive approach which just involves the association (assuming that the genres already exist and we just need to do the many-to-many mapping), can also be done. Our approach assumes that the genres to a new book will be comma separated in our post body. The attachment of these will be done iteratively.

books.js

import express from 'express';
import Author from '../models/Author';
import Book from '../models/Book';
import Genre from '../models/Genre';
const router = express.Router();
// Get all books with associated authors and genres
router.get('/', (req, res, next) => {
  Book
  .fetchAll({withRelated: ['author','genres']})
  .then((books)=>{
    res.json(books);
  })
});
//Insert a book with the particular doctor and genres.
router.post('/', (req, res, next) => {
  let genres = req.body.genres;
  if(genres){
    genres = genres.split(',').map((genre)=>{
      return genre.trim();
    })
  }
  else{
    genres = ['undefined']
  }
  Book
  .forge({
    title : req.body.title,
    year : req.body.year || null,
    author_id : req.body.author_id
  })
  .save()
  .then((book)=>{
    genres.forEach((genre_name)=>{
      Genre
      .where({name : genre_name})
      .fetch()
      .then((genre)=>{
        if(genre){
          book.genres().attach(genre)
        }
        else{
          Genre
          .forge({
            name : genre_name
          })
          .save()
          .then((new_genre)=>{
            book.genres().attach(new_genre)
          })
        }
      })
    })
  })
  .then(()=>{
    res.json("Values Inserted")
  })
})
module.exports = router;

Our initial step in the post API involves parsing the post body and creating an array of the comma separated genres. We then create an entry in the db for the book with the given title. We then iteratively check if the specified genre exists in the db. If it does, we directly make the association else, we first make the entry for the genre and then make the association.

A thing to notice here is that Bookshelf is smart enough to make the entry in our join table through the simple attach method. A read of the Bookshelf documentation reveals how to explicitly control this mapping behaviour, but personally, I find this out of the box method extremely helpful.

I’ve also used a hacky then() chaining in the resolution of the insertion. The correct way would be to have the iteration loop as a promise and when we resolve it, we return the JSON. Give it a try on your own. It’s a good exercise on the control flow of JavaScript.

Soooooooooo, I think that takes care of basic DB manipulation that our combination of knex and node has to offer. In Part 3, we’ll be containerizing our application and the associated services so that it can be deployed to the interwebs 😎