A brief look at Sequelize, an ORM for Node.js with MySQL, PostgreSQL or SQLITE3

; Date: Mon Mar 25 2013

Tags: Node.JS »»»» MySQL »»»» Databases

Want to do some database code but not think too much about it?  Such as, avoid SQL?  You can have your SQL and a simplified model of your database thanks to a module I just found for Node.js called Sequelize.  It adds an ORM-like layer on top of MySQL, PostgreSQL or SQLITE3, allowing you to do database interactions using JavaScript code rather than SQL.  It's fairly nice and easy to use, however I think it's likely there are some limitations to the complexity of what you can do with Sequelize.

Installation:  npm install sequelize

Basic usage:

var Sequelize = require("sequelize");
var sequelize = new Sequelize('databaseName', 'username', 'password', {
    host: "my.server.tld",
    dialect: 'mysql'
});

This creates a connection to a MySQL database on the named host.  The parameters object is used to tailor what kind of database to connect with, as well as a large ream of options.  Sequelize was originally written for MySQL and the PostgreSQL and SQLITE3 support are more recent.

Next, you create a table definition this way:-

User = sequelize.define('User', {
    id: Sequelize.INTEGER,
    username: Sequelize.STRING,
    password: Sequelize.STRING,
    email: Sequelize.STRING
});

User.sync().success(function() {
    .. success code
}).error(function(err) {
    ... error code
});

Under the covers this causes SQL to be generated and executed, and with the right options it gets printed on the console.  The User.sync() call is what forces this SQL to execute.

CREATE TABLE IF NOT EXISTS `Users` (
        `id` INTEGER NOT NULL auto_increment ,
        `username` VARCHAR(255),
        `password` VARCHAR(255),
        `email` VARCHAR(255),
        `createdAt` DATETIME NOT NULL,
        `updatedAt` DATETIME NOT NULL,
        PRIMARY KEY (`id`)
) ENGINE=InnoDB;

The createdAt and updatedAt columns are generated by Sequelize for bookeeping.

Writing a record to the database is trivial and straight-forward:-

User.create({
    id: id,
    username: username,
    password: password,
    email: email
}).success(function(user) {
    callback();
}).error(function(err) {
    callback(err);
});

Likewise, to find a record:-

User.find({ where: { id: id } }).success(function(user) {
    if (!user) {
        callback('User ' + id + ' does not exist');
    } else {
        callback(null, {
            id: user.id, username: user.username, password: user.password, email: user.email
        });
    }
});

The update and delete functions are likewise as simple and straight-forward.

The table definitions can optionally attach any of a long list of validation parameters to each column definition.  This includes the obvious validations like "is it a number" but includes things like "IP Address" or "Credit Card Number."  The validations will help to ensure the data is consistent.

It does support something like joins to connect entries in one table to entries in another table.  These are called Associations and Sequelze supports One-to-One, One-to-Many and Many-to-Many associations.  Under the covers it adds columns to each table as appropriate, and methods in the classes.

var User = sequelize.define('User', {/* ... */})

var Project = sequelize.define('Project', {/* ... */})

// One-way associations
Project.hasOne(User)

This module can do a lot more - see  http://www.sequelizejs.com/#home for more info