Sequelize, the JavaScript ORM, in practice

node.js is well-know for its good connectivity with nosql databases. a less know fact is that it's also very efficient with relational databases. among the dozens orms out there in javascript, one stands out for relational databases: sequelize . it's quite easy to learn but there are not many pointers about how to organize model code with this module. here are a few tips we learned by using sequelize in a medium size project.

sequelize 101

sequelize claims to supports mysql, postgresql and sqlite. the sequelize docs explain the first steps with the javascript orm. first, initialize a database connection, then a few models, without worrying about primary or foreign keys:

var sequelize = new sequelize('database', 'username'[, 'password'])
var project = sequelize.define('project', {
  title:       sequelize.string,
  description: sequelize.text
});
var task = sequelize.define('task', {
  title:       sequelize.string,
  description: sequelize.text,
  deadline:    sequelize.date
});
project.hasmany(task);

next, create new instances and persist them, query the database, etc.

// create an instance
var task = task.build({title: 'very important task'})
task.title // ==> 'very important task'
// persist an instance
task.save()
  .error(function(err) {
    // error callback
  })
  .success(function() {
    // success callback
  });

// query persistence for instances
var tasks = task.all({ where: ['dealdine < ?', new date()] })
  .error(function(err) {
    // error callback
  })
  .success(function() {
    // success callback
  });

sequelize uses promises so you can chain error and success callbacks, and it all plays well with unit tests.

all that is pretty well documented, but the sequelize documentation only covers the basic usage. once you start using sequelize in real world projects, finding the right way to implement a feature gets trickier.

model file structure

all the examples in the sequelize documentation show all model declarations grouped in a single file. once a project reaches production size, this is not a viable approach. the alternative is to import models from a module using sequelize.import() .

the problem is that relationships rely on several models. when you declare a relationship, models from both sides of the relationship must already be imported. you should not import model files from other model files because of node.js module caching policy (more on that later on); instead, you can define relationships in a standalone file.

here is the file structure we've been working with:

models/
  index.js      # import all models and creates relationships
  phonenumber.js
  task.js
  user.js
  ...

and here is how the main models/index.js initializes the entire model:

var sequelize = require('sequelize');
var config    = require('config').database;  // we use node-config to handle environments

// initialize database connection
var sequelize = new sequelize(
  config.name,
  config.username,
  config.password,
  config.options
);

// load models
var models = [
  'phonenumber',
  'task',
  'user'
];
models.foreach(function(model) {
  module.exports[model] = sequelize.import(__dirname + '/' + model);
});

// describe relationships
(function(m) {
  m.phonenumber.belongsto(m.user);
  m.task.belongsto(m.user);
  m.user.hasmany(m.task);
  m.user.hasmany(m.phonenumber);
})(module.exports);

// export connection
module.exports.sequelize = sequelize;

using models in code

from other parts of the application, if you need a model class, require the models/index.js instead of the standalone model file. that way, you don't have to repeat the sequelize initialization.

var models = require('./models');
var user = models.user;

var user = user.build({ first_name: "john", last_name: "doe "});

the problem is, when you require the models/index.js file, node may use a cached version of the module... or not:

from nodejs.org :

multiple calls to require('foo') may not cause the module code to be executed multiple times. (...) modules are cached based on their resolved filename. since modules may resolve to a different filename based on the location of the calling module (loading from node_modules folders), it is not a guarantee that require('foo') will always return the exact same object, if it would resolve to different files.

that means that using require('./models') to get the models may create more than one connection to the database. to avoid that, the models variable must be somehow singleton-esque. this can be easily achieved, if you're using a framework like expressjs , by attaching the models module to the application:

app.set('models', require('./models'));

and when you need to require a class of the model in a controller, use this application setting rather than a direct import:

var user = app.get('models').user;

accessing other models

sequelize models can be extended with class and instance methods. you can add abilities to model classes, much like in a true activerecord implementation. but a problem arises when adding a method that depends on another model: how can a model access another one?

// in models/user.js
module.exports = function(sequelize, datatypes) {
  return sequelize.define('user', {
    first_name: datatypes.string,
    last_name: datatypes.string,
  }, {
    instancemethods: {
      counttasks: function() {
        // how to implement this method ?
      }
    }
  });
};

if the two models share a relationship, there is a way. here, one user has many tasks , that makes the task model accessible through user.associations['tasks'].target . and here is yet another problem: since sequelize doesn't use prototype-based inheritance, how can a user instance gain access to the user class? digging into the sequelize source brings the protected __factory to the light. with all this undocumented knowledge, it is now possible to write the counttasks() instance method:

counttasks: function() {
  return this.__factory.associations['tasks'].target.count({ where: { user_id: this.id } });
}

note that task.count() returns a promise, so counttasks() also returns a promise:

user.counttasks().success(function(nbtasks) {
  // do somethig with the user task count
});

extending models (a.k.a behaviors)

what if you need to reuse several methods across several models? sequelize doesn't have a behavior system per se (or "concerns" in the ruby on rails terminology), although it's quite easy to implement . for now, you're condemned to import common methods before the call to sequelize.define() and use sequelize.utils._.extend() to add it to the instancemethods or classmethods object:

// in models/friendlyurl.js
module.exports = function(keys) {
  return {
    geturl: function() {
      var ret = '';
      keys.foreach(function(key) {
        ret += this[key];
      })
      return ret
        .tolowercase()
        .replace(/^\s+|\s+$/g, "")    // trim whitespace
        .replace(/[_|\s]+/g, "-")
        .replace(/[^a-z0-9-]+/g, "")
        .replace(/[-]+/g, "-")
        .replace(/^-+|-+$/g, "");
    }
  };
}

// in models/user.js
var friendlyurlmethods = require('./friendlyurl')(['first_name', 'last_name']);
module.exports = function(sequelize, datatypes) {
  return sequelize.define('user', {
    first_name: datatypes.string,
    last_name: datatypes.string,
  }, {
    instancemethods: sequelize.utils._.extend({}, friendlyurlmethods, {
      counttasks: function() {
        return this.__factory.associations['tasks'].target.count({ where: { user_id: this.id } });
      }
    });
  })
};

now the user model instances gain access to a geturl() method:

var user = user.build({ first_name: 'john', last_name: 'doe' });
user.geturl(); // 'john_doe'

a limitation of this trick is that you must define behaviors before the actual model. this forbids behaviors accessing other models.

query series

sequelize provides a tool called the querychainer to ease the resynchronization of queries.

new sequelize.utils.querychainer()
  .add(user, 'find', [id])
  .add(task, 'findall')
  .error(function(err) { /* hmm not good :> */ })
  .success(function(results) {
    var user = results[0];
    var tasks = results[1];
    // do things with the results
  });

after using it a little, this utility turns out to be very limited. most notably, querychainer executes all queries in parallel by default. and you only get access to the results of the queries in the final callback - no way to pass values from one query to the other.

we've found it much more convenient to use a generic resynchronizing module like async , which provides the wonderful async.auto() utility. this method lets you list tasks together with dependencies, and determines which task can be run in parallel, and which must be run in series.

async.auto([
  user: function(next) {
    user.find(id).complete(next);
  },
  tasks: ['user', function(next) {
    tasks.findall({ where: { user_id: user.id } }}).complete(next);
  }]
], function(err, results) {
    var user = results.user;
    var tasks = results.tasks;
    // do things with the results
});

notice the complete() method, which is an alternative to the two success() and error() callbacks. complete() accepts a callback with the signature (err, res) , which is more natural in the node.js world, and compatible with async .

prefetching

one thing orms are usually good at is minimizing queries. sequelize offers a prefetching feature, allowing to group two queries in a single one using a join. for instance, if you want to retrieve a task together with the related user, write the query as follows:

task.find({ where: { id: id } }, include: ['user'])
  .error(function(err) {
    // error callback
  })
  .success(function(task) {
    task.getuser(); // does not trigger a new query
  });

this is another undocumented feature, although the documentation should be updated soon .

migrations

sequelize provides a migration command line utility. but because it only allows modifying the model using sequelize commands (and not calling any asynchronous method of your own ), this migration command falls short.

as of now, we've been handling migrations manually using numbered sql files and a custom utility to run them in order.

custom sql queries

sequelize is built over database adapters, and as such provides a way to execute arbitrary sql queries against the database. here is an example:

var util = require('util');
var query = 'select * from `task` ' +
            'left join `user` on `task`.`userid` = `user`.`id` ' +
            'where `user`.`last_name` = %s';
var escapedname = sequelize.constructor.utils.escape(last_name);
querywithparams = util.format(query, escapedname);
sequelize.query(querywithparams, task)
  .error(function(err) {
    // error callback
  })
  .success(function(tasks) {
    task.getuser(); // does not trigger a new query
  });

sequelize.query() returns a promise just like other query functions. if you provide the model to use for hydration ( task in this case), the query() method returns model instances rather than a simple json.

note that you must escape values by hand before concatenating them into the sql query. for strings, sequelize.constructor.utils.escape() is your friend. for integers, util.format('%d') should do the trick.

conclusion

is sequelize ready for prime time ? almost. the learning curve is made longer by an incomplete documentation, but most of the features required by a production-level orm are there. however, i wouldn't recommend it for production just yet if you're not ready to run on your own fork, since the rate at which prs are merged in the sequelize github repository is low.